
Just looking at the information schema in 7.4 and noticed something 
odd/annoying/problematic:

create table pk(f1 int primary key);
create table fk1(f1 int references pk(f1));
create table fk2(f1 int references pk(f1));

select * from information_schema.referential_constraints;

-[ RECORD 1 ]-------------+----------
constraint_catalog        | test
constraint_schema         | public
constraint_name           | $1
unique_constraint_catalog | test
unique_constraint_schema  | public
unique_constraint_name    | pk_pkey
match_option              | NONE
update_rule               | NO ACTION
delete_rule               | NO ACTION
-[ RECORD 2 ]-------------+----------
constraint_catalog        | test
constraint_schema         | public
constraint_name           | $1
unique_constraint_catalog | test
unique_constraint_schema  | public
unique_constraint_name    | pk_pkey
match_option              | NONE
update_rule               | NO ACTION
delete_rule               | NO ACTION


Notice that the two records are identical because the two constraint names 
are the same. ISTM that we should have a way of usefully examining specific 
constraints without having to name them. Can we add the constraint OID or 
some other identifier (table?) or ensure that constraint names are unique?

This problem applies to all the info schema tables that use constraint name.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/ 


Fri, 07 Nov 2003 00:00:31 +1100
Philip Warner <pjw@rhyme.com.au>
Information Schema and constraint names not unique
Philip Warner <pjw@rhyme.com.au> writes:
> Notice that the two records are identical because the two constraint names 
> are the same. ISTM that we should have a way of usefully examining specific 
> constraints without having to name them. Can we add the constraint OID or 

No.  The schemas of the information_schema views are defined by the
standard; I don't think we get to invent columns, especially not columns
with such PG-specific contents as OIDs.

> some other identifier (table?) or ensure that constraint names are unique?

The reason the spec defines these views this way is that it expects
constraint names to be unique across a whole schema.  We don't enforce
that, and I don't think we want to start doing so (that was already
proposed and shot down at least once).  You are of course free to use
constraint names that are distinct if you want to follow the spec's
lead.

			regards, tom lane

Thu, 06 Nov 2003 10:11:38 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
Tom Lane wrote:

>The reason the spec defines these views this way is that it expects
>constraint names to be unique across a whole schema.  We don't enforce
>that, and I don't think we want to start doing so (that was already
>proposed and shot down at least once).  You are of course free to use
>constraint names that are distinct if you want to follow the spec's
>lead.
>  
>
Would a good halfway house be to ensure that generated names were unique 
within a schema (e.g. instead of generating "$1" generate 
"tablename$1")? I know this might make looking to see if something is a 
generated constraint mildly harder. It would have the advantage of a 
slightly more meaningful name on the constraint.

Doing that we still wouldn't enforce the spec's requirements for 
uniqueness of constraint names within a schema (which are arguably 
silly), but wouldn't violate them ourselves.

(I'm sure there are wrinkles I haven't thought of, though. Not sure 
about what it would do to backwards compatibility, for instance.)

cheers

andrew


Thu, 06 Nov 2003 10:43:59 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Information Schema and constraint names not unique
Andrew Dunstan <andrew@dunslane.net> writes:
>> The reason the spec defines these views this way is that it expects
>> constraint names to be unique across a whole schema.  We don't enforce
>> that, and I don't think we want to start doing so (that was already
>> proposed and shot down at least once).

> Would a good halfway house be to ensure that generated names were unique 
> within a schema (e.g. instead of generating "$1" generate 
> "tablename$1")?

No, because that buys into all of the serialization and deadlocking
problems that doing it the spec's way entail --- essentially, you cannot
add a new constraint without obtaining some kind of schema-wide lock.
See prior discussions.

			regards, tom lane

Thu, 06 Nov 2003 10:54:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
Tom Lane writes:

> > Would a good halfway house be to ensure that generated names were unique
> > within a schema (e.g. instead of generating "$1" generate
> > "tablename$1")?
>
> No, because that buys into all of the serialization and deadlocking
> problems that doing it the spec's way entail

I don't think we really need a method to guarantee unique names.  It would
already help a lot if we just added the table name, or something that was
until a short time before the action believed to be the table name, or
even only the table OID, before (or after) the $1.

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 6 Nov 2003 17:37:32 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Information Schema and constraint names not unique 
Peter Eisentraut <peter_e@gmx.net> writes:
> I don't think we really need a method to guarantee unique names.  It would
> already help a lot if we just added the table name, or something that was
> until a short time before the action believed to be the table name, or
> even only the table OID, before (or after) the $1.

I don't have a problem with switching from "$1" to "tablename_$1", or
some such, for auto-generated constraint names.  But if it's not
guaranteed unique, does it really satisfy Philip's concern?

			regards, tom lane

