From: | "Jim C(dot) Nasby" <jim(at)nasby(dot)net> |
---|---|
To: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ERROR: Memory exhausted in AllocSetAlloc(188) |
Date: | 2003-05-19 19:15:17 |
Message-ID: | 20030519191517.GG40542@flake.decibel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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?"
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Foster | 2003-05-19 20:09:41 | Re: PostgreSQL Performance on OpenBSD |
Previous Message | Chris Palmer | 2003-05-19 17:20:37 | Re: PostgreSQL Performance on OpenBSD |
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2003-05-19 21:37:08 | Re: ERROR: Memory exhausted in AllocSetAlloc(188) |
Previous Message | Jim C. Nasby | 2003-05-19 19:10:35 | Re: Feature suggestions (long) |