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)

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

Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)uk> writes:

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

Uhm. That rather sucks. I was able to reproduce it too.

It seems to happen after I pause for a bit, and not when I run the script in
fast succession.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Gregory Stark wrote:

> Uhm. That rather sucks. I was able to reproduce it too.
>
> It seems to happen after I pause for a bit, and not when I run the script in
> fast succession.

Thanks for the verification Greg. I'm wondering if the GiST part is a
red herring, and in fact it is related to some bizarre interaction
between CLUSTER/VACUUM/autovacuum?

ATB,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063

FWIW,
I have experienced some oddities in performing SELECT statements after
restarting on an 8.2 system, whereby I occasionally would get a ton of
duplicate records when I would do a select statement (my assumption is that
they are deleted tuples being returned). If I executed the same select
statement again, I would get the correct number of records.

Thanks for exploring this issue ladies and gents.

r.b.

Quoting Mark Cave-Ayland <mark(dot)cave-ayland(at)siriusit(dot)co(dot)uk>:

> Gregory Stark wrote:
>
> > Uhm. That rather sucks. I was able to reproduce it too.
> >
> > It seems to happen after I pause for a bit, and not when I run the script
> in
> > fast succession.
>
> Thanks for the verification Greg. I'm wondering if the GiST part is a
> red herring, and in fact it is related to some bizarre interaction
> between CLUSTER/VACUUM/autovacuum?
>
>
> ATB,
>
> Mark.
>
> --
> Mark Cave-Ayland
> Sirius Corporation - The Open Source Experts
> http://www.siriusit.co.uk
> T: +44 870 608 0063
> _______________________________________________
> postgis-devel mailing list
> postgis-devel(at)postgis(dot)refractions(dot)net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>

--
Robert W. Burgholzer
--
Finding the occasional straw of truth awash in a great ocean of confusion and
bamboozle requires intelligence, vigilance, dedication and courage. But if we
don't practice these tough habits of thought, we cannot hope to solve the truly
serious problems that face us -- and we risk becoming a nation of suckers, up
for grabs by the next charlatan who comes along.
-- Carl Sagan, "The Fine Art of Baloney Detection," Parade, February 1, 1987

Web Hydrology Objects - Online Collaborative Modeling:
http://sourceforge.net/projects/npsource/
Home Page:
http://soulswimmer.dynalias.net/

"Robert W. Burgholzer" <rburghol(at)vt(dot)edu> writes:

> FWIW,
> I have experienced some oddities in performing SELECT statements after
> restarting on an 8.2 system, whereby I occasionally would get a ton of
> duplicate records when I would do a select statement (my assumption is that
> they are deleted tuples being returned). If I executed the same select
> statement again, I would get the correct number of records.

Sounds like this item fixed in 8.2.10:

# Fix possible duplicate output of tuples during a GiST index scan (Teodor)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!