Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore

On Thu, 19 Apr 2001, Tom Lane wrote:

> Jan Wieck <JanWieck(at)yahoo(dot)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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington