[postgis-users] Always loosing my data to "invalid memory allocrequest size"

[Mark Cave-Ayland]

Hi Dustin,

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Dustin Moore
> Sent: 15 January 2006 20:06
> To: postgis-users at postgis.refractions.net
> Subject: [postgis-users] Always loosing my data to "invalid memory
> allocrequest size"
> 
> I'm about a week into my experience with PostGIS, but I'm an old hand at
> MySQL.  I have a table 'frame' that has about 1.3 million entries:
> 
>                                          Table "public.frame"
>       Column     |          Type          |
> Modifiers
> ----------------+------------------------+--------------------------------
> --------------------------
>   frame_id       | bigint                 | not null default
> nextval('frame_frame_id_seq'::regclass)
>   frame_file     | character varying(512) | not null
>   frame_number   | integer                | not null
>   frame_rand     | integer                |
>   frame_loc      | geometry               | not null
>   frame_route_id | bigint                 |
> Indexes:
>      "frame_pkey" PRIMARY KEY, btree (frame_id)
>      "frame_file_index" btree (frame_file)
>      "index_frame_loc" gist (frame_loc) CLUSTER
> Check constraints:
>      "enforce_dims_frame_loc" CHECK (ndims(frame_loc) = 2)
>      "enforce_geotype_frame_loc" CHECK (geometrytype(frame_loc) =
> 'POINT'::text OR frame_loc IS NULL)
>      "enforce_srid_frame_loc" CHECK (srid(frame_loc) = 4326)
> 
> 
> It seems that everytime I do a large update I corrupt the database and
> loose everything.
> In particular, a command like this:
> update frame set frame_route_id = route_id from route where
> frame_file=route_file;
> 	[route being a simple table with a btree indexe on route_file]
> 
> I get errors like "ERROR:  invalid memory alloc request size 1671807492"
> and these
> errors won't go away. If I then attempt to pg_dump -Fc it will fail with
> the same error. So
> basically, the data is gone (or at least I can't recover it in its
> entirety).

This does look as if it's being triggered by the PostgreSQL memory
allocator, however as Michael suggests it isn't necessarily caused by an out
of memory condition.

In order to help determine where the problem is, I would suggest the
following:


- Recreate the database cluster using initdb (you can do this in a different
directory if you wish to keep your current directory as a backup).

- Load your data from scratch into the database. pg_dump the loaded
database. Perform your UPDATE query.

- If the crash still occurs, re-load your data on a different server.

- If the crash occurs on a different server, you may have found a bug. Drop
any geometry columns, pg_dump your data again, then pg_restore into a newly
initdb-d cluster *without* PostGIS loaded. If your UPDATE crashes on both
servers, then you need to file a bug report with the PostgreSQL people. If
your UPDATE no longer crashes, then we'll need you to help us find the bug
in PostGIS.


The most probably cause of these issues is bad hardware, so I would suggest
that you check your RAM using MemTest, and run some read/write diagnostics
on your drives to see if the problem could be caused by a
kernel/filesystem/hardware bug.

> I'm running Postgis 1.1.0 on Postgresql 8.2.1 with Geos and Proj on Debian
> stable.
> I compiled Postresql from scratch. I've also had the same problem with
> Postgis 1.0.6.
> My swap file size is 3GB. Are there some memory  or buffer settings I
> should be messing with?

I guess you mean PostgreSQL 8.1.2? Can you confirm the output of SELECT
version()?

> Is there a tested "stable" configuration of Postgis and Postgresql that
> can handle a
> table with 100 million rows?

While there isn't a list of tested "stable" distributions, I know of a
couple of people who are happily running databases of over 400 million rows
without any difficulties. As long as you are running a stable series kernel,
you shouldn't find too many problems. 


Kind regards,

Mark.

------------------------
WebBased Ltd
17 Research Way
Plymouth
PL6 8BT

T: +44 (0)1752 797131
F: +44 (0)1752 791023

http://www.webbased.co.uk   
http://www.infomapper.com
http://www.swtc.co.uk  

This email and any attachments are confidential to the intended recipient
and may also be privileged. If you are not the intended recipient please
delete it from your system and notify the sender. You should not copy it or
use it for any purpose nor disclose or distribute its contents to any other
person.