[postgis-devel] Broken scroll cursors on gist index with enable_seqscan=off
Mark Cave-Ayland
mark.cave-ayland at siriusit.co.uk
Thu Aug 21 03:15:45 PDT 2008
Martin Schäfer wrote:
> Hi,
>
> I'm using PostGIS 1.3.1 on PostgreSQL 8.2.4. But the same problem has also been reported for PostGIS 1.3.3 on PostgreSQL 8.3.1.
>
> 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 "FETCH ABSOLUTE -1 IN C63" commands return zero rows, when clearly they should return one row, namely the last row in the cursor, which is not empty.
>
> If I change to ENABLE_SEQSCAN = ON, then the FETCH behave as expected.
> Also, the combination ENABLE_SEQSCAN = OFF and removing the where clause from the DECLARE statement means that all the FETCH behave as expected too.
> So somehow the combination of ENABLE_SEQSCAN = OFF *AND* the use of a gist index causes the scroll cursor to break.
>
> To me this seems to be a bug. If it is, can it be fixed?
>
> Martin Schaefer
> Cadcorp Ltd.
Interesting. PostgreSQL is very good at hiding implementation detail,
i.e. the result set you receive is a scan result, regardless of whether
it came from a full heap scan or an index scan. The only time we've seen
this kind of bug before is when we had a bug in the GiST R-Tree index
routines, although I would expect that a bug in the && would have shown
up a long time before now...
The key question to ask is: does the same thing happen if you build an
index on a non-PostGIS datatype and an index scan is used within a
cursor? I would suggest using contrib/btree_gist to build a similar GiST
index on a int field or similar to make things as similar as possible.
Without spending more time on this now, I can't say outright whether it
is a PostgreSQL/GiST issue or a PostGIS issue. If you can't recreate
this using a GiST index on a non-PostGIS datatype then I'd suggest
adding it to the PostGIS bug tracker and we'll start adding it to the
TODO list :)
HTH,
Mark.
--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
More information about the postgis-devel
mailing list