Re: inserting to a multi-table view

On Sun, Sep 28, 2008 at 5:31 PM, Seb <spluque@gmail.com> wrote:
> I've read this thread with great interest as I'm coming to PostgreSQL
> from the MS Access world of databases, where one can enter new data into
> queries/forms and tables get automatically updated/deleted/inserted into
> where expected.

Well,  I was able to get PostgreSQL Update-able views to work nearly
as well as the update-able queries did in Access.

In the case of update-able joined queries, I would expect that
MS-Access had the advantage since it was using Pessimistic locking on
native Access tables rather than the Optimistic locking that MS-Access
uses on all ODBC linked tables.  As a side note, you'll notice that
MS-Access will not allow update-able queries based on ODBC linked
table like it does on its native tables for this reason.


> I'm also leaning towards using natural keys where possible and was
> wondering how best to create multi-table views that can be
> updated/deleted/inserted into.

Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
update-able views.  Choose one or the other.

> Particularly, I'm
> curious to learn how PostgreSQL database maintainers handle data
> entry/modification requiring multi-table queries.  Thanks.

My opionion is that Multitable update-able views are not safe if you
plan to allow multiple users concurrent access to the view.  Because
of this I scrapped these kinds of views for multiple prepared
statements issued in a serializable level transaction.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug