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.