Comments on Exclusion Constraints and relateddatatypes
in [PgSql]
|
Prev: ALTER TABLE .... make constraint DEFERRABLE
Next: pgsql: Prevent the injection of invalidly encodedstrings by PL/Python
From: Simon Riggs on 22 Mar 2010 10:28 On Mon, 2010-03-22 at 10:13 -0400, Tom Lane wrote: > 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. 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 -- 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: Peter Eisentraut on 22 Mar 2010 10:40 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. -- 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: Simon Riggs on 22 Mar 2010 11:01 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 -- 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: Simon Riggs on 22 Mar 2010 11:42 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. -- Simon Riggs www.2ndQuadrant.com -- 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: Simon Riggs on 22 Mar 2010 12:47
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |