Re: Automatically Updatable Foreign Key Views

On 24 September 2015 at 13:32, Raymond Brinzer <ray(dot)brinzer(at)gmail(dot)com>
wrote:

>
> Any thoughts on this would be welcome. This is something which I
> would personally find exceptionally valuable; if there are problems
> with the idea, I'd like to know. As well, if my description isn't
> clear enough I'd be happy to explain.
>
>
I think the problem with this is that you cannot be certain at any point in
time that a query such as:
SELECT u.name, p.number FROM phone_numbers p JOIN users u ON p.user_id =
u.id;
will never eliminate tuples that don't match the join condition, *even if
there is a foreign key defined on the join condition*.

The reason for this is that referenced tables are not updated immediately,
they're only updated by triggers at the end of the statement, or
transaction, depending if the foreign key is DEFERRED or not.

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.

If foreign keys were updated immediately, like indexes normally are, then
this wouldn't be an issue.

I've attached a file with 2 examples of when this can happen.

Regards

David Rowley

--
David Rowley http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Training & Services