Обсуждение: 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
On 24 September 2015 at 13:32, Raymond Brinzer <ray.brinzer@gmail.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:
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/
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Вложения
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