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: (view raw or whole 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:

  Bruce Momjian  <bruce(at)momjian(dot)us>
  PG East:
  + 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-2015 The PostgreSQL Global Development Group