diff options
| -rw-r--r-- | src/exchangedb/common-0001.sql | 752 | ||||
| -rw-r--r-- | src/exchangedb/drop-common.sql | 19 | ||||
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 322 | ||||
| -rw-r--r-- | src/exchangedb/shard-0001-part.sql | 67 |
4 files changed, 860 insertions, 300 deletions
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql index 23b50d25d..01e68dfb5 100644 --- a/src/exchangedb/common-0001.sql +++ b/src/exchangedb/common-0001.sql @@ -1136,7 +1136,560 @@ BEGIN END $$; +-------------------------------------------------------------------------- +-- Tables for P2P payments +-------------------------------------------------------------------------- + +------------------------------- purse_requests ---------------------------------------- + +CREATE OR REPLACE FUNCTION create_table_purse_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' + ',purse_expiration INT8 NOT NULL' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',age_limit INT4 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',balance_val INT8 NOT NULL DEFAULT (0)' + ',balance_frac INT4 NOT NULL DEFAULT (0)' + ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by marge_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' + 'ON ' || table_name || ' ' + '(merge_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' + 'UNIQUE (purse_requests_serial_id) ' + ); +END +$$; + +---------------------------- purse_merges ----------------------------- + +CREATE OR REPLACE FUNCTION create_table_purse_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY '-- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)'--REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE + ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' + ',merge_timestamp INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' + 'UNIQUE (purse_merge_request_serial_id) ' + ); +END +$$; + +------------------------- account_merges ---------------------------- + +CREATE OR REPLACE FUNCTION create_table_account_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'account_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' -- REFERENCES purse_requests (purse_pub) + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE account_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' + 'UNIQUE (account_merge_request_serial_id) ' + ); +END +$$; + +------------------------- contracts ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_contracts( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'contracts'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' + ',e_contract BYTEA NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE contracts_' || partition_suffix || ' ' + 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' + 'UNIQUE (contract_serial_id) ' + ); +END +$$; + +--------------------------- history_requests -------------------------- + + +CREATE OR REPLACE FUNCTION create_table_history_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'history_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + ',request_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',history_fee_val INT8 NOT NULL' + ',history_fee_frac INT4 NOT NULL' + ',PRIMARY KEY (reserve_pub,request_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + +END +$$; + +--------------------------- close_requests --------------------------- + +CREATE OR REPLACE FUNCTION create_table_close_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + ',close_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',close_val INT8 NOT NULL' + ',close_frac INT4 NOT NULL' + ',PRIMARY KEY (reserve_pub,close_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + +END +$$; + +------------------------------- purse_deposits ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_purse_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',PRIMARY KEY (purse_pub,coin_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by coin_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' + 'UNIQUE (purse_deposit_serial_id) ' + ); +END +$$; + +---------------------------- wads_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_wads_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',partner_serial_id INT8 NOT NULL' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',execution_time INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' + 'UNIQUE (wad_out_serial_id) ' + ); +END +$$; + +--------------------------- wad_out_entries -------------------------- + +CREATE OR REPLACE FUNCTION create_table_wad_out_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_out_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_out_serial_id INT8' -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' + ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' + ',purse_expiration INT8 NOT NULL' + ',merge_timestamp INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wad_fee_val INT8 NOT NULL' + ',wad_fee_frac INT4 NOT NULL' + ',deposit_fees_val INT8 NOT NULL' + ',deposit_fees_frac INT4 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' + 'UNIQUE (wad_out_entry_serial_id) ' + ); +END +$$; + +-------------------------- wads_in -------------------------------- + +CREATE OR REPLACE FUNCTION create_table_wads_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',origin_exchange_url TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',arrival_time INT8 NOT NULL' + ',UNIQUE (wad_id, origin_exchange_url)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' + 'UNIQUE (wad_in_serial_id) ' + ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' + 'UNIQUE (wad_id, origin_exchange_url) ' + ); +END +$$; + + +------------------------- wads_in_entries -------------------------- + +CREATE OR REPLACE FUNCTION create_table_wad_in_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_in_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_in_serial_id INT8' -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' + ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' + ',purse_expiration INT8 NOT NULL' + ',merge_timestamp INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wad_fee_val INT8 NOT NULL' + ',wad_fee_frac INT4 NOT NULL' + ',deposit_fees_val INT8 NOT NULL' + ',deposit_fees_frac INT4 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' + 'UNIQUE (wad_in_entry_serial_id) ' + ); +END +$$; + +------------------------------------------------------------------- ------------------------- Partitions ------------------------------ +------------------------------------------------------------------- CREATE OR REPLACE FUNCTION create_hash_partition( source_table_name VARCHAR @@ -1250,6 +1803,35 @@ BEGIN ALTER TABLE IF EXISTS cs_nonce_locks DETACH partition cs_nonce_locks_default; + ALTER TABLE IF EXISTS purse_requests + DETACH partition purse_requests_default; + + ALTER TABLE IF EXISTS purse_merges + DETACH partition purse_merges_default; + + ALTER TABLE IF EXISTS account_merges + DETACH partition account_merges_default; + + ALTER TABLE IF EXISTS contracts + DETACH partition contracts_default; + + ALTER TABLE IF EXISTS history_requests + DETACH partition history_requests_default; + + ALTER TABLE IF EXISTS close_requests + DETACH partition close_requests_default; + + ALTER TABLE IF EXISTS purse_deposits + DETACH partition purse_deposits_default; + + ALTER TABLE IF EXISTS wad_out_entries + DETACH partition wad_out_entries_default; + + ALTER TABLE IF EXISTS wads_in + DETACH partition wads_in_default; + + ALTER TABLE IF EXISTS wad_in_entries + DETACH partition wad_in_entries_default; END $$; @@ -1289,6 +1871,17 @@ BEGIN DROP TABLE IF EXISTS prewire_default; DROP TABLE IF EXISTS cs_nonce_locks_default; + DROP TABLE IF EXISTS purse_requests_default; + DROP TABLE IF EXISTS purse_merges_default; + DROP TABLE IF EXISTS account_merges_default; + DROP TABLE IF EXISTS contracts_default; + DROP TABLE IF EXISTS history_requests_default; + DROP TABLE IF EXISTS close_requests_default; + DROP TABLE IF EXISTS purse_deposits_default; + DROP TABLE IF EXISTS wad_out_entries_default; + DROP TABLE IF EXISTS wads_in_default; + DROP TABLE IF EXISTS wad_in_entries_default; + END $$; @@ -1481,6 +2074,76 @@ BEGIN ); PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + ---------------- P2P ---------------------- + + PERFORM create_hash_partition( + 'purse_requests' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_requests_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'purse_merges' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'account_merges' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_account_merges_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'contracts' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_contracts_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'history_requests' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'close_requests' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'purse_deposits' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_deposits_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wad_out_entries' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wad_out_entries_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wads_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wad_in_entries' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wad_in_entries_partition(num_partitions::varchar); + num_partitions=num_partitions-1; EXIT WHEN num_partitions=0; @@ -1627,6 +2290,22 @@ BEGIN DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE ; + ALTER TABLE IF EXISTS purse_requests + DROP CONSTRAINT IF EXISTS purse_requests_reserve_pub_request_timestamp_pkey + ; + + ALTER TABLE IF EXISTS close_requests + DROP CONSTRAINT IF EXISTS close_requests_reserve_pub_close_timestamp_pkey + ; + + ALTER TABLE IF EXISTS purse_deposits + DROP CONSTRAINT IF EXISTS purse_deposits_purse_pub_coin_pub_pkey + ; + + ALTER TABLE IF EXISTS wads_in + DROP CONSTRAINT IF EXISTS wads_in_wad_id_origin_exchange_url_key CASCADE + ; + END $$; @@ -1832,6 +2511,79 @@ BEGIN ,local_user ); + ------------------- P2P -------------------- + + PERFORM create_foreign_hash_partition( + 'purse_requests' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'purse_merges' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'account_merges' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'contracts' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'history_requests' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'close_requests' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'purse_deposits' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'wad_out_entries' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'wads_in' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + PERFORM create_foreign_hash_partition( + 'wad_in_entries' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ,local_user + ); + END $$; diff --git a/src/exchangedb/drop-common.sql b/src/exchangedb/drop-common.sql index 5c4bddfdb..b14a00c82 100644 --- a/src/exchangedb/drop-common.sql +++ b/src/exchangedb/drop-common.sql @@ -61,6 +61,25 @@ DROP FUNCTION IF EXISTS create_table_reserves; DROP FUNCTION IF EXISTS create_table_cs_nonce_locks; DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition; +DROP FUNCTION IF EXISTS create_table_purse_requests; +DROP FUNCTION IF EXISTS add_constraints_to_purse_requests_partition; +DROP FUNCTION IF EXISTS create_table_purse_merges; +DROP FUNCTION IF EXISTS add_constraints_to_purse_merges_partition; +DROP FUNCTION IF EXISTS create_table_account_merges; +DROP FUNCTION IF EXISTS add_constraints_to_account_merges_partition; +DROP FUNCTION IF EXISTS create_table_contracts; +DROP FUNCTION IF EXISTS add_constraints_to_contracts_partition; +DROP FUNCTION IF EXISTS create_table_history_requests; +DROP FUNCTION IF EXISTS create_table_close_requests; +DROP FUNCTION IF EXISTS create_table_purse_deposits; +DROP FUNCTION IF EXISTS add_constraints_to_purse_deposits_partition; +DROP FUNCTION IF EXISTS create_table_wad_out_entries; +DROP FUNCTION IF EXISTS add_constraints_to_wad_out_entries_partition; +DROP FUNCTION IF EXISTS create_table_wads_in; +DROP FUNCTION IF EXISTS add_constraints_to_wads_in_partition; +DROP FUNCTION IF EXISTS create_table_wad_in_entries; +DROP FUNCTION IF EXISTS add_constraints_to_wad_in_entries_partition; + DROP FUNCTION IF EXISTS create_partitioned_table; DROP FUNCTION IF EXISTS create_hash_partition; DROP FUNCTION IF EXISTS create_range_partition; diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index a478a0d16..b35348235 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -991,21 +991,8 @@ COMMENT ON COLUMN partners.master_sig -- ------------------------------ purse_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_requests - (purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) - ,merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32) - ,purse_expiration INT8 NOT NULL - ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) - ,age_limit INT4 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,balance_val INT8 NOT NULL DEFAULT (0) - ,balance_frac INT4 NOT NULL DEFAULT (0) - ,purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64) - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_requests(); + COMMENT ON TABLE purse_requests IS 'Requests establishing purses, associating them with a contract but without a target reserve'; COMMENT ON COLUMN purse_requests.purse_pub @@ -1021,45 +1008,18 @@ COMMENT ON COLUMN purse_requests.balance_val COMMENT ON COLUMN purse_requests.purse_sig IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST'; --- FIXME: change to materialized index by marge_pub! -CREATE INDEX IF NOT EXISTS purse_requests_merge_pub - ON purse_requests (merge_pub); - CREATE TABLE IF NOT EXISTS purse_requests_default PARTITION OF purse_requests FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' - 'UNIQUE (purse_requests_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_purse_requests_partition('default'); -- ------------------------------ purse_merges ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_merges - (purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ,reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)--REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE - ,merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64) - ,merge_timestamp INT8 NOT NULL - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_merges(); + COMMENT ON TABLE purse_merges IS 'Merge requests where a purse-owner requested merging the purse into the account'; COMMENT ON COLUMN purse_merges.partner_serial_id @@ -1073,47 +1033,17 @@ COMMENT ON COLUMN purse_merges.merge_sig COMMENT ON COLUMN purse_merges.merge_timestamp IS 'when was the merge message signed'; -CREATE INDEX IF NOT EXISTS purse_merges_purse_pub - ON purse_merges (purse_pub); --- FIXME: change to materialized index by reserve_pub! -CREATE INDEX IF NOT EXISTS purse_merges_reserve_pub - ON purse_merges (reserve_pub); -COMMENT ON INDEX purse_merges_reserve_pub - IS 'needed in reserve history computation'; - CREATE TABLE IF NOT EXISTS purse_merges_default PARTITION OF purse_merges FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_merges_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' - 'UNIQUE (purse_merge_request_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_purse_merges_partition('default'); - -- ------------------------------ account_merges ---------------------------------------- -CREATE TABLE IF NOT EXISTS account_merges - (account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) -- REFERENCES purse_requests (purse_pub) - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_account_merges(); + COMMENT ON TABLE account_merges IS 'Merge requests where a purse- and account-owner requested merging the purse into the account'; COMMENT ON COLUMN account_merges.reserve_pub @@ -1123,48 +1053,17 @@ COMMENT ON COLUMN account_merges.purse_pub COMMENT ON COLUMN account_merges.reserve_sig IS 'signature by the reserve private key affirming the merge, of type TALER_SIGNATURE_WALLET_ACCOUNT_MERGE'; -CREATE INDEX IF NOT EXISTS account_merges_purse_pub - ON account_merges (purse_pub); -COMMENT ON INDEX account_merges_purse_pub - IS 'needed when checking for a purse merge status'; - --- FIXME: change to materialized index by reserve_pub! -CREATE INDEX IF NOT EXISTS account_merges_by_reserve_pub - ON account_merges (reserve_pub); - CREATE TABLE IF NOT EXISTS account_merges_default PARTITION OF account_merges FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE account_merges_' || partition_suffix || ' ' - 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' - 'UNIQUE (account_merge_request_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_account_merges_partition('default'); -- ------------------------------ contracts ---------------------------------------- -CREATE TABLE IF NOT EXISTS contracts - (contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) - ,pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32) - ,e_contract BYTEA NOT NULL - ,purse_expiration INT8 NOT NULL - ,PRIMARY KEY (purse_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_contracts(); + COMMENT ON TABLE contracts IS 'encrypted contracts associated with purses'; COMMENT ON COLUMN contracts.purse_pub @@ -1178,35 +1077,13 @@ CREATE TABLE IF NOT EXISTS contracts_default PARTITION OF contracts FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE contracts_' || partition_suffix || ' ' - 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' - 'UNIQUE (contract_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_contracts_partition('default'); -- ------------------------------ history_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS history_requests - (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE - ,request_timestamp INT8 NOT NULL - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,history_fee_val INT8 NOT NULL - ,history_fee_frac INT4 NOT NULL - ,PRIMARY KEY (reserve_pub,request_timestamp) - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_history_requests(); + COMMENT ON TABLE history_requests IS 'Paid history requests issued by a client against a reserve'; COMMENT ON COLUMN history_requests.request_timestamp @@ -1220,18 +1097,10 @@ CREATE TABLE IF NOT EXISTS history_requests_default PARTITION OF history_requests FOR VALUES WITH (MODULUS 1, REMAINDER 0); - -- ------------------------------ close_requests ---------------------------------------- -CREATE TABLE IF NOT EXISTS close_requests - (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE - ,close_timestamp INT8 NOT NULL - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,close_val INT8 NOT NULL - ,close_frac INT4 NOT NULL - ,PRIMARY KEY (reserve_pub,close_timestamp) - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_close_requests(); + COMMENT ON TABLE close_requests IS 'Explicit requests by a reserve owner to close a reserve immediately'; COMMENT ON COLUMN close_requests.close_timestamp @@ -1248,17 +1117,8 @@ CREATE TABLE IF NOT EXISTS close_requests_default -- ------------------------------ purse_deposits ---------------------------------------- -CREATE TABLE IF NOT EXISTS purse_deposits - (purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) - ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) - -- ,PRIMARY KEY (purse_pub,coin_pub) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_purse_deposits(); + COMMENT ON TABLE purse_deposits IS 'Requests depositing coins into a purse'; COMMENT ON COLUMN purse_deposits.partner_serial_id @@ -1272,43 +1132,17 @@ COMMENT ON COLUMN purse_deposits.amount_with_fee_val COMMENT ON COLUMN purse_deposits.coin_sig IS 'Signature of the coin affirming the deposit into the purse, of type TALER_SIGNATURE_PURSE_DEPOSIT'; --- FIXME: change to materialized index by coin_pub! -CREATE INDEX IF NOT EXISTS purse_deposits_by_coin_pub - ON purse_deposits (coin_pub); - CREATE TABLE IF NOT EXISTS purse_deposits_default PARTITION OF purse_deposits FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' - 'UNIQUE (purse_deposit_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_purse_deposits_partition('default'); -- ------------------------------ wads_out ---------------------------------------- -CREATE TABLE IF NOT EXISTS wads_out - (wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24) - ,partner_serial_id INT8 NOT NULL REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,execution_time INT8 NOT NULL - ) - PARTITION BY HASH (wad_id); +SELECT create_table_wads_out(); + COMMENT ON TABLE wads_out IS 'Wire transfers made to another exchange to transfer purse funds'; COMMENT ON COLUMN wads_out.wad_id @@ -1320,54 +1154,17 @@ COMMENT ON COLUMN wads_out.amount_val COMMENT ON COLUMN wads_out.execution_time IS 'Time when the wire transfer was scheduled'; -CREATE INDEX IF NOT EXISTS wads_out_index_by_wad_id - ON wads_out (wad_id); - CREATE TABLE IF NOT EXISTS wads_out_default PARTITION OF wads_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wads_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' - 'UNIQUE (wad_out_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_wads_out_partition('default'); -- ------------------------------ wads_out_entries ---------------------------------------- -CREATE TABLE IF NOT EXISTS wad_out_entries - (wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,wad_out_serial_id INT8 -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE - ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32) - ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32) - ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64) - ,purse_expiration INT8 NOT NULL - ,merge_timestamp INT8 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,wad_fee_val INT8 NOT NULL - ,wad_fee_frac INT4 NOT NULL - ,deposit_fees_val INT8 NOT NULL - ,deposit_fees_frac INT4 NOT NULL - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64) - ) - PARTITION BY HASH (purse_pub); --- FIXME: convert to materialized index! -CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_reserve_pub - ON wad_out_entries (reserve_pub); +SELECT create_table_wad_out_entries(); + COMMENT ON TABLE wad_out_entries IS 'Purses combined into a wad'; COMMENT ON COLUMN wad_out_entries.wad_out_serial_id @@ -1397,36 +1194,12 @@ CREATE TABLE IF NOT EXISTS wad_out_entries_default PARTITION OF wad_out_entries FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' - 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' - 'UNIQUE (wad_out_entry_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_wad_out_entries_partition('default'); - -- ------------------------------ wads_in ---------------------------------------- -CREATE TABLE IF NOT EXISTS wads_in - (wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24) - ,origin_exchange_url TEXT NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,arrival_time INT8 NOT NULL - ,UNIQUE (wad_id, origin_exchange_url) - ) - PARTITION BY HASH (wad_id); +SELECT create_table_wads_in(); + COMMENT ON TABLE wads_in IS 'Incoming exchange-to-exchange wad wire transfers'; COMMENT ON COLUMN wads_in.wad_id @@ -1442,44 +1215,13 @@ CREATE TABLE IF NOT EXISTS wads_in_default PARTITION OF wads_in FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wads_in_' || partition_suffix || ' ' - 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' - 'UNIQUE (wad_in_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_wads_in_partition('default'); -- ------------------------------ wads_in_entries ---------------------------------------- -CREATE TABLE IF NOT EXISTS wad_in_entries - (wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE - ,wad_in_serial_id INT8 -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE - ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32) - ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32) - ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64) - ,purse_expiration INT8 NOT NULL - ,merge_timestamp INT8 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,wad_fee_val INT8 NOT NULL - ,wad_fee_frac INT4 NOT NULL - ,deposit_fees_val INT8 NOT NULL - ,deposit_fees_frac INT4 NOT NULL - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64) - ) - PARTITION BY HASH (purse_pub); +SELECT create_table_wad_in_entries(); + COMMENT ON TABLE wad_in_entries IS 'list of purses aggregated in a wad according to the sending exchange'; COMMENT ON COLUMN wad_in_entries.wad_in_serial_id @@ -1504,31 +1246,11 @@ COMMENT ON COLUMN wad_in_entries.reserve_sig IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE'; COMMENT ON COLUMN wad_in_entries.purse_sig IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE'; --- FIXME: convert to materialized index! -CREATE INDEX IF NOT EXISTS wad_in_entries_reserve_pub - ON wad_in_entries (reserve_pub); -COMMENT ON INDEX wad_in_entries_reserve_pub - IS 'needed to compute reserve history'; CREATE TABLE IF NOT EXISTS wad_in_entries_default PARTITION OF wad_in_entries FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' - 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' - 'UNIQUE (wad_in_entry_serial_id) ' - ); -END -$$; - SELECT add_constraints_to_wad_in_entries_partition('default'); diff --git a/src/exchangedb/shard-0001-part.sql b/src/exchangedb/shard-0001-part.sql index d971b0c7a..aa912c058 100644 --- a/src/exchangedb/shard-0001-part.sql +++ b/src/exchangedb/shard-0001-part.sql @@ -89,6 +89,33 @@ BEGIN PERFORM create_table_cs_nonce_locks(shard_suffix); PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); + PERFORM create_table_purse_requests(shard_suffix); + PERFORM add_constraints_to_purse_requests_partition(shard_suffix); + + PERFORM create_table_purse_merges(shard_suffix); + PERFORM add_constraints_to_purse_merges_partition(shard_suffix); + + PERFORM create_table_account_merges(shard_suffix); + PERFORM add_constraints_to_account_merges_partition(shard_suffix); + + PERFORM create_table_contracts(shard_suffix); + PERFORM add_constraints_to_contracts_partition(shard_suffix); + + PERFORM create_table_history_requests(shard_suffix); + + PERFORM create_table_close_requests(shard_suffix); + + PERFORM create_table_purse_deposits(shard_suffix); + PERFORM add_constraints_to_purse_deposits_partition(shard_suffix); + + PERFORM create_table_wad_out_entries(shard_suffix); + PERFORM add_constraints_to_wad_out_entries_partition(shard_suffix); + + PERFORM create_table_wads_in(shard_suffix); + PERFORM add_constraints_to_wads_in_partition(shard_suffix); + + PERFORM create_table_wad_in_entries(shard_suffix); + PERFORM add_constraints_to_wad_in_entries_partition(shard_suffix); END $$; @@ -193,6 +220,46 @@ BEGIN 'DROP TABLE IF EXISTS %I CASCADE' ,'cs_nonce_locks_' || shard_suffix ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'purse_requests_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'purse_merges_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'account_merges_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'contracts_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'history_requests_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'close_requests_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'purse_deposits_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'wad_out_entries_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'wads_in_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'wad_in_entries_' || shard_suffix + ); END $$; |