Thread
-
[BUG?] tgconstrrelid doesn't survive a dump/restore
Joel Burton <jburton@scw.org> — 2001-04-18T17:08:06Z
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 <jburton@scw.org> Director of Information Systems, Support Center of Washington -
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-18T18:38:58Z
Joel Burton <jburton@scw.org> writes: > tgconstrrelid (in pg_trigger) holds table references in a RI trigger. > The value in this field is not successfully recreated after a > dump/restore. Yes, this problem was noted a couple months ago. AFAIK it was not fixed for 7.1, but I concur that it should be fixed. regards, tom lane
-
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Joel Burton <jburton@scw.org> — 2001-04-18T20:25:16Z
On Wed, 18 Apr 2001, Tom Lane wrote: > Joel Burton <jburton@scw.org> writes: > > tgconstrrelid (in pg_trigger) holds table references in a RI trigger. > > The value in this field is not successfully recreated after a > > dump/restore. > > Yes, this problem was noted a couple months ago. AFAIK it was not fixed > for 7.1, but I concur that it should be fixed. Jan/Philip/Tom -- Do we know if the problem is in pg_dump, or is there no way to pass the tgconstrrelid value in the CREATE CONSTRAINT TRIGGER statement? (I've read the dev docs on RI, but I haven't seen anyplace that documents what the arguments for the call are exactly, and a muddled wading through the source didn't help much.) If there are no better suggestions for the before-the-real-fix fix, I could make RI_pre_dump() and RI_post_dump() functions that would stick this information into another table so that I won't lose that info. (Or, can I always rely on digging it out of the preserved fields in pg_trig?) Thanks! -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
-
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-18T20:30:53Z
Joel Burton <jburton@scw.org> writes: > Do we know if the problem is in pg_dump, or is there no way > to pass the tgconstrrelid value in the CREATE CONSTRAINT TRIGGER > statement? IIRC, pg_dump is just failing to transfer the value; it needs to emit an additional clause in the CREATE CONSTRAINT command to do so. regards, tom lane
-
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Philip Warner <pjw@rhyme.com.au> — 2001-04-19T02:29:15Z
At 16:30 18/04/01 -0400, Tom Lane wrote: > >IIRC, pg_dump is just failing to transfer the value; it needs to emit >an additional clause in the CREATE CONSTRAINT command to do so. > From memory, this is one of the non-standard SQL things that pg_dump still does (ie. definining the constraint using rule definitions). I'll see if I can find a way of constructing the FK constraint properly, but don't hold your breath. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ -
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Philip Warner <pjw@rhyme.com.au> — 2001-04-19T02:30:55Z
At 16:25 18/04/01 -0400, Joel Burton wrote: > >Do we know if the problem is in pg_dump, or is there no way >to pass the tgconstrrelid value in the CREATE CONSTRAINT TRIGGER >statement? > It's because pg_dump is not designed to dump these constraints *as* constraints. We just need to make pg_dump clever enough to do that. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ -
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Jan Wieck <janwieck@yahoo.com> — 2001-04-19T13:42:22Z
Philip Warner wrote: > At 16:25 18/04/01 -0400, Joel Burton wrote: > > > >Do we know if the problem is in pg_dump, or is there no way > >to pass the tgconstrrelid value in the CREATE CONSTRAINT TRIGGER > >statement? > > > > It's because pg_dump is not designed to dump these constraints *as* > constraints. We just need to make pg_dump clever enough to do that. IMHO there's nothing fundamentally wrong with having pg_dump dumping the constraints as special triggers, because they are implemented in PostgreSQL as triggers. And the required feature to correctly restore the tgconstrrelid is already in the backend, so pg_dump should make use of it (right now, after a dump/restore, a DROP of a table involved in referential integrity wouldn't correctly remove the triggers from the referencing/referenced opposite table(s)). The advantage of having pg_dump output these constraints as proper ALTER TABLE commands would only be readability and easier portability (from PG to another RDBMS). Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Philip Warner <pjw@rhyme.com.au> — 2001-04-19T14:32:21Z
At 08:42 19/04/01 -0500, Jan Wieck wrote: >> >> It's because pg_dump is not designed to dump these constraints *as* >> constraints. We just need to make pg_dump clever enough to do that. > > IMHO there's nothing fundamentally wrong with having pg_dump > dumping the constraints as special triggers, because they are > implemented in PostgreSQL as triggers. Not sure if it's fundamentally wrong, but ISTM that making pg_dump use the SQL standards whenever possible will make dump files portable across versions as well as other RDBMSs. It is also, as you say, more readable. > and the required > feature to correctly restore the tgconstrrelid is already in > the backend, so pg_dump should make use of it No problem there - just tell me how... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ -
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Tom Lane <tgl@sss.pgh.pa.us> — 2001-04-19T14:40:28Z
Jan Wieck <JanWieck@yahoo.com> writes: > IMHO there's nothing fundamentally wrong with having pg_dump > dumping the constraints as special triggers, because they are > implemented in PostgreSQL as triggers. ... > The advantage of having pg_dump output these constraints as > proper ALTER TABLE commands would only be readability and > easier portability (from PG to another RDBMS). More to the point, it would allow easier porting to future Postgres releases that might implement constraints differently. So I agree with Philip that it's important to have these constructs dumped symbolically wherever possible. However, if that's not likely to happen right away, I think a quick hack to restore tgconstrrelid in the context of the existing approach would be a good idea. regards, tom lane
-
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Joel Burton <jburton@scw.org> — 2001-04-19T17:10:03Z
On Thu, 19 Apr 2001, Tom Lane wrote: > Jan Wieck <JanWieck@yahoo.com> writes: > > IMHO there's nothing fundamentally wrong with having pg_dump > > dumping the constraints as special triggers, because they are > > implemented in PostgreSQL as triggers. ... > > The advantage of having pg_dump output these constraints as > > proper ALTER TABLE commands would only be readability and > > easier portability (from PG to another RDBMS). > > More to the point, it would allow easier porting to future Postgres > releases that might implement constraints differently. So I agree with > Philip that it's important to have these constructs dumped symbolically > wherever possible. > > However, if that's not likely to happen right away, I think a quick hack > to restore tgconstrrelid in the context of the existing approach would > be a good idea. Not having the right value was stopping me in a project, so I put together a rather fragile hack: First, a view that shows info about relationships: CREATE VIEW dev_ri_detech AS SELECT t.oid AS trigoid, c.relname AS trig_tbl, t.tgrelid, rtrunc(text(f.proname), 3) AS trigfunc, t.tgconstrname, c2.relname FROM pg_trigger t JOIN pg_class c ON (t.tgrelid = c.oid) JOIN pg_proc f ON (t.tgfoid = f.oid) LEFT JOIN pg_class c2 ON (t.tgconstrrelid = c2.oid) WHERE t.tgisconstraint; Then, the new part, a function that iterates over RI sets (grouped by name*). It stores the 'other' table in pgconstrrelid, knowing that the '_ins' action is for the child, and that '_del' and '_upd' are for the parent. * - It requires that your referential integrity constraints have unique names (not a bad idea anyway). eg: CREATE TABLE child (pid INT CONSTRAINT child__ref_pid REFERENCES parent) * - it completely relies on how RI is handled as of Pg7.1, including the exact names of the RI functions. After a dump/restore cycle, just select dev_ri_fix(); It does seem to work, but do try it on a backup copy of your database, please! create function dev_ri_fix() returns int as ' declare count_fixed int := 0; rec_ins record; rec_del record; upd_oid oid; begin for rec_ins in select trigoid, tgrelid, tgconstrname from dev_ri_detect where rtrunc(trigfunc,3)='ins' loop select trigoid, tgrelid into rec_del from dev_ri_detect where tgconstrname=rec_ins.tgconstrname and rtrunc(trigfunc,3)='del'; if not found then raise notice 'No Match: % %', rec_ins.tgconstrname, rec_ins.trigoid; else upd_oid := trigoid from dev_ri_detect where tgconstrname=rec_ins.tgconstrname and rtrunc(trigfunc,3)='upd'; update pg_trigger set tgconstrrelid=rec_del.tgrelid where oid=rec_ins.trigoid; update pg_trigger set tgconstrrelid=rec_ins.tgrelid where oid=rec_del.trigoid; update pg_trigger set tgconstrrelid=rec_ins.tgrelid where oid=upd_oid; count_fixed :=count_fixed + 1; end if; end loop; return count_fixed; end; ' language 'plpgsql'; (it's not terribly optimized--I normally work w/databases <=300 tables) Also helpful: sometimes, after dropping, rebuilding and tinkering with a schema, I find that I'm left w/half of my referential integrity: (the parent has upd/del rules, but the child has no ins, or vice versa). The following query helps find these: SELECT tgconstrname, comma(trigfunc) as funcs, count(*) as count FROM dev_ri_detect GROUP BY tgconstrname HAVING count(*) < 3; It also requires that you have named constraints. It uses a function, comma(), that just aggregates a resultset into a comma-separated list. This function (which I find generally useful) is in Roberto Mello's Cookbook, via techdocs.postgresql.org. Anyway, here's hoping that someone fixes the dumping problem (emitting as real constraints would be *much* nicer), but in the meantime, this stuff may be useful. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington -
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Joel Burton <jburton@scw.org> — 2001-04-19T17:12:42Z
On Thu, 19 Apr 2001, Tom Lane wrote: > Jan Wieck <JanWieck@yahoo.com> writes: > > IMHO there's nothing fundamentally wrong with having pg_dump > > dumping the constraints as special triggers, because they are > > implemented in PostgreSQL as triggers. ... > > The advantage of having pg_dump output these constraints as > > proper ALTER TABLE commands would only be readability and > > easier portability (from PG to another RDBMS). > > More to the point, it would allow easier porting to future Postgres > releases that might implement constraints differently. So I agree with > Philip that it's important to have these constructs dumped symbolically > wherever possible. > > However, if that's not likely to happen right away, I think a quick hack > to restore tgconstrrelid in the context of the existing approach would > be a good idea. A while ago, I wrote up a small tutorial example about using RI w/Postgres. There wasn't much response to a RFC, but it might be helpful for people trying to learn what's in pg_trigger. It includes a discussion about how to disable RI, change an action, etc. It's at http://www.ca.postgresql.org/mhonarc/pgsql-docs/archive/pgsql-docs.200012 -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington