Bug with child tables referencing parent table? - Mailing list 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.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.