Re: Postgresql goes into recovery mode ....

Dmitry,
Absolutely right.  If I am going to propose solutions
to others, I should think as much about them as I
would my own.  Thank you for straightening me out.
Jeff

--- Dmitry Tkach <dmitry@openratings.com> wrote:
>
> >
> > 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.
> >
>
> It does NOT give you the same result.
> Try this: in your example, after you populate the
> tables, do
> update test1 set this=id;
> select * from test1:
>
>   id | this
> ----+------
>    1 | 1
>    2 | 2
>    3 | 3
>    4 | 4
>
> update test1 set this = (select that from test2
> where id = test1.id);
> select * from test1;
>
>   id | this
> ----+------
>    1 | one
>    2 | two
>    3 |
>    4 |
>
> See? this is not AT ALL what's desired - '3' and '4'
> are gone from test1.this!
>
> Dima
>
> > 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/
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> send an appropriate
> > subscribe-nomail command to
> majordomo@postgresql.org so that your
> > message can get through to the mailing list
> cleanly
> >
>
>
>


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