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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

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