Re: ERROR: Memory exhausted in AllocSetAlloc(188)

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