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

     1  -- +goose Up
     2  
     3  -- +goose StatementBegin
     4  DO
     5  $$
     6  BEGIN
     7      IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'margin_mode_type') THEN
     8          CREATE TYPE margin_mode_type as enum('MARGIN_MODE_UNSPECIFIED', 'MARGIN_MODE_CROSS_MARGIN', 'MARGIN_MODE_ISOLATED_MARGIN');
     9      END IF;
    10  END;
    11  $$
    12  LANGUAGE plpgsql;
    13  -- +goose StatementEnd
    14  
    15  alter table margin_levels
    16      add column if not exists margin_mode margin_mode_type not null default ('MARGIN_MODE_CROSS_MARGIN'),
    17      add column if not exists margin_factor NUMERIC not null default (0),
    18      add column if not exists order_margin HUGEINT not null default (0),
    19      add column if not exists order_margin_account_id bytea;
    20  
    21  alter table current_margin_levels
    22      add column if not exists margin_mode margin_mode_type not null default ('MARGIN_MODE_CROSS_MARGIN'),
    23      add column if not exists margin_factor NUMERIC not null default (0),
    24      add column if not exists order_margin HUGEINT not null default (0),
    25      add column if not exists order_margin_account_id bytea;
    26  
    27  -- +goose StatementBegin
    28  drop trigger if exists update_current_margin_levels on margin_levels;
    29  CREATE OR REPLACE FUNCTION update_current_margin_levels()
    30     RETURNS TRIGGER
    31     LANGUAGE PLPGSQL AS
    32  $$
    33  BEGIN
    34  INSERT INTO current_margin_levels(account_id,
    35                                    order_margin_account_id,
    36                                    timestamp,
    37                                    maintenance_margin,
    38                                    search_level,
    39                                    initial_margin,
    40                                    collateral_release_level,
    41                                    order_margin,
    42                                    tx_hash,
    43                                    vega_time,
    44                                    margin_mode,
    45                                    margin_factor) VALUES(NEW.account_id,
    46                                                      NEW.order_margin_account_id,
    47                                                      NEW.timestamp,
    48                                                      NEW.maintenance_margin,
    49                                                      NEW.search_level,
    50                                                      NEW.initial_margin,
    51                                                      NEW.collateral_release_level,
    52                                                      NEW.order_margin,
    53                                                      NEW.tx_hash,
    54                                                      NEW.vega_time,
    55                                                      NEW.margin_mode,
    56                                                      NEW.margin_factor)
    57      ON CONFLICT(account_id) DO UPDATE SET
    58                                     order_margin_account_id=EXCLUDED.order_margin_account_id,
    59                                     timestamp=EXCLUDED.timestamp,
    60                                     maintenance_margin=EXCLUDED.maintenance_margin,
    61                                     search_level=EXCLUDED.search_level,
    62                                     initial_margin=EXCLUDED.initial_margin,
    63                                     collateral_release_level=EXCLUDED.collateral_release_level,
    64                                     order_margin=EXCLUDED.order_margin,
    65                                     tx_hash=EXCLUDED.tx_hash,
    66                                     vega_time=EXCLUDED.vega_time,
    67                                     margin_mode=EXCLUDED.margin_mode,
    68                                     margin_factor=EXCLUDED.margin_factor;
    69  
    70  
    71  RETURN NULL;
    72  END;
    73  $$;
    74  -- +goose StatementEnd
    75  
    76  CREATE TRIGGER update_current_margin_levels AFTER INSERT ON margin_levels FOR EACH ROW EXECUTE function update_current_margin_levels();
    77  
    78  DROP VIEW all_margin_levels;
    79  DROP MATERIALIZED VIEW conflated_margin_levels;
    80  
    81  
    82  CREATE MATERIALIZED VIEW conflated_margin_levels
    83              WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS
    84  SELECT account_id,
    85         order_margin_account_id,
    86         time_bucket('1 minute', vega_time) AS bucket,
    87         last(maintenance_margin, vega_time) AS maintenance_margin,
    88         last(search_level, vega_time) AS search_level,
    89         last(initial_margin, vega_time) AS initial_margin,
    90         last(collateral_release_level, vega_time) AS collateral_release_level,
    91         last(order_margin, vega_time) AS order_margin,
    92         last(timestamp, vega_time) AS timestamp,
    93         last(tx_hash, vega_time) AS tx_hash,
    94         last(vega_time, vega_time) AS vega_time,
    95         last(margin_mode, vega_time) AS margin_mode,
    96         last(margin_factor, vega_time) AS margin_factor
    97  FROM margin_levels
    98  GROUP BY account_id, order_margin_account_id, bucket WITH NO DATA;
    99  
   100  -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures
   101  -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day )
   102  SELECT add_continuous_aggregate_policy('conflated_margin_levels', start_offset => INTERVAL '1 day',
   103      end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute');
   104  
   105  CREATE VIEW all_margin_levels AS
   106  (
   107  SELECT margin_levels.account_id,
   108         margin_levels.order_margin_account_id,
   109         margin_levels."timestamp",
   110         margin_levels.maintenance_margin,
   111         margin_levels.search_level,
   112         margin_levels.initial_margin,
   113         margin_levels.collateral_release_level,
   114         margin_levels.order_margin,
   115         margin_levels.tx_hash,
   116         margin_levels.vega_time,
   117         margin_levels.margin_mode,
   118         margin_levels.margin_factor
   119  FROM margin_levels
   120  UNION ALL
   121  SELECT conflated_margin_levels.account_id,
   122         conflated_margin_levels.order_margin_account_id,
   123         conflated_margin_levels."timestamp",
   124         conflated_margin_levels.maintenance_margin,
   125         conflated_margin_levels.search_level,
   126         conflated_margin_levels.initial_margin,
   127         conflated_margin_levels.collateral_release_level,
   128         conflated_margin_levels.order_margin,
   129         conflated_margin_levels.tx_hash,
   130         conflated_margin_levels.vega_time,
   131         conflated_margin_levels.margin_mode,
   132         conflated_margin_levels.margin_factor
   133  FROM conflated_margin_levels
   134  WHERE conflated_margin_levels.vega_time < (SELECT coalesce(min(margin_levels.vega_time), 'infinity') FROM margin_levels));
   135  
   136  -- +goose Down
   137  -- nothing to do, we're not going to convert it back