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.
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.
Thoughts?
regards, tom lane
