Re: Postgresql goes into recovery mode ....

--- Rajesh Kumar Mallah <mallah(at)trade-india(dot)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(at)trade-india(dot)com>
>
>
>
> > (consult mailing list )
>

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/