Thread: orphaned RI constraints
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? Thanks, Andy.
Somazx Interesting <somazx@home.com> writes:
> 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.
IIRC, pg_dump scripts made by 7.1 pg_dump did not dump the FROM part of
the trigger definition, so dropping the referenced table of an RI trigger
reloaded from such a dump didn't make the trigger go away.
This is fixed in 7.1.2 (not sure about 7.1.1).
regards, tom lane
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";
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.
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? Thanks, Andy.
On Mon, 9 Jul 2001, 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. Right (forgot the on table). The triggers *on* the table that was dropped should be gone, you should only be left with the triggers on the other table of the constraint, so use that table's name (not the table you dropped).
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?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
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.