[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

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

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

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 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

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.

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

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

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. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Sat, 21 Apr 2001, Philip Warner wrote:

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.

Woo-hoo! Thanks.

I posted a plpgsql script yesterday that tries to restore the name if it's
already been lost to a dump/restore cycle.

It would be a more robust solution if, instead of relying on pgconstrname,
I could get into the trigger arguments. However, these does not seem to be
any way for me to do this from plpgsql, as the functions for manipulating
bytea fields aren't very useful for this, an I can't coerce bytea into
text or anything like that.

Can anyone offer help on this? If I can get into the real args, I'll fix
up the script so that it can be run once by the people w/o tgconstrrelid,
and then, once Philip's done his work, we'll never lose it again! :-)

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington

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 |/

Add a "FROM <opposite-relname>" after the "ON <relname>" to
the CREATE CONSTRAINT TRIGGER statements. That's it.

I'll make the change ASAP.

I'm about to do this - does anyone object to me adding the 7.0 backward
compatibility changes at the same time?

----------------------------------------------------------------
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 |/

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 |/