Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore - Mailing list pgsql-hackers

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