Re: [GENERAL] 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 | |782372 | false| false > > >| 6 || \000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 > > > 782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true | > > >true | |782372 | false| false > > >| 6 || \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 > 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] 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 | |782372 | false| false >| 6 || \000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 > 782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true | >true | |782372 | false| false >| 6 || \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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] 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 | |782372 | false| false | 6 || \000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true | true | |782372 | false| false | 6 || \000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 you should be able to do DROP TRIGGER "RI_ConstraintTrigger_782384"; DROP TRIGGER "RI_ConstraintTrigger_782386"; ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] orphaned RI constraints
Somazx Interesting <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
