David Rowley <david.rowley@xxxxxxxxxxxxxxx> writes: > On 24 September 2015 at 13:32, Raymond Brinzer <ray.brinzer@xxxxxxxxx> > wrote: >> Any thoughts on this would be welcome. > The problem is that an UPDATE/DELETE could take place which causes the > foreign key to be violated and you may try and perform an UPDATE to the > view before the foreign key is cascaded by the trigger at end of > statement/transaction. Remember that a statement could execute inside of a > volatile function being called by some outer query. Yeah. We discussed this awhile back in the context of a proposal to optimize query plans on the assumption that foreign-key constraints hold (which would allow joins to be removed in some cases). That proposal was to only apply the optimization if there were no unfired trigger events in the current transaction, which would imply that there were no unperformed foreign key checks. That's valid as far as it goes, and you could imagine narrowing the restriction even more by checking to see if there were specifically any FK triggers queued for the query's table(s). However the potential delay between planning and execution made it a real mess to be sure if the optimization is safe, so I kind of doubt that it'll ever be accepted. In this context, using a similar approach would mean that it would be state-dependent whether an update on a view was allowed at all, which seems way too messy IMO. Even worse, if one update was allowed then the next one would not be, because the update on the view's underlying table would have queued FK check trigger events. In fact, I think this means an auto update through the view couldn't be allowed to update more than one row, because the first row update might have invalidated the FK constraint thus breaking the assumption needed for the second update to be well-defined. That statement is independent of any particular implementation approach. There are probably ways around that, such as not allowing the FK-involved columns to be auto updatable, but it's really looking like a mess. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
