Skip site navigation (1) Skip section navigation (2)

Re: BUG #4806: Bug with GiST index and empty integer array?

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Joerg Kiegeland <kiegeland(at)ikv(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4806: Bug with GiST index and empty integer array?
Date: 2010-02-25 22:29:35
Message-ID: 201002252229.o1PMTZs11530@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
I can reproduce this but in current CVS by installing /contrib/intarray.

---------------------------------------------------------------------------

Joerg Kiegeland wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:      4806
> Logged by:          Joerg Kiegeland
> Email address:      kiegeland(at)ikv(dot)de
> PostgreSQL version: PostgreSQL8.3.7
> Operating system:   Windows XP
> Description:        Bug with GiST index and empty integer array?
> Details: 
> 
> The GiST index seems not to be able to find empty integer arrays. 
> 
> The bug can be easily reproduced on a simple test database:
> 
> 
> 
> To create the table and the data execute: 
> 
> CREATE TABLE test_intarray_table (
>     id text NOT NULL PRIMARY KEY,
>     intarray_column integer[]
> );
> 
> INSERT INTO test_intarray_table (id, intarray_column) VALUES ('x', '{}');
> 
> CREATE INDEX intarray_index ON test_intarray_table USING gist
> (intarray_column);
> 
> 
> 
> 
> To query the data execute:
> 
> SET ENABLE_SEQSCAN TO OFF; --disable sequential scan, which is performed for
> small tables
> 
> SELECT * FROM test_intarray_table WHERE "intarray_column" = '{}';
> 
> 
> 
> 
> 
> The result set of this query does not include the row with id x, though the
> condition should match! When deleting the index intarray_index, x is found!
> 
> So we proposed our customer to simply delete the index. However it goes
> slower then. I would expect the GiST index to either report an error that
> empty array queries are not supported (like GIN index does) or otherwise to
> return the correct result, since the result of a query should be independent
> of an index usage.
> 
> As we reduced this bug to this little example from a very large database and
> a much larger query (took hours for this "simplification"), we disabled the
> sequential scan, however in our large database we could reproduce this error
> without disabling the sequential scan.
> 
> The bug also appears with PostgreSQL8.4 Beta and with PostgreSQL8.3.6.
> 
> -- 
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2010-02-25 23:28:55
Subject: Re: BUG #4769: xmlconcat produces invalid xml values -> data corruption
Previous:From: Tim BunceDate: 2010-02-25 22:16:06
Subject: Re: New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group