I've got a problem which I think may be a bug in Postgres, but I wonder if
I'm missing something. Two tables, A & B have foreign key relations to each
other. A 3rd table C, inherits from A. A stored procedure updates a row in
C, adds a row each in B & C. I get an integrity violation. All the foreign
keys are deferrable, and the stored procedure is called from within a
transaction with constraints deferred. (And the foreign keys do refer to
rows that exist.)
The cycle is handled in the DDL by creating A without the foreign keys,
creating B with the foreign keys referencing A, then using ALTER TABLE to
add the foreign key constraints onto A after A, B & C have all been created.
But if I create B with foreign keys referencing C instead of A, I don't get
the integrity violation. It's as though while evaluating the constraints at
the end of the transaction, it can find the C row I just inserted, but
cannot find the A row which is the C row's "base class".
Even stranger is that if I start with that theory and try to construct a
simple test case, I cannot reproduce it. But I can start with my much more
complex code and strip it down to a fairly bare example, and the problem
does reproduce--including that if I create B to refer to C instead of A, the
error goes away.
This is all in 7.3.4, on OS X 10.2.6. The sample gives the error message
"ERROR: $1 referential integrity violation - key referenced from
PatientReassignment not found in PatientRelated". If the definition of
PatientReassignment is changed so that the foreign keys reference
PatientCall instead of PatientRelated, the error goes away. Here's the
sample:
------------ sample 1
create table "PatientRelated" (
"id" int8 primary key,
"ReassignmentId" int8 null
) without oids;
create table "PatientCall" (
constraint "pk" primary key ("id")
) inherits ("PatientRelated") without oids;
create table "PatientReassignment" (
"id" int8 primary key,
"OrigId" int8 references "PatientRelated" deferrable not null,
"NewId" int8 references "PatientRelated" deferrable not null
) without oids;
alter table "PatientRelated" add constraint
"fk1" foreign key ("ReassignmentId") references "PatientReassignment"
deferrable;
create function "PatientCall_reassign" () returns int8 as '
begin
update "PatientCall" set "ReassignmentId" = 3 where id = 1;
insert into "PatientCall" values (2, NULL);
insert into "PatientReassignment" values (3, 1, 2);
return 2;
end;
' language 'plpgsql';
insert into "PatientCall" values (1, NULL);
begin; set constraints all deferred;
select "PatientCall_reassign" ();
commit;
------------
Yet this sample does *not* give an integrity violation, even though it's
exactly the same structure (unless I made a mistage) with different names:
------------ sample 2
create table base (
id int8 primary key,
transferid int8 null
) without oids;
create table owned (
constraint "pk" primary key (id)
) inherits (base) without oids;
create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;
alter table base add constraint
"fk1" foreign key (transferid) references transfer deferrable;
create function test() returns int8 as '
begin
update owned set transferid = 3 where id = 1;
insert into owned values (2, NULL);
insert into transfer values (3, 1, 2);
return 0;
end;
' language 'plpgsql';
insert into owned values (1, NULL);
begin; set constraints all deferred; select test(); commit;
------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
