<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">So, it seems that the table gets full and corrupted to some extent. After my import script inserts ~290 rows, and then postgres crashes...<div><br></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div style="text-align: left;">mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );</div></div><div><div style="text-align: left;"> count </div></div><div><div style="text-align: left;">-------</div></div><div><div style="text-align: left;"> 284</div></div><div><div style="text-align: left;">(1 row)</div></div><div><div style="text-align: left;"><br></div></div><div><div style="text-align: left;">The connection to the server was lost. Attempting reset: Failed.</div></div><div style="text-align: left;"><br></div></blockquote>So, can't insert any more rows...<br><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div style="text-align: left;"><br></div><div><div style="text-align: left;">!> delete from addresses where id > 50;</div></div><div><div style="text-align: left;">You are currently not connected to a database.</div></div></blockquote><div><br></div>Postgres client/connection is crashed.<div><br><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div style="text-align: left;">!> \q</div></div><div><div style="text-align: left;">Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4)</div></div><div><div style="text-align: left;">Type "help" for help.</div></div><div><div style="text-align: left;"><br></div></div><div><div style="text-align: left;">mydb=# delete from addresses where id > 50;</div></div><div><div style="text-align: left;">DELETE 234</div></div><div style="text-align: left;"><br></div></blockquote></div><div>Deleting rows works...</div><div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div style="text-align: left;"><br></div><div><div style="text-align: left;">mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );</div></div><div><div style="text-align: left;"> count </div></div><div><div style="text-align: left;">-------</div></div><div><div style="text-align: left;"> 50</div></div><div><div style="text-align: left;">(1 row)</div></div><div><div style="text-align: left;"><br></div></div><div><div style="text-align: left;">The connection to the server was lost. Attempting reset: Failed.</div></div></blockquote><div><br></div>SELECT and DELETE work, but I can't insert any new rows, until...<br><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div style="text-align: left;"><br></div><div><div style="text-align: left;">!> \q</div></div><div><div style="text-align: left;">Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb psql (9.1.4)</div></div><div><div style="text-align: left;">Type "help" for help.</div></div><div><div style="text-align: left;"><br></div></div><div><div style="text-align: left;">mydb=# vacuum;vacuum full;vacuum full analyze;</div></div><div><div style="text-align: left;">VACUUM</div></div><div><div style="text-align: left;">VACUUM</div></div><div><div style="text-align: left;">NOTICE: no notnull values, invalid stats</div></div><div><div style="text-align: left;">VACUUM</div></div><div><div style="text-align: left;">mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );</div></div><div><div style="text-align: left;"> count </div></div><div><div style="text-align: left;">-------</div></div><div><div style="text-align: left;"> 50</div></div><div><div style="text-align: left;">(1 row)</div></div><div><div style="text-align: left;"><br></div></div><div><div style="text-align: left;">INSERT 0 1</div></div><div><div style="text-align: left;">mydb=# </div></div><div style="text-align: left;"><br></div></blockquote>So, it appears there's some weird corruption going on... Still, not sure what to try next. My PostGIS is via Macports, not sure how to enable the debug mode...<br><div><br><div><div>On 2012-07-03, at 2:03 PM, René Fournier wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><div>On 2012-07-03, at 1:43 PM, Mark Cave-Ayland wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>On 03/07/12 12:34, René Fournier wrote:<br><br><blockquote type="cite"><br></blockquote><blockquote type="cite">If I quit and restart psql, same error occurs. Only if I drop the table<br></blockquote><blockquote type="cite">can I insert more rows — but again, only up to around 300 rows or 100 kb<br></blockquote><blockquote type="cite">(not sure where the limit is)... So, it's not dependent on the kind of<br></blockquote><blockquote type="cite">coordinates or row data, just the number of inserts and/or the amount of<br></blockquote><blockquote type="cite">data. Any ideas what is causing the problem here?<br></blockquote><br>Strange - from what you're saying, it sounds as if it could be to do with the index splitting code, but without a backtrace (<a href="http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace">http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace</a>) it's tricky to say exactly.<br><br>One small nit - I notice you've got a geography column but you're using ST_GeomFromText() rather than ST_GeogFromText() which is likely introducing an extra cast somewhere. Does changing to ST_GeogFromText() help prevent the crash at all?<br></div></blockquote></div><br><div>Hi Mark,</div><div>Thanks for the tip... I changed to GeogFromText(), but the error/crash still occurs, at the same point... After the crash, here's what the tables look like:</div><div><br></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div> List of relations</div></div><div><div> Schema | Name | Type | Owner | Size | Description </div></div><div><div>--------+--------------------+----------+----------+------------+-------------</div></div><div><div> public | accounts | table | postgres | 8192 bytes | </div></div><div><div> public | accounts_id_seq | sequence | postgres | 8192 bytes | </div></div><div><div> public | addresses | table | postgres | 96 kB | </div></div><div><div> public | addresses_id_seq | sequence | postgres | 8192 bytes | </div></div><div><div> public | geography_columns | view | postgres | 0 bytes | </div></div><div><div> public | geometry_columns | table | postgres | 8192 bytes | </div></div><div><div> public | languages | table | postgres | 8192 bytes | </div></div><div><div> public | languages_id_seq | sequence | postgres | 8192 bytes | </div></div><div><div> public | spatial_ref_sys | table | postgres | 3000 kB | </div></div><div><div> public | territories | table | postgres | 416 kB | </div></div><div><div> public | territories_id_seq | sequence | postgres | 8192 bytes | </div></div><div><div> public | users | table | postgres | 96 kB | </div></div><div><div> public | users_id_seq | sequence | postgres | 8192 bytes | </div></div><div><br></div></blockquote>Is it strange that the geography_columns view is 0 bytes? Even though both territories and addresses have a geography column (POLYGON and POINT respectively) and an associated GIST index?<div><br></div><div>...Rene</div><div><br></div><div><br><br></div></div>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></blockquote></div><br></div></div></body></html>