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:

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

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

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

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

DOH!

I installed bison and there is a tiny little compile-stopper. Use the
attached diff instead.

(I forgot to declare 'i' :) )

Chris

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, Pennsylvania 19026