============================================================================
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.Schwarzer@tu-clausthal.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.