[postgis-devel] CLUSTER in 8.3

Obe, Regina robe.dnd at cityofboston.gov
Thu Dec 4 22:48:08 PST 2008


On second thought - maybe I'm hallucinating now - I could have sworn when I did this as a single transaction

drop table ttt;
create table ttt as select * from all_counties;
create index ttt_gix on ttt using gist(the_geom);
cluster ttt using ttt_gix;
select count(*) from ttt;


that my table ttt returned 0.

but repeating the same exercise, it seems to be working fine and returning 3141.  

Can't replicate what I was seeing before now.  Hmm some sort of heisenbug it seems.



-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net on behalf of Obe, Regina
Sent: Fri 12/5/2008 1:43 AM
To: PostGIS Development Discussion; PostGIS Development Discussion
Subject: RE: [postgis-devel] CLUSTER in 8.3
 
On further inspection.  If I do this:

drop table ttt;
create table ttt as select * from all_counties;
create index ttt_gix on ttt using gist(the_geom);
cluster ttt using ttt_gix;


--Then do this after the above is done
select count(*) from ttt;

count is 3141

So I think the fact you are doing a count in the same transaction may be clobbering it.


-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net on behalf of Obe, Regina
Sent: Fri 12/5/2008 1:36 AM
To: PostGIS Development Discussion
Subject: RE: [postgis-devel] CLUSTER in 8.3
 
Paul,

Hold on a minute. I was counting the wrong table.  Yap I get the same behavior, so I guess you are not crazy.

drop table ttt;
create table ttt as select * from all_counties;
create index ttt_gix on ttt using gist(the_geom);
cluster ttt using ttt_gix;
select count(*) from ttt;

--count is 0




-----Original Message-----
From: postgis-devel-bounces at postgis.refractions.net on behalf of Paul Ramsey
Sent: Fri 12/5/2008 12:42 AM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] CLUSTER in 8.3
 
And everything works hunky dory with postgis 1.4.0 and pgsql 8.2.11...

On Thu, Dec 4, 2008 at 9:36 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
> I just confirmed this happening under PostGIS 1.3.4 also...
>
> P
>
> On Thu, Dec 4, 2008 at 9:35 PM, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>> You clustered ttt then you counted all_counties...
>>
>> P
>>
>> On Thu, Dec 4, 2008 at 8:44 PM, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
>>> I tried your test on my all_counties table and it worked fine.  But I was
>>> doing it from pgadmin III.  and I have left out that  clusture thing you
>>> were doing.  What does that do? :)
>>>
>>> create table ttt as select * from all_counties;
>>> create index ttt_gix on ttt using gist(the_geom);
>>> cluster ttt using ttt_gix;
>>> select count(*) from all_counties;
>>>
>>> -- gives me 3141 records --
>>>
>>> I guess we must have similar county tables since we have the same record
>>> count.
>>>
>>> My projection is in 3395.
>>>
>>> running
>>> "PostgreSQL 8.3.5 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
>>> 20071124 (Red Hat 4.1.2-42)"
>>> "POSTGIS="1.3.4" GEOS="3.0.3-CAPI-1.4.2" PROJ="Rel. 4.5.0, 22 Oct 2006"
>>> USE_STATS"
>>>
>>> Hope that helps,
>>> Regina
>>>
>>> -----Original Message-----
>>> From: postgis-devel-bounces at postgis.refractions.net on behalf of Paul Ramsey
>>> Sent: Thu 12/4/2008 3:37 PM
>>> To: PostGIS Development Discussion
>>> Subject: Re: [postgis-devel] CLUSTER in 8.3
>>>
>>> Maybe I'm doing something wrong... here's a log of how I exercised this:
>>>
>>> pramsey=# create table ttt as select * from counties;
>>> SELECT
>>> pramsey=# create index ttt_gix on ttt using gist(the_geom);
>>> CREATE INDEX
>>> pramsey=# clusture
>>> pramsey=# select count(*) from ttt;
>>>  count
>>> -------
>>>   3141
>>> (1 row)
>>>
>>> pramsey=# cluster ttt using ttt_gix;
>>> CLUSTER
>>> pramsey=# select count(*) from ttt;
>>>  count
>>> -------
>>>      0
>>> (1 row)
>>>
>>> pramsey=# select version();
>>>                                                            version
>>> ------------------------------------------------------------------------------------------------------------------------------
>>>  PostgreSQL 8.3.5 on i386-apple-darwin9.5.0, compiled by GCC
>>> i686-apple-darwin9-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5465)
>>> (1 row)
>>>
>>> pramsey=# select postgis_full_version();
>>>                                 postgis_full_version
>>> -------------------------------------------------------------------------------------
>>>  POSTGIS="1.3.4SVN" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.6.0, 21 Dec
>>> 2007" USE_STATS
>>> (1 row)
>>>
>>> pramsey=#
>>>
>>>
>>> On Wed, Dec 3, 2008 at 8:43 AM, Kevin Neufeld <kneufeld at refractions.net>
>>> wrote:
>>>> Is seems fine (for Points anyway) on my 8.3.3 install running on an old
>>>> FC3
>>>> box.
>>>>
>>>> Did you run out of disk space?  Did the transaction get half way through
>>>> and
>>>> was forced to stop?
>>>>
>>>> -- Kevin
>>>>
>>>> Paul Ramsey wrote:
>>>>>
>>>>> Is it just me, or has clustering on spatial indexes stopped working in
>>>>> 8.3? First, the syntax of CLUSTER has been changed, from "CLUSTER
>>>>> [index] ON [table]" to "CLUSTER [table] USING [index]". Second, when I
>>>>> run the new syntax using a gist index as the target, the index
>>>>> disappears and all row count drops to zero!
>>>>>
>>>>> P.
>>>>> _______________________________________________
>>>>> postgis-devel mailing list
>>>>> postgis-devel at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>>
>>>> _______________________________________________
>>>> postgis-devel mailing list
>>>> postgis-devel at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>
>>>
>>>
>>>
>>>
>>>
>>> ________________________________
>>>
>>> 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.
>>>
>>> ________________________________
>>>
>>> Help make the earth a greener place. If at all possible resist printing this
>>> email and join us in saving paper.
>>>
>>> _______________________________________________
>>> postgis-devel mailing list
>>> postgis-devel at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>>>
>>>
>>
>
_______________________________________________
postgis-devel mailing list
postgis-devel at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-devel








-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20081205/c5eccfa3/attachment.html>


More information about the postgis-devel mailing list