From mallah@trade-india.com Wed Apr 10 11:22:09 2002 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-- From jeff_eckermann@yahoo.com Wed Apr 10 15:51:24 2002 Received: from web20809.mail.yahoo.com (web20809.mail.yahoo.com [216.136.226.198]) by postgresql.org (Postfix) with SMTP id 0F6F847596C for ; Wed, 10 Apr 2002 11:51:24 -0400 (EDT) Message-ID: <20020410155128.61589.qmail@web20809.mail.yahoo.com> Received: from [68.14.98.218] by web20809.mail.yahoo.com via HTTP; Wed, 10 Apr 2002 08:51:28 PDT Date: Wed, 10 Apr 2002 08:51:28 -0700 (PDT) From: Jeff Eckermann Subject: Re: Postgresql goes into recovery mode .... To: Rajesh Kumar Mallah , PostgResql SQL Mailing List In-Reply-To: <3CB4212C.E16A21A7@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200204/118 X-Sequence-Number: 7383 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/ From jllachan@nsd.ca Wed Apr 10 16:29:38 2002 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) From mallah@trade-india.com Thu Apr 11 06:31:41 2002 Received: from trade-india.com (unknown [130.94.20.28]) by postgresql.org (Postfix) with SMTP id 70FD2475C4A for ; Thu, 11 Apr 2002 02:31:41 -0400 (EDT) Received: (qmail 29905 invoked from network); 11 Apr 2002 06:43:47 -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; 11 Apr 2002 06:43:47 -0000 Message-ID: <3CB52E8B.A7103324@trade-india.com> Date: Thu, 11 Apr 2002 12:04:51 +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: Jean-Luc Lachance Cc: Jeff Eckermann , PostgResql SQL Mailing List Subject: Re: Postgresql goes into recovery mode .... References: <20020410155128.61589.qmail@web20809.mail.yahoo.com> <3CB46888.503DC660@nsd.ca> Content-Type: multipart/alternative; boundary="------------0C56D649D7A1F09A35098E20" X-Archive-Number: 200204/126 X-Sequence-Number: 7391 --------------0C56D649D7A1F09A35098E20 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit jeff i feel the second subselect is required becoz *without* the where clause the query: UPDATE email_bank set userid=(select userid from t_a where email_id=email_bank.email_id) would have updated *all* records in email_bank (1 million) where as i want to update only 35 K in those which are in t_a so "exists" is reqd. i also feel UPDATE .. FROM is better Sql for this purpose which can be written as: update email_bank set userid=t_a.userid from t_a where t_a.email_id = email_bank.email_id ; the above also puts my SQL in abnormnal state. Yes Jean UPDATE .. FROM works in 7.1.3. acutally what i want to know is even if my SQL were wrong what is the way out from the recovery mode? and less importantly is my SQL really wrong? regds mallah. Jean-Luc Lachance wrote: > 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 > (consult mailing list ) --------------0C56D649D7A1F09A35098E20 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit  
jeff  i feel  the  second subselect is  required  becoz *without* the where clause the
query:

UPDATE  email_bank set userid=(select userid  from t_a where
email_id=email_bank.email_id)

would have updated *all* records in email_bank  (1 million) where as
i want to update only 35 K in those which are in t_a  so "exists" is reqd.

i also feel UPDATE .. FROM  is better Sql for this purpose which
can be written as:

update  email_bank set userid=t_a.userid from t_a
where t_a.email_id = email_bank.email_id ;
 

the above also puts  my SQL in  abnormnal state.

Yes Jean  UPDATE .. FROM works in 7.1.3.

acutally  what i want to know  is even if my SQL were wrong
what is the way out  from  the recovery mode? and less importantly
is my SQL really wrong?

regds
mallah.
 
 
 

Jean-Luc Lachance wrote:

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 <mallah@trade-india.com>


 
(consult mailing list )
--------------0C56D649D7A1F09A35098E20-- From jeff_eckermann@yahoo.com Thu Apr 11 14:45:54 2002 Received: from web20809.mail.yahoo.com (web20809.mail.yahoo.com [216.136.226.198]) by postgresql.org (Postfix) with SMTP id 9FBF9476FC5 for ; Thu, 11 Apr 2002 10:45:54 -0400 (EDT) Message-ID: <20020411144605.25000.qmail@web20809.mail.yahoo.com> Received: from [68.14.98.218] by web20809.mail.yahoo.com via HTTP; Thu, 11 Apr 2002 07:46:05 PDT Date: Thu, 11 Apr 2002 07:46:05 -0700 (PDT) From: Jeff Eckermann Subject: Re: Postgresql goes into recovery mode .... To: Rajesh Kumar Mallah Cc: PostgResql SQL Mailing List In-Reply-To: <3CB52E8B.A7103324@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200204/136 X-Sequence-Number: 7401 --- Rajesh Kumar Mallah wrote: > > jeff i feel the second subselect is required > becoz *without* the where > clause the > query: > > UPDATE email_bank set userid=(select userid from > t_a where > email_id=email_bank.email_id) > > would have updated *all* records in email_bank (1 > million) where as > i want to update only 35 K in those which are in t_a > so "exists" is reqd. > True, strictly speaking: but since that statement still gives you the result you want (see example below), I assume that you are concerned about performance issues. You will need to play around with alternatives. A hack that may improve things: 1. select eb.* into temp email_bank_temp from email_bank eb inner join t_a on eb.email_id = t_a.email_id; 2. update email_bank set userid = (select userid from t_a where userid = email_bank.userid); (or whatever your preferred syntax is :-) ). If there are a small number of matches, this could translate into a big win. Test example referred to above: jeff=# create table test1 (id int4, this text); CREATE jeff=# create table test2 (id int4, that text); CREATE jeff=# insert into test1 (id) values (1); INSERT 31237 1 jeff=# insert into test1 (id) values (2); INSERT 31238 1 jeff=# insert into test1 (id) values (3); INSERT 31239 1 jeff=# insert into test1 (id) values (4); INSERT 31240 1 jeff=# insert into test2 values (1, 'one'); INSERT 31241 1 jeff=# insert into test2 values (2, 'two'); INSERT 31242 1 jeff=# update test1 set this = (select that from test2 where id = test1.id); UPDATE 4 jeff=# select * from test1; id | this ----+------ 1 | one 2 | two 3 | 4 | (4 rows) jeff=# select count (*) from test1 where this is null; count ------- 2 (1 row) > i also feel UPDATE .. FROM is better Sql for this > purpose which > can be written as: > > update email_bank set userid=t_a.userid from t_a > where t_a.email_id = email_bank.email_id ; > > > the above also puts my SQL in abnormnal state. > > Yes Jean UPDATE .. FROM works in 7.1.3. > > acutally what i want to know is even if my SQL > were wrong > what is the way out from the recovery mode? and > less importantly > is my SQL really wrong? > > regds > mallah. > > > > > Jean-Luc Lachance wrote: > > > 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 > > > > > (consult mailing list ) > __________________________________________________ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/