Re: [postgis-devel] CLUSTER in 8.3 on GIST indexes break

Mark Cave-Ayland wrote:
> On Thu, 2008-12-04 at 13:51 -0800, Kevin Neufeld wrote:
>
>> Wow, that's bad. I just updated to PostgreSQL 8.3.5 from 8.3.3 and I now get the same thing.
>>
>> test=# create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 10000);
>> SELECT
>> test=# create index tmp_geom_idx on tmp using gist (the_geom);
>> CREATE INDEX
>> test=# analyze tmp;
>> ANALYZE
>> test=# select count(*) from tmp;
>> count
>> -------
>> 10000
>> (1 row)
>>
>> test=# cluster tmp using tmp_geom_idx;
>> CLUSTER
>> test=# analyze tmp;
>> ANALYZE
>> test=# select count(*) from tmp;
>> count
>> -------
>> 0
>> (1 row)
>>
>> test=# select version();
>> version
>> ---------------------------------------------------------------------------------------------------
>> PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
>> (1 row)
>>
>> test=# select postgis_full_version();
>> postgis_full_version
>>
>> -------------------------------------------------------------------------------------------------------------------------------------------
>> POSTGIS="1.4.0SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.4.9, 29 Oct 2004" (procs from 1.4 USE_GEOS=1 USE_PROJ=1
>> USE_STATS=1 need upgrade)
>> (1 row)
>>
>> -- Kevin
>
>
> Yuck. If you can definitely confirm that this works on 8.3.3 but not
> 8.3.5 then it's probably work a post on -hackers :(
>
>
> ATB,
>
> Mark.
>

Confirmed. It seems something changed in GIST from 8.3.3 to 8.3.5

-- 8.3.3 CLUSTER on GIST index works fine.
test=# create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 10000);
SELECT
test=# create index tmp_geom_idx on tmp using gist (the_geom);
CREATE INDEX
test=# analyze tmp;
ANALYZE
test=# select count(*) from tmp;
count
-------
10000
(1 row)

test=# cluster tmp using tmp_geom_idx;
CLUSTER
test=# analyze tmp;
ANALYZE
test=# select count(*) from tmp;
count
-------
10000
(1 row)

test=# select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2)
(1 row)

test=# select postgis_full_version();
postgis_full_version

-------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="1.4.0SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.4.9, 29 Oct 2004" (procs from 1.4 USE_GEOS=1 USE_PROJ=1
USE_STATS=1 need upgrade)
(1 row)