Re: Automatically Updatable Foreign Key Views

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@sss.pgh.pa.us> wrote:
> 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



--
Ray Brinzer