
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   |/

Tue, 28 Nov 2000 10:09:59 +1100
Philip Warner <pjw@rhyme.com.au>
Constraint names using 'user namespace'?
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/


Tue, 28 Nov 2000 00:33:57 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Constraint names using 'user namespace'?
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

Tue, 28 Nov 2000 00:24:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraint names using 'user namespace'? 
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   |/

Tue, 28 Nov 2000 16:43:22 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Constraint names using 'user namespace'? 
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

Tue, 28 Nov 2000 01:03:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Constraint names using 'user namespace'? 
> 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


Tue, 28 Nov 2000 14:18:07 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Constraint names using 'user namespace'? 
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 Service and other goodies at
  http://donb.photo.net.

Tue, 28 Nov 2000 06:29:49 -0800
Don Baccus <dhogaza@pacifier.com>
RE: Constraint names using 'user namespace'? 