Something in this chain of changes is probably the cause... P.
On Fri, Feb 3, 2012 at 2:44 AM, Andreas Forø Tollefsen <andrea...@gmail.com> wrote: > Hi again... > > Downgraded to revision 8001 and the query works. > > Query returned successfully: 162082 rows affected, 114992 ms execution time. > > Andreas > > 2012/2/3 Andreas Forø Tollefsen <andrea...@gmail.com> >> >> Hi again, >> >> I have a issue with the server crashing when running a simple query i have >> been using together with previous versions of postgis on the same system. >> The query intersects two tables with geometry. While i never have had >> problems with this query before, it is now terminates my postgresql server. >> It used to take ~5 min to complete, but now it terminates after 2 minutes. >> When running the query i can see using top that memory rises to 96 % >> before terminating the postgreql service. >> >> My setup is Intel(R) Xeon(R) CPU E31270 @ 3.40GHz with 12GB of ram >> running: >> version >> >> ---------------------------------------------------------------------------------------------------------------- >> PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real >> (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit >> (1 row) >> >> postgis_full_version >> >> ---------------------------------------------------------------------------------------------------------------------------------------------------------- >> POSTGIS="2.0.0alpha4SVN" GEOS="3.4.0dev-CAPI-1.8.0" PROJ="Rel. 4.7.1, 23 >> September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" >> USE_STATS >> (1 row) >> shared_buffers = 4GB # min 128kB >> temp_buffers = 64MB # min 800kB >> work_mem = 64MB # min 64kB >> maintenance_work_mem = 512MB # min 1MB >> >> Running the query: >> DROP TABLE IF EXISTS geoepr_cell; >> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO >> geoepr_cell >> FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid AND >> a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, g.geom) >> AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid, g.startyear, >> g.endyear, g.cowgroup, a.gridyear ORDER BY gid ; >> terminates the server with the below messages. I have used the >> query previously without problems, but after updating to latest subversion >> it do not work anymore. I am suspecting there is some memory issue. >> I tried reducing the shared_buffers and disabling memory overcommit >> (bottom: http://www.postgresql.org/docs/9.1/static/kernel-resources.html). >> >> Any help on this would be very much welcome. >> >> All the best, >> Andreas >> >> Log: >> >> 2012-02-03 09:27:01 CET DETAIL: The postmaster has commanded this server >> process to roll back the current transaction and exit, because another >> server process exited abnormally and possibly corrupted shared memory. >> 2012-02-03 09:27:01 CET HINT: In a moment you should be able to reconnect >> to the database and repeat your command. >> 2012-02-03 09:27:02 CET LOG: all server processes terminated; >> reinitializing >> 2012-02-03 09:27:03 CET LOG: database system was interrupted; last known >> up at 2012-02-03 09:25:37 CET >> 2012-02-03 09:27:04 CET LOG: database system was not properly shut down; >> automatic recovery in progress >> 2012-02-03 09:27:04 CET LOG: consistent recovery state reached at >> 7F/E90EF588 >> 2012-02-03 09:27:04 CET LOG: record with zero length at 7F/E90EF588 >> 2012-02-03 09:27:04 CET LOG: redo is not required >> 2012-02-03 09:27:04 CET LOG: database system is ready to accept >> connections >> 2012-02-03 09:27:04 CET LOG: autovacuum launcher started >> 2012-02-03 09:30:07 CET LOG: incomplete startup packet >> 2012-02-03 09:30:08 CET LOG: received fast shutdown request >> 2012-02-03 09:30:08 CET LOG: aborting any active transactions >> 2012-02-03 09:30:09 CET LOG: autovacuum launcher shutting down >> 2012-02-03 09:30:09 CET FATAL: terminating connection due to >> administrator command >> 2012-02-03 09:30:09 CET FATAL: terminating connection due to >> administrator command >> 2012-02-03 09:30:09 CET STATEMENT: DROP TABLE IF EXISTS geoepr_cell; >> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO >> geoepr_cell >> FROM priogridall a, priogrid p, geoepreth2 g WHERE a.gid = p.gid >> AND a.gridyear = 1946 AND a.gwcode = g.cowcode AND ST_Intersects(p.cell, >> g.geom) >> AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid, >> g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ; >> 2012-02-03 09:31:09 CET LOG: received immediate shutdown request >> 2012-02-03 09:31:09 CET WARNING: terminating connection because of crash >> of another server process >> 2012-02-03 09:31:09 CET DETAIL: The postmaster has commanded this server >> process to roll back the current transaction and exit, because another >> server process exited abnormally and possibly corrupted shared memory. >> 2012-02-03 09:31:09 CET HINT: In a moment you should be able to reconnect >> to the database and repeat your command. >> > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
