Thread: Postgresql goes into recovery mode ....
Hi , <p>I ran a query in my server and it runs till my pgsql is put in recovery mode. <p>SQL: <p><tt>UPDATE email_bankset userid=(select userid from t_a where email_id=email_bank.email_id)</tt><br /><tt>where exists (select email_idfrom t_a where email_bank.email_id=t_a.email_id) ;</tt><tt></tt><p>email_bank has 1 million record and t_a as 35Krecords<tt></tt><p><b>explain is:</b><tt></tt><p><tt>NOTICE: QUERY PLAN:</tt><tt></tt><p><tt>Seq Scan on email_bank (cost=0.00..628392.37 rows=1009182 width=34)</tt><br /><tt> SubPlan</tt><br /><tt> -> Index Scan usingt_a_1 on t_a (cost=0.00..196.12 rows=325 width=4)</tt><br /><tt> -> Index Scan using t_a_1 on t_a (cost=0.00..196.12rows=325 width=4)</tt><tt></tt><p><tt>EXPLAIN</tt><p>I find that postgresql initially consumes abt, 52% CPU runs for sometime and then CPU utilization falls to <br />normal , but system load keeps increasing <p>also pgsqldoes not respond to cancel requests even. <p>when i try to make another connection i get the error "Database systemin recovery mode ..." <p>what am i supposed to do to bring postgresql to an usable state from <br />here? <p>systemconfigs are: <p>database: RH62 + pgsql 7.1.3 + 1 GB ram + dualPIII 800 + SCSI HDD <br />btw i was using PSQL 7.2client client to connect to PGSQL 7.1.3 (is that ok?) <br /> <br /> <p>thanks for help in advance.. <br /> <p>regds<br />malalh. <br />
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/
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> > 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)
<br />jeff i feel the second subselect is required becoz *without* the where clause the <br />query: <p><tt>UPDATE email_bank set userid=(select userid from t_a where</tt><br /><tt>email_id=email_bank.email_id)</tt><p>wouldhave updated *all* records in email_bank (1 million) where as <br />i wantto update only 35 K in those which are in t_a so "exists" is reqd. <p>i also feel UPDATE .. FROM is better Sql forthis purpose which <br />can be written as: <p><tt>update email_bank set userid=t_a.userid from t_a</tt><br /><tt>wheret_a.email_id = email_bank.email_id ;</tt><br /> <p>the above also puts my SQL in abnormnal state. <p>Yes Jean UPDATE .. FROM works in 7.1.3. <p>acutally what i want to know is even if my SQL were wrong <br />what is the wayout from the recovery mode? and less importantly <br />is my SQL really wrong? <p>regds <br />mallah. <br /> <br /> <br /> <p>Jean-Luc Lachance wrote: <blockquote type="CITE">If I reacall, it (UPDATE ... FROM) does not work with 7.1.<br />I think it was added only in 7.2. <p> way is the only way with 7.1. <p>Bottom line Rajesh: you should upgrade to7.2.1 <p>JLL <p>Jeff Eckermann wrote: <br />> <br />> I think the second subselect (in the EXISTS clause) is <br/>> not necessary, as it will always return true if the <br />> where clause in the first subselect (in the SET<br />> clause) is satisfied. <br />> <br />> Another way to write it (untested): <br />> update email_bankset userid = t_a.userid <br />> where email_id = t_a.email_id; <br />> <br />> --- Rajesh Kumar Mallah<mallah@trade-india.com></blockquote><br /> <blockquote type="CITE"><b>(consult mailing list )</b></blockquote>
--- Rajesh Kumar Mallah <mallah@trade-india.com> 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 <mallah@trade-india.com> > > > > > (consult mailing list ) > __________________________________________________ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/