Re: RI triggers and schemas
От | Stephan Szabo |
---|---|
Тема | Re: RI triggers and schemas |
Дата | |
Msg-id | 20020327100947.C38816-100000@megazone23.bigpanda.com обсуждение исходный текст |
Ответ на | Re: RI triggers and schemas (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
On Wed, 27 Mar 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > The advantage that I see is that we get more control over the time > > qualifications used for tuples which may come into play for match > > partial. I'm not sure that it's worth the effort to try doing it > > this way, but I figured I'd try it. > > It might be better to address that directly, eg: > > - define another SnapShot value that has the semantics you want > > - add a field to Scan plan nodes to specify explicitly the snapshot > you want used. Presumably by default the planner would fill this > with the standard QuerySnapshot, but you could > > - find a way to override the default (if nothing else, walk the > completed plan tree and tweak the snapshot settings). > > I believe it's already true that scan plan nodes lock down the target > snapshot during plan node startup, by copying QuerySnapshot into node > local execution state. So maybe you don't even need the above hack; > perhaps just twiddling QuerySnapshot right before ExecutorStart would > get the job done. > > It might be useful to discuss exactly what is bad or broken about the > current RI implementation, so we can get a clearer idea of what ought > to be done. I know that y'all are dissatisfied with it but I'm not > sure I fully understand the issues. Well, let's see, the big things in the current functionality are: For update locking is much stronger than we actually need to guarantee the constraint. There are some cases that the current constraints may get wrong. We haven't come to an agreement on some of these cases, but... On the insert/update fk check, we should not check rows that aren't valid since the intermediate states don't need to be valid. In fact this is already patched, but it opens upanother possible failure case below, so I'm mentioning it. On the noaction pk checks, if other rows have been added such that there are no failures of the constraint there shouldn'tbe an error. That was the NOT EXISTS addition to the constraint that was objected to in a previous patch. Formatch full this could be a simple check for an equal row, but for match partial it seems alot more complicated sinceeach fk row may have multiple matching rows in the pk table and those rows may be different for each fk row. On the referential actions, we need to agree on the behavior of the cases. If you do something like (with a deferred ondelete cascade) begin; delete from pk; insert into fk; end; is it supposed to be a failure? On 7.2 it would be. Currentlyit wouldn't be because it sees the inserted row as being invalid by the time it checks. I think it should be,but the old check may not have been the right place depending on the answers to the below: If we did instead: begin; delete from pk; insert into fk; insert into pk; end; is there a row in fk at the end or not? If we did: begin; insert into fk; delete from pk; insert into fk; insert into pk; end; do we end up with zero, oneor two rows in fk? Some things that would be good to add:Making the foreign key stuff work with inheritance. Adding match partial. This gets complicated with the referential actions particularly update cascade. My reading of thematch partial update cascade says that if a row gets updated due to having all of its matching rows being updated by thesame statement that all of the rows that matched this row were updated to non-distinct values for the columns of the fkrow that were not NULL.
В списке pgsql-hackers по дате отправления: