Archive Node: Duplicated entries in the zkapp_account_preconditions table

[dkijania]

Paul have found an archive node issue on TW2.

When trying to patch the archive dbs with missing blocks, He got errors because there were multiple entries in the zkapp_account_preconditions table that are identical except for the id field.

That may have happened because there were distinct rows that became identical after the receipt_chain_hash field was patched. The duplicates are an issue because Zkapp_account_precondition.add_if_doesn't_exist function checks if a unique value exists, and return an error if there is more than one.

Probably the table should have a UNIQUE constraint on all the fields except id. The solution might be to find the duplicates, delete the extra ones, and fixup the references to the duplicates.

For the "1" db, there are 80 entries with duplicates in zkapp_account_precondition, and in support of Paul theory of why there are duplicates, all have non-NULL entries for receipt_chain_hash.

[dkijania]

Steps to reproduce:

1. Download dump from archive-2-postgresql-0 (--namespace testworld-2-0) at 03JAN2024
2. Download precomputed block: testworld-2-0-20693-3NKKDYxkPEz9vhDjpGvTjKFJ3djuA58R5zUyPB3J2nLTTcgW9Kq3.json
3. run archive_block application from rampup with arguments:
   mina-archive-blocks --archive-uri <uri> -precomputed testworld-2-0-20693-3NKKDYxkPEz9vhDjpGvTjKFJ3djuA58R5zUyPB3J2nLTTcgW9Kq3.json
4. Observe error:

12-23"},"message":"Error when adding block data to the database, rolling back transaction: $error","metadata":{"error":"Unexpected result from <postgres://postgres:_@127.0.0.1:5432/archive2>: Received 2 tuples, expected at most one. Query: \"SELECT id FROM zkapp_account_precondition WHERE (balance_id = $1 OR (balance_id IS NULL AND $1 IS NULL)) AND (nonce_id = $2 OR (nonce_id IS NULL AND $2 IS NULL)) AND (receipt_chain_hash = $3 OR (receipt_chain_hash IS NULL AND $3 IS NULL)) AND (delegate_id = $4 OR (delegate_id IS NULL AND $4 IS NULL)) AND (state_id = $5 OR (state_id IS NULL AND $5 IS NULL)) AND (action_state_id = $6 OR (action_state_id IS NULL AND $6 IS NULL)) AND (proved_state = $7 OR (proved_state IS NULL AND $7 IS NULL)) AND (is_new = $8 OR (is_new IS NULL AND $8 IS NULL))\"."}}