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

Re: Comments on Exclusion Constraints and related datatypes

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Comments on Exclusion Constraints and related datatypes
Date: 2010-03-22 13:58:17
Message-ID: 603c8f071003220658k2624d4d7wa12ec397ebb5065d@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Mar 22, 2010 at 9:15 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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.

I'd be OK with that.

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

As a non-user of geometric datatypes, I have no opinion on this one
way or the other.

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

This would be a nice thing to fix, and I was thinking about doing it,
but I just ran out of time.  I think it can be left for 9.1.  I have
not infrequently wanted to build an IP allocation database, and this
would be perfect for that.

> 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?

That doesn't seem particularly consistent with what we've done elsewhere.

...Robert

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2010-03-22 14:03:57
Subject: Re: Proposal: access control jails (and introduction asaspiring GSoC student)
Previous:From: Robert HaasDate: 2010-03-22 13:54:30
Subject: Re: Proposal: access control jails (and introduction as aspiring GSoC student)

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