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

     1  -- +goose Up
     2  -- So because I think of some obscure bug in timescaledb, we can't simply 
     3  -- create_hypertable ('oracle_data', ...), because in the past it had a previous
     4  -- column, 'matched_spec_ids'. When you drop a column in postgres, I don't think
     5  -- it actually goes away, it just gets flagged as 'removed' and the CSV import
     6  -- doesn't work, complaining about 
     7  -- - attribute 6 of type _timescaledb_internal._hyper_184_47_chunk has wrong type
     8  -- - 'Table has type bigint, but query expects timestamp with time zone.'
     9  -- So we have to create a new table, copy the data in, drop the old table, and rename.
    10     CREATE TABLE oracle_data_temp (LIKE oracle_data INCLUDING ALL);
    11  
    12     INSERT INTO oracle_data_temp
    13     SELECT *
    14       FROM oracle_data;
    15  
    16       DROP TABLE oracle_data;
    17  
    18      ALTER TABLE oracle_data_temp
    19  RENAME TO oracle_data;
    20  
    21     SELECT create_hypertable ('oracle_data', 'vega_time', chunk_time_interval => INTERVAL '1 day', migrate_data => TRUE);
    22  
    23  -- oracle_data_oracle_specs is fine though we don't have to faff around with it because we never dropped any columns
    24     SELECT create_hypertable ('oracle_data_oracle_specs', 'vega_time', chunk_time_interval => INTERVAL '1 day', migrate_data => TRUE);
    25  
    26  -- +goose Down
    27  -- to de-hypertable ourselves we have to make a new table and copy stuff in
    28  -- It would be nice to create the new table like this but it confuses timescaledb
    29     CREATE TABLE oracle_data_temp (LIKE oracle_data INCLUDING ALL);
    30  
    31     INSERT INTO oracle_data_temp
    32     SELECT *
    33       FROM oracle_data;
    34  
    35       DROP TABLE oracle_data;
    36  
    37      ALTER TABLE oracle_data_temp
    38  RENAME TO oracle_data;
    39  
    40  -- same for the join table to specs
    41     CREATE TABLE oracle_data_oracle_specs_temp (LIKE oracle_data_oracle_specs INCLUDING ALL);
    42  
    43     INSERT INTO oracle_data_oracle_specs_temp
    44     SELECT *
    45       FROM oracle_data_oracle_specs;
    46  
    47       DROP TABLE oracle_data_oracle_specs;
    48  
    49      ALTER TABLE oracle_data_oracle_specs_temp
    50  RENAME TO oracle_data_oracle_specs;