Re: ERROR: Memory exhausted in AllocSetAlloc(188)

Dear Postgresql gurus,

I have a problem (7.3.1 on linux) with a query eating all my memory. First it
take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
issues, could my problem solved by upgrading to 7.3.2?

Thanks a lot, description follows

Tilo

(the query involves a table with 33925848 rows, but only a few thousand rows
should be returned)

tschwarz=# explain select * from feature_point_delta_avg where sequence_id
=325058;
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------
Subquery Scan feature_point_delta_avg (cost=1541301.27..1551163.80
rows=43833 width=28)
-> Aggregate (cost=1541301.27..1551163.80 rows=43833 width=28)
-> Group (cost=1541301.27..1544588.78 rows=438334 width=28)
-> Sort (cost=1541301.27..1542397.11 rows=438334
width=28)
Sort Key: trace.sequence_id, trace.trace_id
-> Hash Join (cost=3424.87..1488310.69 rows=438334
width=28)
Hash Cond: ("outer".image_id =
"inner".image_id)
-> Hash Join (cost=2797.82..1383310.62
rows=13185846 width=20)
Hash Cond: ("outer".trace_id =
"inner".trace_id)
-> Seq Scan on d_kalman
(cost=0.00..1046060.48 rows=33925848 width=12)
-> Hash (cost=2782.76..2782.76
rows=6025 width=8)
-> Seq Scan on trace
(cost=0.00..2782.76 rows=6025 width=8)
Filter: (sequence_id =
325058)
-> Hash (cost=624.26..624.26 rows=1111
width=8)
-> Seq Scan on label_data ld
(cost=0.00..624.26 rows=1111 width=8)
Filter: ((right_eye IS NULL) AND
(visible_features(ld.*) > 0))
(16 rows)

Time: 80.24 ms
tschwarz=# select * from feature_point_delta_avg where sequence_id
=325058;

[... waiting for ca. 10 minutes ...]

ERROR: Memory exhausted in AllocSetAlloc(188)
tschwarz=# show sort_mem ;
sort_mem
----------
16384
(1 row)

On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote:
> Dear Postgresql gurus,
>
> I have a problem (7.3.1 on linux) with a query eating all my memory. First it
> take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
> issues, could my problem solved by upgrading to 7.3.2?
>
> Thanks a lot, description follows
>
> Tilo
>
> (the query involves a table with 33925848 rows, but only a few thousand rows
> should be returned)

Please send the definition of the view and of your tables.

I couldn't immediatly puzzle out your reply email address so you'll just
have to read this in the archive.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington

On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote:
> On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote:
> > Dear Postgresql gurus,
> >
> > I have a problem (7.3.1 on linux) with a query eating all my memory. First it
> > take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
> > issues, could my problem solved by upgrading to 7.3.2?
> >
> > Thanks a lot, description follows
> >
> > Tilo
> >
> > (the query involves a table with 33925848 rows, but only a few thousand rows
> > should be returned)
>

I'm having the same problem...

INSERT INTO zip4 (carrt_id
, add_on_low
, add_on_high)
SELECT cr.carrt_id
, to_number(
CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND'
THEN '-1'
ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no)
END
, '0')
, to_number(
CASE WHEN zip_add_on_high_no LIKE '%ND'
THEN '-1'
ELSE zip_add_on_high_no
END
, '0')
FROM zip_carrt zc, postal_code pc, carrt cr
WHERE pc.postal_code = zc.zip_code
AND cr.postal_code_id = pc.postal_code_id
AND cr.car_rt_code = zc.carrier_route_id
;
ERROR: Memory exhausted in AllocSetAlloc(108)

usps=# \d zip_carrt
Table "public.zip_carrt"
Column | Type | Modifiers
--------------------+----------------------+-----------
zip_code | character varying(5) |
carrier_route_id | character varying(4) |
zip_add_on_low_no | character varying(4) |
zip_add_on_high_no | character varying(4) |

usps=# \d postal_code
Table "public.postal_code"
Column | Type |
Modifiers
----------------+-----------------------+-------------------------------------------------------------------------
postal_code_id | integer | not null default
nextval('public.postal_code_postal_code_id_seq'::text)
postal_code | character varying(10) | not null
state_code | character(2) |
Indexes: postal_code_pkey primary key btree (postal_code_id),
postal_code_postal_code_key unique btree (postal_code)
Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION

