Thread: Constraint names using 'user namespace'?
Just noticed this: pjw=# create table pk1(f1 integer, constraint zzz primary key(f1)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'zzz' for table 'pk1' CREATE pjw=# create table zzz(f1 integer); ERROR: Relation 'zzz' already exists Is there a good reason why the automatically created items do not have a 'pg_' in front of their names? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner writes: > pjw=# create table pk1(f1 integer, constraint zzz primary key(f1)); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'zzz' for > table 'pk1' > CREATE > pjw=# create table zzz(f1 integer); > ERROR: Relation 'zzz' already exists > > Is there a good reason why the automatically created items do not have a > 'pg_' in front of their names? One thing that has always bugged me is why indexes have names at all. I can never think of any. The table name and the attribute name(s)/number(s) should suffice. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Philip Warner <pjw@rhyme.com.au> writes:
> Just noticed this:
> pjw=# create table pk1(f1 integer, constraint zzz primary key(f1));
> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'zzz' for
> table 'pk1'
> CREATE
> pjw=# create table zzz(f1 integer);
> ERROR: Relation 'zzz' already exists
> Is there a good reason why the automatically created items do not have a
> 'pg_' in front of their names?
Not a good idea. I think it should probably be pk1_zzz in this case.
If we do either, it will break the recently submitted pg_dump patch that
uses the index name as the constraint name. I thought that patch was
wrongheaded anyway, and would recommend reversing it...
regards, tom lane
At 00:24 28/11/00 -0500, Tom Lane wrote: >Philip Warner <pjw@rhyme.com.au> writes: >> Just noticed this: > >> pjw=# create table pk1(f1 integer, constraint zzz primary key(f1)); >> NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'zzz' for >> table 'pk1' >> CREATE >> pjw=# create table zzz(f1 integer); >> ERROR: Relation 'zzz' already exists > >> Is there a good reason why the automatically created items do not have a >> 'pg_' in front of their names? > >Not a good idea. I think it should probably be pk1_zzz in this case. That would at least be consistent, but it's still using 'user namespace' for system-related items, which seems like a bad practice if it can be avoided. I don't mind a longer name, if that is your objection: pg_constraint_pk1_zzz or some such. >If we do either, it will break the recently submitted pg_dump patch that Not too hard to fix. >uses the index name as the constraint name. I thought that patch was >wrongheaded anyway, and would recommend reversing it... I wasn't too keen on it, but could not come up with any good arguments against it. We need a unified approach to constraints, but in the mean time it seems OK. Do you have any more definite objections? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
>>> Is there a good reason why the automatically created items do not have a
>>> 'pg_' in front of their names?
>>
>> Not a good idea. I think it should probably be pk1_zzz in this case.
> That would at least be consistent, but it's still using 'user namespace'
> for system-related items, which seems like a bad practice if it can be
> avoided. I don't mind a longer name, if that is your objection:
> pg_constraint_pk1_zzz or some such.
No, my objection is that I couldn't get rid of the index with "drop index".
A pg_ prefix has semantic connotations that I think are inappropriate
for a user-table index.
As for the treading-on-user-namespace issue, we already do that for all
implicitly created indexes (see UNIQUE, PRIMARY KEY, etc). I'd prefer
to treat named constraints consistently with that long-established
practice until we have a better idea that can be implemented uniformly
across that whole set of constructs. (Once we have schemas, for
example, it might be practical to give indexes a separate namespace
from tables, which'd help a lot.)
>> uses the index name as the constraint name. I thought that patch was
>> wrongheaded anyway, and would recommend reversing it...
> I wasn't too keen on it, but could not come up with any good arguments
> against it. We need a unified approach to constraints, but in the mean time
> it seems OK. Do you have any more definite objections?
What I didn't like was the entirely unjustified assumption that a
primary key constraint *has* a name. Introducing a name where none
existed is just as bad a sin as dropping one, if not worse (which
case do you think is more common?). Given the choice between
those two evils, I'll take the one that takes less code, until such
time as we can do it right...
regards, tom lane
> As for the treading-on-user-namespace issue, we already do that for all > implicitly created indexes (see UNIQUE, PRIMARY KEY, etc). I'd prefer > to treat named constraints consistently with that long-established > practice until we have a better idea that can be implemented uniformly > across that whole set of constructs. (Once we have schemas, for > example, it might be practical to give indexes a separate namespace > from tables, which'd help a lot.) Surely the best way to do it would be to make the unique and primary key implicitly created indices totally invisible to the user. Or at least add a 'system' flag to their entries in the pg_indexes table. Create a pg_constraint table instead that people can use to find constraints. To support this, dropping unique and pk constraints would no longer be possible (and _should_ no longer be possible) with a CREATE/DROP INDEX command, and instead would be achieved with a functional ALTER TABLE ADD/DROP CONSTRAINT statement. This seems good in that in the future, the way pk's and uniques are implemented may change (and no longer be indices for some reason), and any changes will be invisible to the user. And while we're at it, add not null and fk constraints to pg_constraint, and make the fk triggers totally invisible to the user, for similar reasons. I'm not sure what to do with check constraints - they seem fairly clearly declared as it is... Chris
At 02:18 PM 11/28/00 +0800, Christopher Kings-Lynne wrote:
>> As for the treading-on-user-namespace issue, we already do that for all
>> implicitly created indexes (see UNIQUE, PRIMARY KEY, etc). I'd prefer
>> to treat named constraints consistently with that long-established
>> practice until we have a better idea that can be implemented uniformly
>> across that whole set of constructs. (Once we have schemas, for
>> example, it might be practical to give indexes a separate namespace
>> from tables, which'd help a lot.)
>
>Surely the best way to do it would be to make the unique and primary key
>implicitly created indices totally invisible to the user. Or at least add a
>'system' flag to their entries in the pg_indexes table. Create a
>pg_constraint table instead that people can use to find constraints.
Oracle has a "user_constraints" table. Explicitly named contraints have
that name entered into the user's namespace, implicitly named constraints
get stuffed into "sys" in the form "sys.cnnnnn", where "nnnnn" is drawn
from some system sequence.
In Oracle you NEED the user_constraints table, particularly for RI constraint
errors, because their wonderful error messages just give you the RI constraint
name. If you've not given it a meaningful name yourself, which typically one
doesn't ("integer references some_table"), you need to do a select on the
user_constraints table to see what went wrong.
Keep PG's superior error messages no matter what else is done :)
The above is offered as a datapoint, that's all.
>To support this, dropping unique and pk constraints would no longer be
>possible (and _should_ no longer be possible) with a CREATE/DROP INDEX
>command, and instead would be achieved with a functional ALTER TABLE
>ADD/DROP CONSTRAINT statement.
This is essentially the case in Oracle, though I suspect you could dig
around, find the name of the unannounced unique index, and drop it by
hand if you wanted.
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.