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>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: operator exclusion constraints
Date: 2009-12-07 05:26:42
Message-ID: 4253.1260163602@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> [ exclusion constraint patch ]

Applied after quite a lot of editorialization.  For future reference,
here is a summary of what I did:

* Reworded and renamed stuff to try to be consistent about calling these
things "exclusion constraints".  The original code and docs bore quite
a few traces of the various other terminologies, which is not too
surprising given the history but would have been confusing to future
readers of the code.

* Moved the verification of new exclusion constraints into index_build
processing as per discussion.

* Unified the EXCLUDE parsing path with the existing unique/pkey path
by the expedient of adding an excludeOpNames list to IndexStmt.  This
got rid of quite a lot of duplicated code, and fixed a number of bizarre
corner cases like the bogus wording of the index creation NOTICE messages.

* Cleaned up some things that didn't really meet project practices.
To mention a couple: one aspect of the "try to make the patch look
like it had always been there" rule is to insert new stuff in unsurprising
places.  Adding code at the end of a list or file very often doesn't meet
this test.  I tried to put the EXCLUDE constraint stuff beside
UNIQUE/PRIMARY KEY handling where possible.  Another pet peeve that was
triggered a lot in this patch is that you seemed to be intent on fitting
ereport() calls into 80 columns no matter what, and would break the error
message texts in random places to make it fit.  There's a good practical
reason not to do that: it makes it hard to grep the source code for an
error message.  You can break at phrase boundaries if you must, but
in general I prefer to just let the text go past the right margin.

There are a couple of loose ends yet:

* I made CREATE...LIKE processing handle exclusion constraints the same
as unique/pkey constraints, ie, they're processed by INCLUDING INDEXES.
There was some discussion of rearranging things to make these be processed
by INCLUDING CONSTRAINTS instead, but no consensus that I recall.  In
any case, failing to copy them at all is clearly no good.

* 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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Euler Taveira de OliveiraDate: 2009-12-07 05:27:38
Subject: Re: EXPLAIN BUFFERS
Previous:From: KaiGai KoheiDate: 2009-12-07 05:07:27
Subject: Re: [PATCH] Largeobject Access Controls (r2460)

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