Thread: Comments on Exclusion Constraints and related datatypes
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
On Mon, Mar 22, 2010 at 9:15 AM, Simon Riggs <simon@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
Simon Riggs <simon@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
On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote: > Simon Riggs <simon@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. OK, will change. > > * 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. > > 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." Hence my comment. -- Simon Riggs www.2ndQuadrant.com
Simon Riggs <simon@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
On mån, 2010-03-22 at 13:15 +0000, Simon Riggs wrote: > * 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. http://pgfoundry.org/projects/ip4r/ provides an overlap operator.
On Mon, 2010-03-22 at 16:40 +0200, Peter Eisentraut wrote: > On mån, 2010-03-22 at 13:15 +0000, Simon Riggs wrote: > > * 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. > > http://pgfoundry.org/projects/ip4r/ provides an overlap operator. Like I said, nothing in the base server. -- Simon Riggs www.2ndQuadrant.com
Simon Riggs <simon@2ndQuadrant.com> wrote: > On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote: >> Simon Riggs <simon@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
On Mon, 2010-03-22 at 10:32 -0500, Kevin Grittner wrote: > Simon Riggs <simon@2ndQuadrant.com> wrote: > > On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote: > >> Simon Riggs <simon@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. -- Simon Riggs www.2ndQuadrant.com
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@2ndQuadrant.com> wrote: > > > On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote: > > >> Simon Riggs <simon@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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
Simon Riggs <simon@2ndQuadrant.com> wrote: > Yes, for most people touching != overlap. So it just looks like a > bug. A quick search of the web turned up a definition of overlap in geometry as meaning that two polygons share at least one *internal* point, which would be consistent with your interpretation; but there is the issue of breaking existing code. Perhaps people are now accustomed to following the existing overlaps test with a test that the area of intersection is non-zero? Anyway, based on what I found, we should document the current behavior, as the term in PostgreSQL doesn't seem to match the conventional definition in geometry. -Kevin
Simon Riggs wrote: >> >> 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 guess I must be strange, then, it doesn't strike me as particularly unnatural. By all means document it, though, if the docs are not clear on the point. cheers andrew
On Mon, 2010-03-22 at 09:00 -0700, David Fetter wrote: > > 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. If you're happy to document it, good, thanks. -- Simon Riggs www.2ndQuadrant.com
On Mon, Mar 22, 2010 at 04:47:37PM +0000, Simon Riggs wrote: > On Mon, 2010-03-22 at 09:00 -0700, David Fetter wrote: > > > > 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. > > If you're happy to document it, good, thanks. I'm seeing a lot of places where this might be documented. Any ideas as to which ones are appropriate? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
David Fetter <david@fetter.org> writes:
> I'm seeing a lot of places where this might be documented. Any ideas
> as to which ones are appropriate?
I would think "Geometric Functions and Operators" would be the most
appropriate spot ...
regards, tom lane
On Mon, Mar 22, 2010 at 01:46:51PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > I'm seeing a lot of places where this might be documented. Any ideas > > as to which ones are appropriate? > > I would think "Geometric Functions and Operators" would be the most > appropriate spot ... Please find patch enclosed. :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@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
Attachment
David Fetter wrote:
> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> index 9881ff4..9313112 100644
> --- a/doc/src/sgml/func.sgml
> +++ b/doc/src/sgml/func.sgml
> @@ -7134,7 +7134,7 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
> </row>
> <row>
> <entry> <literal>&&</literal> </entry>
> - <entry>Overlaps?</entry>
> + <entry>Overlaps? One point in common makes this true.</entry>
> <entry><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></entry>
> </row>
> <row>
Hmm, how does this look in horizontal space? (The <row> makes me think
it's a table.)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Mon, Mar 22, 2010 at 04:04:16PM -0300, Alvaro Herrera wrote:
> David Fetter wrote:
>
> > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> > index 9881ff4..9313112 100644
> > --- a/doc/src/sgml/func.sgml
> > +++ b/doc/src/sgml/func.sgml
> > @@ -7134,7 +7134,7 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
> > </row>
> > <row>
> > <entry> <literal>&&</literal> </entry>
> > - <entry>Overlaps?</entry>
> > + <entry>Overlaps? One point in common makes this true.</entry>
> > <entry><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></entry>
> > </row>
> > <row>
>
> Hmm, how does this look in horizontal space? (The <row> makes me think
> it's a table.)
Looks OK to me. The entry above, "Closest point to first operand on
second operand" is actually wider.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@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
On Mon, Mar 22, 2010 at 1:15 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > > * Circles, Boxes and other geometric datatypes defined "overlaps" to > include touching shapes. So > > * 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. I think our unusual data types are one of the strong points of Postgres but they're missing a lot of operators and opclasses to make them really useful. There's no reason we couldn't have separate overlaps and overlaps-internally operators just like we have <=,>= and <,>. And it would be nice to flesh out the network data type more fully, perhaps merging in as much of ip4r as makes sense. I remember when I tried to use geometric data types I was stymied by missing operators. In particular I was surprised that point <in> box wasn't a gist indexable method. I think that particular case has been addressed but I think there are many more like it. -- greg
On Mon, 2010-03-22 at 10:55 -0700, David Fetter wrote: > On Mon, Mar 22, 2010 at 01:46:51PM -0400, Tom Lane wrote: > > David Fetter <david@fetter.org> writes: > > > I'm seeing a lot of places where this might be documented. Any ideas > > > as to which ones are appropriate? > > > > I would think "Geometric Functions and Operators" would be the most > > appropriate spot ... > > Please find patch enclosed. :) I think you've missed my point. I don't suppose anybody would disagree that box '((0,0),(1,1))' && box '((0,0),(2,2))' is true. The fact that these two boxes share the point (0,0) has nothing to do with the obvious fact that the larger box completely contains the smaller one. What I was talking about was that box '((0,0),(1,1))' && box '((1,1),(2,2))' returns true, even though they touch at only a single point, and share zero area. Perhaps the end result here is the realisation that a normal search operator like overlaps (&&) might not be the right operator to use for exclusion constraints. For the latter, we might want something that defines the left and bottom edges of a box to belong to the box (>=), whereas the top edges are the boundary but do not belong to the box itself (<). In a timerange that might be easier to visualise: the start timestamp is inclusive, the end timestamp is exclusive, so that each timestamp exists in only one timerange when the timeranges are overlapping with no gaps. -- Simon Riggs www.2ndQuadrant.com
Simon Riggs <simon@2ndQuadrant.com> wrote: > I think you've missed my point. > What I was talking about was that box '((0,0),(1,1))' && box > '((1,1),(2,2))' returns true, even though they touch at only a > single point, and share zero area. FWIW, that's what I would take away from "one point in common" -Kevin
David Fetter wrote:
> On Mon, Mar 22, 2010 at 04:04:16PM -0300, Alvaro Herrera wrote:
> > David Fetter wrote:
> >
> > > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
> > > index 9881ff4..9313112 100644
> > > --- a/doc/src/sgml/func.sgml
> > > +++ b/doc/src/sgml/func.sgml
> > > @@ -7134,7 +7134,7 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple
> > > </row>
> > > <row>
> > > <entry> <literal>&&</literal> </entry>
> > > - <entry>Overlaps?</entry>
> > > + <entry>Overlaps? One point in common makes this true.</entry>
> > > <entry><literal>box '((0,0),(1,1))' && box '((0,0),(2,2))'</literal></entry>
> > > </row>
> > > <row>
> >
> > Hmm, how does this look in horizontal space? (The <row> makes me think
> > it's a table.)
>
> Looks OK to me. The entry above, "Closest point to first operand on
> second operand" is actually wider.
Patch applied.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ None of us is going to be here forever. +
Greg Stark wrote:
> On Mon, Mar 22, 2010 at 1:15 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> >
> > * Circles, Boxes and other geometric datatypes defined "overlaps" to
> > include touching shapes. So
> >
> > * 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.
>
> I think our unusual data types are one of the strong points of
> Postgres but they're missing a lot of operators and opclasses to make
> them really useful.
>
> There's no reason we couldn't have separate overlaps and
> overlaps-internally operators just like we have <=,>= and <,>. And it
> would be nice to flesh out the network data type more fully, perhaps
> merging in as much of ip4r as makes sense.
Added to TODO:
Add overlaps geometric operators that ignore point overlaps
* http://archives.postgresql.org/pgsql-hackers/2010-03/msg00861.php
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ None of us is going to be here forever. +
Robert Haas wrote: > > * 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. Added to TODO: Add INET overlaps operator, for use by exclusion constraints * http://archives.postgresql.org/pgsql-hackers/2010-03/msg00845.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +