Programming error: Out of Memory

On 11/16/2010 3:55 PM, Jorge Ar�valo wrote:

Hello,

I'm getting an error message in my Python script:

pg.ProgrammingError: ERROR: out of memory
DETAIL: Failed on request of size 16.

The query that caused the error was:

UPDATE table1 SET string = (SELECT string FROM table2 WHERE
ST_Centroid(table1.wkb_geometry)&& table2. the_geom AND
(ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0)
LIMIT 1 OFFSET 0) WHERE string is null;

table1: a table with one geometry column of type "polygon" (square
polygons). 1277500 rows.
table2: a table with one geometry column of type "polygon". A few rows (12).

Both geometry columns have gist indexes over them.

This query was executed inside a loop, with another 10 similar
queries, using table1 against different tables (table2, table3,
table4, etc). The error is not always produced in the same part of the
loop (sometimes with table2, sometimes with table3...). I attach the
relevant part of PostgreSQL log.

My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM
Things I've tried: Increase the SHMMAX kernel variable from 32MB to
128MB. Increase SHMALL too. Change postgresql.conf parameters
shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to
128MB. Didn't work.

I think it's a problem with data, because I've tested this other times
with other data at worked. What could be happening?

Many thanks in advance

Probably the OOM, google: postgres oom
there are lots and lots of pages about it.

-Andy

2010/11/16 Andy Colson <andy@squeakycode.net>:

On 11/16/2010 3:55 PM, Jorge Arévalo wrote:

Hello,

I'm getting an error message in my Python script:

pg.ProgrammingError: ERROR:  out of memory
DETAIL:  Failed on request of size 16.

The query that caused the error was:

UPDATE table1 SET string = (SELECT string FROM table2 WHERE
ST_Centroid(table1.wkb_geometry)&&  table2. the_geom AND
(ST_Distance(ST_Centroid(table1.wkb_geometry), table2.the_geom)<=0)
LIMIT 1 OFFSET 0) WHERE string is null;

table1: a table with one geometry column of type "polygon" (square
polygons). 1277500 rows.
table2: a table with one geometry column of type "polygon". A few rows
(12).

Both geometry columns have gist indexes over them.

This query was executed inside a loop, with another 10 similar
queries, using table1 against different tables (table2, table3,
table4, etc). The error is not always produced in the same part of the
loop (sometimes with table2, sometimes with table3...). I attach the
relevant part of PostgreSQL log.

My enviroment: Ubuntu 10.10, PostgreSQL 8.4, PostGIS 1.5.2, 2GB RAM
Things I've tried: Increase the SHMMAX kernel variable from 32MB to
128MB. Increase SHMALL too. Change postgresql.conf parameters
shared_buffers to 120MB, work_mem to 32MB, effective_cache_size to
128MB. Didn't work.

I think it's a problem with data, because I've tested this other times
with other data at worked. What could be happening?

Many thanks in advance

Probably the OOM, google: postgres oom
there are lots and lots of pages about it.

-Andy

Hi Andy,

Thanks for the tip. The only difference I see is the postgres process
is not killed after the error (maybe is restarted). Anyway, I'm going
to read about it.

Thanks again,

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

I'm getting an error message in my Python script:

pg.ProgrammingError: ERROR: out of memory
DETAIL: Failed on request of size 16.

That's pretty odd because the memory map you provided isn't showing any
particular signs of bloat. I wonder whether either Python or PostGIS
is leaking memory directly (ie, through malloc calls, which wouldn't
show in the memory map).

Can you boil it down to a self-contained test case?

regards, tom lane

On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

I'm getting an error message in my Python script:

pg.ProgrammingError: ERROR:  out of memory
DETAIL:  Failed on request of size 16.

That's pretty odd because the memory map you provided isn't showing any
particular signs of bloat.  I wonder whether either Python or PostGIS
is leaking memory directly (ie, through malloc calls, which wouldn't
show in the memory map).

Can you boil it down to a self-contained test case?

                       regards, tom lane

