<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7653.38">
<TITLE>RE: [postgis-devel] CLUSTER in 8.3</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Just filed a bug report about this. I'm convinced its something that is real and should be fixed right away since it means possible loss of data. I was successful in recreating the issue with Kevin's example finally (well I guess misery loves company).<BR>
<BR>
-----------------------------------------------------------------------------------<BR>
Bug report is :Clustering on GIST INDEX clobbers records in table intermittently<BR>
(observed on 8.3.5 installs)<BR>
--Reference number: 4567<BR>
<BR>
This doesn't always happen to me but does intermittently, and for others it happens all the time. Several of us have tried to recreate the issue. For me it happens once in a while on EL 4, 8.3.5 install. We have confirmed it happens on all GIST type indexes.<BR>
<BR>
We have not noticed this problem prior to 8.3.5 -- checkout this thread for details.<BR>
<A HREF="http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004284.html">http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004284.html</A><BR>
<BR>
<A HREF="http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004275.html">http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004275.html</A><BR>
<BR>
<A HREF="http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004283.html">http://postgis.refractions.net/pipermail/postgis-devel/2008-December/004283.html</A><BR>
<BR>
To recreate:<BR>
1) restart your postgresql service<BR>
2) run below<BR>
<BR>
test=# create temp table tmp as select st_makepoint(random(), random()) as the_geom from generate_series(1, 10000);<BR>
SELECT<BR>
test=# create index tmp_geom_idx on tmp using gist (the_geom);<BR>
CREATE INDEX<BR>
test=# analyze tmp;<BR>
ANALYZE<BR>
test=# select count(*) from tmp;<BR>
count<BR>
-------<BR>
10000<BR>
(1 row)<BR>
<BR>
test=# cluster tmp using tmp_geom_idx;<BR>
CLUSTER<BR>
test=# analyze tmp;<BR>
ANALYZE<BR>
test=# select count(*) from tmp;<BR>
count<BR>
-------<BR>
0<BR>
(1 row)<BR>
<BR>
Running the same exercise for me on 8.3.1 always seems to work correctly as far as I can tell.<BR>
<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-devel-bounces@postgis.refractions.net on behalf of Kevin Neufeld<BR>
Sent: Fri 12/5/2008 3:56 PM<BR>
To: PostGIS Development Discussion<BR>
Subject: Re: [postgis-devel] CLUSTER in 8.3<BR>
<BR>
FYI,<BR>
<BR>
I've tried numerous permutations of index type on various data types.<BR>
<BR>
I can't reproduce the problem a btree index on any datatype, but the problem is repeatable on<BR>
- our gist,<BR>
- btree_gist on integers,<BR>
- btree_gist on characters, and<BR>
- btree_gist on text using varchar_pattern_ops.<BR>
<BR>
-- Kevin<BR>
<BR>
Chris Hodgson wrote:<BR>
> Kevin Neufeld wrote:<BR>
>> Mark Cave-Ayland wrote:<BR>
>> > I think the GiST part is a red herring - there is no way that a<BR>
>> "SELECT<BR>
>>> COUNT(*) FROM foo" can use an index in PostgreSQL. My suspicion is<BR>
>>> that it's related to the use of ANALYZE/VACUUM/CLUSTER.<BR>
>>><BR>
>><BR>
>> Maybe. You're right that "SELECT ..." doesn't use the index, but the<BR>
>> CLUSTER physically reorders the table based on the index. If there is<BR>
>> a bizzare bug in our GiST implementation that produces an empty<BR>
>> traversal list some of the time, the table would be empty.<BR>
>><BR>
>> Never mind, I just saw Paul's post. This is good news for us in that<BR>
>> it's not related to our implementation of GiST ... but it still could<BR>
>> be related to PostgreSQL's implementation, no?<BR>
> Well, can anyone replicate the problem by clustering on a non-gist<BR>
> index? If so, then we should really be able to just throw this problem<BR>
> at the Postgres Devs. I'd be surprised if this was the case though, I<BR>
> can't believe no-one else would have come across this yet...<BR>
><BR>
> Chris<BR>
> _______________________________________________<BR>
> postgis-devel mailing list<BR>
> postgis-devel@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
_______________________________________________<BR>
postgis-devel mailing list<BR>
postgis-devel@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-devel">http://postgis.refractions.net/mailman/listinfo/postgis-devel</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
</STRONG></P></BODY></HTML>
<P><hr size=1></P>
<P><STRONG><font size="2" color="339900"> Help make the earth a greener place. If at all possible resist printing this email and join us in saving paper. </p> <p> </font></STRONG></P>