Well, those are two high-quality answers, and I appreciate them. Not really the news I was hoping for, of course, though I suppose it's a small consolation that the problem is not in the model, but the implementation. That leaves the possibility open in principle, at least, though the technical details aren't promising. I need to think through what's been said; possibly I'll have a follow-up question or two later. At any rate, thank you both. On Thu, Sep 24, 2015 at 10:23 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > 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 -- Ray Brinzer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
