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

Incorrect cursor behaviour with gist index

From: Martin Schäfer <Martin(dot)Schaefer(at)cadcorp(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Incorrect cursor behaviour with gist index
Date: 2008-09-11 14:23:10
Message-ID: 38D7001B29E62F469A4CB27B323584EF99CE@dev011_ex.Dev.cadcorp.net (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

I'm using PostgreSQL 8.3.1 with PostGIS 1.3.3.

I have the following table:

CREATE TABLE fog_4752 (
    description text,
    gid integer NOT NULL,
    item_class text,
    item_id integer,
    origin_x double precision,
    origin_y double precision,
    origin_z double precision,
    geometry geometry,
    CONSTRAINT enforce_dims_geometry CHECK ((ndims(geometry) = 2)),
    CONSTRAINT enforce_srid_geometry CHECK ((srid(geometry) = 27700)) );

INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 6, 'Polygon', 6, 270463.5995574299, 660527.33722885954, 0, '0103000020346C0000010000000500000042098568C0E014411917774DA44F26419475BFC6784608411917774DA44F26419475BFC678460841936EDB0B1901224142098568C0E01441936EDB0B1901224142098568C0E014411917774DA44F2641');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 4, 'Polygon', 4, 306782.6950348168, 112627.83974142233, 0, '0103000020346C00000100000005000000288A4FB70C430741E06CA5E47060F240EAA4C6336FD11941E06CA5E47060F240EAA4C6336FD1194130FD41FD044F0241288A4FB70C43074130FD41FD044F0241288A4FB70C430741E06CA5E47060F240');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 5, 'Polygon', 5, 224805.30810014351, 415632.86486705049, 0, '0103000020346C00000100000005000000A0BDB7907EBA04415A4590094F4612417256A12EEB1311415A4590094F4612417256A12EEB1311419892D7F01B3B2041A0BDB7907EBA04419892D7F01B3B2041A0BDB7907EBA04415A4590094F461241');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 1, 'Polygon', 1, 317159.57945692743, 809954.47290725145, 0, '0103000020346C0000010000000500000018E0648798E71641E6B7DC1478FF2A415CE0AA36489F0F41E6B7DC1478FF2A415CE0AA36489F0F41861465CF1170264118E0648798E71641861465CF1170264118E0648798E71641E6B7DC1478FF2A41');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 2, 'Polygon', 2, 457247.5191554199, 527703.21662584448, 0, '0103000020346C00000100000005000000B50BF40E7B642041475B545A4EF4224186DD520906081741475B545A4EF4224186DD5209060817411E9EFD061D821A41B50BF40E7B6420411E9EFD061D821A41B50BF40E7B642041475B545A4EF42241');
INSERT INTO fog_4752 (description, gid, item_class, item_id, origin_x, origin_y, origin_z, geometry) VALUES ('Polygon', 3, 'Polygon', 3, 567242.49402979179, 197718.29200272885, 0, '0103000020346C000001000000050000003FCF4C7C885E23415E698CEE51801041BA452CFB42811E415E698CEE51801041BA452CFB42811E41E075E49D8189FE403FCF4C7C885E2341E075E49D8189FE403FCF4C7C885E23415E698CEE51801041');

ALTER TABLE ONLY fog_4752 ADD CONSTRAINT fog_4752_pkey PRIMARY KEY (gid);

CREATE INDEX fog_4752_geometry_sidx ON fog_4752 USING gist (geometry);



Now I'm running these SQL commands:

SET ENABLE_SEQSCAN = OFF;
BEGIN;
DECLARE C63 SCROLL CURSOR FOR
select * from fog_4752
where (geometry && setsrid('BOX(111697.268 85647.94,655446.012 679205.729)'::box2d,27700) and intersects(geometry,'SRID=27700;POLYGON((655446.011617731 679205.729188659,111697.267899139 679205.729188659,111697.267899139 85647.940243935,655446.011617731 85647.940243935,655446.011617731 679205.729188659))'::geometry));
FETCH ABSOLUTE -1 IN C63;
FETCH ABSOLUTE 1 IN C63;
FETCH FORWARD 10 IN C63;
FETCH ABSOLUTE -1 IN C63;
CLOSE C63;
END;

The query used to create the cursor selects 5 of the 6 rows in the table.

The problem is this: The "FETCH ABSOLUTE -1 IN C63" commands return zero rows, when clearly they should return one row, namely the last row in the cursor.

As far as I understand, the ENABLE_SEQSCAN = OFF forces the query to use the gist index, which would otherwise not be used. However, if the cursor would select a suitably sized subset of a large enough table, then the gist index would be used regardless of the setting of ENABLE_SEQSCAN. So simply setting ENABLE_SEQSCAN = ON is not a solution that will work in all cases.

To me this seems to be a bug in PostgreSQL. If it is, can it be fixed?

Regards,

Martin Schäfer
Principal Software Engineer
Cadcorp
Computer Aided Development Corporation Ltd.
1 Heathcock Court, London, WC2R 0NT
martin(dot)schaefer(at)cadcorp(dot)com
www.cadcorp.com

pgsql-bugs by date

Next:From: MuraliDate: 2008-09-13 23:26:54
Subject: BUG #4416: Between operator is not working as expected
Previous:From: Tom LaneDate: 2008-09-10 19:44:00
Subject: Re: Strange behavior for query comparing 8.2.5 and 8.3.0

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