Thread: Information Schema and constraint names not unique
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 |/
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
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
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
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
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
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."
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
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
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 |/
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
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 |/
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
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 |/
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, Pennsylvania19073
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
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
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, Pennsylvania19073
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
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
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
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, Pennsylvania19073
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 |/