Thread

1. (9.1) btree_gist support for searching on "not equals"

   Jeff Davis <pgsql@j-davis.com> — 2010-05-21T20:47:36Z

   This patch adds support to btree_gist for searching on <> ("not
   equals").
   
   This allows an interesting use of exclusion constraints:
   
   Say you have a table:
   
     create table zoo
     (
       cage int,
       animal text,
       exclude using gist (cage with =, animal with <>)
     );
   
   That will permit you to add as many zebras as you want to a given cage,
   and as many lions as you want to another cage, but will not allow you to
   mix zebras and lions in the same cage.
   
   It also allows you to enforce the constraint that only one tuple exists
   in a table by doing something like:
   
     create table a
     (
       i int,
       exclude using gist (i with <>),
       unique (i)
     );
   
   Regards,
   	Jeff Davis
   
   

2. Re: (9.1) btree_gist support for searching on "not equals"

   Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-05-21T22:02:46Z

   On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
   > It also allows you to enforce the constraint that only one tuple exists
   > in a table by doing something like:
   >
   >    create table a
   >    (
   >      i int,
   >      exclude using gist (i with<>),
   >      unique (i)
   >    );
   
   FWIW, this is achievable a lot more easily:
   CREATE UNIQUE INDEX "a_single_row" ON a ((1));
   
   
   Regards,
   Marko Tiikkaja
   
   

3. Re: (9.1) btree_gist support for searching on "not equals"

   Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2010-05-25T01:03:22Z

   Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
   
   > On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
   > > It also allows you to enforce the constraint that only one tuple exists
   > > in a table by doing something like:
   > >
   > >    create table a
   > >    (
   > >      i int,
   > >      exclude using gist (i with<>),
   > >      unique (i)
   > >    );
   
   +1.  I've not read the code, but it might be considerable that we can
   abort index scans if we find a first index entry for "i". While we must
   scan all candidates for "WHERE i <> ?", but we can abort for the constraint
   case because we know existing values are all the same.
   
   > FWIW, this is achievable a lot more easily:
   > CREATE UNIQUE INDEX "a_single_row" ON a ((1));
   
   The former exclusion constraint means "one same value for all rows",
   but your alternative means "a_single_row", right?
   
   Regards,
   ---
   Takahiro Itagaki
   NTT Open Source Software Center
   
   
   
   

4. Re: (9.1) btree_gist support for searching on "not equals"

   Jeff Davis <pgsql@j-davis.com> — 2010-05-25T03:25:18Z

   On Sat, 2010-05-22 at 01:02 +0300, Marko Tiikkaja wrote:
   > On 5/21/10 11:47 PM +0300, Jeff Davis wrote:
   > > It also allows you to enforce the constraint that only one tuple exists
   > > in a table by doing something like:
   > >
   > >    create table a
   > >    (
   > >      i int,
   > >      exclude using gist (i with<>),
   > >      unique (i)
   > >    );
   > 
   > FWIW, this is achievable a lot more easily:
   > CREATE UNIQUE INDEX "a_single_row" ON a ((1));
   > 
   
   Yes, you're right. Also, neither of us accounted for NULLs, so I suppose
   a NOT NULL is necessary as well.
   
   I think the original case (same values only) is potentially useful
   enough that we should support it.
   
   Regards,
   	Jeff Davis
   
   
   

5. Re: (9.1) btree_gist support for searching on "not equals"

   Robert Haas <robertmhaas@gmail.com> — 2010-05-25T03:39:23Z

   On Mon, May 24, 2010 at 11:25 PM, Jeff Davis <pgsql@j-davis.com> wrote:
   > I think the original case (same values only) is potentially useful
   > enough that we should support it.
   
   +1.
   
   -- 
   Robert Haas
   EnterpriseDB: http://www.enterprisedb.com
   The Enterprise Postgres Company