Skip site navigation (1) Skip section navigation (2)

Re: inserting to a multi-table view

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: (view raw, whole thread or download thread mbox)
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.

Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)

In response to


pgsql-general by date

Next:From: Matthew PulisDate: 2008-09-29 04:43:52
Subject: PostgreSQL Cache
Previous:From: Klint GoreDate: 2008-09-29 03:17:15
Subject: Re: NULL values seem to short-circuit my unique index

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group