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(at)openratings(dot)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(at)trade-india(dot)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(at)postgresql(dot)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/