Sequnce of steps: a) As can be seen from the trigger function below. we need a value from a sequence. This is getting such a value. select next_wbuidx(); next_wbuidx ------------- 26121 (1 row) b) perform the update. what was of interest here had been determined earlier with some selects.... update account_item set receipttype='PY',detail='PY' where accountidx in (8617,8562,8616,8511,8615) and receipttype='EI'; UPDATE 346305 Time: 434837.447 ms c) issue a check query to ensure we did hit all rows that should be affected. This uses a slightly different query to cross check the short-cut values used with the uodate. The actual values are not too interesting. I just left a sample row for reference. The values there indicated it was one of the rows discovered earlier. select * from account_item whore accountidx in (select idx from account where domainidx in (2,3) and contextidx in (8510, 33362709) and accountid not like '%Interest%') and receipttype='EI'; wbuidx | userid | ts | idx | origwbuidx | accountidx | namespace | originatoridx | referenceidx | dedicationidx | groupidx | receipttype | detail | valuedate | effvaluedate | amount | remain | currency | creditdebit | label --------+--------+-------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+----------+-------------+----------+------------+--------------+-----------+-----------+----------+-------------+ ------- 25880 | 601 | 2011-08-16 05:36:25.947873+02 | 215165864 | 25880 | 8615 | Accounting | 8516 | 215165861 | | | EI | interest | 2011-08-31 | 2011-08-16 | 1.750000 | 1.750000 | EUR | -1 | [truncated]... (29 rows) Time: 14916.083 ms d) assuming the update was to scrict, retry targeting the left-overs update account_item set receipttype='PY',detail='PY' where accountidx in (8616) and receipttype='EI'; ERROR: duplicate key value violates unique constraint "pk_account_item" e) check for culprits select idx,count(*) from account_item group by idx having count(*) > 1; idx | count -----------+------- 215165864 | 2 215165896 | 2 215165927 | 2 215165959 | 2 215165991 | 2 215166023 | 2 215166155 | 2 215166187 | 2 215166219 | 2 215166251 | 2 215166283 | 2 215166315 | 2 215166347 | 2 215166379 | 2 215166411 | 2 217011495 | 2 217011527 | 2 217011659 | 2 217011691 | 2 217011723 | 2 217011877 | 2 217011909 | 2 217012041 | 2 217012104 | 2 217012136 | 2 217012168 | 2 217012200 | 2 217012232 | 2 217012264 | 2 (29 rows) f) look for details of a sample select * from account_item where idx = 215165864; wbuidx | userid | ts | idx | origwbuidx | accountidx | namespace | originatoridx | referenceidx | dedicationidx | groupidx | receipttype | detail | valuedate | effvaluedate | amount | remain | currency | creditdebit | l abel --------+--------+-------------------------------+-----------+------------+------------+------------+---------------+--------------+---------------+----------+-------------+----------+------------+--------------+----------+----------+----------+-------------+-- ----- 26121 | pgsql | 2011-08-17 11:13:15.593382+02 | 215165864 | 25880 | 8615 | Accounting | 8516 | 215165861 | | | PY | PY | 2011-08-31 | 2011-08-16 | 1.750000 | 1.750000 | EUR | -1 | 25880 | 601 | 2011-08-16 05:36:25.947873+02 | 215165864 | 25880 | 8615 | Accounting | 8516 | 215165861 | | | EI | interest | 2011-08-31 | 2011-08-16 | 1.750000 | 1.750000 | EUR | -1 | (2 rows) ----------------------------------------- Closer inspection: "wbuidx" column is the one that gets the current value (the one provided by the select next_wbuidx(); call above; This indicates the update "worked", this is the row as it should be available after the update. The second row looks exactly like the version before the update...... ------------------------------------- More details on the participating objects: Table "business.account_item" Column | Type | Modifiers ---------------+-----------------------------+----------------------------------- wbuidx | bigint | not null userid | character varying(40) | not null default "current_user"() ts | timestamp(6) with time zone | not null default now() idx | bigint | not null origwbuidx | bigint | not null accountidx | bigint | not null namespace | character varying(200) | not null originatoridx | bigint | not null referenceidx | bigint | dedicationidx | bigint | groupidx | bigint | receipttype | character varying(200) | not null detail | character varying(200) | not null valuedate | date | not null effvaluedate | date | not null amount | numeric(24,6) | not null remain | numeric(24,6) | not null currency | character varying(3) | not null creditdebit | numeric(1,0) | not null label | character varying(200) | Indexes: "pk_account_item" PRIMARY KEY, btree (idx), tablespace "pa_index_01" "uk_account_item_01" UNIQUE, btree (accountidx, namespace, idx) CLUSTER, tablespace "pa_index_02" "x_account_item_01" btree (referenceidx, accountidx), tablespace "pa_index_02" "x_account_item_account_owbu" btree (accountidx, origwbuidx) WHERE namespace::text = 'Accounting'::text, tablespace "pa_index_04" "x_account_item_open" btree (accountidx, namespace) WHERE remain <> 0::numeric, tablespace "pa_index_01" "x_account_item_py3" btree (accountidx, referenceidx) WHERE referenceidx IS NOT NULL, tablespace "pa_index_03" "x_account_item_py4" btree (dedicationidx, accountidx, namespace) WHERE dedicationidx IS NOT NULL, tablespace "pa_index_03" "x_account_item_py4b" btree (dedicationidx, accountidx) WHERE namespace::text = 'Accounting'::text AND dedicationidx IS NOT NULL, tablespace "pa_index_04" "x_account_item_py5" btree (referenceidx, accountidx, creditdebit), tablespace "pa_index_02" "x_account_item_py5b" btree (referenceidx, creditdebit, accountidx) WHERE namespace::text = 'Accounting'::text, tablespace "pa_index_02" "x_account_item_wbuidx" btree (wbuidx), tablespace "pa_index_04" "x_fk_account_item_account" btree (accountidx), tablespace "pa_index_03" Rules: ru_account_item_del_01 AS ON DELETE TO account_item WHERE old.wbuidx <> curr_wbuidx() DO INSERT INTO h_account_item (changetype, wbuidx, ts, userid, idx, origwbuidx, accountidx, namespace, originatoridx, referenceidx, dedicationidx, groupidx, receipttype, detail, valuedate, effvaluedate, amount, remain, currency, creditdebit, label) VALUES ('D'::bpchar, old.wbuidx, old.ts, old.userid, old.idx, old.origwbuidx, old.accountidx, old.namespace, old.originatoridx, old.referenceidx, old.dedicationidx, old.groupidx, old.receipttype, old.detail, old.valuedate, old.effvaluedate, old.amount, old.remain, old.currency, old.creditdebit, old.label) Triggers: tr_account_item_ins_01 BEFORE INSERT ON account_item FOR EACH ROW EXECUTE PROCEDURE insertaccount_item() tr_account_item_upd_01 BEFORE UPDATE ON account_item FOR EACH ROW EXECUTE PROCEDURE updateaccount_item() Inherits: business_base Tablespace: "pa_data_04" Table "history.h_account_item" Column | Type | Modifiers ---------------+-----------------------------+----------------------------------- wbuidx | bigint | not null userid | character varying(40) | not null default "current_user"() ts | timestamp(6) with time zone | not null default now() idx | bigint | not null origwbuidx | bigint | not null newwbuidx | bigint | not null changetype | character(1) | not null accountidx | bigint | not null namespace | character varying(200) | not null originatoridx | bigint | not null referenceidx | bigint | dedicationidx | bigint | groupidx | bigint | receipttype | character varying(200) | not null detail | character varying(200) | not null valuedate | date | not null effvaluedate | date | not null amount | numeric(24,6) | not null remain | numeric(24,6) | not null currency | character varying(3) | not null creditdebit | numeric(1,0) | not null label | character varying(200) | Indexes: "x_h_account_item_01" btree (idx, wbuidx, newwbuidx), tablespace "pa_index_04_h" "x_h_account_item_newwbu" btree (newwbuidx), tablespace "pa_index_03" "x_h_account_item_wbu" btree (wbuidx) Rules: ru_h_account_item_del_01 AS ON DELETE TO h_account_item DO INSTEAD NOTHING ru_h_account_item_upd_01 AS ON UPDATE TO h_account_item DO INSTEAD NOTHING Triggers: tr_h_account_item_ins_01 BEFORE INSERT ON h_account_item FOR EACH ROW EXECUTE PROCEDURE inserth_account_item() Inherits: history_base Tablespace: "pa_data_04_h" Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | | Description ----------+--------------------+------------------+---------------------+---------+------------+--------------+----------+------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------+------------- business | updateaccount_item | trigger | | trigger | volatile | poolarranger | plpgsql | BEGIN | : If : ((OLD.WBUIDX <> NEW.WBUIDX) Or (Not equals(NEW.ACCOUNTIDX,OLD.ACCOUNTIDX)) Or (Not equals(NEW.NAMESPACE,OLD.NAMESPACE)) Or (Not equals( NEW.ORIGINATORIDX,OLD.ORIGINATORIDX)) Or (Not equals(NEW.REFERENCEIDX,OLD.REFERENCEIDX)) Or (Not equals(NEW.DEDICATIONIDX,OLD.DEDICATIONIDX)) Or (Not equals(NEW.GROUPIDX,OLD.GROUPIDX)) Or (Not equals(NEW.RECEIPTTYPE,OLD.RECEIPTTYPE)) Or (Not equals(NEW.DET AIL,OLD.DETAIL)) Or (Not equals(NEW.VALUEDATE,OLD.VALUEDATE)) Or (Not equals(NEW.EFFVALUEDATE,OLD.EFFVALUEDATE)) Or (Not equals(NEW.AMOUNT,OLD.AMOUNT)) Or (Not equals(NEW.REMAIN,OLD.REMAIN)) Or (Not equals(NEW.CURRENCY,OLD.CURRENCY)) Or (Not equals(NEW.CR EDITDEBIT,OLD.CREDITDEBIT)) Or (Not equals(NEW.LABEL,OLD.LABEL))) : Then : NEW.ORIGWBUIDX := OLD.ORIGWBUIDX; : : If : (OLD.WBUIDX = NEW.WBUIDX) : Then : NEW.WBUIDX := CURR_WBUIDX(); : End If; : : NEW.TS := Current_Timestamp; : : NEW.TS := Current_Timestamp; : NEW.USERID := Current_User; : : NEW.IDX := OLD.IDX; : If ( OLD.WBUIDX <> CURR_WBUIDX() ) : Then : Insert Into H_ACCOUNT_ITEM : (CHANGETYPE,WBUIDX, TS, USERID, IDX, ORIGWBUIDX,ACCOUNTIDX,NAMESPACE,ORIGINATORIDX,REFERENCEIDX,DEDICATIONIDX,GROUPIDX,RECEIPTTYPE,DETAI : 'U',OLD.WBUIDX, OLD.TS, OLD.USERID, OLD.IDX, OLD.ORIGWBUIDX,OLD.ACCOUNTIDX,OLD.NAMESPACE,OLD.ORIGINATORIDX,OLD.REFERENCEIDX,OLD.DEDICAT IONIDX,OLD.GROUPIDX,OLD.RECEIPTTYPE,OLD.DETAIL,OLD.VALUEDATE,OLD.EFFVALUEDATE,OLD.AMOUNT,OLD.REMAIN,OLD.CURRENCY,OLD.CREDITDEBIT,OLD.LABEL); : : End If; : End If; : Return NEW; : END; (1 row)