Our current interpretation of the difference between foreign keys with
ON UPDATE/DELETE NO ACTION and those with ON UPDATE/DELETE RESTRICT
is that they mean the same thing but RESTRICT checks are not deferrable.
It follows from this that the trigger code ought to be the same for
NO ACTION and RESTRICT cases, and so it occurred to me that we could
get rid of a few hundred lines in ri_triggers.c if we removed the
duplicated code.
Comparing the actual code in the different functions, though, there is
a difference: the NO ACTION triggers call ri_Check_Pk_Match to see if
another PK row has been inserted/modified to provide the same key values
that the trigger subject row no longer does. (ri_Check_Pk_Match also
makes some checks for NULL-key cases, but these are redundant with tests
made in the main trigger functions, so they ought to go away.) The
RESTRICT triggers do not do this. It's fairly easy to construct a case
where it makes a difference:
create temp table pp (f1 int primary key);
create temp table cc (f1 int references pp on update no action);
insert into pp values(12);
insert into pp values(11);
update pp set f1=f1+1; -- now we have 13 and 12
insert into cc values(13); -- ok
update pp set f1=f1+1; -- now we have 14 and 13, FK is still OK
update pp set f1=f1+1; -- would result in 15 and 14, so FK fails
If you change the foreign key to be ON UPDATE RESTRICT, the second
UPDATE fails because the RESTRICT trigger doesn't notice that another
PK row has been modified to provide the key required by the FK row.
I think that the argument for having the RESTRICT triggers behave
like this is that the SQL spec envisions the RESTRICT check occurring
immediately when the individual PK row is updated/deleted, and so there
would be no opportunity for another PK row to be updated into its place.
(Or, in plainer English, RESTRICT should mean "you can't modify this
row's keys at all if it has dependents".) Because we implement RESTRICT
through an AFTER trigger that can't run earlier than end-of-statement,
we can't exactly match the spec's semantics, but we can get fairly
close so long as you don't think about what would be seen by
e.g. user-written triggers executing during the statement.
I'm happy with continuing to have this behavioral difference between
the two sets of triggers, but wanted to throw it up for discussion:
does anyone think it'd be better to apply ri_Check_Pk_Match in the
RESTRICT triggers too?
regards, tom lane