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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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. +
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2010-02-25 23:28:55 | Re: BUG #4769: xmlconcat produces invalid xml values -> data corruption |
Previous Message | Tim Bunce | 2010-02-25 22:16:06 | Re: New PL/Perl failure with Safe 2.2x due to recursion (8.x & 9.0) |