So, it seems that the table gets full and corrupted to some extent. After my
import script inserts ~290 rows, and then postgres crashes...
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
-------
284
(1 row)
The connection to the server was lost. Attempting reset: Failed.
So, can't insert any more rows...
!> delete from addresses where id > 50;
You are currently not connected to a database.
Postgres client/connection is crashed.
!> \q
Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres mydb
psql (9.1.4)
Type "help" for help.
mydb=# delete from addresses where id > 50;
DELETE 234
Deleting rows works...
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
-------
50
(1 row)
The connection to the server was lost. Attempting reset: Failed.
SELECT and DELETE work, but I can't insert any new rows, until...
!> \q
Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres mydb
psql (9.1.4)
Type "help" for help.
mydb=# vacuum;vacuum full;vacuum full analyze;
VACUUM
VACUUM
NOTICE: no notnull values, invalid stats
VACUUM
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id,
territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388
51.089941)') );
count
-------
50
(1 row)
INSERT 0 1
mydb=#
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...
On 2012-07-03, at 2:03 PM, René Fournier wrote:
> On 2012-07-03, at 1:43 PM, Mark Cave-Ayland wrote:
>
>> On 03/07/12 12:34, René Fournier wrote:
>>
>>>
>>> If I quit and restart psql, same error occurs. Only if I drop the table
>>> can I insert more rows — but again, only up to around 300 rows or 100 kb
>>> (not sure where the limit is)... So, it's not dependent on the kind of
>>> coordinates or row data, just the number of inserts and/or the amount of
>>> data. Any ideas what is causing the problem here?
>>
>> Strange - from what you're saying, it sounds as if it could be to do with
>> the index splitting code, but without a backtrace
>> (http://trac.osgeo.org/postgis/wiki/DevWikiGettingABackTrace) it's tricky to
>> say exactly.
>>
>> 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?
>
> Hi Mark,
> 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:
>
> List of relations
> Schema | Name | Type | Owner | Size | Description
> --------+--------------------+----------+----------+------------+-------------
> public | accounts | table | postgres | 8192 bytes |
> public | accounts_id_seq | sequence | postgres | 8192 bytes |
> public | addresses | table | postgres | 96 kB |
> public | addresses_id_seq | sequence | postgres | 8192 bytes |
> public | geography_columns | view | postgres | 0 bytes |
> public | geometry_columns | table | postgres | 8192 bytes |
> public | languages | table | postgres | 8192 bytes |
> public | languages_id_seq | sequence | postgres | 8192 bytes |
> public | spatial_ref_sys | table | postgres | 3000 kB |
> public | territories | table | postgres | 416 kB |
> public | territories_id_seq | sequence | postgres | 8192 bytes |
> public | users | table | postgres | 96 kB |
> public | users_id_seq | sequence | postgres | 8192 bytes |
>
> 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?
>
> ...Rene
>
>
>
> _______________________________________________
> 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
