Thread: Programming error: Out of Memory
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 -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arevalo@deimos-space.com http://mobility.grupodeimos.com/ http://gis4free.wordpress.com
Attachment
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