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 ROW EXECUTE 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 found in 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 <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
