AccountsDb plugin account updates have no in-slot ordering

[ckamm]

Problem

Currently the accountsdb plugin can be notified about multiple updates for the same account in one slot. When messages are sent to a database asynchronously the order of the updates in the same slot cannot be reconstructed.

So if an account receives two updates in a slot, the update message could arrive in postgres in reverse order or true order. There's no way to know which account state is the right one at the end of the slot.

Proposed Solution

The example postgres plugin could track the number of updates sent per slot and capture the current number (and also the updated_on time!) before sending the DbWorkItem through the channel to be processed asynchronously.

I'd be up for making this change if you agree it makes sense, @lijunwangs

[lijunwangs]

Good finding. Yes -- I was aware of this issue and was planning address in a future PR. There are two ways to solve the issue:

1. Do not queue the account update if there is already an ongoing update to the same account.
2. Use the write_version already in the account to prevent update of with lower write_version overwriting update with higher one -- the write_version is similar to the 'number' mechanism you mentioned -- which is already built-in the validator.

Need to do some comparison and see which is more efficient.

[ckamm]

Write version would be excellent! That'd need changes in the accountsdb-plugin api though. I assume that is still fine?

[brianlong]

Regarding #1, we still want to queue the next update if there is already an update in the queue. The second update is more important than the first if the second is newer.

We assume that multiple account updates per slot will arrive out of order. So, #2 write_version is the preferred solution because we can easily ignore the older updates when updating the DB.

The DB schema might look like this:

=> \d account;
                          Table "public.account"
   Column      |            Type             | Collation | Nullable | Default 
---------------+-----------------------------+-----------+----------+---------
 pubkey        | bytea                       |           | not null | 
 owner         | bytea                       |           |          | 
 lamports      | bigint                      |           | not null | 
 slot          | bigint                      |           | not null | 
 write_version | bigint                      |           | not null | 
 executable    | boolean                     |           | not null | 
 rent_epoch    | bigint                      |           | not null | 
 data          | bytea                       |           |          | 
 updated_on    | timestamp without time zone |           | not null | 

The SQL upsert statement might look like:

"INSERT INTO account (pubkey, slot, owner, lamports, executable, rent_epoch, data, updated_on, write_version)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
ON CONFLICT (pubkey)
DO UPDATE SET slot=$2, owner=$3, lamports=$4, executable=$5, rent_epoch=$6, data=$7, updated_on=$8, write_version=$9
WHERE write_version < $9"

Unfortunately, this Postgres plugin will not be ready for production until the order of account updates can be assured. #blocker

[lijunwangs]

Yes. This will be addressed ASAP!

[lijunwangs]

This issue has been addressed and solution merged into v1.8

[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.