Thread: On-line interview tomorrow
I am taking part in an on-line Q&A talk tomorrow about PostgreSQL. It will be at: http://searchdatabase.techtarget.com/Online_Events/searchDatabase_Online_Events_Page Here is the information: --------------------------------------------------------------------------- PostgreSQL in the Enterprise When: Mar 21, 2001 at 01:00 PM EST (18:00 GMT) Speaker: Bruce Momjian, Vice President, Database Development, Great Bridge, LLC Topic: PostgreSQL is one of the major open source database management systems vying for acceptance in the enterprise. This Live Expert Q&A will focus on PostgreSQL's current and future suitability for large-scale, mission-critical systems. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
hello i am trying to remove a foreign key that i placed on a database. what is the easiest way to find/delete the constraint? thanks chris
okay, i screwed up. i dropped some triggers from a table. now that table is not accessible to me. can't drop it...can't select...can't pg_dump. message is error: RelationBuildTriggers: 2 record(s) not found for rel accessor_group or a pg_dump results in: getTables(): relation 'accessor_group': 6 Triggers were expected, but got 4. is there any way that i can re-insert them? if i'm screwed, then how do i drop this table? i really appreciate your help. chris -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of chris markiewicz Sent: Tuesday, March 20, 2001 3:16 PM To: 'PostgreSQL-general' Subject: finding and removing a constraint... hello i am trying to remove a foreign key that i placed on a database. what is the easiest way to find/delete the constraint? thanks chris ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, 20 Mar 2001, chris markiewicz wrote: > hello > > i am trying to remove a foreign key that i placed on a database. what is > the easiest way to find/delete the constraint? The constraint will generate three triggers in pg_trigger. You can find the correct triggers by looking at the trigger arguments and then you should be able to drop those triggers (note: one of the triggers is on the referencing table, the other two are on the referenced table.)
On Tue, 20 Mar 2001, chris markiewicz wrote: > okay, i screwed up. i dropped some triggers from a table. now that table > is not accessible to me. > > can't drop it...can't select...can't pg_dump. > > message is error: RelationBuildTriggers: 2 record(s) not found for rel > accessor_group > > or a pg_dump results in: getTables(): relation 'accessor_group': 6 Triggers > were expected, but got 4. > > is there any way that i can re-insert them? > > if i'm screwed, then how do i drop this table? Okay... (you should really use drop trigger, not deleting from pg_trigger). You need to set reltriggers on the pg_class row for the table. Probably something like: update pg_class set reltriggers=4 where relname='accessor_group';
"chris markiewicz" <cmarkiew@commnav.com> writes:
> message is error: RelationBuildTriggers: 2 record(s) not found for rel
> accessor_group
Set reltriggers to 4 (instead of 6) in the pg_class entry for that
table.
regards, tom lane
On Tue, Mar 20, 2001 at 01:41:22PM -0800, Stephan Szabo wrote: > On Tue, 20 Mar 2001, chris markiewicz wrote: > > > okay, i screwed up. i dropped some triggers from a table. now that table > > is not accessible to me. > > > > can't drop it...can't select...can't pg_dump. > > > > message is error: RelationBuildTriggers: 2 record(s) not found for rel > > accessor_group > > > > or a pg_dump results in: getTables(): relation 'accessor_group': 6 Triggers > > were expected, but got 4. > > > > is there any way that i can re-insert them? > > > > if i'm screwed, then how do i drop this table? > > Okay... (you should really use drop trigger, not deleting from > pg_trigger). The problem is that the syntax create table SomeTable ( SomeColumn int4 references OtherTable ( SomeColumn ), . . . creates an <unnamed> trigger which you can't drop with drop trigger because it doesn't have a name. What you suggest does work for me, i.e. deleting from pg_trigger and then adjusting the trigger count in pg_class but it is quite cumbersome. The lesson here seems to be: Either have you schema so well thought through that you don't need to mess with triggers created in the above fashion once you have them in place, or make sure that all the FK references you create are named. Regards, Frank
> The problem is that the syntax > > create table SomeTable ( > SomeColumn int4 references OtherTable ( SomeColumn ), > . . . > > creates an <unnamed> trigger which you can't drop with drop trigger > because it doesn't have a name. What you suggest does work for me, i.e. > deleting from pg_trigger and then adjusting the trigger count in > pg_class but it is quite cumbersome. You should be able to, you just have to use the *real* trigger name (first column in pg_trigger, will look like RI_ConstraintTrigger_<oid> or something like that) and make sure to "" it because it's a quoted mixed case name.
On Tue, Mar 20, 2001 at 02:18:23PM -0800, Stephan Szabo wrote: > > > The problem is that the syntax > > > > create table SomeTable ( > > SomeColumn int4 references OtherTable ( SomeColumn ), > > . . . > > > > creates an <unnamed> trigger which you can't drop with drop trigger > > because it doesn't have a name. What you suggest does work for me, i.e. > > deleting from pg_trigger and then adjusting the trigger count in > > pg_class but it is quite cumbersome. > > You should be able to, you just have to use the *real* trigger name (first > column in pg_trigger, will look like RI_ConstraintTrigger_<oid> or > something like that) and make sure to "" it because it's a quoted mixed > case name. Oh. That must've been the problem; I didn't know you had to quote it, because I did try using that name. Cheers, Frank
On Wed, 21 Mar 2001, Frank Joerdens wrote: > On Tue, Mar 20, 2001 at 02:18:23PM -0800, Stephan Szabo wrote: > > > > > The problem is that the syntax > > > > > > create table SomeTable ( > > > SomeColumn int4 references OtherTable ( SomeColumn ), > > > . . . > > > > > > creates an <unnamed> trigger which you can't drop with drop trigger > > > because it doesn't have a name. What you suggest does work for me, > i.e. > > > deleting from pg_trigger and then adjusting the trigger count in > > > pg_class but it is quite cumbersome. > > > > You should be able to, you just have to use the *real* trigger name > (first > > column in pg_trigger, will look like RI_ConstraintTrigger_<oid> or > > something like that) and make sure to "" it because it's a quoted > mixed > > case name. > > Oh. That must've been the problem; I didn't know you had to quote it, > because > I did try using that name. Yeah, it confused me at first. If you don't quote it it'll try lowercasing the entire name on you and then it won't match.