> On Sun, 4 Nov 2007, Tom Lane wrote:
>> Would it be possible for you to send me (off-list) all of the CREATE
>> CONSTRAINT TRIGGER commands appearing in the dump?
> [done]
Hmm, this is messier than I thought. What evidently has happened is
that at one time or another, one of the two tables involved in an FK
relationship has been dropped and re-created. If you'd had proper
FK constraints the constraints would have gone away cleanly, but with
these old trigger definitions there was no mechanism to make that
happen, and so the triggers on the other table remained in place.
In some cases it looks like the FK constraint was re-established
with a fresh ALTER TABLE ADD CONSTRAINT command, leading to redundant
sets of triggers on one side of the relationship, while in other cases
it wasn't, leading to a partially functional FK constraint :-(
This explains why your dump contains some invalid data: the constraint
wasn't being enforced against the FK table.
So the question is what to do when we see a situation like this.
As the code stands, it will try to re-create an FK constraint after
seeing the two triggers on the PK table, regardless of whether a
trigger is present on the FK table. That has a couple of disadvantages:
* it may try to create an FK constraint that was only partially enforced
before, leading to errors like we saw in Olivier's report.
* in the situation where the user had re-established the constraint,
we may create redundant FK constraints.
The only thing I can think to do differently is to insist on seeing
all three matching triggers before we create the FK constraint.
This could be programmed so that we make only one constraint not two
when there's redundant triggers in the input. The downside is that
we'd fail to translate a constraint that was only partially enforced
in the source database. Maybe that's the best thing; it's symmetric
with what will happen when we see only the trigger on the FK table
and none for the PK table. (We can't do anything else in that case,
for lack of sufficient information.)
It would be nice if we could throw warnings for incomplete trigger
sets, but I see no very helpful way to do that --- we'd only be
sure we could emit the warning upon client disconnect, and then
it's too late to be sure the user would see the warning.
Comments, better ideas?
regards, tom lane