usps=# \d carrt
Table "public.carrt"
Column | Type |
Modifiers
----------------+----------------------+-------------------------------------------------------------
carrt_id | integer | not null default
nextval('public.carrt_carrt_id_seq'::text)
postal_code_id | integer | not null
car_rt_code | character varying(5) | not null
Indexes: carrt_pkey primary key btree (carrt_id),
carrt_postal_code_id_key unique btree (postal_code_id,
car_rt_code)
Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ON DELETE NO ACTION

usps=# select count(*) from postal_code;
count
-------
42678
(1 row)

usps=# select count(*) from carrt;
count
--------
627814

zip_carrt is ~35M rows, and zip4 is empty.
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

I happened to catch pgsql is the act:
14692 jnasby 1 10 0 1471M 738M cpu3 52:18 48.02% postgres

Seems like there's definetly some kind of memory leak.

shared_buffers = 5000 # min max_connections*1 or 16, 8KB each
#max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
#max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
wal_buffers = 10
sort_mem = 30000 # min 64, size in KB
vacuum_mem = 16000 # min 1024, size in KB
effective_cache_size = 100000 # typically 8KB each

This is on a sun box with 1G of memory. Also, forgot to describe the
table I'm inserting into:

Table "public.zip4"
Column | Type | Modifiers
-------------+----------+-----------------------------------------------------------
zip4_id | integer | not null default
nextval('public.zip4_zip4_id_seq'::text)
carrt_id | integer | not null
add_on_low | smallint | not null
add_on_high | smallint | not null
Indexes: zip4_pkey primary key btree (zip4_id),
zip4_carrt_id_key unique btree (carrt_id, add_on_low, add_on_high)
Check constraints: "zip4_add_on_low" ((add_on_low >= -1) AND (add_on_low <= 9999))
"zip4_add_on_high" ((add_on_high >= -1) AND (add_on_high <= 9999))
"$1" (add_on_low <= add_on_high)
Foreign Key constraints: $2 FOREIGN KEY (carrt_id) REFERENCES carrt(carrt_id) ON UPDATE NO ACTION ON DELETE NO ACTION

On Mon, May 19, 2003 at 02:15:17PM -0500, Jim C. Nasby wrote:
> On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote:
> > On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote:
> > > Dear Postgresql gurus,
> > >
> > > I have a problem (7.3.1 on linux) with a query eating all my memory. First it
> > > take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
> > > issues, could my problem solved by upgrading to 7.3.2?
> > >
> > > Thanks a lot, description follows
> > >
> > > Tilo
> > >
> > > (the query involves a table with 33925848 rows, but only a few thousand rows
> > > should be returned)
> >
>
> I'm having the same problem...
>
> INSERT INTO zip4 (carrt_id
> , add_on_low
> , add_on_high)
> SELECT cr.carrt_id
> , to_number(
> CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND'
> THEN '-1'
> ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no)
> END
> , '0')
> , to_number(
> CASE WHEN zip_add_on_high_no LIKE '%ND'
> THEN '-1'
> ELSE zip_add_on_high_no
> END
> , '0')
> FROM zip_carrt zc, postal_code pc, carrt cr
> WHERE pc.postal_code = zc.zip_code
> AND cr.postal_code_id = pc.postal_code_id
> AND cr.car_rt_code = zc.carrier_route_id
> ;
> ERROR: Memory exhausted in AllocSetAlloc(108)
>
> usps=# \d zip_carrt
> Table "public.zip_carrt"
> Column | Type | Modifiers
> --------------------+----------------------+-----------
> zip_code | character varying(5) |
> carrier_route_id | character varying(4) |
> zip_add_on_low_no | character varying(4) |
> zip_add_on_high_no | character varying(4) |
>
> usps=# \d postal_code
> Table "public.postal_code"
> Column | Type |
> Modifiers
> ----------------+-----------------------+-------------------------------------------------------------------------
> postal_code_id | integer | not null default
> nextval('public.postal_code_postal_code_id_seq'::text)
> postal_code | character varying(10) | not null
> state_code | character(2) |
> Indexes: postal_code_pkey primary key btree (postal_code_id),
> postal_code_postal_code_key unique btree (postal_code)
> Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> usps=# \d carrt
> Table "public.carrt"
> Column | Type |
> Modifiers
> ----------------+----------------------+-------------------------------------------------------------
> carrt_id | integer | not null default
> nextval('public.carrt_carrt_id_seq'::text)
> postal_code_id | integer | not null
> car_rt_code | character varying(5) | not null
> Indexes: carrt_pkey primary key btree (carrt_id),
> carrt_postal_code_id_key unique btree (postal_code_id,
> car_rt_code)
> Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> usps=# select count(*) from postal_code;
> count
> -------
> 42678
> (1 row)
>
> usps=# select count(*) from carrt;
> count
> --------
> 627814
>
> zip_carrt is ~35M rows, and zip4 is empty.
> --
> Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
> Member: Triangle Fraternity, Sports Car Club of America
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"

