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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
