David Rowley <david.rowley@2ndquadrant.com> writes:
> On 24 September 2015 at 13:32, Raymond Brinzer <ray.brinzer@gmail.com>
> 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
