Re: WIP: generalized index constraints

From: Brendan Jurd <direvus(at)gmail(dot)com>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: generalized index constraints
Date: 2009-08-21 04:34:21
Message-ID: 37ed240d0908202134h9a6d901s8e4aaee36311c45c@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nikhil Sontakke 2009-08-21 05:58:22 Re: UPDATE ... SET (a, b, c) = (expr)
Previous Message Josh Berkus 2009-08-21 04:18:52 Re: Multi-pass planner