This is strange and as of now I do not have a reliable way of reproducing.
Nevertheless,
either there is a major blunder on my side that urgently needs being
pointed at and eliminated
or there is something really strange with PG.
Short version:
I update some rows of a table changing non-primary key column values.
Afterwards some of the updated rows are returned from a query with
the version from before and after the update.
Consequently the PK is detected inconsistent later on and errors are
reported accordingly.
Longer Version: please see text attachment
server_version | 8.4.7
server_version_num | 80407
OS: NetBSD 5.99.38
Sizes:
account_item 12 GB 6,8079,402 rows
While the update was executing another process was active that was
issuing a sequence of select.
Running that very sequence on a copy clone of the database (before the
update)
worked without such effect.
I had 3 similar occurrences before.
But those were on a DB instance used for development and I could not
verify the primary key was active during update.
Here it is verified it was in place. So the "bad" entries probably could
have been rejected due to PK violation?
Not much input I can give for decent analysis,
but either someone can point me to the obvious
or it is something thats worth being watched for somehow....
Rainer
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)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