--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Are you also using 7.3.1?

Jim C. Nasby wrote:
> I happened to catch pgsql is the act:
> 14692 jnasby 1 10 0 1471M 738M cpu3 52:18 48.02% postgres
>
> Seems like there's definetly some kind of memory leak.
>
> shared_buffers = 5000 # min max_connections*1 or 16, 8KB each
> #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
> #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
> #max_locks_per_transaction = 64 # min 10
> wal_buffers = 10
> sort_mem = 30000 # min 64, size in KB
> vacuum_mem = 16000 # min 1024, size in KB
> effective_cache_size = 100000 # typically 8KB each
>
> This is on a sun box with 1G of memory. Also, forgot to describe the
> table I'm inserting into:
>
> Table "public.zip4"
> Column | Type | Modifiers
> -------------+----------+-----------------------------------------------------------
> zip4_id | integer | not null default
> nextval('public.zip4_zip4_id_seq'::text)
> carrt_id | integer | not null
> add_on_low | smallint | not null
> add_on_high | smallint | not null
> Indexes: zip4_pkey primary key btree (zip4_id),
> zip4_carrt_id_key unique btree (carrt_id, add_on_low, add_on_high)
> Check constraints: "zip4_add_on_low" ((add_on_low >= -1) AND (add_on_low <= 9999))
> "zip4_add_on_high" ((add_on_high >= -1) AND (add_on_high <= 9999))
> "$1" (add_on_low <= add_on_high)
> Foreign Key constraints: $2 FOREIGN KEY (carrt_id) REFERENCES carrt(carrt_id) ON UPDATE NO ACTION ON DELETE NO ACTION
>
> On Mon, May 19, 2003 at 02:15:17PM -0500, Jim C. Nasby wrote:
>
>>On Sat, May 17, 2003 at 01:29:10PM +1000, Martijn van Oosterhout wrote:
>>
>>>On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote:
>>>
>>>>Dear Postgresql gurus,
>>>>
>>>>I have a problem (7.3.1 on linux) with a query eating all my memory. First it
>>>>take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses some memory
>>>>issues, could my problem solved by upgrading to 7.3.2?
>>>>
>>>>Thanks a lot, description follows
>>>>
>>>> Tilo
>>>>
>>>>(the query involves a table with 33925848 rows, but only a few thousand rows
>>>>should be returned)
>>>
>>I'm having the same problem...
>>
>>INSERT INTO zip4 (carrt_id
>> , add_on_low
>> , add_on_high)
>> SELECT cr.carrt_id
>> , to_number(
>> CASE WHEN coalesce(zip_add_on_low_no, zip_add_on_high_no) LIKE '%ND'
>> THEN '-1'
>> ELSE coalesce(zip_add_on_low_no, zip_add_on_high_no)
>> END
>> , '0')
>> , to_number(
>> CASE WHEN zip_add_on_high_no LIKE '%ND'
>> THEN '-1'
>> ELSE zip_add_on_high_no
>> END
>> , '0')
>> FROM zip_carrt zc, postal_code pc, carrt cr
>> WHERE pc.postal_code = zc.zip_code
>> AND cr.postal_code_id = pc.postal_code_id
>> AND cr.car_rt_code = zc.carrier_route_id
>>;
>>ERROR: Memory exhausted in AllocSetAlloc(108)
>>
>>usps=# \d zip_carrt
>> Table "public.zip_carrt"
>> Column | Type | Modifiers
>>--------------------+----------------------+-----------
>> zip_code | character varying(5) |
>> carrier_route_id | character varying(4) |
>> zip_add_on_low_no | character varying(4) |
>> zip_add_on_high_no | character varying(4) |
>>
>>usps=# \d postal_code
>> Table "public.postal_code"
>> Column | Type |
>>Modifiers
>>----------------+-----------------------+-------------------------------------------------------------------------
>> postal_code_id | integer | not null default
>>nextval('public.postal_code_postal_code_id_seq'::text)
>> postal_code | character varying(10) | not null
>> state_code | character(2) |
>>Indexes: postal_code_pkey primary key btree (postal_code_id),
>> postal_code_postal_code_key unique btree (postal_code)
>>Foreign Key constraints: $1 FOREIGN KEY (state_code) REFERENCES state(state_code) ON UPDATE NO ACTION ON DELETE NO ACTION
>>
>>usps=# \d carrt
>> Table "public.carrt"
>> Column | Type |
>>Modifiers
>>----------------+----------------------+-------------------------------------------------------------
>> carrt_id | integer | not null default
>>nextval('public.carrt_carrt_id_seq'::text)
>> postal_code_id | integer | not null
>> car_rt_code | character varying(5) | not null
>>Indexes: carrt_pkey primary key btree (carrt_id),
>> carrt_postal_code_id_key unique btree (postal_code_id,
>>car_rt_code)
>>Foreign Key constraints: $1 FOREIGN KEY (postal_code_id) REFERENCES postal_code(postal_code_id) ON UPDATE NO ACTION ON DELETE NO ACTION
>>
>>usps=# select count(*) from postal_code;
>> count
>>-------
>> 42678
>>(1 row)
>>
>>usps=# select count(*) from carrt;
>> count
>>--------
>> 627814
>>
>>zip_carrt is ~35M rows, and zip4 is empty.
>>--
>>Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
>>Member: Triangle Fraternity, Sports Car Club of America
>>Give your computer some brain candy! www.distributed.net Team #1828
>>
>>Windows: "Where do you want to go today?"
>>Linux: "Where do you want to go tomorrow?"
>>FreeBSD: "Are you guys coming, or what?"
>
>

