Memory exhausted in AllocSetAlloc
От | george young |
---|---|
Тема | Memory exhausted in AllocSetAlloc |
Дата | |
Msg-id | 20010606122406.122d4a6d.gry@ll.mit.edu обсуждение исходный текст |
Ответы |
Re: Memory exhausted in AllocSetAlloc
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: Memory exhausted in AllocSetAlloc (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
[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]
В списке pgsql-sql по дате отправления: