Re: ERROR: Memory exhausted in AllocSetAlloc(188) - Mailing list pgsql-general

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?"