Thread: Memory exhausted in AllocSetAlloc
[Postgresql 7.0.3, intel Linux, 4 cpu, 2 GB RAM ] create table stepparams(step_proc text, name text, txt text, units text, step_ver int2, width int2, xpos int2, param_edit int2, xlevel int2, mandatory int2, primary key(step_proc)); The table stepparams has 15799 rows, 387 distinct values of step_proc. For each value stepparams.step_proc e.g. 'foo', there should be a postgres table named "s_foo". I want to check this for consistancy, i.e. find any stepparams.step_proc that does not have a corresponding table. I thought this was a straight forward query: select distinct step_proc from stepparams sp where not exists (select* from pg_class where text(relname)= 's_' || sp.step_proc); but, after a while I get an error: FATAL 1: Memory exhausted in AllocSetAlloc() pqReadData() -- backend closed the channel unexpectedly. [indeed the backend had grown to 400MB, 173MB resident!] So, I tried another formulation: select distinct sp.step_proc from stepparams sp where substring(sp.step_proc from 2)not in (select sp.step_proc from pg_class); but the same error results. I tried: select distinct step_proc from stepparams sp where not exists (select * from pg_class where text(relname)='s_' || sp.step_proc); but again the same error. What am I doing wrong? Here appended are the explain for these queries: explain select distinct sp.step_proc from stepparams sp where substring(sp.step_proc from 2) not in (select sp.step_procfrom pg_class); NOTICE: QUERY PLAN: Unique (cost=0.00..198558.04 rows=1580 width=12) -> Index Scan using stepparams_idx on stepparams sp (cost=0.00..198518.54rows=15799 width=12) SubPlan -> Seq Scan on pg_class (cost=0.00..24.96 rows=896 width=4) explain select distinct step_proc from stepparams sp where sp.step_proc not in (select 's_' || sp.step_proc from pg_class); NOTICE: QUERY PLAN: Unique (cost=0.00..198518.54 rows=1580 width=12) -> Index Scan using stepparams_idx on stepparams sp (cost=0.00..198479.04rows=15799 width=12) SubPlan -> Seq Scan on pg_class (cost=0.00..24.96 rows=896 width=4) explain select distinct step_proc from stepparams sp where not exists (select * from pg_class where text(relname)= 's_' ||sp.step_proc); NOTICE: QUERY PLAN: Unique (cost=56244.75..56244.75 rows=0 width=12) -> Sort (cost=56244.75..56244.75 rows=1 width=12) -> Seq Scanon stepparams sp (cost=0.00..56244.74 rows=1 width=12) SubPlan -> Seq Scan on pg_class (cost=0.00..31.68rows=9 width=85) -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]
You probably should upgrade to 7.1.2. I think alot of memory leaking conditions were fixed between 7.0 and 7.1. On Wed, 6 Jun 2001, george young wrote: > [Postgresql 7.0.3, intel Linux, 4 cpu, 2 GB RAM ] > > create table stepparams(step_proc text, name text, txt text, units text, step_ver int2, width int2, > xpos int2, param_edit int2, xlevel int2, mandatory int2, primary key(step_proc)); > > The table stepparams has 15799 rows, 387 distinct values of step_proc. > > For each value stepparams.step_proc e.g. 'foo', there should be a postgres table named "s_foo". > I want to check this for consistancy, i.e. find any stepparams.step_proc that does not have a corresponding table. > > I thought this was a straight forward query: > select distinct step_proc > from stepparams sp > where not exists (select * from pg_class where text(relname)= 's_' || sp.step_proc); > but, after a while I get an error: FATAL 1: Memory exhausted in AllocSetAlloc() > pqReadData() -- backend closed the channel unexpectedly. > [indeed the backend had grown to 400MB, 173MB resident!]
george young <gry@ll.mit.edu> writes: > What am I doing wrong? Using 7.0.3 ;-) Update to 7.1.*. It doesn't leak memory during queries (or at least, not as badly as prior releases). regards, tom lane