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
