Thread: On-line interview tomorrow

On-line interview tomorrow

From
Bruce Momjian
Date:
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

finding and removing a constraint...

From
"chris markiewicz"
Date:
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


RE: finding and removing a constraint...

From
"chris markiewicz"
Date:
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


Re: finding and removing a constraint...

From
Stephan Szabo
Date:
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.)


Re: RE: finding and removing a constraint...

From
Stephan Szabo
Date:
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';


Re: RE: finding and removing a constraint...

From
Tom Lane
Date:
"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

Re: RE: finding and removing a constraint...

From
Frank Joerdens
Date:
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

Re: RE: finding and removing a constraint...

From
Stephan Szabo
Date:
> 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.


Re: RE: finding and removing a constraint...

From
Frank Joerdens
Date:
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

Re: RE: finding and removing a constraint...

From
Stephan Szabo
Date:
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.