[postgis-users] ordering spatial data in tables - WARNING! BUG?

[Kevin Neufeld]

This is a known PostgreSQL 8.3.5 bug (#4567).

http://archives.postgresql.org/pgsql-hackers/2008-12/msg00278.php
http://archives.postgresql.org/pgsql-bugs/2008-12/msg00037.php

It looks like the there's fix for it as well.

Cheers,
Kevin


Ben Madin wrote:
> G'day Vincent,
> 
> This didn't work as anticipated - in fact it appears to have truncated 
> the table!
> 
> On 08/01/2009, at 5:01 AM, postgis-users-request at postgis.refractions.net 
> <mailto:postgis-users-request at postgis.refractions.net> wrote:
> 
>> Another and more <classic> solution (despite slower on big tables) to
>> keep spatially close rows together on disk is to use postgresql CLUSTER
>> statement.
>> http://www.postgresql.org/docs/8.3/static/sql-cluster.html
>>
>> CLUSTER reorders data on disk using a specified index, therefore
>> increasing access speed when reading rows in the index order.
>>
>> Example :
>> CREATE INDEX idx_mytable_the_geom ON mytable USING GIST(the_geom);
>> CLUSTER mytable USING idx_mytable_the_geom;
> 
> CLUSTER works on a normal index :
> 
> # select count(*) from gis_roads;
>  count 
> -------
>  89618
> (1 row)
> 
> # CLUSTER gis_roads USING gis_roads_gid_key;
> 
> CLUSTER
> # select count(*) from gis_roads;
>  count 
> -------
>  89618
> (1 row)
> 
> all good!
> 
> But when I try on the gist index 
> 
> 
> # CLUSTER gis_roads USING gis_roads_gist;
> CLUSTER
> 
> # select count(*) from gis_roads;
>  count 
> -------
>      0
> (1 row)
> 
> whoops!
> 
> Is this something I missed? Is this a PostgreSQL bug... surely the 
> cluster command shouldn't be allowed to destroy a whole table.
> 
> cheers
> 
> Ben
> 
> 
> 
> 
> 
> -- 
> 
> Ben Madin
> REMOTE INFORMATION
> 
> t : +61 8 9192 5455
> f : +61 8 9192 5535
> m : 0448 887 220
> Broome   WA   6725
> 
> ben at remoteinformation.com.au <mailto:ben at remoteinformation.com.au>
> 
> 
> 
> Out here, it pays to know...
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users