Re: operator exclusion constraints

On Thu, Mar 11, 2010 at 5:29 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Indexes:
>>     "foo_pkey" PRIMARY KEY, btree (f1), tablespace "ts1"
>>     "foo_f2_exclusion" btree (f2), tablespace "ts1"
>>     "foo_f3_exclusion" btree (f3) DEFERRABLE INITIALLY DEFERRED
>> Exclusion constraints:
>>     "foo_f2_exclusion" EXCLUDE USING btree (f2 WITH =)
>>     "foo_f3_exclusion" EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED
>
>> This might have been defensible back when the idea was to keep constraints
>> decoupled from indexes, but now it just looks bizarre.

The only really bizarre part is the "DEFERRABLE INITIALLY DEFERRED" on
the index.

>>  We should either
>> get rid of the "Exclusion constraints:" display and attach the info to
>> the index entries, or hide indexes that are attached to exclusion
>> constraints.  I lean to the former on the grounds of the precedent for
>> unique/pkey indexes --- which is not totally arbitrary, since an index
>> is usable as a query index regardless of its function as a constraint.
>> It's probably a debatable point though.

There is a third option -- print PRIMARY keys twice, once as a btree
index and again as a constraint where it says somehting like "USING
index foo_pkey"
I think in the long term that would be best -- especially if we
combine it with a patch to be able to create a new primary key
constraint using an existing index. That's something people have been
asking for anyways and I think it's a somewhat important property that
these lines can be copy pasted and run nearly as-is to recreate the
objects.

I definitely agree that your other proposed way to go is worse. I
think people need a list of indexes in one place.

So given the current syntax for creating these I think your proposed
change is the least worst alternative.

--
greg