[postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table

[René Fournier]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120703/0568199b/attachment.html>