Re: orphaned RI constraints

At 01:30 PM 7/6/2001 -0700, you wrote:
>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.
>
>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.

I think that is exactly what happened. Tom mentioned 7.1 had that problem
and until yesterday the development server was still v7.1


>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";

Perfect. I'll do this, thanks for the help.

Andy.