pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql 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