Postgres AccountsDB plugin does not capture all historical account writes

[ckamm]

Problem

The plugin wants to allow capturing historic account writes in the account_audit table and uses a AFTER UPDATE OR DELETE trigger on the account table to accomplish that.

However, the individual writes to account have the form:

INSERT INTO account ... ON CONFLICT (pubkey) DO UPDATE SET ... 
    WHERE acct.slot < excluded.slot 
        OR (acct.slot = excluded.slot AND acct.write_version < excluded.write_version)

which means that older conflicting writes where the WHERE condition is false will not update the account table at all and not end up in account_audit.

Out-of-order writes will always be seen, even when using only one thread to write to postgres, because the account writes don't necessarily arrive at the accountsdb plugin in order.

(Here's a log that shows this out of order arrival. Note that the write_version is monotonic, but the slot isn't - even when looking at a single account.

<pubkey>                                     <slot>    <write version>
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550443 180070299602
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550447 180070324306
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550447 180070325347
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550452 180070352904
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550453 180070358229
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550456 180070362308
6WGoQr5mJAEpYCdX6qjju2vEnJuD7e8ZeYes7X7Shi7E 113550454 180070363471

Proposed Solution

Possibly writes need to be inserted into account_audit explicitly?

@lijunwangs

[lijunwangs]

Thanks for the finding -- will take a look.

[github-actions]

This issue has been automatically locked since there has not been any activity in past 7 days after it was closed. Please open a new issue for related bugs.