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

Comments on Exclusion Constraints and related datatypes

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Comments on Exclusion Constraints and related datatypes
Date: 2010-03-22 13:15:19
Message-ID: 1269263719.8481.464.camel@ebony (view raw or flat)
Thread:
Lists: pgsql-hackers
Exclusion constraints are good. There's a few annoyances around them,
that are minor but feel should be addressed for 9.0.

* Exclusion indexes are created with the suffix "_exclusion". That's a
very long suffix and will overflow most defined reports/screens. It
would be much better to use just "_excl", so that the number of
characters for index suffixes is always 4 or less characters, whatever
type of index they are.

* Circles, Boxes and other geometric datatypes defined "overlaps" to
include touching shapes. So
SELECT circle '((0,0), 1)' && circle '((2,0),1)';
is true, which is fairly strange and makes those datatypes very counter
intuitive. Considering they are instructional aids, this is bad.

* inet datatypes don't have a commutative operator on which a unique
index can be built. There is no "overlaps" equivalent, which again is a
shame because that stops them being used with the new feature.

That leaves me thinking that we're shipping Postgres 9.0 with a new
feature that isn't real-world usable with built-in datatypes. It's not
even easily usable for demo applications either with the noddy
datatypes. I do appreciate that the main use case is with add-in
datatypes but this is a shame for such a great feature.

Also, if the only common sense usage of exclusion constraints is GIST,
why does the syntax default to "btree"? This means that 

  alter table boxes add exclude using gist (position with &&);

is required, to avoid this ERROR

  alter table boxes add exclude (position with &&);
  ERROR:  data type box has no default operator class for access method
"btree"
  HINT:  You must specify an operator class for the index or define a
default operator class for the data type.

Surely in the absence of a relevant btree operator class we should
automatically check for GIST operator classes?

-- 
 Simon Riggs           www.2ndQuadrant.com


Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2010-03-22 13:39:47
Subject: Re: Proposal: access control jails (and introduction asaspiring GSoC student)
Previous:From: Devrim GÜNDÜZDate: 2010-03-22 13:07:49
Subject: Re: Updated Turkish stopwords list for Tsearch2

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