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

[Kevin Neufeld]

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)