Обсуждение: 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? 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
"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
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)
> "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, Pennsylvania19026
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
"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