Slashing protection database fails reaching max index

[jframe]

Web3signer slashing protection database inserts will eventually fail due postgresql reaching the maximum value of the index.

When this happens we will get an error like this:
org.postgresql.util.PSQLException: ERROR: nextval: reached maximum value of sequence "signed_attestations_id_seq" (2147483647) [statement:"INSERT INTO signed_attestations (validator_id, signing_root, source_epoch, target_epoch) VALUES (?, ?, ?, ?)"

A workaround is to reset the sequences:

ALTER SEQUENCE signed_attestations_id_seq RESTART WITH 1;
ALTER SEQUENCE signed_blocks_id_seq RESTART WITH 1;

A better solution is to change the index to use serial8 instead of serial https://www.cybertec-postgresql.com/en/error-nextval-reached-maximum-value-of-sequence/

[yorickdowne]

This is production-impacting on Gnosis Chain, because of the number of validators (10,000) and speed of the chain. Do you have an ETA for a fix?

[siladu]

Thanks for the report, we'll up the priority of this and try to get you an ETA.

[jframe]

I've looked into this and I think the correct solution is to update the sequences and indexes to use bigint instead of integer. This is equivalent to creating to an index using serial8 instead of serial.

So the change I'm planning to do will do this as a database change:

ALTER SEQUENCE signed_attestations_id_seq AS bigint;
ALTER TABLE signed_attestations ALTER COLUMN id TYPE bigint;
ALTER SEQUENCE signed_blocks_id_seq AS bigint;
ALTER TABLE signed_blocks ALTER COLUMN id TYPE bigint;

Unfortunately using alter will require a database table lock. So currently testing to see what impact will be in terms of downtime to change a slashing protection DB of 1000 signers with a history of 1000 signings and a history of 10,000 signings.

[yorickdowne]

Thank you! We have 10,000 signers in one environment and 5,000 in another. We can take planned downtime, as this avoids unplanned downtime down the road.