Re: orphaned RI constraints

On Fri, 6 Jul 2001, Somazx Interesting wrote:

>
> I'm working with two different postgres installations - they're both
> v7.1.2. On one I can drop a table and the related constraint info seems to
> go away with the table, on the other the restraint trigger seems to remain
> in the system tables and then when I try to delete rows from tables once
> related to the dropped table I get errors saying the dropped tabled doesn't
> exist - which I'm interpreting as the RI trigger trying to do its thing and
> failing.
>
> Questions:
>
> 1) Is this possible, or should I look for another explanation.
>
> 2) Can I fix things by dropping the constraint info from the system tables,
> if so how? Is there a function which cleans the system tables checking for
> problems like orphaned triggers, functions and sequences?

Is it possible that the one that's failing was restored from an old dump
output?  I believe there was a problem (I think resolved) where the
triggers lost track of the other table involved after a dump/restore which
could have this effect.

To fix it, you should be able to use DROP TRIGGER on the appropriate
triggers that were created (you can find these through a select on
pg_trigger, using the tgargs to find the appropriate ones).  As a warning,
you need to double quote the trigger name, so for example if you saw the
following rows for the constraint:

  782359 | RI_ConstraintTrigger_782384 |   1654 |      9 | true      |
true           | <unnamed>     |        782372 | false        | false
|       6 |        | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000
  782359 | RI_ConstraintTrigger_782386 |   1655 |     17 | true      |
true           | <unnamed>     |        782372 | false        | false
|       6 |        | <unnamed>\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000

you should be able to do
DROP TRIGGER "RI_ConstraintTrigger_782384";
DROP TRIGGER "RI_ConstraintTrigger_782386";