[postgis-devel] Issue 48 in postgis: Broken scroll cursors with gist index

[codesite-noreply at google.com]

Issue 48: Broken scroll cursors with gist index
http://code.google.com/p/postgis/issues/detail?id=48

New issue report by martin.f.schaefer:
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.

I think that the setting of ENABLE_SEQSCAN would be irrelevant if the
cursor selected a small enough subset of a large enough table.

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

I have also submitted a bug report against PostgreSQL, as I'm not sure
whether this is a PostgreSQL or a PostGIS issue.

Regards,

Martin Schäfer


Issue attributes:
	Status: New
	Owner: ----
	Labels: Type-Defect Priority-Medium

-- 
You received this message because you are listed in the owner
or CC fields of this issue, or because you starred this issue.
You may adjust your issue notification preferences at:
http://code.google.com/hosting/settings