diff options
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 783 |
1 files changed, 783 insertions, 0 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql new file mode 100644 index 000000000..6998320ce --- /dev/null +++ b/src/exchangedb/exchange-0001.sql @@ -0,0 +1,783 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +-- Everything in one big transaction +BEGIN; + +SET search_path TO exchange; + +--------------------------------------------------------------------------- +-- General procedures for DB setup +--------------------------------------------------------------------------- + +CREATE TABLE IF NOT EXISTS exchange_tables + (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,name VARCHAR NOT NULL + ,version VARCHAR NOT NULL + ,action VARCHAR NOT NULL + ,partitioned BOOL NOT NULL + ,by_range BOOL NOT NULL + ,finished BOOL NOT NULL DEFAULT(FALSE)); +COMMENT ON TABLE exchange_tables + IS 'Tables of the exchange and their status'; +COMMENT ON COLUMN exchange_tables.name + IS 'Base name of the table (without partition/shard)'; +COMMENT ON COLUMN exchange_tables.version + IS 'Version of the DB in which the given action happened'; +COMMENT ON COLUMN exchange_tables.action + IS 'Action to take on the table (e.g. create, alter, constrain, unconstrain, or drop)'; +COMMENT ON COLUMN exchange_tables.partitioned + IS 'TRUE if the table is partitioned'; +COMMENT ON COLUMN exchange_tables.by_range + IS 'TRUE if the table is partitioned by range'; +COMMENT ON COLUMN exchange_tables.finished + IS 'TRUE if the respective migration has been run'; + + + +CREATE OR REPLACE FUNCTION create_partitioned_table( + IN table_definition VARCHAR + ,IN table_name VARCHAR + ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables + ,IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + IF shard_suffix IS NOT NULL THEN + table_name=table_name || '_' || shard_suffix; + main_table_partition_str = ''; + END IF; + + EXECUTE FORMAT( + table_definition, + table_name, + main_table_partition_str + ); + +END +$$; + + + + + +CREATE OR REPLACE FUNCTION create_tables( + num_partitions INTEGER + ,shard_domain VARCHAR +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,by_range + FROM exchange_tables + WHERE NOT finished + ORDER BY table_serial_id ASC; +BEGIN + FOR rec IN tc + LOOP + -- First create the master table, either + -- completely unpartitioned, or with one + -- master and the 'default' partition + IF IS NULL num_partitions + THEN + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + ELSE + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,0 + ); + END IF + + IF NOT NULL shard_domain + THEN + -- FIXME: attach shards! + -- FIXME: how will migration work with shards!? + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_XXX_%s (%s)'::text + ,rec.action + ,rec.name + ,i::varchar + ); + END LOOP; + ELSE + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,i::varchar + ); + END LOOP; + END IF + + IF 0 < num_partitions + THEN + -- FIXME: detach default partition! + END IF + + UPDATE exchange_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; + END LOOP; +END +$$; + +COMMENT ON FUNCTION create_tables + IS 'Creates all tables for the given number of partitions that need creating.'; + + + +-- This is run last by dbinit, if partitions exist +-- or if 'force_create_partitions' is set (otherwise, +-- we are not expected to create partitions if there +-- is only 1). +CREATE OR REPLACE FUNCTION create_partitions( + IN part_idx INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + ,action + ,partitioned + ,by_range + FROM exchange_tables + WHERE version=in_version + AND partitioned + ORDER BY table_seria_id ASC; +BEGIN + FOR rec IN tc + LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)' + ,rec.action + ,rec.name + ,shard_idx::varchar + ); + END LOOP; +END +$$; + +COMMENT ON FUNCTION create_partitions + IS 'Creates all partitions that need creating.'; + + + + +CREATE OR REPLACE FUNCTION drop_default_partitions_NG() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +BEGIN + RAISE NOTICE 'Dropping default tables of partitioned tables'; + FOR rec IN tc + LOOP + EXECUTE FORMAT ( + 'DROP TABLE IF EXISTS %s_default ;'::text, + rec.name; +END +$$; + +COMMENT ON FUNCTION drop_default_partitions + IS 'Drop all default partitions once other partitions are attached. + Might be needed in sharding too.'; + + +CREATE OR REPLACE FUNCTION detach_default_partitions_NG() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +BEGIN + RAISE NOTICE 'Detaching all default table partitions'; + FOR rec IN tc + LOOP + EXECUTE FORMAT ( + 'ALTER TABLE IF EXISTS %s DETACH PARTITION %s_default;'::text, + rec.name, + rec.name + ); + END LOOP; +END +$$; + +COMMENT ON FUNCTION detach_default_partitions + IS 'We need to drop default and create new one before deleting the default partitions + otherwise constraints get lost too. Might be needed in sharding too'; + + +CREATE OR REPLACE FUNCTION create_hash_partition_NG( + source_table_name VARCHAR + ,modulus INTEGER + ,partition_num INTEGER + ) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I ' + 'PARTITION OF %I ' + 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,source_table_name || '_' || partition_num + ,source_table_name + ,modulus + ,partition_num-1 + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_partitions_NG( + num_partitions INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +DECLARE + i INTEGER; +BEGIN + PERFORM detach_default_partitions(); + FOR rec IN tc + LOOP + i := num_partitions + LOOP + + PERFORM create_hash_partition( + quote_literal (rec.name) + ,num_partitions + ,i + ); + + i=i-1; + EXIT WHEN i=0; + END LOOP; -- i = num_partitions ... 0 + END LOOP; -- for all partitioned tables + + PERFORM drop_default_partitions(); + +END +$$; + + +-- OLD LOGIC: +------------------------------------------------------------------- +------------------------- Partitions ------------------------------ +------------------------------------------------------------------- + + +CREATE OR REPLACE FUNCTION create_range_partition( + source_table_name VARCHAR + ,partition_num INTEGER +) + RETURNS void + LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'TODO'; +END +$$; + +CREATE OR REPLACE FUNCTION detach_default_partitions() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Detaching all default table partitions'; + + ALTER TABLE IF EXISTS wire_targets + DETACH PARTITION wire_targets_default; + + ALTER TABLE IF EXISTS reserves + DETACH PARTITION reserves_default; + + ALTER TABLE IF EXISTS reserves_in + DETACH PARTITION reserves_in_default; + + ALTER TABLE IF EXISTS reserves_close + DETACH PARTITION reserves_close_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 reserves_open_requests + DETACH partition reserves_open_requests_default; + + ALTER TABLE IF EXISTS reserves_out + DETACH PARTITION reserves_out_default; + + ALTER TABLE IF EXISTS reserves_out_by_reserve + DETACH PARTITION reserves_out_by_reserve_default; + + ALTER TABLE IF EXISTS known_coins + DETACH PARTITION known_coins_default; + + ALTER TABLE IF EXISTS refresh_commitments + DETACH PARTITION refresh_commitments_default; + + ALTER TABLE IF EXISTS refresh_revealed_coins + DETACH PARTITION refresh_revealed_coins_default; + + ALTER TABLE IF EXISTS refresh_transfer_keys + DETACH PARTITION refresh_transfer_keys_default; + + ALTER TABLE IF EXISTS deposits + DETACH PARTITION deposits_default; + +--- TODO range partitioning +-- ALTER TABLE IF EXISTS deposits_by_ready +-- DETACH PARTITION deposits_by_ready_default; +-- +-- ALTER TABLE IF EXISTS deposits_for_matching +-- DETACH PARTITION deposits_default_for_matching_default; + + ALTER TABLE IF EXISTS refunds + DETACH PARTITION refunds_default; + + ALTER TABLE IF EXISTS wire_out + DETACH PARTITION wire_out_default; + + ALTER TABLE IF EXISTS aggregation_transient + DETACH PARTITION aggregation_transient_default; + + ALTER TABLE IF EXISTS aggregation_tracking + DETACH PARTITION aggregation_tracking_default; + + ALTER TABLE IF EXISTS recoup + DETACH PARTITION recoup_default; + + ALTER TABLE IF EXISTS recoup_by_reserve + DETACH PARTITION recoup_by_reserve_default; + + ALTER TABLE IF EXISTS recoup_refresh + DETACH PARTITION recoup_refresh_default; + + ALTER TABLE IF EXISTS prewire + DETACH PARTITION prewire_default; + + 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_decision + DETACH partition purse_decision_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 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 +$$; + +COMMENT ON FUNCTION detach_default_partitions + IS 'We need to drop default and create new one before deleting the default partitions + otherwise constraints get lost too. Might be needed in sharding too'; + + +CREATE OR REPLACE FUNCTION drop_default_partitions() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Dropping default table partitions'; + + DROP TABLE IF EXISTS wire_targets_default; + DROP TABLE IF EXISTS reserves_default; + DROP TABLE IF EXISTS reserves_in_default; + DROP TABLE IF EXISTS reserves_close_default; + DROP TABLE IF EXISTS reserves_open_requests_default; + DROP TABLE IF EXISTS history_requests_default; + DROP TABLE IF EXISTS close_requests_default; + + DROP TABLE IF EXISTS reserves_out_default; + DROP TABLE IF EXISTS reserves_out_by_reserve_default; + DROP TABLE IF EXISTS known_coins_default; + DROP TABLE IF EXISTS refresh_commitments_default; + DROP TABLE IF EXISTS refresh_revealed_coins_default; + DROP TABLE IF EXISTS refresh_transfer_keys_default; + DROP TABLE IF EXISTS deposits_default; +--DROP TABLE IF EXISTS deposits_by_ready_default; +--DROP TABLE IF EXISTS deposits_for_matching_default; + DROP TABLE IF EXISTS refunds_default; + DROP TABLE IF EXISTS wire_out_default; + DROP TABLE IF EXISTS aggregation_transient_default; + DROP TABLE IF EXISTS aggregation_tracking_default; + DROP TABLE IF EXISTS recoup_default; + DROP TABLE IF EXISTS recoup_by_reserve_default; + DROP TABLE IF EXISTS recoup_refresh_default; + 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_decision_default; + DROP TABLE IF EXISTS purse_merges_default; + DROP TABLE IF EXISTS account_merges_default; + DROP TABLE IF EXISTS purse_deposits_default; + DROP TABLE IF EXISTS contracts_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 +$$; + +COMMENT ON FUNCTION drop_default_partitions + IS 'Drop all default partitions once other partitions are attached. + Might be needed in sharding too.'; + +CREATE OR REPLACE FUNCTION create_partitions( + num_partitions INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + modulus INTEGER; +BEGIN + + modulus := num_partitions; + + PERFORM detach_default_partitions(); + + LOOP + + PERFORM create_hash_partition( + 'wire_targets' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'reserves_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_close' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_out_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'known_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_commitments' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_revealed_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_transfer_keys' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'deposits' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_deposits_partition(num_partitions::varchar); + +-- TODO: dynamically (!) creating/deleting deposits partitions: +-- create new partitions 'as needed', drop old ones once the aggregator has made +-- them empty; as 'new' deposits will always have deadlines in the future, this +-- would basically guarantee no conflict between aggregator and exchange service! +-- SEE also: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/ +-- (article is slightly wrong, as this works:) +--CREATE TABLE tab ( +-- id bigint GENERATED ALWAYS AS IDENTITY, +-- ts timestamp NOT NULL, +-- data text +-- PARTITION BY LIST ((ts::date)); +-- CREATE TABLE tab_def PARTITION OF tab DEFAULT; +-- BEGIN +-- CREATE TABLE tab_part2 (LIKE tab); +-- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo'); +-- alter table tab attach partition tab_part2 for values in ('2022-03-21'); +-- commit; +-- Naturally, to ensure this is actually 100% conflict-free, we'd +-- need to create tables at the granularity of the wire/refund deadlines; +-- that is right now configurable via AGGREGATOR_SHIFT option. + +-- FIXME: range partitioning +-- PERFORM create_range_partition( +-- 'deposits_by_ready' +-- ,modulus +-- ,num_partitions +-- ); +-- +-- PERFORM create_range_partition( +-- 'deposits_for_matching' +-- ,modulus +-- ,num_partitions +-- ); + + PERFORM create_hash_partition( + 'refunds' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wire_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'aggregation_transient' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'aggregation_tracking' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'recoup' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'recoup_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'recoup_refresh' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'prewire' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'cs_nonce_locks' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + + + PERFORM create_hash_partition( + 'close_requests' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'reserves_open_requests' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_open_request_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'history_requests' + ,modulus + ,num_partitions + ); + + + ---------------- 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_decision' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_decision_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( + '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; + + END LOOP; + + PERFORM drop_default_partitions(); + +END +$$; |