pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger/for key help
11+ messages / 4 participants
[nested] [flat]
* trigger/for key help
@ 2004-04-11 05:43 Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 11+ messages in thread
From: Bret Hughes @ 2004-04-11 05:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
@ 2004-04-11 15:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
0 siblings, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2004-04-11 15:17 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; +Cc: pgsql-sql
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>';
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-04-11 19:19 ` Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Bret Hughes @ 2004-04-11 19:19 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
@ 2004-04-11 22:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-04-11 22:00 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-11 22:58 ` Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Bret Hughes @ 2004-04-11 22:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
@ 2004-04-12 02:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-12 07:18 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-13 06:20 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2004-04-12 02:27 UTC (permalink / raw)
To: Bret Hughes <bhughes@elevating.com>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-12 07:18 ` Bret Hughes <bhughes@elevating.com>
1 sibling, 0 replies; 11+ messages in thread
From: Bret Hughes @ 2004-04-12 07:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-13 06:20 ` Greg Stark <gsstark@mit.edu>
2004-04-13 13:25 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Greg Stark @ 2004-04-13 06:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bret Hughes <bhughes@elevating.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 06:20 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
@ 2004-04-13 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 17:36 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2004-04-13 13:25 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Bret Hughes <bhughes@elevating.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 06:20 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
2004-04-13 13:25 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-04-13 17:36 ` Greg Stark <gsstark@mit.edu>
2004-04-13 18:35 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Greg Stark @ 2004-04-13 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Stark <gsstark@mit.edu>; Bret Hughes <bhughes@elevating.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: trigger/for key help
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Re: trigger/for key help Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Re: trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 06:20 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
2004-04-13 13:25 ` Re: trigger/for key help Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 17:36 ` Re: trigger/for key help Greg Stark <gsstark@mit.edu>
@ 2004-04-13 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 11+ messages in thread
From: Tom Lane @ 2004-04-13 18:35 UTC (permalink / raw)
To: Greg Stark <gsstark@mit.edu>; +Cc: Bret Hughes <bhughes@elevating.com>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2004-04-13 18:35 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-11 05:43 trigger/for key help Bret Hughes <bhughes@elevating.com>
2004-04-11 15:17 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-04-11 19:19 ` Bret Hughes <bhughes@elevating.com>
2004-04-11 22:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-11 22:58 ` Bret Hughes <bhughes@elevating.com>
2004-04-12 02:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-12 07:18 ` Bret Hughes <bhughes@elevating.com>
2004-04-13 06:20 ` Greg Stark <gsstark@mit.edu>
2004-04-13 13:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-13 17:36 ` Greg Stark <gsstark@mit.edu>
2004-04-13 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox