Re: WIP: generalized index constraints

2009/8/21 Jeff Davis <pgsql(at)j-davis(dot)com>:
> On Fri, 2009-08-21 at 12:23 +1000, Brendan Jurd wrote:
>> The current behaviour seems to be predicated on the unique constraint
>> being an integral part of the index itself.  While this might be true
>> from a system catalog point of view (pg_index.indisunique), if a user
>> says that they want to copy a table's structure INCLUDING INDEXES
>> EXCLUDING CONSTRAINTS then IMO they've made their intention perfectly
>> clear.  They'd expect it to create an index sans the unique
>> constraint.  Ignoring the user's intention and copying the index as-is
>> (including the unique constraint) would be unfriendly.
>
> I don't have strong feelings either way. I think that's probably a
> separate patch, and a fairly small patch.
>

Yeah, as I was writing the above I was thinking that it might end up a
separate patch. However I was also concerned that it might be less
disruptive if we implement your patch with the less-astonishing
behaviour and fix the unique index case in passing, than to commit
your patch with the bad behavior and then fix both.

Up to you.

> Using the principle of least surprise, if a user specified one of
> INDEXES or CONSTRAINTS, but not both, and there is a unique index, we
> should raise an ERROR (or at least a WARNING).

Actually for what it's worth I would expect INCLUDING INDEXES (with no
mention of what to do about constraints) to go ahead and include
constraints on indexes. I only have strong feelings where the user
has gone to the trouble of explicitly stating that they want indexes
but *not* constraints and we include the constraints anyway.

I would be fine with a NOTICE in the former case, so something like
this would be cool

# CREATE TABLE foo (LIKE bar INCLUDING INDEXES);
NOTICE: INCLUDING INDEXES will also include any constraints on those indexes.
HINT: Specify EXCLUDING CONSTRAINTS to omit them.

To my mind the severity is similar to such notices as "NOTICE: CREATE
TABLE / UNIQUE will create implicit index ...".

i.e., "this is probably what you wanted us to do, but just in case you
weren't expecting this side-effect, we're letting you know about it".

>
> There is not much of a problem with backwards compatibility. LIKE is
> shorthand (not stored in catalogs), so it doesn't affect
> pg_dump/restore. And hopefully there aren't a lot of apps out there
> creating tables dynamically using the LIKE syntax.
>

Well, it wouldn't surprise me if people are using LIKE to produce
tables for partitioning arrangements.

Cheers,
BJ