Re: orphaned RI constraints

On Mon, 9 Jul 2001, Jan Wieck wrote:

> Somazx Interesting wrote:
> > At 01:30 PM 7/6/2001 -0700, you wrote:
> > >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";
> >
> > Hi,
> >
> > The above doesn't work for me since DROP TRIGGER requires an ON <table
> > name> argument, and the table which the trigger is on has long since been
> > dropped.
> >
> > Is there something else I can try?
>
>     That's  hard  to believe, because tables that get dropped for
>     sure take all their triggers with them. What's the result of
>
>         SELECT relname FROM pg_class WHERE oid = 782359;
>
>     Should be there and be either "qqq"  or  "qqq2".  That's  the
>     table name these triggers are fired for.
>
>     What's   a   little  confusing  is  that  in  your  case  the
>     tgconstrrelid contains 782372 and not  NULL.  I  assume  from
>     that  that  this  is  not  from  the  database  you're having
>     problems with, right?

The example rows were from my db with some pulled out to make
it a bit more obvious how to get the trigger names.