Thread: [BUG?] tgconstrrelid doesn't survive a dump/restore
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
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
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
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
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 |/
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 |/
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 theyare implemented in PostgreSQL as triggers. And the required feature to correctly restore the tgconstrrelidis 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/referencedopposite table(s)). The advantage of having pg_dump output these constraints as proper ALTER TABLE commands would only be readabilityand 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
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 |/
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
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
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