Okay, yep, seems like a garden variety bug to me...
What's happening is that the update trigger is checking to make sure
that there are no rows referencing the one that was changed, but that's
not sufficient for the deferred no action case possibly. It's got to be
that there are no rows that now fail the constraint after the update (no
rows that reference the one that has changed and do not reference a row
that does exist in the table). There is some confusion on the spec to
some details that we're still working out.
This has gotten mentioned on -hackers, but noone's been completely able
to determine what's supposed to happen for all of the combinations of
referential actions on these types of deferred cases.
On Tue, 16 Jan 2001, Michael Richards wrote:
> Here is a test case that illustrates the problem. I figured I was
> doing it all wrong before and didn't bother to distill and include a
> test case.
>
> create table objects(
> revisionid int4,
> primary key (revisionid));
>
> create table objcatalog(
> minrev int4,
> maxrev int4,
> foreign key (minrev) references objects(revisionid) INITIALLY
> DEFERRED,
> foreign key (maxrev) references objects(revisionid) INITIALLY
> DEFERRED);
>
> insert into objects values (999);
> insert into objcatalog values (999,999);
>
> begin;
> SET CONSTRAINTS ALL DEFERRED;
> update objects set revisionid=1;
> insert into objects values (999);
>
> select * from objects;
> select * from objcatalog;
> commit;