Re: inserting to a multi-table view - Mailing list pgsql-general

On Tue, Jun 17, 2008 at 8:34 AM, Michael Shulman <shulman@mathcamp.org> wrote:

> Thank you very much for pointing this out!  I am somewhat disturbed by
> the example in that thread of a "partially executed update" resulting
> from the obvious way to write an update rule for a view.  I guess I
> need to be calling a function to do it instead, but this again seems
> somewhat kludgy.  Perhaps rules are not as wonderful as they seemed
> when I first encountered them.

Ya, there are a couple of things that I've learned while using rule.
1) Update-able views are only 100% safe when the view is based on a
single base table.
2) Update-able views based on joined tables require you to use
surrogate primary keys.  I.E. if your primary key were natural, and
there was a possibility that it could be changed, the resulting
updates would break.  Since on UPDATE CASCADE Referential Integrity
will cascade to primary key update before the rule is fired. (when the
rule is fired, it will still be using the old primary key before the
cascade occurred.

> This is also disturbing!  I'm not completely clear on what sort of
> overwriting can happen, though; could you give an example or a link?

Lets say you had a view based one the following select:

SELECT T1.att1, T1.att2, (...), T[n-1].att[n-1], T[n].att[n]
  FROM T1
  JOIN T2 ON T1.id = T2.id
  JOIN (...) ON T1.id = (...).id
  JOIN T[n-1] ON T1.id = T[n-1].id
  JOIN T[n] ON ON T1.id = T[n].id;

While your individual update rules are firing for each of your tables
from T1 thru T[n] to change your OLD row to NEW.  Another client could
also at the same time be updating any of the other tables before and
after your update Rules take affect.  The net result is that, some of
what you've changed could over write what the other client commited.
And some of what you've commited could be over written by what the
other client wrote.  The end result is that the view's virtual "row"
appears to be left in an inconsistant state.  This is a case where the
PostgreSQL inheritance has an advantage.  Since you are dealing with
an actual table record, MVCC unsures that only one of the changes will
be commited, not parts of both.

> Are there any plans to fix these problems?  In any case, it seems as
> though these sorts of caveats should appear in the documentation.



I think the reason that it isn't in the documentation is that the
problem is really a design problem and not really a PostgreSQL rule
problem.  As soon as you split a table in to two using a form of
vertical partitioning,  you've introduce the opportunity for update
anomalies to occur when dealing with concurrent database operations.
Since it is by design that the table is split, it is therefore up to
the designer to choose a method to ensure that consistant concurrent
updates are achieved.

Basically what you want to achieve is something like:

begin:
Select for update table T1 where id = old.id;
Select for update table T2 where id = old.id;
Select for update table (...) where id = old.id;
Select for update table T[n-1] where id = old.id;
Select for update table T[n] where id = old.id;
if all the needed row lock are aquired, then
 begin the updates
else rollback
commit;

I also recall a discussion for allowing trigger to be attached to
views.  However, IIRC, Tom Lane indicated that UPDATE triggers would
not be added to views since the possibility for update anomalies would
still exist.


--
Regards,
Richard Broersma Jr.

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