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

     1  -- +goose Up
     2  
     3  -- +goose StatementBegin
     4  DO $$
     5  BEGIN
     6      IF NOT EXISTS (SELECT * FROM timescaledb_information.hypertables WHERE hypertable_name = 'stop_orders') THEN
     7          PERFORM create_hypertable('stop_orders', 'vega_time', chunk_time_interval => INTERVAL '1 day', migrate_data => true);
     8      END IF;
     9  END $$;
    10  -- +goose StatementEnd
    11  
    12  create table if not exists stop_orders_live (
    13      id bytea not null,
    14      oco_link_id bytea,
    15      expires_at timestamp with time zone,
    16      expiry_strategy stop_order_expiry_strategy not null,
    17      trigger_direction stop_order_trigger_direction not null,
    18      status stop_order_status not null,
    19      created_at timestamp with time zone not null,
    20      updated_at timestamp with time zone,
    21      order_id bytea not null,
    22      trigger_price text,
    23      trigger_percent_offset text,
    24      party_id bytea not null,
    25      market_id bytea not null,
    26      vega_time timestamp with time zone not null,
    27      seq_num bigint not null,
    28      tx_hash bytea not null,
    29      submission jsonb,
    30      primary key(id)
    31  );
    32  
    33  create index idx_stop_orders_live_id_vega_time_seq_num on stop_orders_live(id, vega_time desc, seq_num desc);
    34  create index idx_stop_orders_live_created_at_id_vega_time_seq_num on stop_orders_live(created_at desc, id, vega_time desc, seq_num desc);
    35  create index idx_stop_orders_live_market_id_created_at_id_vega_time_seq_num on stop_orders_live(market_id, created_at desc, id, vega_time desc, seq_num desc);
    36  create index idx_stop_orders_live_party_id_created_at_id_vega_time_seq_num on stop_orders_live(party_id, created_at desc, id, vega_time desc, seq_num desc);
    37  
    38  -- +goose StatementBegin
    39  create or replace function stop_orders_live_insert_trigger()
    40  returns trigger
    41      language plpgsql
    42      as $$
    43  begin
    44      delete from stop_orders_live
    45      where id = new.id;
    46  
    47      if new.status in ('STATUS_UNSPECIFIED', 'STATUS_PENDING') then
    48          insert into stop_orders_live
    49          values (new.id, new.oco_link_id, new.expires_at, new.expiry_strategy, new.trigger_direction, new.status,
    50                  new.created_at, new.updated_at, new.order_id, new.trigger_price, new.trigger_percent_offset, new.party_id,
    51                  new.market_id, new.vega_time, new.seq_num, new.tx_hash, new.submission);
    52      end if;
    53  
    54      return new;
    55  end;
    56  $$;
    57  -- +goose StatementEnd
    58  
    59  create trigger stop_orders_live_insert_trigger before insert on stop_orders for each row execute function stop_orders_live_insert_trigger();
    60  
    61  -- +goose Down
    62  
    63  drop trigger if exists stop_orders_live_insert_trigger on stop_orders;
    64  drop function if exists stop_orders_live_insert_trigger;
    65  drop table if exists stop_orders_live;