[BUG?] tgconstrrelid doesn't survive a dump/restore - Mailing list pgsql-hackers

tgconstrrelid (in pg_trigger) holds table references in a RI trigger.
The value in this field is not successfully recreated after a
dump/restore.

---

If I create a simple relationship:
  create table p (id int primary key);  create table c (pid int references p);

and query the system table for the RI triggers:
  select tgrelid, tgname, tgconstrrelid from pg_trigger     where tgisconstraint;

I get (as expected) the trigger information:
   tgrelid |           tgname           | tgconstrrelid  ---------+----------------------------+---------------
29122| RI_ConstraintTrigger_29135 |         29096     29096 | RI_ConstraintTrigger_29137 |         29122     29096 |
RI_ConstraintTrigger_29139|         29122  (3 rows)
 

However, if I dump this database:

[joel@olympus joel]$ pg_dump -sN test1 | grep -v - -- > test1

  CREATE TABLE "p" (          "id" integer NOT NULL,          Constraint "p_pkey" Primary Key ("id")  );

  CREATE TABLE "c" (          "id" integer NOT NULL  );

  CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON  "c"  NOT DEFERRABLE INITIALLY  IMMEDIATE FOR EACH
ROWEXECUTE PROCEDURE  "RI_FKey_check_ins" ('<unnamed>',  'c', 'p', 'UNSPECIFIED', 'id', 'id');
 

  CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "p"  NOT  DEFERRABLE INITIALLY IMMEDIATE  FOR EACH ROW EXECUTE
PROCEDURE"RI_FKey_noaction_del" ('<unnamed>',  'c', 'p', 'UNSPECIFIED', 'id', 'id');
 

  CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "p"  NOT  DEFERRABLE INITIALLY IMMEDIATE  FOR EACH ROW EXECUTE
PROCEDURE"RI_FKey_noaction_upd" ('<unnamed>',   'c', 'p', 'UNSPECIFIED', 'id', 'id');
 


If I drop the database and recreate from the dump:
  drop database test1;  create database test1 with template=template0;  \c test1  \i test1

and re-run the query on the pg_trigger table:
  select tgrelid, tgname, tgconstrrelid from pg_trigger     where tgisconstraint;

PG has lost the information on which table was being referred to
(tgconstrrelid):
   tgrelid |           tgname           | tgconstrrelid  ---------+----------------------------+---------------
29155| RI_ConstraintTrigger_29168 |             0     29142 | RI_ConstraintTrigger_29170 |             0     29142 |
RI_ConstraintTrigger_29172|             0  (3 rows)
 

Thee referential integrity still *works* though --
  test1=# insert into p values (1);  INSERT 29174 1
  test1=# insert into c values (1);  INSERT 29175 1
  test1=# insert into c values (2);  ERROR:  <unnamed> referential integrity violation - key referenced from  c not
foundin p
 
  test1=# update p set id=2;  ERROR:  <unnamed> referential integrity violation - key in p still  referenced from c
  test1=# delete from p;  ERROR:  <unnamed> referential integrity violation - key in p still   referenced from c

The problem is that I've use tools that examine tgconstrrelid to figure
reverse engineer which relationships exist.


Is this a bug? Am I misunderstanding a feature?

(This was run with 7.1RC4; it's possible that this bug doesn't exist in
the release 7.1. I haven't been able to get the CVS server to work for
about 48 hours, so I haven't been able to upgrade.)

Thanks!


-- 
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington