Re: Postgresql goes into recovery mode ....

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