pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: Rajesh Kumar Mallah <mallah@trade-india.com>
To: PostgResql SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Postgresql goes into recovery mode ....
Date: Wed, 10 Apr 2002 08:51:28 -0700 (PDT)
Message-ID: <20020410155128.61589.qmail@web20809.mail.yahoo.com> (raw)
In-Reply-To: <3CB4212C.E16A21A7@trade-india.com>

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/



view thread (5+ messages)  latest in thread

Message-ID: <20020410155128.61589.qmail@web20809.mail.yahoo.com>
Permalink:  ../20020410155128.61589.qmail@web20809.mail.yahoo.com/
Also on:    postgresql.org/message-id/20020410155128.61589.qmail@web20809.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jeff_eckermann@yahoo.com, mallah@trade-india.com
  Subject: Re: Postgresql goes into recovery mode ....
  In-Reply-To: <20020410155128.61589.qmail@web20809.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox