Thread: ERROR: Memory exhausted in AllocSetAlloc(188)

ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tilo Schwarz
Date:
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)


Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Martijn van Oosterhout
Date:
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@svana.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

Attachment

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
"Jim C. Nasby"
Date:
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
DELETENO 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@nasby.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?"

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
"Jim C. Nasby"
Date:
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
ONDELETE 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@nasby.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@nasby.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?"

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Joseph Shraibman
Date:
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
ONDELETE 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@nasby.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@xtenit.com
Increase signal to noise ratio.  http://xis.xtenit.com


Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
"Jim C. Nasby"
Date:
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@nasby.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?"

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tom Lane
Date:
"Jim C. Nasby" <jim@nasby.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

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tom Lane
Date:
"Jim C. Nasby" <jim@nasby.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

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
"Jim C. Nasby"
Date:
On Wed, May 21, 2003 at 04:39:18PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.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@nasby.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?"

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
"Jim C. Nasby"
Date:
On Tue, May 20, 2003 at 12:58:41AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.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@nasby.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?"

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tom Lane
Date:
"Jim C. Nasby" <jim@nasby.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

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Bruce Momjian
Date:
FYI, TODO has:

    * Add deferred trigger queue file (Jan)

so it is a known issue.

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

Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tilo Schwarz
Date:
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.



Re: ERROR: Memory exhausted in AllocSetAlloc(188)

From
Tom Lane
Date:
Tilo Schwarz <mail@tilo-schwarz.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