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
[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
[GENERAL] Slow DROPing Table
It would seem that my postgresql database system is getting slower and slower at dropping tables and possibly other tasks too. Am I being paranoid? I don't think so. Can anyone think of anything that would cause dropping a table with little data contained within it to take several seconds (and growing)? I have vacuumed the database. Here is a list of guessed on the factors which may be involved: - there are several triggers on each table. - the table does have a referential constraint ON DELETE CASCADE - upgraded to 7.1 but not to 7.1.2 - there is very little data in any of the tables - the project is still under development and isn't getting any traffic - we use scripts which create tables, we CREATE and DROP a lot of tables Thanks for any feedback. Andy Koch. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Can not pg_dumpall
At 01:30 PM 6/8/2001 -0700, you wrote: > > At 02:18 PM 6/8/2001 -0400, Tom Lane wrote: > > >Somazx Interesting <[EMAIL PROTECTED]> writes: > > > > getTables(): SELECT (funcname) for trigger sa_eval_trigger_608 > returned 0 > > > > tuples. Expected 1. > > > > pg_dump failed on OnEd, exiting > >This is easy to fix, actually. You need to change reltriggers on the >pg_class row for lessontable_548_118 to match the number of triggers that >are really there (7). Thanks. You've bailed me out once more. I was able to backup the data now - and I can relax a little. I was hoping that the problems preventing me from dropping those tables and doing a pg_dump would resolve the slowness in the system in general (especially dropping tables) - and it did!! Does that make any sense though? Andy. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Can not pg_dumpall
At 02:18 PM 6/8/2001 -0400, Tom Lane wrote: >Somazx Interesting <[EMAIL PROTECTED]> writes: > > getTables(): SELECT (funcname) for trigger sa_eval_trigger_608 returned 0 > > tuples. Expected 1. > > pg_dump failed on OnEd, exiting > >Kinda looks like you dropped some function that was still referenced by >a trigger. Better drop the trigger too. Taking that to mean, "try deleting that orphaned trigger" - but since I don't know which table that trigger was on - I did it by deleting it through pg_trigger, I now get somewhat worse errors: getTables(): relation 'lessontable_548_118': 8 Triggers were expected, but got 7 All the tables that use triggers and functions are expendable as they were only tests - so I tried deleting them and I can't and get another error: PostgreSQL said: ERROR: RelationBuildTriggers: 1 record(s) not found for rel lessontable_548_118 Your query: I think I've made quite the mess of things :) I only care about the data in a few tables, which if I could pg_dump I would just rebuild the database from the sql files and import the little data that's valuable - (all of which doesn't use triggers or functions) - but I can't even dump the data from non trigger/function using tables. It gives this error again: getTables(): relation 'lessontable_548_118': 8 Triggers were expected, but got 7. Andy "knows just enough to be dangerous" Koch. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Can not pg_dumpall
Can anyone help me with this error message I get when I attempt to dump my database? [dfunct@localhost dfunct]$ pg_dumpall > /home/dfunct/OnEd_backups/OnEd_Wednesday.sql getTables(): SELECT (funcname) for trigger sa_eval_trigger_608 returned 0 tuples. Expected 1. pg_dump failed on OnEd, exiting Andy Koch. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Slow droping tables
It would seem that my postgresql database system is getting slower and slower at dropping tables and possibly other tasks too. Am I being paranoid? I don't think so. Can anyone think of anything that would cause dropping a table with little data contained within it to take several seconds (and growing)? I have vacuumed the database. Here is a list of guessed on the factors which may be involved: - there are several short/simpler triggers calling tcl functions on each table. - upgraded to 7.1 but not to 7.1.2 - there is very little data in any of the tables - the project is still under development and isn't getting any traffic - we use scripts which create tables and while testing we've created and dropped a lot of tables - dropping a small table with little or nothing in it results in seconds of hard drive churning. Thanks for any feedback. Andy Koch. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
