Received: from trade-india.com (unknown [130.94.20.28]) by postgresql.org (Postfix) with SMTP id B11BB475944 for ; Wed, 10 Apr 2002 07:22:09 -0400 (EDT) Received: (qmail 3786 invoked from network); 10 Apr 2002 11:34:04 -0000 Received: from ptil-235-129-del.primus-india.net (HELO trade-india.com) (203.196.129.235) by 130.94.22.209 with SMTP; 10 Apr 2002 11:34:04 -0000 Message-ID: <3CB4212C.E16A21A7@trade-india.com> Date: Wed, 10 Apr 2002 16:55:33 +0530 From: Rajesh Kumar Mallah X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en MIME-Version: 1.0 To: PostgResql SQL Mailing List Subject: Postgresql goes into recovery mode .... Content-Type: multipart/alternative; boundary="------------34A5BCE016F536D3029831B7" X-Archive-Number: 200204/115 X-Sequence-Number: 7380 --------------34A5BCE016F536D3029831B7 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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. --------------34A5BCE016F536D3029831B7 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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.
  --------------34A5BCE016F536D3029831B7--