[postgis-devel] CLUSTER in 8.3
Paul Ramsey
pramsey at cleverelephant.ca
Fri Dec 5 09:17:57 PST 2008
Well, psql is autocommitting, and if I do the cluster, then disconnect
and reconnect, the count is still zero. I think I just need to get
the older point releases and walk back and see if it goes away.
P.
On Thu, Dec 4, 2008 at 10:43 PM, Obe, Regina <robe.dnd at cityofboston.gov> wrote:
> 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
>
>
>
>
> ________________________________
>
> 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
>
>
More information about the postgis-devel
mailing list