Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
I'll make the change ASAP. Now in CVS along with PG 7.0 compat. code. 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 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Philip Warner wrote: At 08:42 19/04/01 -0500, Jan Wieck wrote: [...] 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... Add a "FROM opposite-relname" after the "ON relname" to the CREATE CONSTRAINT TRIGGER statements. That's it. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
At 11:29 20/04/01 -0500, Jan Wieck wrote: Philip Warner wrote: At 08:42 19/04/01 -0500, Jan Wieck wrote: 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... Add a "FROM opposite-relname" after the "ON relname" to the CREATE CONSTRAINT TRIGGER statements. That's it. I'll make the change ASAP. 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 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
Jan Wieck [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Thu, 19 Apr 2001, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
On Thu, 19 Apr 2001, Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] 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 FROMpg_trigger t JOINpg_class c ON (t.tgrelid = c.oid) JOINpg_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 selecttrigoid, 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 settgconstrrelid=rec_del.tgrelid where oid=rec_ins.trigoid; update pg_trigger settgconstrrelid=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 [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
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 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
