problem with update order (?)

This seems like a mystery to me. I have the following table "project":

id [integer], project_code [text]
1;"03.0104.1"
2;"03.0104.2"
3;"03.0104.3"
4;"03.0104.4"
with a UNIQUE constraint on the column 'project_code' and the following
trigger function (it is called after delete or update on "project") in order
to recount the last digit of the project code:

CREATE OR REPLACE FUNCTION project_update_delete_after() RETURNS trigger AS
$BODY$   begin       -- if project_code changed...       if            (TG_OP='UPDATE' and
new.project_code!=old.project_code)      -- ... or if project was deleted           or (TG_OP='DELETE') then
--recount the last digit of project_code...           -- ...that are higher than updated/deleted:           execute
format('             update %I.project set project_code=              substr($1.project_code,1,8)
||cast(cast(substr(project_code,9,1)as integer)-1 as text)              where
substr(project_code,1,7)=substr($1.project_code,1,7)             and (cast(substr(project_code,9,1) as integer) >
       cast(substr($1.project_code,9,1) as integer));           ', TG_TABLE_SCHEMA) using old;       end if;
returnNEW;   end;
 
$BODY$ LANGUAGE plpgsql;

Now, when I try to delete the first row of the table (1;"03.0104.1") I get
the following error message:

ERROR:  duplicate key value violates unique constraint "pcode_unique"
DETAIL:  Key (project_code)=(03.0104.2) already exists.
CONTEXT:  SQL statement "           update public.project set project_code=           substr($1.project_code,1,8)
   ||cast(cast(substr(project_code,9,1) as integer)-1 as text)           where
substr(project_code,1,7)=substr($1.project_code,1,7)          and (cast(substr(project_code,9,1) as integer) >
     cast(substr($1.project_code,9,1) as integer));
 

If I delete the 2nd row (2;"03.0104.2") it is working fine. Obviously, in
the case of deleting the 1st row, Postgres tries to update the project_code
of the 3rd row before the 2nd row and that creates the unique constraint
violation. I tried to avoid that by creating an index of the id and 
clustering the table
<http://gbif.blogspot.com/2011/06/ordered-updates-with-postgres.html>  , but
I get the same error message. I have no idea what this problem is caused by,
so I feel forced to post this here.

Stefan 



--
View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-update-order-tp5796557.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.