Before executing the queries, the app reads a small XML file, to get
names and paths, basically. I could simply execute the query in the
console client, to isolate it. Would it be enough?

Just now I'm testing what happens if I add vm.overcommit_memory=2 to
sysctl.conf, and execute sysctl -p. If crashes again, I'll isolate the
query

Thanks again

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Can you boil it down to a self-contained test case?

Before executing the queries, the app reads a small XML file, to get
names and paths, basically. I could simply execute the query in the
console client, to isolate it. Would it be enough?

What I'm hoping for is a SQL script I can run to reproduce the error
from a standing start. Anything you can leave out, or anonymize,
is fine as long as you still get the error.

BTW, PG 8.4.which?

regards, tom lane

On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Can you boil it down to a self-contained test case?

Before executing the queries, the app reads a small XML file, to get
names and paths, basically. I could simply execute the query in the
console client, to isolate it. Would it be enough?

What I'm hoping for is a SQL script I can run to reproduce the error
from a standing start.  Anything you can leave out, or anonymize,
is fine as long as you still get the error.

BTW, PG 8.4.which?

                       regards, tom lane

Still getting the same error. I'm going to simply execute the query in
the client. I'd like to provide you an SQL script. Let me ask about
privacy (doesn't depend on me).

PostgreSQL 8.4.5

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

2010/11/17 Jorge Arévalo <jorge.arevalo@deimos-space.com>:

On Wed, Nov 17, 2010 at 12:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

On Tue, Nov 16, 2010 at 11:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Can you boil it down to a self-contained test case?

Before executing the queries, the app reads a small XML file, to get
names and paths, basically. I could simply execute the query in the
console client, to isolate it. Would it be enough?

What I'm hoping for is a SQL script I can run to reproduce the error
from a standing start.  Anything you can leave out, or anonymize,
is fine as long as you still get the error.

BTW, PG 8.4.which?

                       regards, tom lane

Still getting the same error. I'm going to simply execute the query in
the client. I'd like to provide you an SQL script. Let me ask about
privacy (doesn't depend on me).

PostgreSQL 8.4.5

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

Well, the query works if executed in psql client. Seems to be a Python
problem. I'll execute the rest of the loop queries in the client, just
in case.

But in case is a Python problem, why does it affect the postgres
process, causing a crash?

Thanks again,

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

Well, the query works if executed in psql client. Seems to be a Python
problem. I'll execute the rest of the loop queries in the client, just
in case.

But in case is a Python problem, why does it affect the postgres
process, causing a crash?

Is the query parameterized when issued from Python? If so it might work
differently.

regards, tom lane

On Wed, Nov 17, 2010 at 1:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

Well, the query works if executed in psql client. Seems to be a Python
problem. I'll execute the rest of the loop queries in the client, just
in case.

But in case is a Python problem, why does it affect the postgres
process, causing a crash?

Is the query parameterized when issued from Python?  If so it might work
differently.

                       regards, tom lane

Yes, it takes the table names from another query (select table_names
from other_table), and the fields names from a XML file. Can the query
which fetch the table names cause a memory problem? Seems weird

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

Yes, it takes the table names from another query (select table_names
from other_table), and the fields names from a XML file. Can the query
which fetch the table names cause a memory problem? Seems weird

Well, we were already in pretty-weird territory with an out-of-memory
error and no apparent bloat in the palloc map. I'm still hoping for a
test case ...

regards, tom lane

On Wed, Nov 17, 2010 at 1:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

=?ISO-8859-1?Q?Jorge_Ar=E9valo?= <jorge.arevalo@deimos-space.com> writes:

Yes, it takes the table names from another query (select table_names
from other_table), and the fields names from a XML file. Can the query
which fetch the table names cause a memory problem? Seems weird

Well, we were already in pretty-weird territory with an out-of-memory
error and no apparent bloat in the palloc map.  I'm still hoping for a
test case ...

                       regards, tom lane

Yes, I'll provide you one ASAP (my boss is not here right now, and I
need credentials). Still crashing, so, I'm going to continue working
on it.

Many thanks!

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo@deimos-space.com
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com