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?
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_groupor 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_groupor 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 quotedmixed
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.
3/20/2001 6:18:23 AM, Stephan Szabo <sszabo@megazone23.bigpanda.com> 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.
It might be even easier to just delete according to the oid.
select oid, * from pg_trigger;
delete from pg_trigger where oid = 49469 /* Numbers are easier */;
Brent
---
Brent R. Matzelle
Software Engineer
Information Services
Main Line Health Systems
Tel: 610-240-4566
Pager: 610-640-8437
matzelleb@mlhs.org
Import Notes
Resolved by subject fallback
Stephan Szabo
sszabo@bigpanda.com
On Wed, 21 Mar 2001, Brent R. Matzelle wrote:
3/20/2001 6:18:23 AM, Stephan Szabo <sszabo@megazone23.bigpanda.com> 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.It might be even easier to just delete according to the oid.
select oid, * from pg_trigger;
delete from pg_trigger where oid = 49469 /* Numbers are easier */;
Because he has two fk constraints on the table, plus, two of the
triggers are on the referenced table not the referencing table and
deleting from pg_trigger is unsafe unless you also set reltriggers in
pg_class, it really is generally better to use drop trigger. :)