--
Joseph Shraibman
joseph(at)xtenit(dot)com
Increase signal to noise ratio. http://xis.xtenit.com

On Mon, May 19, 2003 at 08:18:56PM -0400, Joseph Shraibman wrote:
> Are you also using 7.3.1?

7.3.2
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> I'm having the same problem...

How many rows would you expect this command to insert?

You might be running into the fact that pending-trigger-call lists are
kept in memory, but it's hard to tell without knowing the number of
rows involved...

regards, tom lane

On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> > I'm having the same problem...
>
> How many rows would you expect this command to insert?
>
> You might be running into the fact that pending-trigger-call lists are
> kept in memory, but it's hard to tell without knowing the number of
> rows involved...

35M

I re-wrote the query to do a subselect instead of a full join (which
changed the access plan from a hash-join to a subquery), and it looks
like it might finish, but it's still using a heck of a lot of memory...

18252 jnasby 1 40 0 1135M 442M cpu1 290:03 48.07% postgres
19168 jnasby 1 60 0 62M 44M sleep 2:35 8.03% postgres

The second engine is typical of the other engines in terms of memory
usage. Also, I've seen some engines not releasing memory until
termination; there was one that was using 1.6G just sitting at the
prompt.

If it helps, that snapshot is with ~16M tuples in the table, according
to vacuum verbose (started with 0 tuples).
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote:
>> How many rows would you expect this command to insert?
>>
>> You might be running into the fact that pending-trigger-call lists are
>> kept in memory, but it's hard to tell without knowing the number of
>> rows involved...

> 35M

Each pending deferred-trigger action takes about 40 bytes + palloc
overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
that's exactly where your problem is. I'd suggest trying to commit the
changes in smaller batches ...

regards, tom lane

On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> > On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote:
> >> How many rows would you expect this command to insert?
> >>
> >> You might be running into the fact that pending-trigger-call lists are
> >> kept in memory, but it's hard to tell without knowing the number of
> >> rows involved...
>
> > 35M
>
> Each pending deferred-trigger action takes about 40 bytes + palloc
> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
> that's exactly where your problem is. I'd suggest trying to commit the
> changes in smaller batches ...

Ugh... would two triggers double that? Where can I get more info on
what's happening under the covers here, especially on what a deferred
trigger is?
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:
>> Each pending deferred-trigger action takes about 40 bytes + palloc
>> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
>> that's exactly where your problem is. I'd suggest trying to commit the
>> changes in smaller batches ...

