Thread: trigger/for key help
S*t s*t s*t. I have managed to screw up the system tables trying to
delete a foreign key on a new table I was going to start using tomorrow.
elevating-# \d diag_logs Table "diag_logs"Column | Type |
Modifiers
---------+------------------------+---------------------------------------------------------------sernum | integer
| not null default
nextval('public.diag_logs_sernum_seq'::text)display | integer | not nulltdate | date
|not nullttime | time without time zone | not nulltstatus | smallint | not nullttype | smallint
| not null
Indexes: diag_logs_display, diag_logs_tdate, diag_logs_tstatus
Primary key: diag_logs_pkey
There used to be to foreign key constraints named $1 and $2 (I cut and
pasted sql from a dump of another table that caused the trigger names
that I was trying to get rid of)
These were created with alter table like this :
elevating=# ALTER TABLE ONLY diag_logs ADD CONSTRAINT "$2" FOREIGN KEY
(ttype) REFERENCES test_types(num);
ALTER TABLE
based on a bunch of surfing I deleted the six rows in pg_tigger that
referred to
elevating=# delete from pg_trigger where tgargs like '%diag_logs%';
DELETE 6
elevating=# drop table diag_logs;
ERROR: 2 trigger record(s) not found for relation "diag_logs"
so digging around I found that there were still entries in pg_constraint
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs'); conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
----------------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------diag_logs_pkey
| 2200 | p | f | f
| 2041950 | 0 | 0 | |
| | {1} | | | $1 | 2200 | f | f | f
| 2041950 | 0 | 1027502 | a | a |
u | {2} | {1} | | $2 | 2200 | f | f | f
| 2041950 | 0 | 2041960 | a | a |
u | {6} | {1} | |
(3 rows)
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') and contype='f'; conname | connamespace | contype | condeferrable |
condeferred|
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
---------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------$1
| 2200 | f | f | f |
2041950 | 0 | 1027502 | a | a |
u | {2} | {1} | | $2 | 2200 | f | f | f |
2041950 | 0 | 2041960 | a | a |
u | {6} | {1} | |
(2 rows)
elevating=# delete from pg_constraint where conrelid in (select oid from
pg_class where relname = 'diag_logs') and contype='f';
DELETE 2
Still no joy now I seem to have deleted the pk constraint too.
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') ; conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
---------+--------------+---------+---------------+-------------+----------+----------+-----------+-------------+-------------+---------------+--------+---------+--------+--------
(0 rows)
I obviously don't understand the system tables well enough to be dinkin
around in here but here I am and wanting to fix it .
Any ideas, anyone?
Bret
On Sat, 11 Apr 2004, Bret Hughes wrote:
> S*t s*t s*t. I have managed to screw up the system tables trying to
> delete a foreign key on a new table I was going to start using tomorrow.
>
>
> elevating-# \d diag_logs
> Table "diag_logs"
> Column | Type |
> Modifiers
> ---------+------------------------+---------------------------------------------------------------
> sernum | integer | not null default
> nextval('public.diag_logs_sernum_seq'::text)
> display | integer | not null
> tdate | date | not null
> ttime | time without time zone | not null
> tstatus | smallint | not null
> ttype | smallint | not null
> Indexes: diag_logs_display,
> diag_logs_tdate,
> diag_logs_tstatus
> Primary key: diag_logs_pkey
>
> There used to be to foreign key constraints named $1 and $2 (I cut and
> pasted sql from a dump of another table that caused the trigger names
> that I was trying to get rid of)
>
> These were created with alter table like this :
>
> elevating=# ALTER TABLE ONLY diag_logs ADD CONSTRAINT "$2" FOREIGN KEY
> (ttype) REFERENCES test_types(num);
> ALTER TABLE
>
> based on a bunch of surfing I deleted the six rows in pg_tigger that
> referred to
>
> elevating=# delete from pg_trigger where tgargs like '%diag_logs%';
> DELETE 6
For future note, in recent versions (IIRC 7.3 and above), you should
probably use ALTER TABLE DROP CONSTRAINT, in versions older than that, you
should select the triggers and use DROP TRIGGER "<name>" so as to have the
system handle the next step for you.
> elevating=# drop table diag_logs;
> ERROR: 2 trigger record(s) not found for relation "diag_logs"
This is because reltriggers in the pg_class row for the table in question
is incorrect. You can fix this by updating the rows (*). You'll probably
want to fix pg_constraint as well, but I think that the table will
function properly at least until you try to dump it.
(*) something like this should workupdate pg_class set reltriggers=(select count(*) from pg_trigger where
tgrelid=pg_class.oid) where relname='<insert name here>';
On Sun, 2004-04-11 at 10:17, Stephan Szabo wrote:
>
> On Sat, 11 Apr 2004, Bret Hughes wrote:
>
> > S*t s*t s*t. I have managed to screw up the system tables trying to
> > delete a foreign key on a new table I was going to start using tomorrow.
> >
> >
>
> > elevating=# drop table diag_logs;
> > ERROR: 2 trigger record(s) not found for relation "diag_logs"
>
> This is because reltriggers in the pg_class row for the table in question
> is incorrect. You can fix this by updating the rows (*). You'll probably
> want to fix pg_constraint as well, but I think that the table will
> function properly at least until you try to dump it.
>
> (*) something like this should work
> update pg_class set reltriggers=(select count(*) from pg_trigger where
> tgrelid=pg_class.oid) where relname='<insert name here>';
>
ok I updated the rows by relname individually so I could limit issues
should I screw it up.
I can now get a dump of the db but still cannot drop the two tables I
added. I get a different message now :
elevating=# \d diag_logs; Table "diag_logs"Column | Type |
Modifiers
---------+------------------------+---------------------------------------------------------------sernum | integer
| not null default
nextval('public.diag_logs_sernum_seq'::text)display | integer | not nulltdate | date
|not nullttime | time without time zone | not nulltstatus | smallint | not nullttype | smallint
| not null
Indexes: diag_logs_display, diag_logs_tdate, diag_logs_tstatus
Primary key: diag_logs_pkey
elevating=# drop table diag_logs;
ERROR: could not find tuple for constraint 2041954
and
elevating=# \d test_types; Table "test_types" Column | Type | Modifiers
----------------+---------------+--------------------num | smallint | not nullprogname |
character(25)| not nullseverity_level | smallint | not null default 1
Primary key: test_types_pkey
elevating=# drop table test_types;
ERROR: could not find tuple for constraint 2041971
elevating=#
I can find no reference to these oids anywhere.
I wasgonig to restart postmaster incase something is cached but I hate
to since I don't know if it will restart properly.
TIA
Bret
Bret Hughes <bhughes@elevating.com> writes:
> elevating=# drop table test_types;
> ERROR: could not find tuple for constraint 2041971
> I can find no reference to these oids anywhere.
You haven't looked very hard then ;-). I expect this is because
pg_depend has links from the table to the constraints it owns.
You could probably remove the pg_depend entries to make it possible
to drop the table.
Use ALTER TABLE next time, eh? Hand manipulation of the system catalogs
is *not* for those who don't know exactly what they are doing.
regards, tom lane
On Sun, 2004-04-11 at 17:00, Tom Lane wrote: > Bret Hughes <bhughes@elevating.com> writes: > > elevating=# drop table test_types; > > ERROR: could not find tuple for constraint 2041971 > > > I can find no reference to these oids anywhere. > > You haven't looked very hard then ;-). I expect this is because > pg_depend has links from the table to the constraints it owns. > You could probably remove the pg_depend entries to make it possible > to drop the table. > > Use ALTER TABLE next time, eh? Hand manipulation of the system catalogs > is *not* for those who don't know exactly what they are doing. > > regards, tom lane Hmm I thought I had but pg_depend is one that I missed. I deleted all refs to rows in pg_depends and was able to drop the table. FWIW I tried to use alter table but could never get the parser to accept $1 as a constraint name. I used single and double quotes as well as a lame attempt \$1. Thanks I am breathing better. I was about to pg_dump and restore into a test database to see what would happen then since there was no reference to any of the oids in the error messages found in the dump. I can now resume climbing up the learning curve. Bret
Bret Hughes <bhughes@elevating.com> writes:
> FWIW I tried to use alter table but could never get the parser to accept
> $1 as a constraint name. I used single and double quotes as well as a
> lame attempt \$1.
Hm, "$1" works for me ...
regression=# create table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
regression=# create table bar (f1 int references foo);
CREATE TABLE
regression=# \d bar Table "public.bar"Column | Type | Modifiers
--------+---------+-----------f1 | integer |
Foreign-key constraints: "$1" FOREIGN KEY (f1) REFERENCES foo(f1)
regression=# alter table bar drop constraint "$1";
ALTER TABLE
regression=#
regards, tom lane
On Sun, 2004-04-11 at 21:27, Tom Lane wrote: > Bret Hughes <bhughes@elevating.com> writes: > > FWIW I tried to use alter table but could never get the parser to accept > > $1 as a constraint name. I used single and double quotes as well as a > > lame attempt \$1. > > Hm, "$1" works for me ... > > regression=# create table foo (f1 int primary key); > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" > CREATE TABLE > regression=# create table bar (f1 int references foo); > CREATE TABLE > regression=# \d bar > Table "public.bar" > Column | Type | Modifiers > --------+---------+----------- > f1 | integer | > Foreign-key constraints: > "$1" FOREIGN KEY (f1) REFERENCES foo(f1) > > regression=# alter table bar drop constraint "$1"; > ALTER TABLE > regression=# > > regards, tom lane Here is a recreation of what I did: elevating=# create table foo (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE elevating=# create table bar (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar" CREATE TABLE elevating=# ALTER TABLE ONLY foo ADD CONSTRAINT "$1" FOREIGN KEY (f1) REFERENCES bar; ALTER TABLE elevating=# \d foo Table "foo"Column | Type | Modifiers --------+---------+-----------f1 | integer | not null Primary key: foo_pkey Triggers: RI_ConstraintTrigger_2042118 elevating=# \d bar Table "bar"Column | Type | Modifiers --------+---------+-----------f1 | integer | not null Primary key: bar_pkey Triggers: RI_ConstraintTrelevating=# create table foo (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE elevating=# create table bar (f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar" CREATE TABLE elevating=# ALTER TABLE ONLY foo ADD CONSTRAINT "$1" FOREIGN KEY (f1) REFERENCES bar; ALTER TABLE igger_2042119, RI_ConstraintTrigger_2042120 elevating=# Not much help to me. It was at this point I started reading old emails and trying to get rid of the foreign key that I guessed was buried in the trigger names somewhere. As you say, I must not have had the syntax right since: elevating=# alter table foo drop constraint "$1"; ALTER TABLE elevating=# drop table foo; DROP TABLE elevating=# drop table bar; DROP TABLE Well I guess I am brain dead. this is from the psql history: alter table only diag_logs drop contraint "$2"; alter table only diag_logs drop contraint '$2'; alter table only diag_logs drop contraint ; alter table only diag_logs drop contraint '\$2'; alter table only diag_logs drop contraint $2; alter table only diag_logs drop contraint $2; alter table only diag_logs drop contraint \$2; alter table only diag_logs drop contraint "\$2"; alter table only diag_logs drop contraint '*'; alter table only diag_logs drop contraint "$2"; alter table diag_logs drop contraint "$2"; I told you I tried all combos I could think of. All, that is except for spelling constraint correctly :( Anyway, I appreciate the help. Bret
Tom Lane <tgl@sss.pgh.pa.us> writes: > Bret Hughes <bhughes@elevating.com> writes: > > FWIW I tried to use alter table but could never get the parser to accept > > $1 as a constraint name. I used single and double quotes as well as a > > lame attempt \$1. > > Hm, "$1" works for me ... Hm, this reminds me. When I was first learning this stuff I was stymied by the same issue. It took me quite a while to figure out how to drop constraints because of the quoting issue. Of course now it seems obvious, but for someone just starting it adds another roadblock. Is there a reason postgres goes out of its way to pick names that will be harder to work with than necessary? Or is it considered a good thing on the theory that if it's hard to reference it's also hard to accidentally use such names in conflicting ways? Perhaps names like _1 _2 ... would be easier to handle? Or perhaps making $ not require quoting would be helpful? -- greg
Greg Stark <gsstark@mit.edu> writes:
> Is there a reason postgres goes out of its way to pick names that
> will be harder to work with than necessary?
If we use ordinary identifiers for system-generated names then we will
be infringing on user name space --- ie, there's a potential for
conflict. I suppose we could use long randomly-generated names like
ewjncm343cnlen, but are those really easier to work with?
I think a more useful approach is to treat it as a documentation
problem. Perhaps an example in the ALTER TABLE man page would help.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > Is there a reason postgres goes out of its way to pick names that > > will be harder to work with than necessary? > > If we use ordinary identifiers for system-generated names then we will > be infringing on user name space --- ie, there's a potential for > conflict. I suppose we could use long randomly-generated names like > ewjncm343cnlen, but are those really easier to work with? I don't see an unseverable link between "user name space" and "identifiers that don't need to be quoted". Mixed case names for instance seem like perfectly good user name space identifiers. Postgres could just as easily say "the system reserves all identifiers starting with $" and still not require quoting $. > I think a more useful approach is to treat it as a documentation > problem. Perhaps an example in the ALTER TABLE man page would help. -- greg
Greg Stark <gsstark@mit.edu> writes:
>>> Is there a reason postgres goes out of its way to pick names that
>>> will be harder to work with than necessary?
> I don't see an unseverable link between "user name space" and "identifiers
> that don't need to be quoted". Mixed case names for instance seem like
> perfectly good user name space identifiers.
Sure, but they still have to be quoted. The issue here was specifically
about whether the identifiers have to be quoted or not.
> Postgres could just as easily say "the system reserves all identifiers
> starting with $" and still not require quoting $.
At this point identifiers starting with $ are a completely lost cause ;-).
We have the parameter notation $n conflicting if the next character is a
digit, and the dollar-quote mechanism conflicting with any other second
character, and both of these are significantly more useful than the
ability to have unquoted IDs starting with $ would be. So that's a dead
end. I think if we wanted to change the default assignment of
constraint names we'd just go with ordinary identifiers that we hope
won't conflict with names the user picks.
regards, tom lane