Cyclic foreign key dependency & table inheritance

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

re: Cyclic foreign key dependency & table inheritance

Hmm. I read my examples over and over, but as soon as it was emailed back to
me I noticed a mistake ;-)

In the second example, I had foreign key references to the derived table,
like so:

create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;

And that working correctly is consistent with the first example. Changing it
to:

create table transfer (
id int8 primary key,
origownedid int8 references base deferrable not null,
newownedid int8 references base deferrable not null
) without oids;

Gives the constraint violation error, also consistent with the first
example.

So the behavior is consistent and easily explainable. Now my question is
reduced to: is this really correct behavior? Should a foreign key constraint
referencing a base table really not be satisfied by a row of a table that
inherits from the referenced base table??? That seems wrong to me.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: Cyclic foreign key dependency & table inheritance

Hmm. I read my examples over and over, but as soon as it was emailed back to
me I noticed a mistake ;-)

In the second example, I had foreign key references to the derived table,
like so:

create table transfer (
id int8 primary key,
origownedid int8 references owned deferrable not null,
newownedid int8 references owned deferrable not null
) without oids;

And that working correctly is consistent with the first example. Changing it
to:

create table transfer (
id int8 primary key,
origownedid int8 references base deferrable not null,
newownedid int8 references base deferrable not null
) without oids;

Gives the constraint violation error, also consistent with the first
example.

So the behavior is consistent and easily explainable. Now my question is
reduced to: is this really correct behavior? Should a foreign key constraint
referencing a base table really not be satisfied by a row of a table that
inherits from the referenced base table??? That seems wrong to me.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

re: Cyclic foreign key dependency & table inheritance

Scott Ribe <scott_ribe@killerbytes.com> writes:[color=blue]
> 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.[/color]

C will not participate in the foreign key relationship. This is a
well-known shortcoming of the inheritance mechanism. It may get fixed
some day, but don't hold your breath ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

re: Cyclic foreign key dependency & table inheritance

Scott Ribe <scott_ribe@killerbytes.com> writes:[color=blue]
> 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.[/color]

C will not participate in the foreign key relationship. This is a
well-known shortcoming of the inheritance mechanism. It may get fixed
some day, but don't hold your breath ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org