Add pool DB operation listRetiredPools.

[jonathanknowles]

Context

As part of the work to perform garbage collection of retired pools from the database, we need to be able to determine which pools have retired.

Decision

Create a pool database operation listRetiredPools:

listRetiredPools
    :: EpochNo
    -> stm [PoolId]
    -- ^ List all pools with an active retirement epoch that is
    -- earlier than or equal to the specified epoch.

SQLite implementation

The SQLite implementation of this operation can be based on the following view, which lists all pools that have active retirement certificates associated with them:

Click to expand

CREATE VIEW active_pool_retirements AS
SELECT * FROM (
    SELECT
        pool_id,
        retirement_epoch
    FROM (
        SELECT row_number() OVER w AS r, *
        FROM (
            SELECT
                pool_id, slot, slot_internal_index, NULL as retirement_epoch
                FROM pool_registration
            UNION
            SELECT
                pool_id, slot, slot_internal_index, epoch as retirement_epoch
                FROM pool_retirement
        )
        WINDOW w AS (ORDER BY pool_id, slot desc, slot_internal_index desc)
    )
    GROUP BY pool_id
)
WHERE retirement_epoch IS NOT NULL;

Acceptance Criteria

• The pool DB must provide a listRetiredPools operation.
• The listRetiredPools operation must not include pools that have never had retirement certificates associated with them.
• The listRetiredPools operation must not include pools that have had their most-recently-published retirement certificates revoked by subsequent re-registration certificates.
• The listRetiredPools operation must not include pools with retirement epochs that are later than the specified epoch.

Development

QA

Create property tests for the model and SQLite implementations of listRetiredPools to:

• check that they return the same results given the same inputs and initial conditions.
• check that they do not include pools that have never had retirement certificates associated with them.
• check that they do not include pools that have had their most-recently-published retirement certificates revoked by subsequent re-registration certificates.
• check that they do not include pools with active retirement epochs that are later than the specified epoch.

[piotr-iohk]

LGTM.

@jonathanknowles this introduces new DB view active_pool_retirements. Do we need to consider any additional migration testing?

[jonathanknowles]

@jonathanknowles this introduces new DB view active_pool_retirements. Do we need to consider any additional migration testing?

This is a good question. I think our existing tests should be sufficient at this stage.

The view in question is created with a CREATE VIEW IF NOT EXISTS statement: https://github.com/input-output-hk/cardano-wallet/blob/75b583a14faa5bc1875f6dddd2a94a897c47080d/lib/core/src/Cardano/Pool/DB/Sqlite.hs#L489-L495

So it should be idempotent over multiple startups.

In future, if we wish to adjust this view, we can adjust the startup logic to just delete and recreate it, perhaps with a CREATE OR REPLACE VIEW statement. IMHO, this is one of the advantages of views: we can create, adjust, and delete them without having to alter existing tables with existing data.

[piotr-iohk]

lgtm