
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


Wed, 18 Apr 2001 13:08:06 -0400 (EDT)
Joel Burton <jburton@scw.org>
[BUG?] tgconstrrelid doesn't survive a dump/restore
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

Wed, 18 Apr 2001 14:38:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore 
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


Wed, 18 Apr 2001 16:25:16 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore 
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

Wed, 18 Apr 2001 16:30:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUG?] tgconstrrelid doesn't survive a dump/restore 
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   |/

Thu, 19 Apr 2001 12:29:15 +1000
Philip Warner <pjw@rhyme.com.au>
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   |/

Thu, 19 Apr 2001 12:30:55 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Re: [BUG?] tgconstrrelid doesn't survive a
  dump/restore
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


Thu, 19 Apr 2001 08:42:22 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Re: [BUG?] tgconstrrelid doesn't survive a  dump/restore
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   |/

Fri, 20 Apr 2001 00:32:21 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Re: [BUG?] tgconstrrelid doesn't survive a
  dump/restore
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

Thu, 19 Apr 2001 10:40:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore 
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


Thu, 19 Apr 2001 13:10:03 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
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


Thu, 19 Apr 2001 13:12:42 -0400 (EDT)
Joel Burton <jburton@scw.org>
Re: Re: [BUG?] tgconstrrelid doesn't survive a dump/restore