pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgresql goes into recovery mode ....
5+ messages / 3 participants
[nested] [flat]

* Postgresql goes into recovery mode ....
@ 2002-04-10 11:25  Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Rajesh Kumar Mallah @ 2002-04-10 11:25 UTC (permalink / raw)
  To: pgsql-sql

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.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Postgresql goes into recovery mode ....
@ 2002-04-10 15:51  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jeff Eckermann @ 2002-04-10 15:51 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; 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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Postgresql goes into recovery mode ....
@ 2002-04-10 16:30  Jean-Luc Lachance <jllachan@nsd.ca>
  parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jean-Luc Lachance @ 2002-04-10 16:30 UTC (permalink / raw)
  To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: Rajesh Kumar Mallah <mallah@trade-india.com>; pgsql-sql

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)



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Postgresql goes into recovery mode ....
@ 2002-04-11 06:34  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Jean-Luc Lachance <jllachan@nsd.ca>
  0 siblings, 1 reply; 5+ messages in thread

From: Rajesh Kumar Mallah @ 2002-04-11 06:34 UTC (permalink / raw)
  To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: Jeff Eckermann <jeff_eckermann@yahoo.com>; pgsql-sql


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 )

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Postgresql goes into recovery mode ....
@ 2002-04-11 14:46  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jeff Eckermann @ 2002-04-11 14:46 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-sql


--- 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/



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2002-04-11 14:46 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-10 11:25 Postgresql goes into recovery mode .... Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-10 15:51 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-04-10 16:30   ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-04-11 06:34     ` Rajesh Kumar Mallah <mallah@trade-india.com>
2002-04-11 14:46       ` Jeff Eckermann <jeff_eckermann@yahoo.com>

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