Thu, 06 Nov 2003 11:42:13 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I don't think we really need a method to guarantee unique names.  It would
> > already help a lot if we just added the table name, or something that was
> > until a short time before the action believed to be the table name, or
> > even only the table OID, before (or after) the $1.
> 
> I don't have a problem with switching from "$1" to "tablename_$1", or
> some such, for auto-generated constraint names.  But if it's not
> guaranteed unique, does it really satisfy Philip's concern?

It certainly _is_ unique within a schema ...
(But what happens to the constraint name when the table is renamed?)

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No renuncies a nada. No te aferres a nada."

Thu, 6 Nov 2003 13:49:56 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: Information Schema and constraint names not unique
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote:
>> I don't have a problem with switching from "$1" to "tablename_$1", or
>> some such, for auto-generated constraint names.  But if it's not
>> guaranteed unique, does it really satisfy Philip's concern?

> It certainly _is_ unique within a schema ...
> (But what happens to the constraint name when the table is renamed?)

Exactly.  Also consider manually-assigned constraint names that happen
to look like "foo_$n" --- these could cause trouble if table foo is
created later.  To make a guarantee of uniqueness would require more
infrastructure than just a simple hack of the constraint name generator
logic.

BTW we also have some problems with auto-generated names for column
constraints; these generally look like "tablename_columnname", and
that's not unique:

regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
ERROR:  check constraint "foo_f1" already exists

			regards, tom lane

Thu, 06 Nov 2003 11:57:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
Tom Lane wrote:

>Peter Eisentraut <peter_e@gmx.net> writes:
>  
>
>>I don't think we really need a method to guarantee unique names.  It would
>>already help a lot if we just added the table name, or something that was
>>until a short time before the action believed to be the table name, or
>>even only the table OID, before (or after) the $1.
>>    
>>
>
>I don't have a problem with switching from "$1" to "tablename_$1", or
>some such, for auto-generated constraint names.  But if it's not
>guaranteed unique, does it really satisfy Philip's concern?
>
>  
>
He wouldn't see identical rows returned from his query any more, would he?

My point was that doing this nothing would prevent the user creating 
duplicate constraint names but the system would not produce (or would be 
most unlikely to produce) duplicates. I read the thread from last year 
on Google at

 http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=18252.1025635125%40sss.pgh.pa.us&rnum=1&prev=/groups%3Fq%3Dunique%2Bconstraint%2Bnames%2Bgroup:comp.databases.postgresql.hackers%2Bgroup:comp.databases.postgresql.hackers%2Bgroup:comp.databases.postgresql.hackers%2Bgroup:comp.databases.postgresql.hackers%26hl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dcomp.databases.postgresql.hackers%26selm%3D18252.1025635125%2540sss.pgh.pa.us%26rnum%3D1

which was why I thought this would be a move in the right direction 
without encountering those problems.

(I much prefer using tablename to OID, BTW)

cheers

andrew


Thu, 06 Nov 2003 12:01:17 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Information Schema and constraint names not unique
At 03:37 AM 7/11/2003, Peter Eisentraut wrote:
>It would
>already help a lot if we just added the table name, or something that was
>until a short time before the action believed to be the table name, or
>even only the table OID, before (or after) the $1.

Can we allow/bypass the pg_* restriction, and call it pg_<table-oid>_<n>, 
and for pedants like me, add a DB setting that says 'enforce unique 
constraints' ala the spec to avoid manually created constraints being 
non-unique?

