Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f56GOFE09961 for ; Wed, 6 Jun 2001 12:24:15 -0400 (EDT) (envelope-from gry@ll.mit.edu) Received: (from smtp@localhost) by ll.mit.edu (8.11.3/8.8.8) id f56GO9e09869 for ; Wed, 6 Jun 2001 12:24:10 -0400 (EDT) Received: from UNKNOWN( ), claiming to be "soi.llan" via SMTP by llmail, id smtpdAAAUeayTs; Wed Jun 6 12:24:07 2001 Received: from sty (sty [ ]) by soi.llan (8.8.0/8.8.0) with SMTP id MAA12483 for ; Wed, 6 Jun 2001 12:24:06 -0400 (EDT) Date: Wed, 6 Jun 2001 12:24:06 -0400 From: george young To: pgsql-sql@postgresql.org Subject: Memory exhausted in AllocSetAlloc Message-Id: <20010606122406.122d4a6d.gry@ll.mit.edu> X-Mailer: Sylpheed version 0.4.62 (GTK+ 1.2.8; i586-pc-linux-gnu) Organization: MIT Lincoln Laboratory Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200106/68 X-Sequence-Number: 3286 [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_proc from 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.54 rows=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.04 rows=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 Scan on stepparams sp (cost=0.00..56244.74 rows=1 width=12) SubPlan -> Seq Scan on pg_class (cost=0.00..31.68 rows=9 width=85) -- Your mouse has moved. Windows NT must be restarted for the change to take effect. Reboot now? [OK]