PostgreSQL – Order of Execution in SET Clause of UPDATE Query

postgresqlsql-standardupdate

I recently came across this strange behaviour in Postgres. I had a table like the following one:

sasdb=# \d emp_manager_rel 
           Table "db3004db.emp_manager_rel"
   Column   |  Type  | Collation | Nullable | Default 
------------+--------+-----------+----------+---------
 emp_id     | bigint |           |          | 
 manager_id | bigint |           |          | 

select * from emp_manager_rel ;
 emp_id | manager_id 
--------+------------
      1 |        123

I executed the following update statement :

UPDATE 1:

update emp_manager_rel set manager_id = manager_id+emp_id , emp_id=emp_id*4;

which update the table like follow:

 emp_id | manager_id 
--------+------------
      4 |        124

UPDATE 2:
I executed the following query (on the original table, not on the updated)

update emp_manager_rel set  emp_id=emp_id*4 , manager_id = manager_id+emp_id ;

it updates the table like follows:

 emp_id | manager_id 
--------+------------
      4 |        124

I am expecting the value of manager_id on UPDATE 2 to be 127 (because emp_id has been changed to 4 by empid * 4). But, both UPDATES produce the same result. I wonder what will be the order of execution of set clause in ANSI standard.

Best Answer

There is nothing "strange" about this behaviour - it's the only sane way to process such an update.

There is no such thing as "order of updates" - all SET clauses should be assumed to happen in parallel.

The SQL standard requires that the right hand side of the assignment is the value of the columns as it was before the UPDATE statement started. The order in which the column assignments are listed in the UPDATE statement is irrelevant for the outcome of the UPDATE statement.

Any other behaviour of the UPDATE statement would be a bug and a violation of the SQL standard.

For the same reason, the following statement will swap the two column values:

update some_table
   set x = y, y = x;

update some_table
   set y = x, x = y;

Related Solutions

Postgresql – In Postgres, How to merge two tables

One approach could be to use a writable view to transform results in-query, eg

ALTER TABLE utilities.labpreferences
    RENAME TO utilities.old_labpreferences

CREATE TABLE utilities.labpreferences
( utilities.old_labpreferences EXCLUDING DEFAULTS )
WITHOUT OIDS;

CREATE OR REPLACE RULE "labpreferences_select" AS
  ON SELECT TO utilities.labpreferences
  DO INSTEAD
  SELECT -- fields with modifications --
    FROM utilities.old_labpreferences
    WHERE s.id like 'My.%'
  UNION
  SELECT -- fields without modification --
    FROM utilities.old_labpreferences
    WHERE s.id not like 'My.%'
    ;

Backing the changes out is as simple as removing the rule and new (empty) table, and renaming the old table back.

You will also need rules for update/insert/delete to this table - the postgres documentation for DO INSTEAD is pretty good on this front - see http://www.postgresql.org/docs/devel/static/rules-views.html

PostgreSQL – Difference Between After Update and Before Update Triggers

There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.

In a BEFORE trigger, the trigger is executed BEFORE the DML statement is executed.. So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted... But anything you want to do you can do (like check data in a separate table and issue an update/insert/whatever in that table, etc). If, for some reason, the trigger causes an EXCEPTION, then execution ceases and the DML statement is never executed.

In an AFTER trigger, the trigger is executed AFTER the DML statement is executed. You have already lost any ability to modify the :NEW and :OLD records and have it actually mean anything. However, you can still do whatever you want in the trigger. If the trigger causes an exception, for PostgreSQL, this will cause a rollback of the original DML. I do not believe this is true for all RDBMSs - but am having a hard time finding an example right now.

A common use of a BEFORE trigger is to set a timestamp column to "now" before the data has been inserted.

A common use of an AFTER trigger is to populate an audit/history table with the changes.

In either case, you would usually want to issue a ROLLBACK if you find an exception has been raised.

Best Answer

Related Solutions

Postgresql – In Postgres, How to merge two tables

PostgreSQL – Difference Between After Update and Before Update Triggers

Related Question