Re: Reconstructing FKs in pg_dump

On Thu, 26 Sep 2002, Tom Lane wrote:

> Patrick Welche's recent problems (see pgsql-general) point out that the
> old CREATE CONSTRAINT TRIGGER syntax that optionally omits a "FROM
> table" clause doesn't work anymore --- the system *needs* tgconstrrelid
> to be set in an RI constraint trigger record, because the RI triggers
> now use that OID to find the referenced table. (The table name in the
> tgargs field isn't used anymore, mainly because it's not schema-aware.)
>
> This means that RI trigger definitions dating back to 7.0 (or whenever
> it was that we fixed the pg_dump bug about not dumping tgconstrrelid)
> don't work anymore.
>
> There are a couple things I think we should do. One: modify the CREATE
> CONSTRAINT TRIGGER code to try to extract a foreign relation name from
> the tgargs if FROM is missing. Without this, we have no hope of loading
> working FK trigger definitions from old dumps. Two: modify pg_dump to
> extract a name from the tgargs in the same fashion. I'd rather have
> pg_dump do this than the backend, and this will at least make things
> better in the case where you're using a 7.3 pg_dump against an older
> database.

I'd worry about doing things only to pg_dump since that'd still leave
people that did use the old dump in the dark and there'd be nothing even
indicating a problem until they did something that used the constraint.
Even a notice for a missing FROM would be better (although at that
point how far is it to just fixing the problem). I can look at it this
weekend (since it probably was my bug in the first place) unless you'd
rather do it.

> However, if we are going to put that kind of knowledge into pg_dump,
> it would only be a small further step to have it dump these triggers
> as ALTER TABLE ADD CONSTRAINT commands instead. Which would be a lot
> better for forward compatibility than dumping the raw triggers.

Wasn't there still some question about the fact that ATAC causes a
check of the constraint which for large tables is not insignificant.
I don't remember if there was any consensus on how to deal with that.