> Ugh... would two triggers double that? Where can I get more info on
> what's happening under the covers here, especially on what a deferred
> trigger is?

A deferred trigger is an AFTER trigger. If you can do your work in
BEFORE triggers, you should.

Two triggers firing on the same row action (insert/update/delete) do not
double the memory --- there's one trigger queue entry per action. It
looks like it costs about 8 more bytes for each additional deferred
trigger that needs to be fired on the same row action.

Beyond that, read the code --- it's in backend/commands/trigger.c.

regards, tom lane

On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:

> Each pending deferred-trigger action takes about 40 bytes + palloc
> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
> that's exactly where your problem is. I'd suggest trying to commit the
> changes in smaller batches ...

Probably it's time to add the TODO item:

* Flush deferred trigger queue to disk when it grows too large

The performance is probably awful, but better than failing with that
error anyway...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)

FYI, TODO has:

* Add deferred trigger queue file (Jan)

so it is a known issue.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Jim C. Nasby" <jim(at)nasby(dot)net> writes:
> > On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:
> >> Each pending deferred-trigger action takes about 40 bytes + palloc
> >> overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
> >> that's exactly where your problem is. I'd suggest trying to commit the
> >> changes in smaller batches ...
>
> > Ugh... would two triggers double that? Where can I get more info on
> > what's happening under the covers here, especially on what a deferred
> > trigger is?
>
> A deferred trigger is an AFTER trigger. If you can do your work in
> BEFORE triggers, you should.
>
> Two triggers firing on the same row action (insert/update/delete) do not
> double the memory --- there's one trigger queue entry per action. It
> looks like it costs about 8 more bytes for each additional deferred
> trigger that needs to be fired on the same row action.
>
> Beyond that, read the code --- it's in backend/commands/trigger.c.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Martijn van Oosterhout writes:
> On Sun, May 11, 2003 at 06:21:28PM +0200, Tilo Schwarz wrote:
> > Dear Postgresql gurus,
> >
> > I have a problem (7.3.1 on linux) with a query eating all my memory.
> > First it take 2G RAM and then 2G swap. Now I saw, that 7.3.2 addresses
> > some memory issues, could my problem solved by upgrading to 7.3.2?
> >
> > Thanks a lot, description follows
> >
> > Tilo
> >
> > (the query involves a table with 33925848 rows, but only a few thousand
> > rows should be returned)
>
> Please send the definition of the view and of your tables.

To prevent nasty line breaks, I put the definitions in a text file at:

http://www.tilo-schwarz.de/OutOfMem.txt

In the meantime I read Toms' comments regarding the "pending-trigger-call
lists" - does that apply to my problem too (I have just a SELECT)?

Thanks a lot!

Tilo

PS:
> I couldn't immediatly puzzle out your reply email address so you'll just
> have to read this in the archive.

I "encrypted" my mail address (obviously too complicated), because since I
posted a few times on this list, I get about ten times the amount of spam
than I got before.

Tilo Schwarz <mail(at)tilo-schwarz(dot)de> writes:
> I have a problem (7.3.1 on linux) with a query eating all my memory.
> http://www.tilo-schwarz.de/OutOfMem.txt

You didn't tell us much about the functions involved in these views,
but I suspect the problem has to do with the fact that you're passing
whole-row references (foo.*) to the functions. The mechanism for
handling whole-row references is horrid --- it leaks memory that isn't
reclaimed till end of query. We need to redesign it someday. In the
meantime, try passing just the columns you need, explicitly.

regards, tom lane

We already have this TODO item:

* Add deferred trigger queue file (Jan)

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:
>
> > Each pending deferred-trigger action takes about 40 bytes + palloc
> > overhead, probably 48 bytes altogether ... 48 * 35M comes to 1.68G, so
> > that's exactly where your problem is. I'd suggest trying to commit the
> > changes in smaller batches ...
>
> Probably it's time to add the TODO item:
>
> * Flush deferred trigger queue to disk when it grows too large
>
> The performance is probably awful, but better than failing with that
> error anyway...
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> Oh, oh, las chicas galacianas, lo har?n por las perlas,
> ?Y las de Arrakis por el agua! Pero si buscas damas
> Que se consuman como llamas, ?Prueba una hija de Caladan! (Gurney Halleck)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073