<div>Hi again,</div><div> </div><div>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.</div><div>The query intersects two tables with geometry. While i never have had problems with this query before, it is now terminates my postgresql server.</div>
<div>It used to take ~5 min to complete, but now it terminates after 2 minutes.</div><div>When running the query i can see using top that memory rises to 96 % before terminating the postgreql service.</div><div> </div><div>
My setup is Intel(R) Xeon(R) CPU E31270 @ 3.40GHz with 12GB of ram running:</div><div> version<br>----------------------------------------------------------------------------------------------------------------<br>
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<br>(1 row)<br> postgis_full_version<br>----------------------------------------------------------------------------------------------------------------------------------------------------------<br>
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<br>(1 row)<br></div>
<div>shared_buffers = 4GB # min 128kB<br>temp_buffers = 64MB # min 800kB<br>work_mem = 64MB # min 64kB<br>maintenance_work_mem = 512MB # min 1MB<br></div><div>
</div><div>Running the query:</div><div>DROP TABLE IF EXISTS geoepr_cell;<div> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell<br> 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)<br>
AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ;<br></div><div>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.</div>
<div>I tried reducing the shared_buffers and disabling memory overcommit (bottom: <a href="http://www.postgresql.org/docs/9.1/static/kernel-resources.html">http://www.postgresql.org/docs/9.1/static/kernel-resources.html</a>).</div>
<div> </div><div>Any help on this would be very much welcome.</div><div> </div><div>All the best,</div><div>Andreas</div><div> </div><div>Log:</div><div> </div></div><div>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.<br>
2012-02-03 09:27:01 CET HINT: In a moment you should be able to reconnect to the database and repeat your command.<br>2012-02-03 09:27:02 CET LOG: all server processes terminated; reinitializing<br>2012-02-03 09:27:03 CET LOG: database system was interrupted; last known up at 2012-02-03 09:25:37 CET<br>
2012-02-03 09:27:04 CET LOG: database system was not properly shut down; automatic recovery in progress<br>2012-02-03 09:27:04 CET LOG: consistent recovery state reached at 7F/E90EF588<br>2012-02-03 09:27:04 CET LOG: record with zero length at 7F/E90EF588<br>
2012-02-03 09:27:04 CET LOG: redo is not required<br>2012-02-03 09:27:04 CET LOG: database system is ready to accept connections<br>2012-02-03 09:27:04 CET LOG: autovacuum launcher started<br>2012-02-03 09:30:07 CET LOG: incomplete startup packet<br>
2012-02-03 09:30:08 CET LOG: received fast shutdown request<br>2012-02-03 09:30:08 CET LOG: aborting any active transactions<br>2012-02-03 09:30:09 CET LOG: autovacuum launcher shutting down<br>2012-02-03 09:30:09 CET FATAL: terminating connection due to administrator command<br>
2012-02-03 09:30:09 CET FATAL: terminating connection due to administrator command<br>2012-02-03 09:30:09 CET STATEMENT: DROP TABLE IF EXISTS geoepr_cell;</div><div> SELECT a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear INTO geoepr_cell<br>
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)<br> AND g.startyear <= 1946 AND g.endyear >= 1946 GROUP BY a.gid, g.startyear, g.endyear, g.cowgroup, a.gridyear ORDER BY gid ;<br>
2012-02-03 09:31:09 CET LOG: received immediate shutdown request<br>2012-02-03 09:31:09 CET WARNING: terminating connection because of crash of another server process<br>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.<br>
2012-02-03 09:31:09 CET HINT: In a moment you should be able to reconnect to the database and repeat your command.<br></div><div> </div>