Date: Tue, 18 Mar 2014 05:22:39 -0700 (PDT)
From: ssylla <stefansylla@gmx.de>
To: pgsql-sql@postgresql.org
Message-ID: <1395145359428-5796557.post@n5.nabble.com>
Subject: problem with update order (?)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql