code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0003_successor_markets.sql (about) 1 -- +goose Up 2 ALTER TABLE markets 3 ADD IF NOT EXISTS parent_market_id BYTEA NULL, 4 ADD IF NOT EXISTS insurance_pool_fraction NUMERIC NULL 5 ; 6 7 ALTER TABLE markets_current 8 ADD IF NOT EXISTS parent_market_id BYTEA NULL, 9 ADD IF NOT EXISTS insurance_pool_fraction NUMERIC NULL 10 ; 11 12 -- +goose StatementBegin 13 CREATE OR REPLACE FUNCTION update_current_markets() 14 RETURNS TRIGGER 15 LANGUAGE PLPGSQL AS 16 $$ 17 BEGIN 18 INSERT INTO markets_current(id,tx_hash,vega_time,instrument_id,tradable_instrument,decimal_places,fees,opening_auction,price_monitoring_settings,liquidity_monitoring_parameters,trading_mode,state,market_timestamps,position_decimal_places,lp_price_range, linear_slippage_factor, quadratic_slippage_factor, parent_market_id, insurance_pool_fraction) 19 VALUES(NEW.id,NEW.tx_hash,NEW.vega_time,NEW.instrument_id,NEW.tradable_instrument,NEW.decimal_places,NEW.fees,NEW.opening_auction,NEW.price_monitoring_settings,NEW.liquidity_monitoring_parameters,NEW.trading_mode,NEW.state,NEW.market_timestamps,NEW.position_decimal_places,NEW.lp_price_range, NEW.linear_slippage_factor, NEW.quadratic_slippage_factor, NEW.parent_market_id, NEW.insurance_pool_fraction) 20 ON CONFLICT(id) DO UPDATE SET 21 tx_hash=EXCLUDED.tx_hash, 22 instrument_id=EXCLUDED.instrument_id, 23 tradable_instrument=EXCLUDED.tradable_instrument, 24 decimal_places=EXCLUDED.decimal_places, 25 fees=EXCLUDED.fees, 26 opening_auction=EXCLUDED.opening_auction, 27 price_monitoring_settings=EXCLUDED.price_monitoring_settings, 28 liquidity_monitoring_parameters=EXCLUDED.liquidity_monitoring_parameters, 29 trading_mode=EXCLUDED.trading_mode, 30 state=EXCLUDED.state, 31 market_timestamps=EXCLUDED.market_timestamps, 32 position_decimal_places=EXCLUDED.position_decimal_places, 33 lp_price_range=EXCLUDED.lp_price_range, 34 linear_slippage_factor=EXCLUDED.linear_slippage_factor, 35 quadratic_slippage_factor=EXCLUDED.quadratic_slippage_factor, 36 vega_time=EXCLUDED.vega_time, 37 parent_market_id=EXCLUDED.parent_market_id, 38 insurance_pool_fraction=EXCLUDED.insurance_pool_fraction; 39 RETURN NULL; 40 END; 41 $$; 42 -- +goose StatementEnd 43 44 CREATE TABLE IF NOT EXISTS market_lineage ( 45 market_id BYTEA NOT NULL PRIMARY KEY, 46 parent_market_id BYTEA, -- the root market in the lineage chain will not have a parent, but all subsequent children will 47 root_id BYTEA NOT NULL, -- market id of the first market in the lineage chain 48 vega_time TIMESTAMP WITH TIME ZONE NOT NULL 49 ); 50 51 52 -- +goose StatementBegin 53 CREATE OR REPLACE FUNCTION update_market_lineage() 54 RETURNS TRIGGER 55 LANGUAGE PLPGSQL AS 56 $$ 57 DECLARE 58 lineage_root bytea; 59 BEGIN 60 -- market lineage should only be updated if a market moved from opening auction to continuous trading 61 IF OLD.trading_mode != 'TRADING_MODE_OPENING_AUCTION' OR NEW.trading_mode != 'TRADING_MODE_CONTINUOUS' THEN 62 RETURN NULL; 63 END IF; 64 65 -- make sure the record doesn't already exist 66 SELECT root_id 67 INTO lineage_root 68 FROM market_lineage WHERE market_id = NEW.parent_market_id; 69 70 IF lineage_root IS NULL THEN 71 -- if the parent market doesn't exist in the lineage chain, then the market is the root of the lineage chain 72 lineage_root := NEW.id; 73 END IF; 74 75 -- insert the lineage entry 76 INSERT INTO market_lineage (market_id, parent_market_id, root_id, vega_time) 77 VALUES (NEW.id, NEW.parent_market_id, lineage_root, NEW.vega_time) 78 ON CONFLICT (market_id) 79 DO NOTHING; 80 81 RETURN NULL; 82 END; 83 $$; 84 -- +goose StatementEnd 85 86 CREATE TRIGGER update_market_lineage AFTER INSERT OR UPDATE ON markets FOR EACH ROW EXECUTE FUNCTION update_market_lineage(); 87 88 ALTER TYPE proposal_error ADD VALUE IF NOT EXISTS 'PROPOSAL_ERROR_INVALID_SUCCESSOR_MARKET'; 89 90 -- +goose Down 91 92 -- We cannot just drop a value from an enum so we have to create a new type 93 CREATE TYPE proposal_error_new AS enum('PROPOSAL_ERROR_UNSPECIFIED', 'PROPOSAL_ERROR_CLOSE_TIME_TOO_SOON', 'PROPOSAL_ERROR_CLOSE_TIME_TOO_LATE', 'PROPOSAL_ERROR_ENACT_TIME_TOO_SOON', 'PROPOSAL_ERROR_ENACT_TIME_TOO_LATE', 'PROPOSAL_ERROR_INSUFFICIENT_TOKENS', 'PROPOSAL_ERROR_INVALID_INSTRUMENT_SECURITY', 'PROPOSAL_ERROR_NO_PRODUCT', 'PROPOSAL_ERROR_UNSUPPORTED_PRODUCT', 'PROPOSAL_ERROR_NO_TRADING_MODE', 'PROPOSAL_ERROR_UNSUPPORTED_TRADING_MODE', 'PROPOSAL_ERROR_NODE_VALIDATION_FAILED', 'PROPOSAL_ERROR_MISSING_BUILTIN_ASSET_FIELD', 'PROPOSAL_ERROR_MISSING_ERC20_CONTRACT_ADDRESS', 'PROPOSAL_ERROR_INVALID_ASSET', 'PROPOSAL_ERROR_INCOMPATIBLE_TIMESTAMPS', 'PROPOSAL_ERROR_NO_RISK_PARAMETERS', 'PROPOSAL_ERROR_NETWORK_PARAMETER_INVALID_KEY', 'PROPOSAL_ERROR_NETWORK_PARAMETER_INVALID_VALUE', 'PROPOSAL_ERROR_NETWORK_PARAMETER_VALIDATION_FAILED', 'PROPOSAL_ERROR_OPENING_AUCTION_DURATION_TOO_SMALL', 'PROPOSAL_ERROR_OPENING_AUCTION_DURATION_TOO_LARGE', 'PROPOSAL_ERROR_MARKET_MISSING_LIQUIDITY_COMMITMENT', 'PROPOSAL_ERROR_COULD_NOT_INSTANTIATE_MARKET', 'PROPOSAL_ERROR_INVALID_FUTURE_PRODUCT', 'PROPOSAL_ERROR_MISSING_COMMITMENT_AMOUNT', 'PROPOSAL_ERROR_INVALID_FEE_AMOUNT', 'PROPOSAL_ERROR_INVALID_SHAPE', 'PROPOSAL_ERROR_INVALID_RISK_PARAMETER', 'PROPOSAL_ERROR_MAJORITY_THRESHOLD_NOT_REACHED', 'PROPOSAL_ERROR_PARTICIPATION_THRESHOLD_NOT_REACHED', 'PROPOSAL_ERROR_INVALID_ASSET_DETAILS', 'PROPOSAL_ERROR_UNKNOWN_TYPE', 'PROPOSAL_ERROR_UNKNOWN_RISK_PARAMETER_TYPE', 'PROPOSAL_ERROR_INVALID_FREEFORM', 'PROPOSAL_ERROR_INSUFFICIENT_EQUITY_LIKE_SHARE', 'PROPOSAL_ERROR_INVALID_MARKET', 'PROPOSAL_ERROR_TOO_MANY_MARKET_DECIMAL_PLACES', 'PROPOSAL_ERROR_TOO_MANY_PRICE_MONITORING_TRIGGERS', 'PROPOSAL_ERROR_ERC20_ADDRESS_ALREADY_IN_USE'); 94 95 -- Delete anything that was using the enum value we are dropping because if we have to roll back then the data should be invalid too 96 DELETE FROM proposals WHERE reason = 'PROPOSAL_ERROR_INVALID_SUCCESSOR_MARKET'; 97 98 -- Temporarily drop the proposals_current view so we can drop the enum 99 DROP VIEW IF EXISTS proposals_current; 100 101 -- Change the table to use the new enum without the value we're dropping 102 ALTER TABLE proposals ALTER COLUMN reason TYPE proposal_error_new USING reason::text::proposal_error_new; 103 104 -- Drop the old enum which contains the value(s) we need to remove 105 DROP TYPE proposal_error; 106 107 -- Rename the new enum to the old name 108 ALTER TYPE proposal_error_new RENAME TO proposal_error; 109 110 -- Recreate the proposals_current view 111 CREATE VIEW proposals_current AS ( 112 SELECT DISTINCT ON (id) * FROM proposals ORDER BY id, vega_time DESC 113 ); 114 115 116 DROP TRIGGER IF EXISTS update_market_lineage ON markets; 117 DROP FUNCTION IF EXISTS update_market_lineage; 118 DROP TABLE IF EXISTS market_lineage CASCADE; 119 120 -- +goose StatementBegin 121 CREATE OR REPLACE FUNCTION update_current_markets() 122 RETURNS TRIGGER 123 LANGUAGE PLPGSQL AS 124 $$ 125 BEGIN 126 INSERT INTO markets_current(id,tx_hash,vega_time,instrument_id,tradable_instrument,decimal_places,fees,opening_auction,price_monitoring_settings,liquidity_monitoring_parameters,trading_mode,state,market_timestamps,position_decimal_places,lp_price_range, linear_slippage_factor, quadratic_slippage_factor) 127 VALUES(NEW.id,NEW.tx_hash,NEW.vega_time,NEW.instrument_id,NEW.tradable_instrument,NEW.decimal_places,NEW.fees,NEW.opening_auction,NEW.price_monitoring_settings,NEW.liquidity_monitoring_parameters,NEW.trading_mode,NEW.state,NEW.market_timestamps,NEW.position_decimal_places,NEW.lp_price_range, NEW.linear_slippage_factor, NEW.quadratic_slippage_factor) 128 ON CONFLICT(id) DO UPDATE SET 129 tx_hash=EXCLUDED.tx_hash, 130 instrument_id=EXCLUDED.instrument_id, 131 tradable_instrument=EXCLUDED.tradable_instrument, 132 decimal_places=EXCLUDED.decimal_places, 133 fees=EXCLUDED.fees, 134 opening_auction=EXCLUDED.opening_auction, 135 price_monitoring_settings=EXCLUDED.price_monitoring_settings, 136 liquidity_monitoring_parameters=EXCLUDED.liquidity_monitoring_parameters, 137 trading_mode=EXCLUDED.trading_mode, 138 state=EXCLUDED.state, 139 market_timestamps=EXCLUDED.market_timestamps, 140 position_decimal_places=EXCLUDED.position_decimal_places, 141 lp_price_range=EXCLUDED.lp_price_range, 142 linear_slippage_factor=EXCLUDED.linear_slippage_factor, 143 quadratic_slippage_factor=EXCLUDED.quadratic_slippage_factor, 144 vega_time=EXCLUDED.vega_time; 145 RETURN NULL; 146 END; 147 $$; 148 -- +goose StatementEnd 149 150 ALTER TABLE markets_current 151 DROP IF EXISTS parent_market_id, 152 DROP IF EXISTS insurance_pool_fraction 153 ; 154 155 ALTER TABLE markets 156 DROP IF EXISTS parent_market_id, 157 DROP IF EXISTS insurance_pool_fraction 158 ;