PostgreSQL TPROC-C consistency check 2 failed

[sm-shaw]

Discussed in #686

^{Originally posted by mitsu-ko April 4, 2024}
Hi,

I use consistency check feature in HammerDB 4.10 on PostgreSQL 15.6. Before benchmark, 'checkschema' is done without error. After running TPC-C benchmark, I run 'checkschame', consistency check fail is outputted under following.

Vuser 1 created - WAIT IDLE
Vuser 1:RUNNING
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
Error in Virtual User 1: TPROC-C Schema check failed tpcc schema consistency check 2 failed
Vuser 1:FINISHED FAILED

Does it means HammerDB bug or PostgreSQL bug? And I'd like to know more detail information why check is failed. It's important problem. But error message is too simple.

Regards,

[sm-shaw]

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.