Re: should I worry? - Mailing list pgsql-hackers

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

I looked back in our CVS history and found that the above statement
is incorrect, or at least an oversimplification.  Ever since PG 7.0
(the first release with FOREIGN KEY support) there has been a mechanism
to auto-drop the FK triggers on the other side of the relationship.
Since 7.3 the pg_depend mechanism has handled it, but before that DROP
TABLE did a scan of pg_trigger for entries having tgconstrrelid pointing
to the doomed table.  So how come it broke?  Further study provided a
pathway that could cause this: 7.0 pg_dump failed to include
tgconstrrelid (the "FROM table" clause) in its CREATE CONSTRAINT TRIGGER
commands.  This was fixed in 7.1 and all later releases, but was never
back-patched to 7.0.x.  In 7.3 and later, the backend has a hack to
regenerate the missing tgconstrrelid value when loading an RI constraint
trigger definition, but 7.0-7.2 will just load the definition and set
tgconstrrelid = 0.

So the scenario must have gone like this:

1. Create some FOREIGN KEY constraints in 7.0.
2. Dump the database using 7.0's pg_dump.
3. Load into 7.0, 7.1, or 7.2.
4. Drop and recreate the table on one side of the FK relationship,  but don't re-create the FK constraint.
5. Continue to use the database up to the present day without ever  noticing that the FK constraint was only partially
enforced. (Dumps and reloads would not have changed its status.)
 

It's still not clear to me how perso managed to have only a DELETE
trigger and no UPDATE trigger for the FK reference from
perso_competences, but all the other inconsistencies seem to be
explainable by this mechanism.

Also, I was wondering why some of the trigger definitions had
names like "RI_ConstraintTrigger_28974011" rather than the underlying
constraint name, which is always "<unnamed>" in this set of triggers.
I now notice that these entries are also associated with duplicated
sets of triggers, which makes me think they are the result of manual
attempts at patching broken RI trigger sets.

I don't suppose there is enough history of schema changes in this DB
to confirm or deny these theories?

Anyway, the conclusion that the breakage must have gone undetected
since 7.2 or before makes me feel that maybe this isn't quite as
critical as I thought before.  There can't be a huge number of people
in such situations, and the FKs aren't working per spec for them
anyway.
        regards, tom lane