Thread: Automatically Updatable Foreign Key Views

Greetings.

I love PostgreSQL's support of automatically updatable views, limited
though it is.  I would like to point out what I believe is another
case where views can be updated, without ambiguity.  I'm going to call
this a "foreign key view".  For example, given a view V which joins a
table C with the tables it references with foreign keys, C could be
updated through V.

I'll start with a concrete example, for the sake of simplicity.

create table users (
  id serial primary key,
  name text unique not null
);

create table phone_numbers (
  id serial primary key,
  user_id integer not null references users,
  number text not null
);

CREATE VIEW show_phone_numbers AS SELECT u.name, p.number FROM
phone_numbers p JOIN users u ON p.user_id = u.id;

Here show_phone_numbers would be an updatable view.  A conservative
approach would allow entries from phone_numbers to be created,
updated, or deleted though show_phone_numbers, but leave the users
table alone.  It might follow these rules:

1) An insert into a foreign key view is allowed if the values for the
fields from the parent tables match existing records in those tables.
2) An update to a foreign key view is allowed if the new values are
only for fields within the child table.
3) A delete from a foreign key view removes records only from the child table.

Though allowing effects in the parent tables may also, with proper
consideration, be viable, I believe that even the limited, fairly
clear case of keeping effects in the child would be extremely useful
in making normalized schemata easier to work with.

To put the idea more generally, let me give myself a little syntax
(I'm not proposing new syntax; just trying to get the idea across).

Let:
foreign_key_join(x)

Mean:
SELECT * FROM (x NATURAL LEFT JOIN foreign_key_view(y1) NATURAL LEFT
JOIN foreign_key_join(y2)...)
/* Note the recursion */

Where (y1, y2, y3...) are the tables x references, and we assume that
the constrained and constraining fields in each table have the same
names (just for the sake of being able to express the idea in SQL).
Then:

CREATE VIEW v as foreign_key_join(x);

Would be updatable.  As, of course, would a more limited view joining
on some of the foreign keys, or joining to parents without joining to
their parents.

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.

--
Ray Brinzer

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

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