Skip site navigation (1) Skip section navigation (2)

Re: operator exclusion constraints

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: operator exclusion constraints
Date: 2010-03-11 05:29:45
Message-ID: 13182.1268285385@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Awhile back I wrote:
> * I'm not too satisfied with the behavior of psql's \d:

> regression=# create table foo (f1 int primary key using index tablespace ts1,
> regression(# f2 int, EXCLUDE USING btree (f2 WITH =) using index tablespace ts1,
> regression(# f3 int, EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED);
> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "foo_f2_exclusion" for table "foo"
> NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "foo_f3_exclusion" for table "foo"
> CREATE TABLE
> regression=# \d foo
>       Table "public.foo"
>  Column |  Type   | Modifiers 
> --------+---------+-----------
>  f1     | integer | not null
>  f2     | integer | 
>  f3     | integer | 
> 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

> regression=# 

> This might have been defensible back when the idea was to keep constraints
> decoupled from indexes, but now it just looks bizarre.  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.

Attached is a patch against HEAD that folds exclusion constraints into
\d's regular indexes list.  With this, the above example produces

      Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 f1     | integer | not null
 f2     | integer | 
 f3     | integer | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (f1), tablespace "ts1"
    "foo_f2_exclusion" EXCLUDE USING btree (f2 WITH =), tablespace "ts1"
    "foo_f3_exclusion" EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED

Any objections?

			regards, tom lane


Attachment: describe-indexes.patch
Description: text/x-patch (9.0 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2010-03-11 05:58:35
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration
Previous:From: Bruce MomjianDate: 2010-03-11 01:39:37
Subject: Re: Re: Hot Standby query cancellation and Streaming Replication integration

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group