Thread: Duplicate constraint names in 7.0.3
Hi, I have noticed that it is possible to create duplicate CHECK (haven't tried other) constraints in 7.0.3 by doing something like this: CREATE TABLE "test" ( "a" int4, CHECK (a < 400), CONSTRAINT "$1" CHECK (a > 5) ); I was just fiddling around with trying to implement the 'DROP CONSTRAINT' code (it's quite hard - don't wait up for me!) and it would seem to be a bad thing that it's possible to have two constraints with the same name in a table. Surely there should be a UNIQUE (rcrelid, rcname) on pg_relcheck?, or at least better checking in the CREATE TABLE code? Chris
If I read the spec correctly, table constraint names are supposed to be unique across a schema. So technically the constraint name should also not conflict with the name of an fk constraint, or a unique index. In addition, generated constraint names are supposed to follow the same syntax rules (which includes the uniqueness) which seems to imply that in cases like the below, that's not an error, and a different name should be generated for the unnamed constraint. However, unnamed column constraints seem to get the empty string as a name. I'd say don't worry about it for the purposes of drop constraint. :) On Fri, 4 May 2001, Christopher Kings-Lynne wrote: > Hi, > > I have noticed that it is possible to create duplicate CHECK (haven't tried > other) constraints in 7.0.3 by doing something like this: > > CREATE TABLE "test" ( > "a" int4, > CHECK (a < 400), > CONSTRAINT "$1" CHECK (a > 5) > ); > > I was just fiddling around with trying to implement the 'DROP CONSTRAINT' > code (it's quite hard - don't wait up for me!) and it would seem to be a bad > thing that it's possible to have two constraints with the same name in a > table. > > Surely there should be a UNIQUE (rcrelid, rcname) on pg_relcheck?, or at > least better checking in the CREATE TABLE code?
I left it unsaid that, in fact, all constraint names should be unique. Unnamed column constraints as far as I can tell get a '$n' automatically assigned name. Maybe the create table function should process named constraints first, and then the unnamed ones to prevent such a problem? Chris -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] Sent: Friday, 4 May 2001 10:48 AM To: Christopher Kings-Lynne Cc: Hackers Subject: Re: [HACKERS] Duplicate constraint names in 7.0.3 If I read the spec correctly, table constraint names are supposed to be unique across a schema. So technically the constraint name should also not conflict with the name of an fk constraint, or a unique index. In addition, generated constraint names are supposed to follow the same syntax rules (which includes the uniqueness) which seems to imply that in cases like the below, that's not an error, and a different name should be generated for the unnamed constraint. However, unnamed column constraints seem to get the empty string as a name. I'd say don't worry about it for the purposes of drop constraint. :) On Fri, 4 May 2001, Christopher Kings-Lynne wrote: > Hi, > > I have noticed that it is possible to create duplicate CHECK (haven't tried > other) constraints in 7.0.3 by doing something like this: > > CREATE TABLE "test" ( > "a" int4, > CHECK (a < 400), > CONSTRAINT "$1" CHECK (a > 5) > ); > > I was just fiddling around with trying to implement the 'DROP CONSTRAINT' > code (it's quite hard - don't wait up for me!) and it would seem to be a bad > thing that it's possible to have two constraints with the same name in a > table. > > Surely there should be a UNIQUE (rcrelid, rcname) on pg_relcheck?, or at > least better checking in the CREATE TABLE code?
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> If I read the spec correctly, table constraint names are supposed to be
> unique across a schema.
That's what the spec says, but I doubt we should enforce it. For one
thing, what do you do with inherited constraints? Invent a random name
for them? No thanks. The absolute limit of what I'd accept is
constraint name unique for a given table ... and even that seems like
an unnecessary restriction.
>> I was just fiddling around with trying to implement the 'DROP CONSTRAINT'
>> code (it's quite hard - don't wait up for me!) and it would seem to be a bad
>> thing that it's possible to have two constraints with the same name in a
>> table.
A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all*
constraints named "foo" on the target table.
regards, tom lane
On Thu, 3 May 2001, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > If I read the spec correctly, table constraint names are supposed to be > > unique across a schema. > > That's what the spec says, but I doubt we should enforce it. For one > thing, what do you do with inherited constraints? Invent a random name > for them? No thanks. The absolute limit of what I'd accept is > constraint name unique for a given table ... and even that seems like > an unnecessary restriction. The only thing I'd say is it might be confusing to people that some constraint names must be unique (unique, primary key) and that others can be duplicated (check, foreign key), not that all that many people probably name their unique constraints. > >> I was just fiddling around with trying to implement the 'DROP CONSTRAINT' > >> code (it's quite hard - don't wait up for me!) and it would seem to be a bad > >> thing that it's possible to have two constraints with the same name in a > >> table. > > A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all* > constraints named "foo" on the target table. Definately true if non-unique names are allowed.
> A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all* > constraints named "foo" on the target table. Then it should probably be a good thing to avoid the automatic generation of duplicate names? I might take a look at that, actually... Chris
OK, I have modifed heap.c so that it won't automatically generate duplicate constraint names. I have _not_ compiled this yet, as it's a bit of a pain for me cos I don't have bison, etc. However, it looks good to me, and if someone else wants to test it and then maybe think about if the patch is necessary that's fine by me. If no-one wants to test it, I will eventually get around to testing it myself. Given that this is my first code patch for Postgres - I should treat it with caution! Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > Kings-Lynne > Sent: Friday, 4 May 2001 12:33 PM > To: Hackers > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > > A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all* > > constraints named "foo" on the target table. > > Then it should probably be a good thing to avoid the automatic > generation of > duplicate names? I might take a look at that, actually... > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
Attachment
Can you send a context diff please? Thanks. [ Charset ISO-8859-1 unsupported, converting... ] > OK, > > I have modifed heap.c so that it won't automatically generate duplicate > constraint names. > > I have _not_ compiled this yet, as it's a bit of a pain for me cos I don't > have bison, etc. However, it looks good to me, and if someone else wants to > test it and then maybe think about if the patch is necessary that's fine by > me. > > If no-one wants to test it, I will eventually get around to testing it > myself. > > Given that this is my first code patch for Postgres - I should treat it with > caution! > > Chris > > > -----Original Message----- > > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > > Kings-Lynne > > Sent: Friday, 4 May 2001 12:33 PM > > To: Hackers > > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > > > > > A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all* > > > constraints named "foo" on the target table. > > > > Then it should probably be a good thing to avoid the automatic > > generation of > > duplicate names? I might take a look at that, actually... > > > > Chris > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Assuming that generating a context diff is a matter of going 'cvs diff -c heap.c' then attached is a context diff. To jog people's memories, it's intended to fix the problem with the following code creating duplicate constraint names: CREATE TABLE "test" ( "a" int4, CHECK (a < 400), CONSTRAINT "$1" CHECK (a > 5) ); Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian > Sent: Tuesday, 8 May 2001 12:42 PM > To: Christopher Kings-Lynne > Cc: Hackers > Subject: Re: [HACKERS] Duplicate constraint names in 7.0.3 > > > > Can you send a context diff please? Thanks. > > [ Charset ISO-8859-1 unsupported, converting... ] > > OK, > > > > I have modifed heap.c so that it won't automatically generate duplicate > > constraint names. > > > > I have _not_ compiled this yet, as it's a bit of a pain for me > cos I don't > > have bison, etc. However, it looks good to me, and if someone > else wants to > > test it and then maybe think about if the patch is necessary > that's fine by > > me. > > > > If no-one wants to test it, I will eventually get around to testing it > > myself. > > > > Given that this is my first code patch for Postgres - I should > treat it with > > caution! > > > > Chris > > > > > -----Original Message----- > > > From: pgsql-hackers-owner@postgresql.org > > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > > > Kings-Lynne > > > Sent: Friday, 4 May 2001 12:33 PM > > > To: Hackers > > > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > > > > > > > > A reasonable interpretation of DROP CONSTRAINT "foo" is to > drop *all* > > > > constraints named "foo" on the target table. > > > > > > Then it should probably be a good thing to avoid the automatic > > > generation of > > > duplicate names? I might take a look at that, actually... > > > > > > Chris > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://www.postgresql.org/search.mpl > > > > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Attachment
DOH! I installed bison and there is a tiny little compile-stopper. Use the attached diff instead. (I forgot to declare 'i' :) ) Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > Kings-Lynne > Sent: Tuesday, 8 May 2001 1:12 PM > To: Hackers > Cc: pgman@candle.pha.pa.us > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > Assuming that generating a context diff is a matter of going 'cvs diff -c > heap.c' then attached is a context diff. > > To jog people's memories, it's intended to fix the problem with the > following code creating duplicate constraint names: > > CREATE TABLE "test" ( > "a" int4, > CHECK (a < 400), > CONSTRAINT "$1" CHECK (a > 5) > ); > > Chris > > > -----Original Message----- > > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian > > Sent: Tuesday, 8 May 2001 12:42 PM > > To: Christopher Kings-Lynne > > Cc: Hackers > > Subject: Re: [HACKERS] Duplicate constraint names in 7.0.3 > > > > > > > > Can you send a context diff please? Thanks. > > > > [ Charset ISO-8859-1 unsupported, converting... ] > > > OK, > > > > > > I have modifed heap.c so that it won't automatically generate > duplicate > > > constraint names. > > > > > > I have _not_ compiled this yet, as it's a bit of a pain for me > > cos I don't > > > have bison, etc. However, it looks good to me, and if someone > > else wants to > > > test it and then maybe think about if the patch is necessary > > that's fine by > > > me. > > > > > > If no-one wants to test it, I will eventually get around to testing it > > > myself. > > > > > > Given that this is my first code patch for Postgres - I should > > treat it with > > > caution! > > > > > > Chris > > > > > > > -----Original Message----- > > > > From: pgsql-hackers-owner@postgresql.org > > > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > > > > Kings-Lynne > > > > Sent: Friday, 4 May 2001 12:33 PM > > > > To: Hackers > > > > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > > > > > > > > > > > A reasonable interpretation of DROP CONSTRAINT "foo" is to > > drop *all* > > > > > constraints named "foo" on the target table. > > > > > > > > Then it should probably be a good thing to avoid the automatic > > > > generation of > > > > duplicate names? I might take a look at that, actually... > > > > > > > > Chris > > > > > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > > TIP 6: Have you searched our list archives? > > > > > > > > http://www.postgresql.org/search.mpl > > > > > > > > [ Attachment, skipping... ] > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > TIP 2: you can get off all lists at once with the unregister command > > > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 853-3000 > > + If your life is a hard drive, | 830 Blythe Avenue > > + Christ can be your backup. | Drexel Hill, > Pennsylvania 19026 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >
Attachment
Applied (Newer version). I quote this one to give it context. Thanks. [ Charset ISO-8859-1 unsupported, converting... ] > OK, > > I have modifed heap.c so that it won't automatically generate duplicate > constraint names. > > I have _not_ compiled this yet, as it's a bit of a pain for me cos I don't > have bison, etc. However, it looks good to me, and if someone else wants to > test it and then maybe think about if the patch is necessary that's fine by > me. > > If no-one wants to test it, I will eventually get around to testing it > myself. > > Given that this is my first code patch for Postgres - I should treat it with > caution! > > Chris > > > -----Original Message----- > > From: pgsql-hackers-owner@postgresql.org > > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher > > Kings-Lynne > > Sent: Friday, 4 May 2001 12:33 PM > > To: Hackers > > Subject: RE: [HACKERS] Duplicate constraint names in 7.0.3 > > > > > > > A reasonable interpretation of DROP CONSTRAINT "foo" is to drop *all* > > > constraints named "foo" on the target table. > > > > Then it should probably be a good thing to avoid the automatic > > generation of > > duplicate names? I might take a look at that, actually... > > > > Chris > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026