Alternatively, I would be happy *not* to enforce constraint name uniqueness 
(and break the spec) so long as we also break the spec and add table OID 
(or something else) to the information schema (table name would be OK so 
long as renaming the table won't break anything).







----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/ 


Fri, 07 Nov 2003 10:54:34 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Information Schema and constraint names not


Philip Warner wrote:

> At 03:37 AM 7/11/2003, Peter Eisentraut wrote:
>
>> It would
>> already help a lot if we just added the table name, or something that 
>> was
>> until a short time before the action believed to be the table name, or
>> even only the table OID, before (or after) the $1.
>
>
> Can we allow/bypass the pg_* restriction, and call it 
> pg_<table-oid>_<n>, and for pedants like me, add a DB setting that 
> says 'enforce unique constraints' ala the spec to avoid manually 
> created constraints being non-unique?
>
> Alternatively, I would be happy *not* to enforce constraint name 
> uniqueness (and break the spec) so long as we also break the spec and 
> add table OID (or something else) to the information schema (table 
> name would be OK so long as renaming the table won't break anything). 


The first seems impractical for reasons given by Tom. Why provide an 
option for behaviour we fear could deadlock etc.?

Regarding the second option, I don't understand what virtue there is in 
breaking the spec more, rather than embedding the table name in the 
constraint name.

(If we do that, if the table is renamed my instinct would be to rename 
constraints in the renamed table with autogenerated names, but I haven't 
looked into it).

cheers

andrew


Thu, 06 Nov 2003 19:16:23 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Information Schema and constraint names not
At 10:54 AM 7/11/2003, Philip Warner wrote:
>add table OID (or something else) to the information schema

Peter may have been alluding to this, and I misunderstood, but one idea 
might be to present a mangled name in the information schema; since the 
spec expects them to be unique, perhaps the schema should present them as 
unique. Downside is that named constraints would also have to be mangled. A 
compromise would be to only mangle the '$n' constraints, and just prepend 
'pg_<tablename>' to the constraint name in the view.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/ 


Fri, 07 Nov 2003 11:23:11 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Information Schema and constraint names not
Philip Warner <pjw@rhyme.com.au> writes:
> Peter may have been alluding to this, and I misunderstood, but one idea 
> might be to present a mangled name in the information schema; since the 
> spec expects them to be unique, perhaps the schema should present them as 
> unique.

Doesn't seem like this would work very well; an application that tried
to do anything with the constraint names it got from the view would soon
find that they were wrong.  (And if you don't want to do anything with
the info you get from the view, why are you bothering to look at it?)

Your argument that we should add the table name to the view does have
some merit though.

			regards, tom lane

Thu, 06 Nov 2003 19:38:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not 
At 11:38 AM 7/11/2003, Tom Lane wrote:
>Your argument that we should add the table name to the view does have
>some merit though.

Sounds good to me. It would need to be added to each view that has 
constraint_name, then we should be able to cross the info schema views and 
get meaningful data.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/ 


Fri, 07 Nov 2003 12:25:19 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Information Schema and constraint names not 
Tom Lane wrote:
> Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> > On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote:
> >> I don't have a problem with switching from "$1" to "tablename_$1", or
> >> some such, for auto-generated constraint names.  But if it's not
> >> guaranteed unique, does it really satisfy Philip's concern?
> 
> > It certainly _is_ unique within a schema ...
> > (But what happens to the constraint name when the table is renamed?)
> 
> Exactly.  Also consider manually-assigned constraint names that happen
> to look like "foo_$n" --- these could cause trouble if table foo is
> created later.  To make a guarantee of uniqueness would require more
> infrastructure than just a simple hack of the constraint name generator
> logic.
> 
> BTW we also have some problems with auto-generated names for column
> constraints; these generally look like "tablename_columnname", and
> that's not unique:
> 
> regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
> ERROR:  check constraint "foo_f1" already exists

Is this a TODO to fix?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 09:04:55 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Information Schema and constraint names not unique
Bruce Momjian wrote:

>Tom Lane wrote:
>  
>
>>Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
>>    
>>
>>>On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote:
>>>      
>>>
>>>>I don't have a problem with switching from "$1" to "tablename_$1", or
>>>>some such, for auto-generated constraint names.  But if it's not
>>>>guaranteed unique, does it really satisfy Philip's concern?
>>>>        
>>>>
>>>It certainly _is_ unique within a schema ...
>>>(But what happens to the constraint name when the table is renamed?)
>>>      
>>>
>>Exactly.  Also consider manually-assigned constraint names that happen
>>to look like "foo_$n" --- these could cause trouble if table foo is
>>created later.  To make a guarantee of uniqueness would require more
>>infrastructure than just a simple hack of the constraint name generator
>>logic.
>>
>>BTW we also have some problems with auto-generated names for column
>>constraints; these generally look like "tablename_columnname", and
>>that's not unique:
>>
>>regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
>>ERROR:  check constraint "foo_f1" already exists
>>    
>>
>
>Is this a TODO to fix?
>  
>

I think there are several of them from this thread:

. make autogenerated column constraint names unique per table (by adding 
"_$n" ?)
. add tableoid or tablename to information_schema.{check_constraints, 
referential_constraints} (I think those are the only places where it 
would be needed, from my quick skimming).
. add tableoid or tablename to autogenerated table constraint names

Is that a fair summary of discussion so far?

(My take)
Using tableoid instead of tablename avoids renaming problems, but makes 
the names horribly opaque IMNSHO. I know I've been annoyed from an 
aesthetic POV more than once by the "$1" thing.


cheers

andrew



Fri, 07 Nov 2003 10:00:18 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Information Schema and constraint names not unique
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
>> ERROR:  check constraint "foo_f1" already exists

> Is this a TODO to fix?

Probably should be.  I'd be inclined to try to fix it by generating
"foo_f1_1", "foo_f1_2", etc until a non-conflicting name is found.

(Note: the reason it's okay to search for a non-conflicting name in this
context is we only need a lock on the single table in question.  It's
no problem anyway in CREATE TABLE, but can still work in ALTER TABLE.)

			regards, tom lane

Fri, 07 Nov 2003 10:05:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
> >> ERROR:  check constraint "foo_f1" already exists
> 
> > Is this a TODO to fix?
> 
> Probably should be.  I'd be inclined to try to fix it by generating
> "foo_f1_1", "foo_f1_2", etc until a non-conflicting name is found.
> 
> (Note: the reason it's okay to search for a non-conflicting name in this
> context is we only need a lock on the single table in question.  It's
> no problem anyway in CREATE TABLE, but can still work in ALTER TABLE.)

Added:

* Allow CREATE TABLE foo (f1 INT CHECK (f1 > 0) CHECK (f1 < 10)) to work
  by searching for non-conflicting constraint names, and prefix with table name

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 10:26:13 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Information Schema and constraint names not unique
Andrew Dunstan <andrew@dunslane.net> writes:
> I think there are several of them from this thread:

> . make autogenerated column constraint names unique per table (by adding 
> "_$n" ?)

Check.

> . add tableoid or tablename to information_schema.{check_constraints, 
> referential_constraints} (I think those are the only places where it 
> would be needed, from my quick skimming).
> . add tableoid or tablename to autogenerated table constraint names

These are mutually exclusive --- I see no reason to do both.

> Using tableoid instead of tablename avoids renaming problems, but makes 
> the names horribly opaque IMNSHO.

Agreed.  I think using the OIDs would be a horrible choice.

			regards, tom lane

Fri, 07 Nov 2003 10:59:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Information Schema and constraint names not unique 
Tom Lane wrote:

>Andrew Dunstan <andrew@dunslane.net> writes:
>  
>
>  
>
>>. add tableoid or tablename to information_schema.{check_constraints, 
>>referential_constraints} (I think those are the only places where it 
>>would be needed, from my quick skimming).
>>. add tableoid or tablename to autogenerated table constraint names
>>    
>>
>
>These are mutually exclusive --- I see no reason to do both.
>
>  
>

In that case I vote for option 2 - it makes the names nicer and gets us 
closer to compliance with the spec.

:-)

(Option 1 is trivially easy by comparison, though).

cheers

andrew


Fri, 07 Nov 2003 11:14:11 -0500
Andrew Dunstan <andrew@dunslane.net>
Re: Information Schema and constraint names not unique


Tom Lane wrote:
> 
>>Using tableoid instead of tablename avoids renaming problems, but makes 
>>the names horribly opaque IMNSHO.
> 
> 
> Agreed.  I think using the OIDs would be a horrible choice.
> 

As a point of reference Oracle uses a naming convention of 'Cnnnn' where 
nnnn is a sequence generated unique value.  So in Oracle system 
generated names are very opaque.  I never saw this as a problem, since 
if you wanted a non-opaque name you could always assign one yourself.

--Barry



Fri, 07 Nov 2003 08:51:21 -0800
Barry Lind <blind@xythos.com>
Re: Information Schema and constraint names not unique
Barry Lind wrote:
> 
> 
> Tom Lane wrote:
> > 
> >>Using tableoid instead of tablename avoids renaming problems, but makes 
> >>the names horribly opaque IMNSHO.
> > 
> > 
> > Agreed.  I think using the OIDs would be a horrible choice.
> > 
> 
> As a point of reference Oracle uses a naming convention of 'Cnnnn' where 
> nnnn is a sequence generated unique value.  So in Oracle system 
> generated names are very opaque.  I never saw this as a problem, since 
> if you wanted a non-opaque name you could always assign one yourself.

What, no dollar signs?  :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Nov 2003 16:25:21 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Information Schema and constraint names not unique
At 02:59 AM 8/11/2003, Tom Lane wrote:
>These are mutually exclusive --- I see no reason to do both.

Not sure that's true; we've taken te design decision to make allow 
user-defined constraint names to be non-unique. Given that, I think we 
should allow people who fall into the trap to be able to use the info 
schemas to get details of their constraints. So, adding enough detail about 
the constraint to uniquely identify it, even if it is a user-created one, 
seems essential.

=> Adding table identification info to constraint details in the info 
schema is necessary.

I don't agree that using OIDs to in constraint names is bad; the table name 
will be misleading when tables are renamed, and encourage use of internal 
data (PG_* tables) when info schemas should do the job for most people. I 
think we're confusing a presentation issue with an internal design issue.



----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/ 


Sat, 08 Nov 2003 13:46:45 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Information Schema and constraint names not