Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Sat, Dec 19, 2009 at 7:16 PM, Christophe Pettus wrote: > >> I understand that it is not possible to read previous rows without >> creating hack using triggers. > > As noted above, that's not correct. You cannot access new values of a > particular row within a single UPDATE statement, but you do see new values > done in the same transaction. > what´s the problem with something as simple as: create function keep_a_in_b_test1() returns trigger as $$ begin new.b = old.a; return new; end; $$ language plpgsql; create trigger trg_keep_a_in_b_test1 before update on test1 for each row execute procedure keep_a_in_b_test1(); -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
Hi, Andrus, First, it does seem that you are expecting PostgreSQL to have the same behavior as a flat-file manager such as FoxPro (indeed, it seems you'd like PG to have the behavior of a *specific* flat-file manager). Despite the superficial similarity in the command syntax, a modern RDBMS is a very different animal from FoxPro, dBase, 4D and the like, and needs to be approached on its own terms rather than expecting the semantics of commands with the same keyword to be the same. While that may seem to be an irritating and pointless transition, modern RDBMSes are so much more powerful than flat-file managers that you'll find the transition well worth your time. On Dec 20, 2009, at 1:12 AM, Andrus wrote: I tried drop table if exists tt ; create temp table tt ( a int, b int ); insert into tt values ( 1,2); insert into tt values ( 3,4); update tt set a=a*10, b=(select sum(a) from tt); select * from tt b has value 4 for every row. So we *dont* see new values done in the same transaction. You seem to have a specific model for execution in mind, and that model is not the one PostgreSQL (or any other standards-compliant SQL database) will use. Within each UPDATE statement, the UPDATE is operating on a snapshot of the database at the time the command begins execution. That's what the SQL standard requires, as Tom Lane noted earlier. If you want to iterate through each row, applying changes, using PL/ pgSQL with cursors is probably the best solution: http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html If you can be a bit more detailed about what you are trying to accomplish, we can help you more. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
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, 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Sun, Dec 20, 2009 at 2:12 AM, Andrus wrote: >> You cannot access new values of a particular row within a single UPDATE >> statement, but you do see new values done in the same transaction. >> This is explain in some detail in the documentation: >> >> >> http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html#XACT-READ-COMMITTED > > I tried > > drop table if exists tt ; > create temp table tt ( a int, b int ); > insert into tt values ( 1,2); > insert into tt values ( 3,4); > update tt set a=a*10, b=(select sum(a) from tt); > select * from tt > > b has value 4 for every row. > > So we *dont* see new values done in the same transaction. > How to fix ? This isn't broken behaviour. First the inserts run and we have 1,2 3,4 When the update fires, the right hand side of the key/value pairs are evaluated simultaneously based on the data in the table AT THE TIME The query starts. b=sum(a) means b=sum(3,1) which means you're setting b=4... This was explained in a previous post by Tom I believe. Unless I'm missing what you're saying. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
You cannot access new values of a particular row within a single UPDATE statement, but you do see new values done in the same transaction. This is explain in some detail in the documentation: http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html#XACT-READ-COMMITTED I tried drop table if exists tt ; create temp table tt ( a int, b int ); insert into tt values ( 1,2); insert into tt values ( 3,4); update tt set a=a*10, b=(select sum(a) from tt); select * from tt b has value 4 for every row. So we *dont* see new values done in the same transaction. How to fix ? Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Dec 19, 2009, at 4:06 PM, Andrus wrote: 1. In my case b expression needs values from previous rows updated in this same command before: b= (select sum(a) from test1 where ) I believe there is a misunderstanding as to what "read committed" isolation level means. Read committed means that a particular transaction will not see uncommitted work in a *different transaction*. It *does* see uncommitted work done previously in the same transaction. So, if you do: BEGIN; UPDATE table1 SET a=1 WHERE b=2; SELECT a FROM table1 WHERE b=2; You will get back 1, even before a COMMIT. I understand that it is not possible to read previous rows without creating hack using triggers. As noted above, that's not correct. You cannot access new values of a particular row within a single UPDATE statement, but you do see new values done in the same transaction. This is explain in some detail in the documentation: http://www.postgresql.org/docs/8.4/interactive/transaction-iso.html#XACT-READ-COMMITTED 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. If it is an invariant condition of your database schema that two particular columns must always have the same value, a trigger is an appropriate way of enforcing that. It seems that splitting update statement into separate UPDATE commands in proper order, one for every column and commiting transaction after every update is the only solution. Again, it does seem you are not quite understanding what read committed isolation mode actually means; I'd encourage you to read the documentation. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Dec 19, 2009, at 3:34 PM, Andrus wrote: FoxPro's and probably dBase's do it differently. Of course, FoxPro and related are not actually relational databases; they're flat-file managers which use comamnds which somewhat resemble the SQL syntax. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
Christophe, It's not clear why you need to do it this way, though. Presumably, since you did some kind of computation that came up with the number '4', you can assign that value instead of using the field a: UPDATE test1 set a=4, b=4; There are two reasons: 1. In my case b expression needs values from previous rows updated in this same command before: b= (select sum(a) from test1 where ) I understood from replies that set transaction isolation level read uncommitted; in PostgreSql is broken: it sets silently committed isolation level. I understand that it is not possible to read previous rows without creating hack using triggers. 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. It seems that splitting update statement into separate UPDATE commands in proper order, one for every column and commiting transaction after every update is the only solution. Fortunately in my case it is allowed to split every column update to separate transaction. Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
I would be quite surprised if there are any SQL databases that do this differently. FoxPro's and probably dBase's do it differently. CREATE CURSOR t ( a i, b i ) INSERT INTO t VALUES (1,2) UPDATE t SET a=3, b=a SELECT * FROM t returns 3 for b Andrus. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
Christophe Pettus writes: > On Dec 19, 2009, at 11:24 AM, Andrus wrote: >> update test1 set a=4, b=a ; >> How to use updated value ? > The problem here isn't the transaction isolation level. The order of > evaluation in an UPDATE statement is (for practical purposes): > Evaluate all of the right-hand side expressions, and then assign them > all to the left-hand side fields. This is required by the SQL standard, and always has been --- I quote SQL92: 6) The s are effectively evaluated before updat- ing the object row. If a contains a reference to a column of T, then the reference is to the value of that column in the object row before any value of the object row is updated. I would be quite surprised if there are any SQL databases that do this differently. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
On Dec 19, 2009, at 11:24 AM, Andrus wrote: set transaction isolation level read uncommitted; create temp table test1 ( a int, b int) on commit drop; insert into test1 values(1,2); update test1 set a=4, b=a ; select * from test1 b value is 1 but must be 4. How to use updated value ? The problem here isn't the transaction isolation level. The order of evaluation in an UPDATE statement is (for practical purposes): Evaluate all of the right-hand side expressions, and then assign them all to the left-hand side fields. It's not clear why you need to do it this way, though. Presumably, since you did some kind of computation that came up with the number '4', you can assign that value instead of using the field a: UPDATE test1 set a=4, b=4; -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to use read uncommitted transaction level and set update order
2009/12/19 Andrus : > > set transaction isolation level read uncommitted; the "isolation level" is for specifying what rows are visible no for columns. besides, postgres doesn't implement "read uncommitted" > update test1 set a=4, b=a ; > > b value is 1 but must be 4. no. b value "must be" 1, you want it to be 4... in an update the columns always hold the old value until the statement is finished, the only way i can think for doing this is with a trigger -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
