code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0091_epochs_add_start_and_end_block_height.sql (about)

     1  -- +goose Up
     2  
     3  -- We will create a materialized view with the API data we need as it only needs to be refreshed when the epoch table is updated
     4  -- we can trigger a refresh when that happens instead of having to query the same data every time.
     5  CREATE MATERIALIZED VIEW IF NOT EXISTS current_epochs AS
     6      WITH epochs_current AS (
     7          SELECT DISTINCT ON (id) * FROM epochs ORDER BY id, vega_time DESC
     8      )
     9          SELECT e.id, e.start_time, e.expire_time, e.end_time, e.tx_hash, e.vega_time, bs.height first_block, be.height last_block
    10          FROM epochs_current AS e
    11          LEFT JOIN blocks bs on e.start_time = bs.vega_time
    12          LEFT JOIN blocks be on e.end_time = be.vega_time
    13  WITH DATA;
    14  
    15  -- We need to have a unique index on the materialized view in order to refresh it concurrently.
    16  CREATE UNIQUE INDEX idx_uq_current_epochs_id on current_epochs(id);
    17  
    18  -- +goose StatementBegin
    19  CREATE OR REPLACE FUNCTION refresh_current_epochs()
    20         RETURNS TRIGGER
    21         LANGUAGE plpgsql AS
    22  $$
    23  BEGIN
    24      REFRESH MATERIALIZED VIEW CONCURRENTLY current_epochs;
    25      RETURN NULL;
    26  END;
    27  $$;
    28  -- +goose StatementEnd
    29  
    30  -- When an insert, update or delete happens on the epochs table, we want to refresh the materialized view.
    31  -- This should be safe to do as an epoch only happens periodically, but the view may be queried very frequently.
    32  CREATE OR REPLACE TRIGGER refresh_current_epochs
    33  AFTER INSERT OR UPDATE OR DELETE ON epochs
    34  FOR EACH STATEMENT
    35  EXECUTE FUNCTION refresh_current_epochs();
    36  
    37  -- +goose Down
    38  DROP TRIGGER IF EXISTS refresh_current_epochs ON epochs;
    39  DROP FUNCTION IF EXISTS refresh_current_epochs;
    40  DROP MATERIALIZED VIEW IF EXISTS current_epochs;