Re: [HACKERS] Information Schema and constraint names not unique
Philip Warner <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Information Schema and constraint names not unique
Andrew Dunstan <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Information Schema and constraint names not unique
Peter Eisentraut <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Information Schema and constraint names not unique
On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> 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 () "No renuncies a nada. No te aferres a nada." ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Information Schema and constraint names not unique
Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Information Schema and constraint names not unique
Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Information Schema and constraint names not unique
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Information Schema and constraint names not unique
Bruce Momjian wrote:
Tom Lane wrote:
Alvaro Herrera <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Information Schema and constraint names not unique
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Information Schema and constraint names not unique
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Information Schema and constraint names not unique
Andrew Dunstan <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Information Schema and constraint names not unique
Tom Lane wrote:
Andrew Dunstan <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] 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 'C' where 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] 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 'C' where > 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
