Search Postgresql Archives

Re: How to use read uncommitted transaction level and set update order

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



You are confusing a few things, and you don't want to hear the
explanations because they are inconvenient.
 
Andrus wrote:
> 1. In my case b expression needs values from previous rows updated in this
> same command before:

You are confusing "to the left of" and "before".
If you want behaviour that deviates from the SQL standard, you will
usually meet fierce resistance from PostgreSQL.

> I understood from replies that
>
> set transaction isolation level read uncommitted;
>
> in PostgreSql is broken: it sets silently committed isolation level.

You should read this:
http://www.postgresql.org/docs/8.4/static/transaction-iso.html
 
I agree that the behaviour may be surprising, but "broken" is polemic.
This is not always a good comparison when standard behaviour is
concerned, but Oracle does it the same way.

> 2. In my planned UPDATE statement instead of 4 there is an expression
> containing one big CASE WHEN expression with many WHEN .. THEN  clauses.
> This command  takes several hundreds of lines.
> Your solution requires repeating this expression two times and thus makes
> sql difficult to read.

... plus the expression would be evaluated twice. But you cannot hold that against
the person who gave you the advice, because you hid that fact.
 
Why don't you let your imagination play a little:
 
1) You could use a subquery like
  UPDATE foo SET col = myex
  FROM (SELECT foo_id, <your 100 lines here> AS myex FROM whatever ...) AS bar
  WHERE foo.foo_id = bar.foo_id;
2) You could define a stable SQL function for your 100 line subquery which
  should be evaluated only once in the UPDAT query.
 
Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux