Closed sm-shaw closed 3 months ago
In HammerDB v4.10 PostgreSQL TPROC-C consistency check 2 fails with the error:
Error in Virtual User 1: TPROC-C Schema check failed tpcc schema consistency check 2 failed
Consistency check 2 is:
For each District within a Warehouse, the value of the most recent Order ID [max(no_o_id)] minus the first Order ID [min(no_o_id)] plus one, for the NEW-ORDER table associated with the District and Warehouse, equals the number of rows in that NEW-ORDER table. That relationship can be illustrated as: max(no_o_id) – min(no_o_id) + 1 = rows in NEW-ORDER where (o_w_id = no_w_id) and (o_d_id = no_d_id)
Checking the output, it can be seen that the consistency check is failing because all the values are out by 1.
Hammerdb Log @ Thu Apr 04 11:32:27 BST 2024
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Vuser 1:Checking tpcc TPROC-C schema
Vuser 1:Check database
Vuser 1:Check tables and indices
Vuser 1:Check procedures
Vuser 1:Check consistency 1
Vuser 1:Check consistency 2
Vuser 1:resArray(0,d_id) = 1
Vuser 1:resArray(0,d_w_id) = 6
Vuser 1:resArray(0,order_max) = 22816
Vuser 1:resArray(0,order_next) = 22815
Vuser 1:resArray(1,d_id) = 1
Vuser 1:resArray(1,d_w_id) = 7
Vuser 1:resArray(1,order_max) = 22892
Vuser 1:resArray(1,order_next) = 22891
Vuser 1:resArray(10,d_id) = 6
Vuser 1:resArray(10,d_w_id) = 6
Vuser 1:resArray(10,order_max) = 22637
Vuser 1:resArray(10,order_next) = 22636
Vuser 1:resArray(11,d_id) = 3
Vuser 1:resArray(11,d_w_id) = 7
Vuser 1:resArray(11,order_max) = 22952
Vuser 1:resArray(11,order_next) = 22951
Vuser 1:resArray(12,d_id) = 2
Vuser 1:resArray(12,d_w_id) = 6
Vuser 1:resArray(12,order_max) = 22419
Vuser 1:resArray(12,order_next) = 22418
Vuser 1:resArray(13,d_id) = 5
Vuser 1:resArray(13,d_w_id) = 7
Vuser 1:resArray(13,order_max) = 22923
Vuser 1:resArray(13,order_next) = 22922
Vuser 1:resArray(14,d_id) = 2
Vuser 1:resArray(14,d_w_id) = 7
Vuser 1:resArray(14,order_max) = 22568
Vuser 1:resArray(14,order_next) = 22567
Vuser 1:resArray(15,d_id) = 9
Vuser 1:resArray(15,d_w_id) = 7
Vuser 1:resArray(15,order_max) = 22499
Vuser 1:resArray(15,order_next) = 22498
Vuser 1:resArray(16,d_id) = 3
Vuser 1:resArray(16,d_w_id) = 6
Vuser 1:resArray(16,order_max) = 22544
Vuser 1:resArray(16,order_next) = 22543
Vuser 1:resArray(17,d_id) = 7
Vuser 1:resArray(17,d_w_id) = 6
Vuser 1:resArray(17,order_max) = 22634
Vuser 1:resArray(17,order_next) = 22633
Vuser 1:resArray(18,d_id) = 4
Vuser 1:resArray(18,d_w_id) = 7
Vuser 1:resArray(18,order_max) = 22449
Vuser 1:resArray(18,order_next) = 22448
Vuser 1:resArray(19,d_id) = 5
Vuser 1:resArray(19,d_w_id) = 6
Vuser 1:resArray(19,order_max) = 22514
Vuser 1:resArray(19,order_next) = 22513
Vuser 1:resArray(2,d_id) = 6
Vuser 1:resArray(2,d_w_id) = 7
Vuser 1:resArray(2,order_max) = 22500
Vuser 1:resArray(2,order_next) = 22499
Vuser 1:resArray(3,d_id) = 10
Vuser 1:resArray(3,d_w_id) = 6
Vuser 1:resArray(3,order_max) = 22674
Vuser 1:resArray(3,order_next) = 22673
Vuser 1:resArray(4,d_id) = 8
Vuser 1:resArray(4,d_w_id) = 7
Vuser 1:resArray(4,order_max) = 22539
Vuser 1:resArray(4,order_next) = 22538
Vuser 1:resArray(5,d_id) = 10
Vuser 1:resArray(5,d_w_id) = 7
Vuser 1:resArray(5,order_max) = 22524
Vuser 1:resArray(5,order_next) = 22523
Vuser 1:resArray(6,d_id) = 7
Vuser 1:resArray(6,d_w_id) = 7
Vuser 1:resArray(6,order_max) = 22529
Vuser 1:resArray(6,order_next) = 22528
Vuser 1:resArray(7,d_id) = 9
Vuser 1:resArray(7,d_w_id) = 6
Vuser 1:resArray(7,order_max) = 22401
Vuser 1:resArray(7,order_next) = 22400
Vuser 1:resArray(8,d_id) = 8
Vuser 1:resArray(8,d_w_id) = 6
Vuser 1:resArray(8,order_max) = 22714
Vuser 1:resArray(8,order_next) = 22713
Vuser 1:resArray(9,d_id) = 4
Vuser 1:resArray(9,d_w_id) = 6
Vuser 1:resArray(9,order_max) = 22605
Vuser 1:resArray(9,order_next) = 22604
This can be resolved by correcting the RETURNING d_next_o_id statement in the NEWORD stored procedure.
> UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id, d_tax INTO no_d_next_o_id, no_d_tax;
< UPDATE district SET d_next_o_id = d_next_o_id + 1 WHERE d_id = no_d_id AND d_w_id = no_w_id RETURNING d_next_o_id - 1, d_tax INTO no_d_next_o_id, no_d_tax;
The fix will not impact performance between versions.
Discussed in https://github.com/TPC-Council/HammerDB/discussions/686