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