Обсуждение: 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 -- 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;
Mark Cave-Ayland <mark.cave-ayland@siriusit.co.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
"Robert W. Burgholzer" <rburghol@vt.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!
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.cave-ayland@siriusit.co.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@postgis.refractions.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/