Prev: [HACKERS] ALTER TABLE .... make constraint DEFERRABLE
Next: ALTER TABLE .... make constraint DEFERRABLE
From: Robert Haas on 22 Mar 2010 09:58
On Mon, Mar 22, 2010 at 9:15 AM, Simon Riggs <simon(a)2ndquadrant.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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 22 Mar 2010 10:13
Simon Riggs <simon(a)2ndQuadrant.com> writes:
> * 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",

No particular objection here.

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

You're approximately twenty years too late to propose changing that,
even if it were clearly a good idea which I doubt.

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

Since your "if" isn't a correct statement, the complaint doesn't follow.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Tom Lane on 22 Mar 2010 10:37
Simon Riggs <simon(a)2ndQuadrant.com> writes:
> On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote:
>>> Also, if the only common sense usage of exclusion constraints is GIST,
>>> why does the syntax default to "btree"?
>>
>> Since your "if" isn't a correct statement, the complaint doesn't follow.

> Docs say
> "The access method must support amgettuple (see Chapter 51); at present
> this means GIN cannot be used. Although it's allowed, there is little
> point in using btree or hash indexes with an exclusion constraint,
> because this does nothing that an ordinary unique constraint doesn't do
> better. So in practice the access method will always be GiST."

Well, I would hope that the lack of GIN support will be cured someday.
I see the above as a statement of what's true in 9.0, not what will
always be true; so it's pretty weak as a justification for introducing a
confusing default behavior.

Actually the statement might be overly strong even now. If you want
uniqueness checks with a hash index, exclusion is the only way to get
that. I'm not sure that that's actually useful versus a standard btree
unique constraint, but it's at least arguably another use case.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 22 Mar 2010 11:32
Simon Riggs <simon(a)2ndQuadrant.com> wrote:
> On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote:
>> Simon Riggs <simon(a)2ndQuadrant.com> writes:

>> > * 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.
>>
>> You're approximately twenty years too late to propose changing
>> that, even if it were clearly a good idea which I doubt.
>
> Possibly. We should at least document that.

Basically, what you feel is missing is documentation that if two
shapes share one or more points they are considered to overlap;
there is no requirement that they share an area?

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: David Fetter on 22 Mar 2010 12:00
On Mon, Mar 22, 2010 at 03:42:39PM +0000, Simon Riggs wrote:
> On Mon, 2010-03-22 at 10:32 -0500, Kevin Grittner wrote:
> > Simon Riggs <simon(a)2ndQuadrant.com> wrote:
> > > On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote:
> > >> Simon Riggs <simon(a)2ndQuadrant.com> writes:
> >
> > >> > * 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.
> > >>
> > >> You're approximately twenty years too late to propose changing
> > >> that, even if it were clearly a good idea which I doubt.
> > >
> > > Possibly. We should at least document that.
> >
> > Basically, what you feel is missing is documentation that if two
> > shapes share one or more points they are considered to overlap;
> > there is no requirement that they share an area?
>
> Yes, for most people touching != overlap. So it just looks like a
> bug.

I don't know which people you've surveyed, but at least in my math
classes, one point in common was sufficient for an overlap. I'd be
happy to write up something that makes this clear.

Cheers,
David.
--
David Fetter <david(a)fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter(a)gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 |  Next  |  Last
Pages: 1 2 3
Prev: [HACKERS] ALTER TABLE .... make constraint DEFERRABLE
Next: ALTER TABLE .... make constraint DEFERRABLE