Received: from beamish.nsd.ca (unknown [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id A718C475A08 for ; Wed, 10 Apr 2002 12:29:38 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id MAA07071; Wed, 10 Apr 2002 12:29:42 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma007053; Wed, 10 Apr 02 12:29:11 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id MAA31173; Wed, 10 Apr 2002 12:29:11 -0400 Message-ID: <3CB46888.503DC660@nsd.ca> Date: Wed, 10 Apr 2002 12:30:00 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.9-31 i686) X-Accept-Language: en MIME-Version: 1.0 To: Jeff Eckermann Cc: Rajesh Kumar Mallah , PostgResql SQL Mailing List Subject: Re: Postgresql goes into recovery mode .... References: <20020410155128.61589.qmail@web20809.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200204/121 X-Sequence-Number: 7386 If I reacall, it (UPDATE ... FROM) does not work with 7.1. I think it was added only in 7.2. way is the only way with 7.1. Bottom line Rajesh: you should upgrade to 7.2.1 JLL Jeff Eckermann wrote: > > I think the second subselect (in the EXISTS clause) is > not necessary, as it will always return true if the > where clause in the first subselect (in the SET > clause) is satisfied. > > Another way to write it (untested): > update email_bank set userid = t_a.userid > where email_id = t_a.email_id; > > --- Rajesh Kumar Mallah > wrote: > > Hi , > > > > I ran a query in my server and it runs till my > > pgsql is put in > > recovery mode. > > > > SQL: > > > > UPDATE email_bank set userid=(select userid from > > t_a where > > email_id=email_bank.email_id) > > where exists (select email_id from t_a where > > email_bank.email_id=t_a.email_id) ; > > > > email_bank has 1 million record and t_a as 35K > > records > > > > explain is: > > > > NOTICE: QUERY PLAN: > > > > Seq Scan on email_bank (cost=0.00..628392.37 > > rows=1009182 width=34) > > SubPlan > > -> Index Scan using t_a_1 on t_a > > (cost=0.00..196.12 rows=325 > > width=4) > > -> Index Scan using t_a_1 on t_a > > (cost=0.00..196.12 rows=325 > > width=4) > > > > EXPLAIN > > > > I find that postgresql initially consumes abt, 52 % > > CPU runs for > > sometime and then CPU utilization falls to > > normal , but system load keeps increasing > > > > also pgsql does not respond to cancel requests even. > > > > when i try to make another connection i get the > > error "Database system > > in recovery mode ..." > > > > what am i supposed to do to bring postgresql to an > > usable state from > > here? > > > > system configs are: > > > > database: RH62 + pgsql 7.1.3 + 1 GB ram + dualPIII > > 800 + SCSI HDD > > btw i was using PSQL 7.2 client client to connect to > > PGSQL 7.1.3 (is > > that ok?) > > > > > > > > thanks for help in advance.. > > > > > > regds > > malalh. > > > > > > __________________________________________________ > Do You Yahoo!? > Yahoo! Tax Center - online filing with TurboTax > http://taxes.yahoo.com/ > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)