Re: Postgresql goes into recovery mode ....

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(at)trade-india(dot)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/