search

helium / blockchain-etl

Blockchain follower that follows and stores the Helium blockchain
Apache License 2.0
64 stars 37 forks source link

rewards and transactions discrepancies #207

Closed olb1ue closed 11 months ago

olb1ue commented 3 years ago

Some individual gateway rewards amounts slightly differ on the rewards table vs the transactions table.

These two queries should return the same value for amount

SELECT block, rewards.gateway, rewards.amount 
FROM transactions, jsonb_to_recordset(transactions.fields::jsonb->'rewards') 
AS rewards(gateway text, amount bigint) 
WHERE rewards.gateway = '112E3tsDHhH9CoWCw1TnamUkvWAWB5truMp1oeJnEiKxSeF8eXPQ' 
AND transactions.hash = 'lreK4y8PSV0-Iz2latcVIJnqxOgGAdMXg9wRTguqbIs';
SELECT * FROM rewards WHERE transaction_hash = 'lreK4y8PSV0-Iz2latcVIJnqxOgGAdMXg9wRTguqbIs' 
AND gateway = '112E3tsDHhH9CoWCw1TnamUkvWAWB5truMp1oeJnEiKxSeF8eXPQ';

However the former returns 9770581 and the latter gives 9770402 for amount.

The attached json file has other examples of gateways and transaction hashes that also have discrepancies.

Here are a few more examples: https://gist.github.com/olb1ue/994a14fbd72d6dcd58e8234a0a1a22ad

normium commented 3 years ago

I've done some analysis, and broadly there are discrepancies in certain transactions, but they tend to even out so that overall reward totals are equal.

taking the last 7 days of earnings:

dates: 2021-07-13 to 2021-07-19
blocks: 918956 to 929437

full network:
tx sum: 113,651,849,255,373
rewards sum: 113,651,849,257,455 (-2,082)

limit to: 112E3tsDHhH9CoWCw1TnamUkvWAWB5truMp1oeJnEiKxSeF8eXPQ
tx sum: 1,317,079,052
rewards sum: 1,317,080,526 (-1,474)

limit to: 112qRid4JuAqAAEf6KMuhMbPyKSrWzXJDvSkQaFCCXEbbP1R4ugz
tx sum: 2,192,879,111
rewards sum: 2,192,877,550 (+1,561)

the full network is off by only -2082 bones over 7 days, but one individual gateway is off by -1474, and another is +1,561 pretty much any gateway I've ever checked is like this

After looking in more detail I produced a report which outputs which transactions have discrepancies (where discrepancy is a different reward in the rewards table for a gateway to the transaction value), here is the output for blocks today until now:

