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

Hi Kevin,

Yeah I see exactly the same problem on 8.3.5 too, although it seems
random - what seems to happen is that sometimes the contents of the
temporary table disappears. I've attached a test script which causes the
error *some* of the time, although it tends to occur more often just
after the server has been restarted.

I've been invoking the attached script against a PostgreSQL
8.3.5/PostGIS 1.3.4 installation, and when the bug hits I see the
following psql output against a freshly restarted server:


postgis13=# \i /tmp/postgis-strange.sql
SELECT
CREATE INDEX
ANALYZE
  count
-------
  10000
(1 row)

CLUSTER
ANALYZE
  count
-------
  10000
(1 row)

postgis13=# \i /tmp/postgis-strange.sql
psql:/tmp/postgis-strange.sql:2: ERROR:  relation "tmp" already exists
psql:/tmp/postgis-strange.sql:3: ERROR:  relation "tmp_geom_idx" already
exists
ANALYZE
  count
-------
  10000
(1 row)

CLUSTER
ANALYZE
  count
-------
      0
(1 row)

postgis13=# \i /tmp/postgis-strange.sql
psql:/tmp/postgis-strange.sql:2: ERROR:  relation "tmp" already exists
psql:/tmp/postgis-strange.sql:3: ERROR:  relation "tmp_geom_idx" already
exists
ANALYZE
  count
-------
      0
(1 row)

CLUSTER
ANALYZE
  count
-------
      0
(1 row)


So in other words, the contents of the temporary table has just
disappeared :(


ATB,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
-- Count script
create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 10000);
create index tmp_geom_idx on tmp using gist (the_geom);
analyze tmp;
select count(*) from tmp;
cluster tmp using tmp_geom_idx;
analyze tmp;
select count(*) from tmp;