Re: RI triggers and schemas - Mailing list 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.