From: | "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | Seb <spluque(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: inserting to a multi-table view |
Date: | 2008-09-29 04:43:49 |
Message-ID: | 396486430809282143q65e985a3ifd147d8d4c1265fa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew Pulis | 2008-09-29 04:43:52 | PostgreSQL Cache |
Previous Message | Klint Gore | 2008-09-29 03:17:15 | Re: NULL values seem to short-circuit my unique index |