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

[René Fournier]

Well, I did some more testing, and found out that the failed query and subsequent crash of the postgresql client isn't caused by the lat/lng coordinates... It happens consistently after a certain number of rows (and/or amount of data) is inserted into the table. Usually close to 300 rows (or 96KB of data according to psql).

                                       postgis_full_version                                       
--------------------------------------------------------------------------------------------------
 POSTGIS="1.5.4" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.8" USE_STATS

So, very strange... If I insert programmatically 280-290 rows (varies seemingly depending on the amount of data in each row), then try to add rows one at a time, here's what happens:

mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );
 count 
-------
   345
(1 row)

INSERT 0 1
mydb=# select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );
 count 
-------
   346
(1 row)

The connection to the server was lost. Attempting reset: Failed.
!> select count(*) from addresses;INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 75, ST_GeomFromText('POINT(-114.267388 51.089941)') );
You are currently not connected to a database.

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?


On 2012-07-02, at 1:22 PM, Sandro Santilli wrote:

> Please file a ticket, and report the output of postgis_full_version()
> 
> --strk;
> 
> On Mon, Jul 02, 2012 at 01:10:55PM +0200, René Fournier wrote:
>> If I try to insert a row containing particular coordinate, the query fails and the DB connection is lost. (By comparison, hundreds of inserts of other coordinates work fine.) Here's a straight copy-and-paste comparison from psql:
>> 
>> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.112534 50.895364)') ) RETURNING id;
>> id  
>> -----
>> 333
>> (1 row)
>> 
>> INSERT 0 1
>> mydb=# INSERT INTO addresses ( account_id, territory_id, location ) VALUES ( 1, 0, ST_GeomFromText('POINT(-114.228869 51.152249)') ) RETURNING id;
>> The connection to the server was lost. Attempting reset: Failed.
>> !> 
>> 
>> Here's the table definition:
>> 
>> CREATE TABLE public.addresses
>> (id serial NOT NULL,
>> account_id int NOT NULL,
>> territory_id int NOT NULL,
>> location GEOGRAPHY(POINT,4326),
>> PRIMARY KEY (id));
>> CREATE INDEX location ON addresses USING GIST (location);
>> 
>> Strange right? FWIW, the queries are being generated programmatically by a script, so the error is not caused by a typo, since hundreds of other inserts work. Also, I've done a little research, two interesting findings:
>> 
>> 1. All the multiplied coordinate values (abs(lat)*abs(lng)) of the SUCCESSFUL inserts are LOWER than the coordinates of failed query.
>> 
>> 2. If I create the table without the index on location, the failed inserts suddenly work. So it seems the problem lies with the PostGIS updating the Index -- maybe it doesn't like the size of the values of the larger coordinates?
>> 
>> Anyway, if you have any ideas of what I can do to fix this, I would love to hear them. Thanks!
>> 
>> ...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/ff0d71a9/attachment.html>