Re: inserting to a multi-table view

On Sun, Sep 28, 2008 at 5:31 PM, Seb <spluque(at)gmail(dot)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