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;
return NEW;
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.