Re: Postgresql goes into recovery mode .... - Mailing list pgsql-sql

From Jeff Eckermann
Subject Re: Postgresql goes into recovery mode ....
Date
Msg-id 20020410155128.61589.qmail@web20809.mail.yahoo.com
Whole thread Raw
In response to Postgresql goes into recovery mode ....  (Rajesh Kumar Mallah <mallah@trade-india.com>)
List pgsql-sql
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 <mallah@trade-india.com>
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/


pgsql-sql by date:

Previous
From: Jeff Eckermann
Date:
Subject: Re: new problem! Is there any Faster workaround ??
Next
From: "Pierre-Andre Michel"
Date:
Subject: How to use an index on a bigint column