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

[Mark Cave-Ayland]

On 03/07/12 12:34, René Fournier wrote:

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

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?


ATB,

Mark.