block 929447, transaction okay: izvf2AS0WTQlKvNG5MRcTz6G8BOdisLdYTqJmXr9o2c
block 929479, transaction okay: psaWKGBz0HAMeM2jjDFc9KFxJZlCppDPpZ0Gfx7B0P4
block 929511, transaction okay: ivGcZKvexze0a08ovtvztPRZ_VG8kw45VSzPLUhLrX8
block 929544, transaction okay: eoeDVJf7tg-Ln_Z3YJHvfCBqc7xnGEqw8ThvB6WuNNU
block 929576, transaction okay: JDXqC9dTbIxsfP3KhQBJ7AFuSW9eTR9DVvq1GmKxzRE
block 929609, transaction okay: a8cFOROWvj6gg5jKxNf2CE0bUeyTVuGDhei17fTQzf4
block 929642, transaction okay: fL-s7a-gK8TgURRBSYa1bkWDk4MxunBY9Mu456I0IzU
block 929674, transaction okay: ss2wdfbXKx89cAKQkezSCjE9FMCjopIeAKIbnrQb4Ls
block 929706, transaction okay: sHFAiSKf2WyMX9xGTorpdkWOXMhWXr6ue7QpR99AXOI
block 929738, transaction okay: yyEAlYVgZMoolnUtNxbpOsMXOJ9IsarBKkLDIGXO23I
block 929771, transaction okay: qUJCxiQsuNCFZ92Mu98pkrYUur3gv8a62os0Y4pf3oo
block 929803, transaction okay: EWh33xTGmTBzgueNr8tlzV4OIQlFeoI1hyDjHZ5IXRg
block 929836, transaction okay: vqD7_8-XS_Ey6R_nNMv1mSX0WJSvFo__COFrP-Z25bw
block 929868, transaction okay: W8dXkhEAbyH_hZ8tKgw0N-Rpd6ULPmGIe1AKmhBtrv4
block 929901, transaction not okay: F8adMhaNH7CAfpZ31AEOZB_G67BG1sFL0BJu--JZ5fc
- rewards: 15674
- discrepancies : 12404
block 929933, transaction okay: h28oEOVXe_BHZR9veH8ZoIvCDMyq5Espv0cPy20EcSo
block 929965, transaction okay: 10KOgNF3Qh7kqEXkNqwo5t_iPE1ooC0dECQkx0D5w2c
block 929997, transaction okay: Wb1b6xH0FgzJzvIpSZaH3vVIh4ZgZwxqUEfXhf7lBz8
block 930029, transaction okay: C0I75gC9dW17r7zf6dRlB7I_X6tq2qBm1AToSOZZzCw
block 930062, transaction not okay: 4LQivB8HJQ3xAARNmrnXiccihJ5t8nEdNBOvqaS-V_g
- rewards: 17078
- discrepancies : 10858
block 930095, transaction okay: B7HSi1zESSDIIaf2e6IlV7FMsG7LwHb2o6vinJM2sV8
block 930127, transaction okay: 1nW7bOaJ-Ph3oARXtZD-LKIgQ51FIMIQFuOGe1NpMHs
block 930160, transaction okay: LHjLYVOVRWRdF3tpELuprkKPSyVtxQu6jn9WH_TQ_SM
block 930193, transaction okay: M0FYevfpNBuB77aBhHVgVRCXEoZGHziFVgA8nn6l8xU
block 930226, transaction okay: I6GQiHTk7Y_FoRPGCvupgNIsxgr-mIUrVsXtPuDenCI
block 930260, transaction not okay: qD8Q9PcSkJLiiHtAf2ltgxgkh_ZrGHz0DzcleA7iP_o
- rewards: 13914
- discrepancies : 10844
block 930292, transaction okay: vKKFEr2Qmap65CvA_AiGcqQlJ2WcEHmO4EkXXG-chCM
block 930325, transaction okay: 0VntSgieGlJ-jg5KFM9QqZtGM7I2zZr1afYQ1Asd7xQ
block 930358, transaction okay: xBpcWjxPPWqXJh5HxUgm2GmknuDPAb294osjkWYYrgU
block 930390, transaction okay: lCxRJGQIlcZz5Yz47uSQN5kZugUJfeJLtysmKsnrRW0
block 930422, transaction okay: frGjR0qALf_qbEjpWv0Ng-TG6q4qcFKKKj262hImd-4
block 930455, transaction okay: zdpaL7EbEDgDwAAywwEUR5TxGkSBy_g5LCfHKtNU8p4
block 930487, transaction not okay: ww7rLXoPaFR7erqf2v1U4MpCwx1HZudELgPlPrzJd1w
- rewards: 15603
- discrepancies : 12112
block 930520, transaction okay: kwxLMkhhJ7W8DvNo9O_3D4RIXY7HyiJ53Nvl6_FRtr4
block 930552, transaction okay: XxPWu09797xod_ahfJ-6oAjhoi1orY0M64_t6DCCM24
block 930585, transaction okay: BMkU3lsG4W7CxXTF3tkzgC6jVFbVF4svQI2747cI9oI
block 930617, transaction okay: 77BVgJfP7KNhLcY0N-oeA3axyKbrEE3DPHTSR6QSfMs
block 930650, transaction okay: 2fnPGdkrXl1wDIzwTbKGYQBdd5mJqco03ZcQyLCoOoA