Back when we agreed that we should treat CREATE CONSTRAINT TRIGGER as a
documented, non-deprecated user command, I made a change so that it
would use the specified name as the actual name of the trigger in
pg_trigger. Pre-8.3 releases would auto-generate sorta-unique names of
the form "RI_ConstraintTrigger_nnn", which (a) I didn't want to document,
(b) seemed entirely inappropriate for user-defined triggers that
aren't actually related to foreign keys, and (c) prevented users from
controlling the firing order of such triggers, which you normally
do by relying on the alphabetic sort of the trigger names.
What I failed to remember was that this would create a problem for
foreign keys that were originally made in 7.2 or before, if they never
got updated to standard foreign-key-constraint syntax (either manually
or through use of contrib/adddepend). We have an example here:
http://archives.postgresql.org/pgsql-hackers/2007-11/msg00064.php
Because a foreign key involves two separate triggers on the referenced
table, and both of them will be dumped with the same "name", such a
dump is guaranteed to fail to load into CVS HEAD.
What could/should we do about this? I see a few alternatives:
1. Revert the behavioral change. I don't want to do this, unless we
also go back to deprecating CREATE CONSTRAINT TRIGGER as a user command.
2. Document it as a known incompatibility and tell users to update any
such triggers into standard FK constraints (either by hand, or by
getting adddepend from pgfoundry). Doesn't seem very friendly,
especially seeing that we dropped adddepend from contrib because we
weren't entirely sure it still worked.
3. Try to auto-update from constraint triggers into real constraints
in pg_dump, more or less by importing the existing adddepend logic for
this into pg_dump. The main problem with this is that it wouldn't help
people who dumped using an older pg_dump.
4. Try to auto-update inside the backend. I don't have an exact
proposal for how this would work, but I'm thinking in terms of having
the conversion key off CREATE CONSTRAINT TRIGGER commands referencing
one of the built-in RI_FKey_xxx trigger functions. The tricky part here
is that we'd see three such commands, but we don't want three copies of
the FK. We could handle that by simply ignoring the two triggers on the
referenced table and generating the constraint when we see the one
trigger on the referencing table. It's pretty Rube Goldbergian :-(.
Also it would fail to reproduce the behavior of a DB in which one or
two of the three triggers are missing, though I'm dubious that we care
about that scenario.
As you can probably tell, I'm leaning to #4, but I wonder if anyone
has objections or better ideas.
regards, tom lane
