Re: [SQL] trigger/for key help
Tom Lane [EMAIL PROTECTED] writes: Bret Hughes [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger/for key help
Greg Stark [EMAIL PROTECTED] 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 ---(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
Re: [SQL] trigger/for key help
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [SQL] trigger/for key help
Greg Stark [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger/for key help
On Sun, 2004-04-11 at 21:27, Tom Lane wrote: Bret Hughes [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] trigger/for key help
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 work
update pg_class set reltriggers=(select count(*) from pg_trigger where
tgrelid=pg_class.oid) where relname='insert name here';
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger/for key help
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 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
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 null
progname | character(25) | not null
severity_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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] trigger/for key help
Bret Hughes [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger/for key help
On Sun, 2004-04-11 at 17:00, Tom Lane wrote: Bret Hughes [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] 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 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
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
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
