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


Fri, 4 May 2001 09:48:23 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
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?


Thu, 3 May 2001 19:48:03 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Duplicate constraint names in 7.0.3
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?


Fri, 4 May 2001 11:17:21 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Duplicate constraint names in 7.0.3
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

Thu, 03 May 2001 23:24:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Duplicate constraint names in 7.0.3 
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.


Thu, 3 May 2001 20:42:57 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: 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


Fri, 4 May 2001 12:33:22 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Duplicate constraint names in 7.0.3 
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
>
Tue, 8 May 2001 11:47:14 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: 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

Tue, 8 May 2001 00:41:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 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
>
Tue, 8 May 2001 13:11:47 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Duplicate constraint names in 7.0.3
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
> >
>
Tue, 8 May 2001 13:23:16 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: Duplicate constraint names in 7.0.3

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

Wed, 9 May 2001 17:13:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Duplicate constraint names in 7.0.3