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 4 3998
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 sc********@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
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 sc********@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
Scott Ribe <sc********@killerbytes.com> writes: 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.
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 ma*******@postgresql.org
Scott Ribe <sc********@killerbytes.com> writes: 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.
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 ma*******@postgresql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Brian Sabolik |
last post by:
I'm not sure if I've broken any Object Oriented rules or not, but ...
I have projects in 2 different solutions that need to use each other's
methods. Therefore I may have an "update" method in...
|
by: Dennis Lerche |
last post by:
Hi
I have a problem regarding cyclic dependency, yeahh I know bad
design. But right at this moment I can't see how it should be
redesigned to avoid this. The problem is that I just can't get it...
|
by: Scott Ribe |
last post by:
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...
|
by: Brendan Jurd |
last post by:
Hi all,
I read on the manual page for Inheritance that:
"A limitation of the inheritance feature is that indexes (including
unique constraints) and foreign key constraints only apply to single...
|
by: D. Dante Lorenso |
last post by:
I'd like to run a clean up command on my tables to
eliminate rows that I'm no longer using in the database.
I want to do something like this:
DELETE FROM tablename
WHERE...
|
by: Alex Satrapa |
last post by:
There's some mention in the (old!) documentation that constraints such as foreign keys won't include data from inheriting tables, eg:
CREATE TABLE foo (
id SERIAL PRIMARY KEY
);
CREATE TABLE...
|
by: fc2004 |
last post by:
Hi,
Is there any tools that could report where cyclic header dependency
happens? this would be useful when working with a large project where
tens or hundreds of headers files may form complex...
|
by: sakis.panou |
last post by:
Hi all,
Can anyone explain to me why the copy constructor of the COuterClass is
getting called for this one? Let me start by saying I reckon this is
seriously bad way of implementing anything of...
|
by: pallav |
last post by:
I have to header files, circuit.h and latch.h that reference each
other and are causing a cyclic dependency.
latch.h file
#include "circuit.h"
typedef boost::shared_ptr<struct LatchLatchPtr;...
|
by: soup007 |
last post by:
Hi,
I am having some difficulties with cyclic dependency between two classes. Situation is something like following -
///A.h
#include "B.h"
class A {
{
int X;
public:
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |