Re: RI triggers and schemas

On Wed, 27 Mar 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)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 up another 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't be an error. That
was the NOT EXISTS addition to the constraint that was objected to
in a previous patch. For match full this could be a simple check for
an equal row, but for match partial it seems alot more complicated
since each 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 on delete cascade)
begin; delete from pk; insert into fk; end;
is it supposed to be a failure? On 7.2 it would be. Currently it
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, one or 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 the match partial update
cascade says that if a row gets updated due to having all of its
matching rows being updated by the same statement that all of the rows
that matched this row were updated to non-distinct values for the
columns of the fk row that were not NULL.