When someone issues this command:

ALTER TABLE test ADD UNIQUE (a, b);

What happens when:

1. A non-unique index is already defined over (a, b)

	- Either add new index or promote existing one to unique?

2. A non-unique index is already defined over (b, a)

	- As above?

3. A primary index is already defined over (a, b)

	- ERROR: unique already implied by primary?

4. A primary index is already defined over (b, a)

	- As above?

5. A unique index is already defined over (a, b)

	- ERROR: unique index already exists over keys?

6. A unique index is already defined over (b, a)

	- As above.  Technically a different index, but effect
	  as far as uniqueness is concerned is identical?

7. No index exists over (a, b) or (b, a)

	- Create a new unique index over (a, b)?

Chris



Tue, 10 Jul 2001 09:39:11 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
More ADD CONSTRAINT behaviour questions
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> 6. A unique index is already defined over (b, a)

> 	- As above.  Technically a different index, but effect
> 	  as far as uniqueness is concerned is identical?

This case *must not* be an error IMHO: it's perfectly reasonable to have
indexes on both (a,b) and (b,a), and if the column pair happens to be
unique, there's no reason why they shouldn't both be marked unique.

Because of that, I'm not too excited about raising an error in any case
except where you have an absolutely identical pre-existing index, ie,
there's already a unique index on (a,b) --- doesn't matter much whether
it's marked primary or not.

For ADD PRIMARY KEY, there mustn't be any pre-existing primary index,
of course.  I can see promoting an extant matching unique index to
primary status, though, rather than making another index.

			regards, tom lane

Mon, 09 Jul 2001 22:31:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More ADD CONSTRAINT behaviour questions 
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > 6. A unique index is already defined over (b, a)
> 
> > 	- As above.  Technically a different index, but effect
> > 	  as far as uniqueness is concerned is identical?
> 
> This case *must not* be an error IMHO: it's perfectly reasonable to have
> indexes on both (a,b) and (b,a), and if the column pair happens to be
> unique, there's no reason why they shouldn't both be marked unique.
> 
> Because of that, I'm not too excited about raising an error in any case
> except where you have an absolutely identical pre-existing index, ie,
> there's already a unique index on (a,b) --- doesn't matter much whether
> it's marked primary or not.
> 
> For ADD PRIMARY KEY, there mustn't be any pre-existing primary index,
> of course.  I can see promoting an extant matching unique index to
> primary status, though, rather than making another index.
> 

Yea, I agree with Tom.  Usually we let the person do whatever they want
except in cases that clearly make no sense or where we can improve it.

Good questions, though.

-- 
  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

Mon, 9 Jul 2001 23:32:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: More ADD CONSTRAINT behaviour questions
On Tue, 10 Jul 2001, Christopher Kings-Lynne wrote:

> When someone issues this command:
> 
> ALTER TABLE test ADD UNIQUE (a, b);
> 
> What happens when:
> 
> 1. A non-unique index is already defined over (a, b)
> 
> 	- Either add new index or promote existing one to unique?

Well, either works, but if you promote, you should have a way
to keep track of the fact you did so, because dropping the
constraint shouldn't drop the index then but demote it.
I'm less sure about what the correct behavior would be for adding
primary keys (if you added a primary key on a unique index and
then dropped the primary key, do you end up with a normal 
unique at the end?)

> 2. A non-unique index is already defined over (b, a)
> 
> 	- As above?
I agree with Tom for 2/4/6, since the indexes are different
for planning purposes.

> 3. A primary index is already defined over (a, b)
> 
> 	- ERROR: unique already implied by primary?

Seems reasonable.  Maybe errors like:
 ERROR: Primary key <name> already defined on test(a,b)
 ERROR: Unique constraint <name> already defined on test(a,b)


Mon, 9 Jul 2001 20:33:49 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: More ADD CONSTRAINT behaviour questions
OK, so just to summarize:

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Christopher
> Kings-Lynne
> Sent: Tuesday, 10 July 2001 9:39 AM
> To: Hackers
> Subject: [HACKERS] More ADD CONSTRAINT behaviour questions
>
>
> When someone issues this command:
>
> ALTER TABLE test ADD UNIQUE (a, b);
>
> What happens when:
>
> 1. A non-unique index is already defined over (a, b)
>
> 	- Either add new index or promote existing one to unique?

Promoting is in my too-hard basket, so I will simply add a new unique index?
Too bad if it slows them down, as they should know better?  Should I issue a
NOTICE warning them that they have overlapping indices?

> 2. A non-unique index is already defined over (b, a)
>
> 	- As above?

Irrelevant as (a,b) will be handled independently of (b,a).  Basically
problem ignored?

> 3. A primary index is already defined over (a, b)
>
> 	- ERROR: unique already implied by primary?

Done.  Implemented.

> 4. A primary index is already defined over (b, a)
>
> 	- As above?

As per (2).

> 5. A unique index is already defined over (a, b)
>
> 	- ERROR: unique index already exists over keys?

Done. Implemented.

> 6. A unique index is already defined over (b, a)
>
> 	- As above.  Technically a different index, but effect
> 	  as far as uniqueness is concerned is identical?

As per (2).

> 7. No index exists over (a, b) or (b, a)
>
> 	- Create a new unique index over (a, b)?

Done.

My current code does all of the above, plus will auto-generate constraint
names, save it only looks at combinations of keys, not permutations - so if
a unique key exists on (a,b), you can't add one over (b,a).  I should be
able to fix this in my next hack session tho.  After that I'll check my use
of locking, then I'll submit a patch.

The other issue is that I'm not sure how much argument checking I should do
in my code, and how much I should leave for DefineIndex?

For example, if you have a table with cols 'a' and 'b' and you go ADD UNIQUE
(c), you get something like this:

ERROR: DefineIndex: Attribute 'c' does not exist.

However, this could be slightly odd error for the user of the ALTER
function.  But I guess this kind of thing happens all thru the postgres
code...  Another thing that I let DefineIndex handle is the ADD UNIQUE (a,a)
kind of thing.

Chris


Mon, 23 Jul 2001 10:01:36 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
RE: More ADD CONSTRAINT behaviour questions
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> 1. A non-unique index is already defined over (a, b)
>> 
>> - Either add new index or promote existing one to unique?

> Promoting is in my too-hard basket, so I will simply add a new unique index?
> Too bad if it slows them down, as they should know better?  Should I issue a
> NOTICE warning them that they have overlapping indices?

Seems reasonable.  I suppose dropping the old index wouldn't be a good
idea ;-)

> The other issue is that I'm not sure how much argument checking I should do
> in my code, and how much I should leave for DefineIndex?

I'd say there's no value in expending code space on duplicated error
checks --- *unless* you can give a more specific/appropriate error
message than DefineIndex would.

			regards, tom lane

Sun, 22 Jul 2001 23:04:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: More ADD CONSTRAINT behaviour questions 