Bug with child tables referencing parent table?

From: Stefan Schwarzer <stefan(dot)schwarzer(at)tu-clausthal(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug with child tables referencing parent table?
Date: 2002-10-31 18:33:56
Message-ID: Pine.SOL.4.44.0210311910250.4296-100000@idefix.rz.tu-clausthal.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

I think I've found a bug (see below). If you think it's not a bug, I
would be thankful for a workaround. I tried omitting the foreign key
constraint. That works but is unsatisfactory.

Please (also) reply to my email address. Thank you!

Your name : Stefan Schwarzer
Your email address : Stefan(dot)Schwarzer(at)tu-clausthal(dot)de

System Configuration
---------------------

Architecture (example: Intel Pentium) : AMD Athlon

Operating System (example: Linux 2.0.26 ELF) : FreeBSD 4.7-STABLE

PostgreSQL version (example: PostgreSQL-7.2.3): PostgreSQL-7.2.3

Compiler used (example: gcc 2.95.2) : gcc 2.95.4

Please enter a FULL description of your problem:
------------------------------------------------

1. Create a table 'test_parent' with a serial key 'id'

2. Create a child table 'test_child1' which inherits from
'test_parent'

3. Insert a row into 'test_child1' with id=1 (for example)

4. Create a child table 'test_child2' which also inherits from
'test_parent' and has a foreign key referencing 'test_parent(id)'

The resulting inheritance hierarchy is:

test_parent (id)
^ ^
| |
test_child1 (id) test_child2 (id, parent_id)

5. Insert a row into 'test_child2' which contains the value 1 (see
step 3) for the foreign key

6. Step 5 should succeed because id=1 is in fact in 'test_parent' but
fails with an error message:

ERROR: <unnamed> referential integrity violation - key referenced from
test_child2 not found in test_parent

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

In psql (with some reformatting for better readability):

svss=# CREATE TABLE test_parent (id SERIAL);
NOTICE: CREATE TABLE will create implicit sequence 'test_parent_id_seq' for
SERIAL column 'test_parent.id'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'test_parent_id_key'
for table 'test_parent'
CREATE

svss=# CREATE TABLE test_child1 (i INTEGER) INHERITS (test_parent);
CREATE

svss=# INSERT INTO test_child1 (id, i) VALUES (1, 2);
INSERT 31667553 1

svss=# SELECT * FROM test_child1;
id | i
----+---
1 | 2
(1 row)

svss=# CREATE TABLE test_child2
(
parent_id INTEGER NOT NULL,
FOREIGN KEY(parent_id) REFERENCES test_parent(id)
)
INHERITS (test_parent);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

svss=# INSERT INTO test_child2 (id, parent_id) VALUES (2, 1);
ERROR: <unnamed> referential integrity violation - key referenced from
test_child2 not found in test_parent

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Sorry, I don't know a fix.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Florian Weimer 2002-10-31 20:25:59 Re: Bug #807: Sequence currupted on recovery after kill -9
Previous Message Darcy Buskermolen 2002-10-31 17:03:52 INTERVAL problems with greater than 2^32 seconds