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
