On Tue, Jun 17, 2008 at 8:34 AM, Michael Shulman <shulman@xxxxxxxxxxxx> 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
