Yes, I did a version of the query where i terminated the connection in the loop after one year, and then reconnected to the server for each year in the query.<div>This did not change anything, and the query still halted on the same year.</div>
<div>Like this:</div><div><div># For each year calculate the distance to border and insert into the borddist table</div><div>yearlist = range(1946, 2009, 1)</div><div>for x in yearlist:</div><div> db1 = psycopg2.connect("host=192.168.1.186 dbname=priogrid user=postgres password=postgres")</div>
<div> cur = db1.cursor()</div><div> print str(time.ctime())+ " Creating borddist for year "+str(x)+"."</div><div> cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, "+str(x)+", MIN(ST_Distance(ST_Transform(a.centroid, 954010), ST_Transform(b.geom, 954010)))/1000 AS borddist \n" \</div>
<div> "FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+" \n" \</div><div> "and a.gwcode = c.gwcode and st_intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")</div>
<div> db1.commit()</div><div> cur.close()</div><div> db1.close()</div><div>db1.commit()</div><div>print str(time.ctime())+ " Done"</div><div>cur.close()</div><div>db1.close()</div></div><div><br></div><div>
I also followed your suggestion to not write any data. Just do a select, without any select into or insert into.</div><div>The same problem occurred.</div><div>Script:</div><div><div> cur.execute("SELECT a.gid, a.gwcode, "+str(x)+", "\</div>
<div> "MIN(ST_Distance(a.centroid, b.geom)) "\</div><div> "FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+" "\</div>
<div> "AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")</div><div> db1.commit()</div><div><br></div><div>Thank you very much for looking into this. I have used over two weeks to try to figure this out.</div>
<div>The only thing i can do is to run the script for 1/2 the years, then restart the server "service postgresql restart" and then run it for the next 1/2.</div><div><br></div><div>Some memory and cpu information.</div>
<div><br></div><div>Here is how it looks in free -m and top when the script have halted.</div></div><div><div><div> total used free shared buffers cached</div></div></div><div><div><div>
Mem: 5977 5371 605 0 139 4735</div><div>-/+ buffers/cache: 495 5481</div><div>Swap: 6075 1 6074</div></div><div><br></div><div><div>top - 09:51:07 up 1 day, 18:44, 2 users, load average: 1.88, 1.32, 1.20</div>
<div>Tasks: 165 total, 2 running, 162 sleeping, 0 stopped, 1 zombie</div><div>Cpu(s): 53.2%us, 1.3%sy, 0.0%ni, 45.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st</div><div>Mem: 6120848k total, 5505868k used, 614980k free, 143004k buffers</div>
<div>Swap: 6221820k total, 1468k used, 6220352k free, 4849556k cached</div><div><br></div><div> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND </div>
<div> 7810 postgres 20 0 2264m 1.0g 1.0g R 100 18.0 988:09.39 postgres </div><div> 1417 andreas 20 0 163m 39m 14m S 3 0.7 3:27.13 compiz </div>
<div> 907 root 20 0 57092 34m 10m S 2 0.6 1:48.73 Xorg </div><div> 7088 andreas 20 0 93012 15m 10m S 2 0.3 0:02.80 gnome-terminal </div>
<div>10772 andreas 20 0 204m 96m 23m S 2 1.6 0:19.88 chromium-browse </div><div> 1525 andreas 20 0 100m 15m 9548 S 1 0.3 5:11.16 unity-panel-ser </div>
<div>10675 andreas 20 0 308m 50m 29m S 1 0.8 0:07.55 chromium-browse </div><div> 1088 root 20 0 9420 1632 964 S 0 0.0 0:07.01 nmbd </div>
<div> 1389 andreas 20 0 5552 2740 700 S 0 0.0 1:17.77 dbus-daemon </div><div>10825 andreas 20 0 35836 19m 6276 S 0 0.3 0:02.94 idle-python2.6 </div>
<div> 1 root 20 0 3040 1780 1220 S 0 0.0 0:02.16 init </div><div> 2 root 20 0 0 0 0 S 0 0.0 0:00.01 kthreadd </div>
<div> 3 root 20 0 0 0 0 S 0 0.0 0:07.21 ksoftirqd/0 </div><div> 6 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0 </div>
<div> 7 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1 </div><div> 9 root 20 0 0 0 0 S 0 0.0 0:03.00 ksoftirqd/1 </div>
<div> 11 root 0 -20 0 0 0 S 0 0.0 0:00.00 cpuset </div></div><div><br></div><div>After i restart the postgresql service:</div><div><div> total used free shared buffers cached</div>
<div>Mem: 5977 4319 1657 0 139 3687</div><div>-/+ buffers/cache: 492 5484</div><div>Swap: 6075 1 6074</div></div><div><br></div><div><div>top - 09:52:33 up 1 day, 18:46, 2 users, load average: 1.42, 1.33, 1.22</div>
<div>Tasks: 164 total, 1 running, 162 sleeping, 0 stopped, 1 zombie</div><div>Cpu(s): 1.3%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st</div><div>Mem: 6120848k total, 4430204k used, 1690644k free, 143092k buffers</div>
<div>Swap: 6221820k total, 1468k used, 6220352k free, 3776880k cached</div><div><br></div><div> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND </div>
<div> 907 root 20 0 52996 30m 6728 S 1 0.5 1:49.78 Xorg </div><div> 1417 andreas 20 0 163m 39m 14m S 1 0.7 3:28.43 compiz </div>
<div> 194 root 20 0 0 0 0 S 0 0.0 0:48.68 usb-storage </div><div> 1447 root 20 0 5564 1000 712 S 0 0.0 0:23.28 udisks-daemon </div>
<div> 1525 andreas 20 0 100m 15m 9548 S 0 0.3 5:11.66 unity-panel-ser </div><div> 1583 andreas 20 0 33552 16m 8984 S 0 0.3 0:03.21 applet.py </div>
<div> 7039 andreas 20 0 44240 23m 6304 S 0 0.4 0:04.51 idle-python2.6 </div><div> 7088 andreas 20 0 93264 15m 10m S 0 0.3 0:03.28 gnome-terminal </div>
<div> 1 root 20 0 3040 1780 1220 S 0 0.0 0:02.16 init </div><div> 2 root 20 0 0 0 0 S 0 0.0 0:00.01 kthreadd </div>
<div> 3 root 20 0 0 0 0 S 0 0.0 0:07.21 ksoftirqd/0 </div><div> 6 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0 </div>
<div> 7 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1 </div><div> 9 root 20 0 0 0 0 S 0 0.0 0:03.00 ksoftirqd/1 </div>
</div><div><br><br><div class="gmail_quote">2011/10/11 Sandro Santilli <span dir="ltr"><<a href="mailto:strk@keybit.net">strk@keybit.net</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="im">On Tue, Oct 11, 2011 at 05:12:24PM +0200, Andreas Forĝ Tollefsen wrote:<br>
> Hi Sandro,<br>
><br>
> What i find strange is that it stops processing at different years on my<br>
> desktop and my laptop. While my desktop stops processing at 1980, my slower<br>
> laptop goes on to 1991 before halting.<br>
> I also tried with different postgresql.conf shared_buffers settings without<br>
> making any difference.<br>
> Therefore it is hard to reproduce this for a single year. I can easily<br>
> process 1980 or 1991 if just running the script for that year.<br>
<br>
</div>But you mentioned you had stopped the backend and restarted for each year ?<br>
Does the problem still occur if you avoid writing any table (could be an I/O<br>
issue) ?<br>
<div><div></div><div class="h5"><br>
--strk;<br>
<br>
() Free GIS & Flash consultant/developer<br>
/\ <a href="http://strk.keybit.net/services.html" target="_blank">http://strk.keybit.net/services.html</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</div></div></blockquote></div><br></div></div>