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