Thread: CREATE CONSTRAINT TRIGGER compatibility issue

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

I wrote:
> 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 :-(.

It's worse than that: after studying the output from 7.2 some more,
I realize that converting to a constraint requires knowing the
referential action type for both updates and deletes, and that is only
available by looking at *both* of the triggers on the referenced table.
So it seems that conversion inside the backend requires a design like
this:

* Ignore RI constraint triggers on referencing relations.

* On seeing the first trigger on a referenced relation, save aside
the function OID and the trigger arguments list in TopMemoryContext
(in a list of pending incomplete old-style FKs).

* On seeing the second trigger on a referenced relation (which we
detect by matching the trigger arguments list to a previously saved
item), we have enough information to generate the constraint.  Then
discard the saved entry from the TopMemoryContext list.

We might be able to simplify this if we were willing to assume things
about the order in which different triggers appear in a dump, but that
seems an unwise assumption.  Note however that this will only work if
both CREATE CONSTRAINT TRIGGER commands are issued in the same backend
session.  That is not exactly a given, seeing that old pg_dump output
uses psql \c to switch users, but AFAICT it will actually be the case
for two triggers of the same table in all pg_dump versions where that
could happen.

This is even more Rube Goldberg-worthy than I thought originally :-(.
But it's definitely doable, and it seems like it will be important
for some nontrivial fraction of our users.  It also seems like a
good idea to forcibly convert remaining old-style foreign key triggers
into constraints, since someday we're likely to wish to make
incompatible changes in the way foreign keys are implemented.

Does anyone have any thoughts about whether these machinations should
be reported to the user?  The notion of silently ignoring a CREATE
CONSTRAINT TRIGGER command seems a bit scary, so I was considering
throwing NOTICEs, say:

CREATE CONSTRAINT TRIGGER ...
NOTICE: ignoring incomplete foreign-key trigger group for constraint "foo" on table "bar"
CREATE CONSTRAINT TRIGGER ...
NOTICE: ignoring incomplete foreign-key trigger group for constraint "foo" on table "bar"
CREATE CONSTRAINT TRIGGER ...
NOTICE: converting foreign-key trigger group into constraint "foo" on table "bar"

But on the other hand such NOTICEs might scare people to no purpose.
Comments?
        regards, tom lane