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

[Mark Cave-Ayland]

On 03/07/12 13:20, René Fournier wrote:

> 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...

Hi René,

I think that you need to create a new bug on the PostGIS bug tracker and 
upload a file that causes the crash on your system, i.e. it can be run 
using "psql -d postgis_db -f crash.sql" so that we can try and reproduce 
what you are seeing.

Also as a matter of interest, do you see anything interesting in the 
PostgreSQL log file at the time of the crash?


ATB,

Mark.