diff options
| -rw-r--r-- | src/exchange-tools/taler-exchange-dbinit.c | 27 | ||||
| -rw-r--r-- | src/exchangedb/Makefile.am | 12 | ||||
| -rw-r--r-- | src/exchangedb/common-0001.sql | 1876 | ||||
| -rw-r--r-- | src/exchangedb/drop0001.sql | 28 | ||||
| -rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 2826 | ||||
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 2487 | ||||
| -rw-r--r-- | src/exchangedb/partition-0001.sql | 312 | ||||
| -rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 56 | ||||
| -rw-r--r-- | src/exchangedb/shard-0001.sql | 1876 | ||||
| -rw-r--r-- | src/include/taler_exchangedb_plugin.h | 15 |
10 files changed, 8628 insertions, 887 deletions
diff --git a/src/exchange-tools/taler-exchange-dbinit.c b/src/exchange-tools/taler-exchange-dbinit.c index 9ec31afc1..1aba05a6d 100644 --- a/src/exchange-tools/taler-exchange-dbinit.c +++ b/src/exchange-tools/taler-exchange-dbinit.c @@ -49,6 +49,10 @@ static int gc_db; */ static uint32_t num_partitions; +/** + * -S option: setup a sharded database + */ +static uint32_t num_shards; /** * Main function that will be run. @@ -113,6 +117,24 @@ run (void *cls, return; } } + else if (1 < + num_shards) + { + if (GNUNET_OK != plugin->setup_shards (plugin->cls, num_shards)) + { + GNUNET_log (GNUNET_ERROR_TYPE_ERROR, + "Could not setup shards. Aborting\n"); + if (GNUNET_OK != plugin->drop_tables (plugin->cls)) + { + GNUNET_log (GNUNET_ERROR_TYPE_ERROR, + "Could not drop tables after failed shard setup, please delete the DB manually\n"); + } + TALER_EXCHANGEDB_plugin_unload (plugin); + plugin = NULL; + global_ret = EXIT_NOTINSTALLED; + return; + } + } if (gc_db || clear_shards) { if (GNUNET_OK != @@ -178,6 +200,11 @@ main (int argc, "NUMBER", "Setup a partitioned database where each table which can be partitioned holds NUMBER partitions on a single DB node (NOTE: this is different from sharding)", &num_partitions), + GNUNET_GETOPT_option_uint ('S', + "shard", + "NUMBER", + "Setup a sharded database whit N shards", + &num_shards), GNUNET_GETOPT_OPTION_END }; enum GNUNET_GenericReturnValue ret; diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 2eb1eb0ad..3df0653ad 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -19,9 +19,19 @@ sql_DATA = \ benchmark-0001.sql \ exchange-0000.sql \ exchange-0001.sql \ - partition-0001.sql \ + shard-0001.sql \ drop0001.sql +BUILT_SOURCES = \ + shard-0001.sql \ + exchange-0001.sql + +exchange-0001.sql: common-0001.sql exchange-0001-part.sql + cat common-0001.sql exchange-0001-part.sql >$@ + +shard-0001.sql: common-0001.sql + cp common-0001.sql $@ + EXTRA_DIST = \ exchangedb.conf \ exchangedb-postgres.conf \ diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql new file mode 100644 index 000000000..6a91cfbaa --- /dev/null +++ b/src/exchangedb/common-0001.sql @@ -0,0 +1,1876 @@ +-- +-- 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; + +-- Check patch versioning is in place. +SELECT _v.register_patch('exchange-0001', NULL, NULL); + +-------------------- Tables ---------------------------- + +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 +$$; + +----------------------- wire_targets --------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_targets( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' + ',payto_uri VARCHAR NOT NULL' + ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' + ',external_id VARCHAR' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +-- We need a seperate function for this, as we call create_table only once but need to add +-- those constraints to each partition which gets created +CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + + EXECUTE FORMAT ( + 'ALTER TABLE wire_targets_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_targets_' || partition_suffix || '_wire_target_serial_id_key ' + 'UNIQUE (wire_target_serial_id)' + ); +END +$$; + +------------------------ reserves ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' + ',current_balance_val INT8 NOT NULL' + ',current_balance_frac INT4 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'ON ' || table_name || ' ' + '(expiration_date' + ',current_balance_val' + ',current_balance_frac' + ');' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' + 'IS ' || quote_literal('used in get_expired_reserves') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'ON ' || table_name || ' ' + '(gc_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' + 'IS ' || quote_literal('for reserve garbage collection') || ';' + ); + +END +$$; + +----------------------- reserves_in ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_reserves_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',wire_reference INT8 NOT NULL' + ',credit_val INT8 NOT NULL' + ',credit_frac INT4 NOT NULL' + ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_in_serial_id);' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section ' + ',execution_date' + ');' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section,' + 'reserve_in_serial_id DESC' + ');' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' + 'UNIQUE (reserve_in_serial_id)' + ); +END +$$; + +--------------------------- reserves_close ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',execution_date INT8 NOT NULL' + ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' + 'ON ' || table_name || ' ' + '(close_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' + 'PRIMARY KEY (close_uuid)' + ); +END +$$; + +---------------------------- reserves_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denom_sig BYTEA NOT NULL' + ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',execution_date INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,'reserves_out' + ,'PARTITION BY HASH (h_blind_ev)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid, execution_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' + ') %s ' + ,table_name + ,'PARTITION BY HASH (reserve_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + +END +$$; + +---------------------------- known_coins ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_known_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'known_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' + ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' + ',denom_sig BYTEA NOT NULL' + ',remaining_val INT8 NOT NULL' + ',remaining_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE known_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' + 'UNIQUE (known_coin_id)' + ); +END +$$; + +---------------------------- refresh_commitments ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_commitments'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' + ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',noreveal_index INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (rc)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- Note: index spans partitions, may need to be materialized. + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' + 'ON ' || table_name || ' ' + '(old_coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' + 'UNIQUE (melt_serial_id)' + ); +END +$$; + +------------------------------ refresh_revealed_coins -------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',freshcoin_index INT4 NOT NULL' + ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_ev BYTEA NOT NULL' -- UNIQUE' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' + ',ev_sig BYTEA NOT NULL' + ',ewv BYTEA NOT NULL' + -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'ON ' || table_name || ' ' + '(melt_serial_id);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' + 'UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' + 'UNIQUE (coin_ev) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' + 'UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + ); +END +$$; + +----------------------------- refresh_transfer_keys ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_transfer_keys'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' + ',transfer_privs BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' + 'UNIQUE (rtc_serial)' + ); +END +$$; + +---------------------------- deposits ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wallet_timestamp INT8 NOT NULL' + ',exchange_timestamp INT8 NOT NULL' + ',refund_deadline INT8 NOT NULL' + ',wire_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_details_serial_id INT8' -- REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE' + ',UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_by_ready'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_deadline INT8 NOT NULL' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (wire_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(wire_deadline ASC, shard ASC, coin_pub);' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_for_matching'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (refund_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(refund_deadline ASC, merchant_pub, coin_pub);' + ); + +END +$$; + +----------------------------- refunds ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' + ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' + ',rtransaction_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refunds_' || partition_suffix || ' ' + 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' + 'UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); +END +$$; + +---------------------------- wire_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wire_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',execution_date INT8 NOT NULL' + ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wtid_raw)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' + 'ON ' || table_name || ' ' + '(wire_target_h_payto);' + ); + + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wire_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' + 'PRIMARY KEY (wireout_uuid)' + ); +END +$$; + +---------------------------- aggregation_transient ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_aggregation_transient( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_transient'; +BEGIN + + EXECUTE FORMAT ( + 'CREATE TABLE IF NOT EXISTS %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +---------------------------- aggregation_tracking ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME chnage to coint_pub + deposit_serial_id for more efficient depost -- or something else ??? + ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (deposit_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'ON ' || table_name || ' ' + '(wtid_raw);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' + 'IS ' || quote_literal('for lookup_transactions') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' + 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' + 'UNIQUE (aggregation_serial_id) ' + ); +END +$$; + +----------------------------- recoup ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub);' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' + 'UNIQUE (recoup_uuid) ' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + +END +$$; + +---------------------------- recoup_refresh ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: any query using this index will be slow. Materialize index or change query? + -- Also: which query uses this index? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' + 'ON ' || table_name || ' ' + '(rrc_serial);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' + 'UNIQUE (recoup_refresh_uuid) ' + ); +END +$$; + +----------------------------- prewire ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' + ',wire_method TEXT NOT NULL' + ',finished BOOLEAN NOT NULL DEFAULT false' + ',failed BOOLEAN NOT NULL DEFAULT false' + ',buf BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (prewire_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'ON ' || table_name || ' ' + '(finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' + 'IS ' || quote_literal('for gc_prewire') || ';' + ); + -- FIXME: find a way to combine these two indices? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'ON ' || table_name || ' ' + '(failed,finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' + 'IS ' || quote_literal('for wire_prepare_data_get') || ';' + ); + +END +$$; + +----------------------------- cs_nonce_locks ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( + shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' + ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' + ',max_denomination_serial INT8 NOT NULL' + ') %s ;' + ,'cs_nonce_locks' + ,'PARTITION BY HASH (nonce)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' + 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' + 'UNIQUE (cs_nonce_lock_serial_id)' + ); +END +$$; + +------------------------- Partitions ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_partition( + 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 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 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; + +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 shardig 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_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; + +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_table_partition( + 'wire_targets' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'reserves_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_close' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'known_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_commitments' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_revealed_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_transfer_keys' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); + + PERFORM create_table_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_table_partition( +-- 'deposits_by_ready' +-- ,modulus +-- ,num_partitions +-- ); +-- +-- PERFORM create_table_partition( +-- 'deposits_for_matching' +-- ,modulus +-- ,num_partitions +-- ); + + PERFORM create_table_partition( + 'refunds' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'wire_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'aggregation_transient' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'aggregation_tracking' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'recoup_refresh' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'prewire' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'cs_nonce_locks' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + + num_partitions=num_partitions-1; + EXIT WHEN num_partitions=0; + + END LOOP; + + PERFORM drop_default_partitions(); + +END +$$; + +--------------------- Sharding --------------------------- + +---------------------- Shards ---------------------------- +CREATE OR REPLACE FUNCTION setup_shard( + shard_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_table_wire_targets(shard_suffix); + PERFORM add_constraints_to_wire_targets_partition(shard_suffix); + + PERFORM create_table_reserves(shard_suffix); + + PERFORM create_table_reserves_in(shard_suffix); + PERFORM add_constraints_to_reserves_in_partition(shard_suffix); + + PERFORM create_table_reserves_close(shard_suffix); + + PERFORM create_table_reserves_out(shard_suffix); + + PERFORM create_table_reserves_out_by_reserve(shard_suffix); + + PERFORM create_table_known_coins(shard_suffix); + PERFORM add_constraints_to_known_coins_partition(shard_suffix); + + PERFORM create_table_refresh_commitments(shard_suffix); + PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); + + PERFORM create_table_refresh_revealed_coins(shard_suffix); + PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); + + PERFORM create_table_refresh_transfer_keys(shard_suffix); + PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); + + PERFORM create_table_deposits(shard_suffix); + PERFORM add_constraints_to_deposits_partition(shard_suffix); + + PERFORM create_table_deposits_by_ready(shard_suffix); + + PERFORM create_table_deposits_for_matching(shard_suffix); + + PERFORM create_table_refunds(shard_suffix); + PERFORM add_constraints_to_refunds_partition(shard_suffix); + + PERFORM create_table_wire_out(shard_suffix); + PERFORM add_constraints_to_wire_out_partition(shard_suffix); + + PERFORM create_table_aggregation_transient(shard_suffix); + + PERFORM create_table_aggregation_tracking(shard_suffix); + PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); + + PERFORM create_table_recoup(shard_suffix); + PERFORM add_constraints_to_recoup_partition(shard_suffix); + + PERFORM create_table_recoup_by_reserve(shard_suffix); + + PERFORM create_table_recoup_refresh(shard_suffix); + PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); + + PERFORM create_table_prewire(shard_suffix); + + PERFORM create_table_cs_nonce_locks(shard_suffix); + PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); + +END +$$; + +------------------------------ Master ---------------------------------- +CREATE OR REPLACE FUNCTION create_foreign_table( + source_table_name VARCHAR + ,modulus INTEGER + ,shard_suffix VARCHAR + ,current_shard_num INTEGER + ) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, shard_suffix; + + EXECUTE FORMAT( + 'CREATE FOREIGN TABLE IF NOT EXISTS %I ' + 'PARTITION OF %I ' + 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) ' + 'SERVER %I' + ,source_table_name || '_' || shard_suffix + ,source_table_name + ,modulus + ,current_shard_num-1 + ,shard_suffix + ); + + EXECUTE FORMAT( + 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"', + source_table_name || '_' || shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION master_prepare_sharding() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + CREATE EXTENSION IF NOT EXISTS postgres_fdw; + + PERFORM detach_default_partitions(); + + ALTER TABLE IF EXISTS wire_targets + DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves + DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_in + DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_close + DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_out + DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey + ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key + ; + + ALTER TABLE IF EXISTS known_coins + DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_commitments + DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey + ; + + ALTER TABLE IF EXISTS refresh_revealed_coins + DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_transfer_keys + DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE + ; + + ALTER TABLE IF EXISTS deposits + DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS deposits_extension_details_serial_id_fkey + ,DROP CONSTRAINT IF EXISTS deposits_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE + ; + + ALTER TABLE IF EXISTS refunds + DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE + ; + + ALTER TABLE IF EXISTS wire_out + DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE + ; + + ALTER TABLE IF EXISTS aggregation_tracking + DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey + ; + + ALTER TABLE IF EXISTS recoup + DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE + ; + + ALTER TABLE IF EXISTS recoup_refresh + DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE + ; + + ALTER TABLE IF EXISTS prewire + DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE + ; + + ALTER TABLE IF EXISTS cs_nonce_locks + DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE + ; + +END +$$; + + +CREATE OR REPLACE FUNCTION create_shard_server( + shard_suffix VARCHAR + ,total_num_shards INTEGER + ,current_shard_num INTEGER + ,remote_host VARCHAR + ,remote_user VARCHAR + ,remote_user_password VARCHAR + ,remote_db_name VARCHAR DEFAULT 'taler-exchange' + ,remote_port INTEGER DEFAULT '5432' + ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating server %s', remote_host; + + EXECUTE FORMAT( + 'CREATE SERVER IF NOT EXISTS %I ' + 'FOREIGN DATA WRAPPER postgres_fdw ' + 'OPTIONS (dbname %L, host %L, port %L)' + ,shard_suffix + ,remote_db_name + ,remote_host + ,remote_port + ); + + EXECUTE FORMAT( + 'CREATE USER MAPPING IF NOT EXISTS ' + 'FOR %s SERVER %I ' + 'OPTIONS (user %L, password %L)' + ,local_user + ,shard_suffix + ,remote_user + ,remote_user_password + ); + + PERFORM create_foreign_table( + 'wire_targets' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_in' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_close' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'known_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_commitments' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_revealed_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_transfer_keys' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'deposits' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); +-- PERFORM create_foreign_table( +-- 'deposits_by_ready' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); +-- PERFORM create_foreign_table( +-- 'deposits_for_matching' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); + PERFORM create_foreign_table( + 'refunds' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'wire_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'aggregation_tracking' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_refresh' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'prewire' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'cs_nonce_locks' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + +END +$$; + +COMMENT ON FUNCTION create_shard_server + IS 'Create a shard server on the master + node with all foreign tables and user mappings'; + +CREATE OR REPLACE FUNCTION create_shards( + num_shards INTEGER + ,domain VARCHAR DEFAULT 'perf.taler' +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + FOR i IN 1..num_shards LOOP + PERFORM create_shard_server( + i + ,num_shards + ,i + ,'shard-' || i::varchar || '.' || domain + ,'taler' + ,'taler' + ,'taler-exchange' + ,'5432' + ,'taler-exchange-httpd' + ); + END LOOP; +END +$$; + +COMMIT;
\ No newline at end of file diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql index 3f43a5693..364578515 100644 --- a/src/exchangedb/drop0001.sql +++ b/src/exchangedb/drop0001.sql @@ -43,43 +43,65 @@ DROP TABLE IF EXISTS wire_accounts CASCADE; DROP TABLE IF EXISTS signkey_revocations CASCADE; DROP TABLE IF EXISTS work_shards CASCADE; DROP TABLE IF EXISTS prewire CASCADE; +DROP FUNCTION IF EXISTS create_table_prewire; DROP TABLE IF EXISTS recoup CASCADE; +DROP FUNCTION IF EXISTS create_table_recoup; DROP FUNCTION IF EXISTS add_constraints_to_recoup_partition; DROP TABLE IF EXISTS recoup_refresh CASCADE; +DROP FUNCTION IF EXISTS create_table_recoup_refresh; DROP FUNCTION IF EXISTS add_constraints_to_recoup_refresh_partition; +DROP TABLE IF EXISTS aggregation_transient CASCADE; +DROP FUNCTION IF EXISTS create_table_aggregation_transient; DROP TABLE IF EXISTS aggregation_tracking CASCADE; +DROP FUNCTION IF EXISTS create_table_aggregation_tracking; DROP FUNCTION IF EXISTS add_constraints_to_aggregation_tracking_partition; DROP TABLE IF EXISTS wire_out CASCADE; +DROP FUNCTION IF EXISTS create_table_wire_out; DROP FUNCTION IF EXISTS add_constraints_to_wire_out_partition; DROP TABLE IF EXISTS wire_targets CASCADE; +DROP FUNCTION IF EXISTS create_table_wire_targets; DROP FUNCTION IF EXISTS add_constraints_to_wire_targets_partition; DROP TABLE IF EXISTS wire_fee CASCADE; DROP TABLE IF EXISTS deposits CASCADE; +DROP FUNCTION IF EXISTS create_table_deposits; DROP TABLE IF EXISTS deposits_by_ready CASCADE; +DROP FUNCTION IF EXISTS create_table_deposits_by_ready; DROP TABLE IF EXISTS deposits_for_matching CASCADE; +DROP FUNCTION IF EXISTS create_table_deposits_for_matching; DROP FUNCTION IF EXISTS add_constraints_to_deposits_partition; DROP TABLE IF EXISTS extension_details CASCADE; DROP TABLE IF EXISTS refunds CASCADE; +DROP FUNCTION IF EXISTS create_table_refunds; DROP FUNCTION IF EXISTS add_constraints_to_refunds_partition; DROP TABLE IF EXISTS refresh_commitments CASCADE; +DROP FUNCTION IF EXISTS create_table_refresh_commitments; DROP FUNCTION IF EXISTS add_constraints_to_refresh_commitments_partition; DROP TABLE IF EXISTS refresh_revealed_coins CASCADE; +DROP FUNCTION IF EXISTS create_table_refresh_revealed_coins; DROP FUNCTION IF EXISTS add_constraints_to_refresh_revealed_coins_partition; DROP TABLE IF EXISTS refresh_transfer_keys CASCADE; +DROP FUNCTION IF EXISTS create_table_refresh_transfer_keys; DROP FUNCTION IF EXISTS add_constraints_to_refresh_transfer_keys_partition; DROP TABLE IF EXISTS known_coins CASCADE; +DROP FUNCTION IF EXISTS create_table_known_coins; DROP FUNCTION IF EXISTS add_constraints_to_known_coins_partition; DROP TABLE IF EXISTS reserves_close CASCADE; +DROP FUNCTION IF EXISTS create_table_reserves_close; DROP FUNCTION IF EXISTS add_constraints_to_reserves_close_partition; DROP TABLE IF EXISTS reserves_out CASCADE; +DROP FUNCTION IF EXISTS create_table_reserves_out; DROP TABLE IF EXISTS reserves_out_by_reserve CASCADE; +DROP FUNCTION IF EXISTS create_table_reserves_out_by_reserve; DROP FUNCTION IF EXISTS add_constraints_to_reserves_out_partition; DROP TABLE IF EXISTS reserves_in CASCADE; +DROP FUNCTION IF EXISTS create_table_reserves_in; DROP FUNCTION IF EXISTS add_constraints_to_reserves_in_partition; DROP TABLE IF EXISTS reserves CASCADE; +DROP FUNCTION IF EXISTS create_table_reserves; DROP TABLE IF EXISTS denomination_revocations CASCADE; DROP TABLE IF EXISTS denominations CASCADE; DROP TABLE IF EXISTS cs_nonce_locks CASCADE; +DROP FUNCTION IF EXISTS create_table_cs_nonce_locks; DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition; DROP TABLE IF EXISTS global_fee CASCADE; @@ -122,14 +144,14 @@ DROP FUNCTION IF EXISTS exchange_do_account_merge; DROP FUNCTION IF EXISTS exchange_do_history_request; DROP FUNCTION IF EXISTS exchange_do_close_request; --- Unregister patch (partition-0001.sql) --- SELECT _v.unregister_patch('partition-0001'); --- Drops for partition-0001.sql DROP FUNCTION IF EXISTS create_table_partition; DROP FUNCTION IF EXISTS create_partitions; DROP FUNCTION IF EXISTS detach_default_partitions; DROP FUNCTION IF EXISTS drop_default_partitions; +DROP FUNCTION IF EXISTS master_prepare_sharding; +DROP FUNCTION IF EXISTS create_foreign_table; +DROP FUNCTION IF EXISTS create_shard_server; -- And we're out of here... diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql new file mode 100644 index 000000000..359fe9f81 --- /dev/null +++ b/src/exchangedb/exchange-0001-part.sql @@ -0,0 +1,2826 @@ +-- +-- 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; + +-- ------------------------------ denominations ---------------------------------------- + +CREATE TABLE IF NOT EXISTS denominations + (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) + ,denom_type INT4 NOT NULL DEFAULT (1) -- 1 == RSA (for now, remove default later!) + ,age_mask INT4 NOT NULL DEFAULT (0) + ,denom_pub BYTEA NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,valid_from INT8 NOT NULL + ,expire_withdraw INT8 NOT NULL + ,expire_deposit INT8 NOT NULL + ,expire_legal INT8 NOT NULL + ,coin_val INT8 NOT NULL + ,coin_frac INT4 NOT NULL + ,fee_withdraw_val INT8 NOT NULL + ,fee_withdraw_frac INT4 NOT NULL + ,fee_deposit_val INT8 NOT NULL + ,fee_deposit_frac INT4 NOT NULL + ,fee_refresh_val INT8 NOT NULL + ,fee_refresh_frac INT4 NOT NULL + ,fee_refund_val INT8 NOT NULL + ,fee_refund_frac INT4 NOT NULL + ); +COMMENT ON TABLE denominations + IS 'Main denominations table. All the valid denominations the exchange knows about.'; +COMMENT ON COLUMN denominations.denom_type + IS 'determines cipher type for blind signatures used with this denomination; 0 is for RSA'; +COMMENT ON COLUMN denominations.age_mask + IS 'bitmask with the age restrictions that are being used for this denomination; 0 if denomination does not support the use of age restrictions'; +COMMENT ON COLUMN denominations.denominations_serial + IS 'needed for exchange-auditor replication logic'; + +CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index + ON denominations + (expire_legal); + + +-- ------------------------------ denomination_revocations ---------------------------------------- + +CREATE TABLE IF NOT EXISTS denomination_revocations + (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ); +COMMENT ON TABLE denomination_revocations + IS 'remembering which denomination keys have been revoked'; + + +-- ------------------------------ wire_targets ---------------------------------------- + +SELECT create_table_wire_targets(); + +COMMENT ON TABLE wire_targets + IS 'All senders and recipients of money via the exchange'; +COMMENT ON COLUMN wire_targets.payto_uri + IS 'Can be a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)'; +COMMENT ON COLUMN wire_targets.wire_target_h_payto + IS 'Unsalted hash of payto_uri'; +COMMENT ON COLUMN wire_targets.kyc_ok + IS 'true if the KYC check was passed successfully'; +COMMENT ON COLUMN wire_targets.external_id + IS 'Name of the user that was used for OAuth 2.0-based legitimization'; + +CREATE TABLE IF NOT EXISTS wire_targets_default + PARTITION OF wire_targets + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_wire_targets_partition('default'); + +-- ------------------------------ reserves ---------------------------------------- + +SELECT create_table_reserves(); + +COMMENT ON TABLE reserves + IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.'; +COMMENT ON COLUMN reserves.reserve_pub + IS 'EdDSA public key of the reserve. Knowledge of the private key implies ownership over the balance.'; +COMMENT ON COLUMN reserves.current_balance_val + IS 'Current balance remaining with the reserve'; +COMMENT ON COLUMN reserves.expiration_date + IS 'Used to trigger closing of reserves that have not been drained after some time'; +COMMENT ON COLUMN reserves.gc_date + IS 'Used to forget all information about a reserve during garbage collection'; + +CREATE TABLE IF NOT EXISTS reserves_default + PARTITION OF reserves + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +-- ------------------------------ reserves_in ---------------------------------------- + +SELECT create_table_reserves_in(); + +COMMENT ON TABLE reserves_in + IS 'list of transfers of funds into the reserves, one per incoming wire transfer'; +COMMENT ON COLUMN reserves_in.wire_source_h_payto + IS 'Identifies the debited bank account and KYC status'; +COMMENT ON COLUMN reserves_in.reserve_pub + IS 'Public key of the reserve. Private key signifies ownership of the remaining balance.'; +COMMENT ON COLUMN reserves_in.credit_val + IS 'Amount that was transferred into the reserve'; + +CREATE TABLE IF NOT EXISTS reserves_in_default + PARTITION OF reserves_in + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_in_partition('default'); + +-- ------------------------------ reserves_close ---------------------------------------- + +SELECT create_table_reserves_close(); + +COMMENT ON TABLE reserves_close + IS 'wire transfers executed by the reserve to close reserves'; +COMMENT ON COLUMN reserves_close.wire_target_h_payto + IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.'; + +CREATE TABLE IF NOT EXISTS reserves_close_default + PARTITION OF reserves_close + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_close_partition('default'); + + +-- ------------------------------ reserves_out ---------------------------------------- + +SELECT create_table_reserves_out(); + +COMMENT ON TABLE reserves_out + IS 'Withdraw operations performed on reserves.'; +COMMENT ON COLUMN reserves_out.h_blind_ev + IS 'Hash of the blinded coin, used as primary key here so that broken clients that use a non-random coin or blinding factor fail to withdraw (otherwise they would fail on deposit when the coin is not unique there).'; +COMMENT ON COLUMN reserves_out.denominations_serial + IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive'; + +CREATE TABLE IF NOT EXISTS reserves_out_default + PARTITION OF reserves_out + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_reserves_out_partition('default'); + + +SELECT create_table_reserves_out_by_reserve(); + +COMMENT ON TABLE reserves_out_by_reserve + IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.'; + +CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default + PARTITION OF reserves_out_by_reserve + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO reserves_out_by_reserve + (reserve_uuid + ,h_blind_ev) + VALUES + (NEW.reserve_uuid + ,NEW.h_blind_ev); + RETURN NEW; +END $$; +COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger() + IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.'; + +CREATE TRIGGER reserves_out_on_insert + AFTER INSERT + ON reserves_out + FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger(); + +CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM reserves_out_by_reserve + WHERE reserve_uuid = OLD.reserve_uuid; + RETURN OLD; +END $$; +COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger() + IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.'; + +CREATE TRIGGER reserves_out_on_delete + AFTER DELETE + ON reserves_out + FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger(); + + +-- ------------------------------ auditors ---------------------------------------- + +CREATE TABLE IF NOT EXISTS auditors + (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32) + ,auditor_name VARCHAR NOT NULL + ,auditor_url VARCHAR NOT NULL + ,is_active BOOLEAN NOT NULL + ,last_change INT8 NOT NULL + ); +COMMENT ON TABLE auditors + IS 'Table with auditors the exchange uses or has used in the past. Entries never expire as we need to remember the last_change column indefinitely.'; +COMMENT ON COLUMN auditors.auditor_pub + IS 'Public key of the auditor.'; +COMMENT ON COLUMN auditors.auditor_url + IS 'The base URL of the auditor.'; +COMMENT ON COLUMN auditors.is_active + IS 'true if we are currently supporting the use of this auditor.'; +COMMENT ON COLUMN auditors.last_change + IS 'Latest time when active status changed. Used to detect replays of old messages.'; + + +-- ------------------------------ auditor_denom_sigs ---------------------------------------- + +CREATE TABLE IF NOT EXISTS auditor_denom_sigs + (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE CASCADE + ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ,auditor_sig BYTEA CHECK (LENGTH(auditor_sig)=64) + ,PRIMARY KEY (denominations_serial, auditor_uuid) + ); +COMMENT ON TABLE auditor_denom_sigs + IS 'Table with auditor signatures on exchange denomination keys.'; +COMMENT ON COLUMN auditor_denom_sigs.auditor_uuid + IS 'Identifies the auditor.'; +COMMENT ON COLUMN auditor_denom_sigs.denominations_serial + IS 'Denomination the signature is for.'; +COMMENT ON COLUMN auditor_denom_sigs.auditor_sig + IS 'Signature of the auditor, of purpose TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.'; + + +-- ------------------------------ exchange_sign_keys ---------------------------------------- + +CREATE TABLE IF NOT EXISTS exchange_sign_keys + (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32) + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,valid_from INT8 NOT NULL + ,expire_sign INT8 NOT NULL + ,expire_legal INT8 NOT NULL + ); +COMMENT ON TABLE exchange_sign_keys + IS 'Table with master public key signatures on exchange online signing keys.'; +COMMENT ON COLUMN exchange_sign_keys.exchange_pub + IS 'Public online signing key of the exchange.'; +COMMENT ON COLUMN exchange_sign_keys.master_sig + IS 'Signature affirming the validity of the signing key of purpose TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.'; +COMMENT ON COLUMN exchange_sign_keys.valid_from + IS 'Time when this online signing key will first be used to sign messages.'; +COMMENT ON COLUMN exchange_sign_keys.expire_sign + IS 'Time when this online signing key will no longer be used to sign.'; +COMMENT ON COLUMN exchange_sign_keys.expire_legal + IS 'Time when this online signing key legally expires.'; + + +-- ------------------------------ signkey_revocations ---------------------------------------- + +CREATE TABLE IF NOT EXISTS signkey_revocations + (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON DELETE CASCADE + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ); +COMMENT ON TABLE signkey_revocations + IS 'Table storing which online signing keys have been revoked'; + + +-- ------------------------------ extension ---------------------------------------- + +CREATE TABLE IF NOT EXISTS extensions + (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,name VARCHAR NOT NULL UNIQUE + ,config BYTEA + ); +COMMENT ON TABLE extensions + IS 'Configurations of the activated extensions'; +COMMENT ON COLUMN extensions.name + IS 'Name of the extension'; +COMMENT ON COLUMN extensions.config + IS 'Configuration of the extension as JSON-blob, maybe NULL'; + + +-- ------------------------------ known_coins ---------------------------------------- + +SELECT create_table_known_coins(); + +COMMENT ON TABLE known_coins + IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations'; +COMMENT ON COLUMN known_coins.denominations_serial + IS 'Denomination of the coin, determines the value of the original coin and applicable fees for coin-specific operations.'; +COMMENT ON COLUMN known_coins.coin_pub + IS 'EdDSA public key of the coin'; +COMMENT ON COLUMN known_coins.remaining_val + IS 'Value of the coin that remains to be spent'; +COMMENT ON COLUMN known_coins.age_commitment_hash + IS 'Optional hash of the age commitment for age restrictions as per DD 24 (active if denom_type has the respective bit set)'; +COMMENT ON COLUMN known_coins.denom_sig + IS 'This is the signature of the exchange that affirms that the coin is a valid coin. The specific signature type depends on denom_type of the denomination.'; + +CREATE TABLE IF NOT EXISTS known_coins_default + PARTITION OF known_coins + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_known_coins_partition('default'); + + +-- ------------------------------ refresh_commitments ---------------------------------------- + +SELECT create_table_refresh_commitments(); + +COMMENT ON TABLE refresh_commitments + IS 'Commitments made when melting coins and the gamma value chosen by the exchange.'; +COMMENT ON COLUMN refresh_commitments.noreveal_index + IS 'The gamma value chosen by the exchange in the cut-and-choose protocol'; +COMMENT ON COLUMN refresh_commitments.rc + IS 'Commitment made by the client, hash over the various client inputs in the cut-and-choose protocol'; +COMMENT ON COLUMN refresh_commitments.old_coin_pub + IS 'Coin being melted in the refresh process.'; + +CREATE TABLE IF NOT EXISTS refresh_commitments_default + PARTITION OF refresh_commitments + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_refresh_commitments_partition('default'); + + +-- ------------------------------ refresh_revealed_coins ---------------------------------------- + +SELECT create_table_refresh_revealed_coins(); + +COMMENT ON TABLE refresh_revealed_coins + IS 'Revelations about the new coins that are to be created during a melting session.'; +COMMENT ON COLUMN refresh_revealed_coins.rrc_serial + IS 'needed for exchange-auditor replication logic'; +COMMENT ON COLUMN refresh_revealed_coins.melt_serial_id + IS 'Identifies the refresh commitment (rc) of the melt operation.'; +COMMENT ON COLUMN refresh_revealed_coins.freshcoin_index + IS 'index of the fresh coin being created (one melt operation may result in multiple fresh coins)'; +COMMENT ON COLUMN refresh_revealed_coins.coin_ev + IS 'envelope of the new coin to be signed'; +COMMENT ON COLUMN refresh_revealed_coins.ewv + IS 'exchange contributed values in the creation of the fresh coin (see /csr)'; +COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev + IS 'hash of the envelope of the new coin to be signed (for lookups)'; +COMMENT ON COLUMN refresh_revealed_coins.ev_sig + IS 'exchange signature over the envelope'; + +CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default + PARTITION OF refresh_revealed_coins + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_refresh_revealed_coins_partition('default'); + + +-- ------------------------------ refresh_transfer_keys ---------------------------------------- + +SELECT create_table_refresh_transfer_keys(); + +COMMENT ON TABLE refresh_transfer_keys + IS 'Transfer keys of a refresh operation (the data revealed to the exchange).'; +COMMENT ON COLUMN refresh_transfer_keys.rtc_serial + IS 'needed for exchange-auditor replication logic'; +COMMENT ON COLUMN refresh_transfer_keys.melt_serial_id + IS 'Identifies the refresh commitment (rc) of the operation.'; +COMMENT ON COLUMN refresh_transfer_keys.transfer_pub + IS 'transfer public key for the gamma index'; +COMMENT ON COLUMN refresh_transfer_keys.transfer_privs + IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been revealed, with the gamma entry being skipped'; + +CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default + PARTITION OF refresh_transfer_keys + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_refresh_transfer_keys_partition('default'); + + +-- ------------------------------ extension_details ---------------------------------------- + +CREATE TABLE IF NOT EXISTS extension_details + (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY + ,extension_options VARCHAR) + PARTITION BY HASH (extension_details_serial_id); +COMMENT ON TABLE extension_details + IS 'Extensions that were provided with deposits (not yet used).'; +COMMENT ON COLUMN extension_details.extension_options + IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the extensions supported by the exchange.'; + +CREATE TABLE IF NOT EXISTS extension_details_default + PARTITION OF extension_details + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + +-- ------------------------------ deposits ---------------------------------------- + +SELECT create_table_deposits(); + +COMMENT ON TABLE deposits + IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).'; +COMMENT ON COLUMN deposits.shard + IS 'Used for load sharding in the materialized indices. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.'; +COMMENT ON COLUMN deposits.known_coin_id + IS 'Used for garbage collection'; +COMMENT ON COLUMN deposits.wire_target_h_payto + IS 'Identifies the target bank account and KYC status'; +COMMENT ON COLUMN deposits.wire_salt + IS 'Salt used when hashing the payto://-URI to get the h_wire'; +COMMENT ON COLUMN deposits.done + IS 'Set to TRUE once we have included this deposit in some aggregate wire transfer to the merchant'; +COMMENT ON COLUMN deposits.extension_blocked + IS 'True if the aggregation of the deposit is currently blocked by some extension mechanism. Used to filter out deposits that must not be processed by the canonical deposit logic.'; +COMMENT ON COLUMN deposits.extension_details_serial_id + IS 'References extensions table, NULL if extensions are not used'; + +CREATE TABLE IF NOT EXISTS deposits_default + PARTITION OF deposits + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_deposits_partition('default'); + + +SELECT create_table_deposits_by_ready(); + +COMMENT ON TABLE deposits_by_ready + IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below'; + +CREATE TABLE IF NOT EXISTS deposits_by_ready_default + PARTITION OF deposits_by_ready + DEFAULT; + + +SELECT create_table_deposits_for_matching(); + +COMMENT ON TABLE deposits_for_matching + IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below'; + +CREATE TABLE IF NOT EXISTS deposits_for_matching_default + PARTITION OF deposits_for_matching + DEFAULT; + + +CREATE OR REPLACE FUNCTION deposits_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + is_ready BOOLEAN; +BEGIN + is_ready = NOT (NEW.done OR NEW.extension_blocked); + + IF (is_ready) + THEN + INSERT INTO deposits_by_ready + (wire_deadline + ,shard + ,coin_pub + ,deposit_serial_id) + VALUES + (NEW.wire_deadline + ,NEW.shard + ,NEW.coin_pub + ,NEW.deposit_serial_id); + INSERT INTO deposits_for_matching + (refund_deadline + ,merchant_pub + ,coin_pub + ,deposit_serial_id) + VALUES + (NEW.refund_deadline + ,NEW.merchant_pub + ,NEW.coin_pub + ,NEW.deposit_serial_id); + END IF; + RETURN NEW; +END $$; +COMMENT ON FUNCTION deposits_insert_trigger() + IS 'Replicate deposit inserts into materialized indices.'; + +CREATE TRIGGER deposits_on_insert + AFTER INSERT + ON deposits + FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger(); + +CREATE OR REPLACE FUNCTION deposits_update_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + was_ready BOOLEAN; +DECLARE + is_ready BOOLEAN; +BEGIN + was_ready = NOT (OLD.done OR OLD.extension_blocked); + is_ready = NOT (NEW.done OR NEW.extension_blocked); + IF (was_ready AND NOT is_ready) + THEN + DELETE FROM deposits_by_ready + WHERE wire_deadline = OLD.wire_deadline + AND shard = OLD.shard + AND coin_pub = OLD.coin_pub + AND deposit_serial_id = OLD.deposit_serial_id; + DELETE FROM deposits_for_matching + WHERE refund_deadline = OLD.refund_deadline + AND merchant_pub = OLD.merchant_pub + AND coin_pub = OLD.coin_pub + AND deposit_serial_id = OLD.deposit_serial_id; + END IF; + IF (is_ready AND NOT was_ready) + THEN + INSERT INTO deposits_by_ready + (wire_deadline + ,shard + ,coin_pub + ,deposit_serial_id) + VALUES + (NEW.wire_deadline + ,NEW.shard + ,NEW.coin_pub + ,NEW.deposit_serial_id); + INSERT INTO deposits_for_matching + (refund_deadline + ,merchant_pub + ,coin_pub + ,deposit_serial_id) + VALUES + (NEW.refund_deadline + ,NEW.merchant_pub + ,NEW.coin_pub + ,NEW.deposit_serial_id); + END IF; + RETURN NEW; +END $$; +COMMENT ON FUNCTION deposits_update_trigger() + IS 'Replicate deposits changes into materialized indices.'; + +CREATE TRIGGER deposits_on_update + AFTER UPDATE + ON deposits + FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger(); + +CREATE OR REPLACE FUNCTION deposits_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + was_ready BOOLEAN; +BEGIN + was_ready = NOT (OLD.done OR OLD.extension_blocked); + + IF (was_ready) + THEN + DELETE FROM deposits_by_ready + WHERE wire_deadline = OLD.wire_deadline + AND shard = OLD.shard + AND coin_pub = OLD.coin_pub + AND deposit_serial_id = OLD.deposit_serial_id; + DELETE FROM deposits_for_matching + WHERE refund_deadline = OLD.refund_deadline + AND merchant_pub = OLD.merchant_pub + AND coin_pub = OLD.coin_pub + AND deposit_serial_id = OLD.deposit_serial_id; + END IF; + RETURN NEW; +END $$; +COMMENT ON FUNCTION deposits_delete_trigger() + IS 'Replicate deposit deletions into materialized indices.'; + +CREATE TRIGGER deposits_on_delete + AFTER DELETE + ON deposits + FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger(); + + +-- ------------------------------ refunds ---------------------------------------- + +SELECT create_table_refunds(); + +COMMENT ON TABLE refunds + IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'; +COMMENT ON COLUMN refunds.deposit_serial_id + IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.'; +COMMENT ON COLUMN refunds.rtransaction_id + IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund'; + +CREATE TABLE IF NOT EXISTS refunds_default + PARTITION OF refunds + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_refunds_partition('default'); + + +-- ------------------------------ wire_out ---------------------------------------- + +SELECT create_table_wire_out(); + +COMMENT ON TABLE wire_out + IS 'wire transfers the exchange has executed'; +COMMENT ON COLUMN wire_out.exchange_account_section + IS 'identifies the configuration section with the debit account of this payment'; +COMMENT ON COLUMN wire_out.wire_target_h_payto + IS 'Identifies the credited bank account and KYC status'; + +CREATE TABLE IF NOT EXISTS wire_out_default + PARTITION OF wire_out + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_wire_out_partition('default'); + +CREATE OR REPLACE FUNCTION wire_out_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM aggregation_tracking + WHERE wtid_raw = OLD.wtid_raw; + RETURN OLD; +END $$; +COMMENT ON FUNCTION wire_out_delete_trigger() + IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint and conflicted with nice partitioning.'; + +CREATE TRIGGER wire_out_on_delete + AFTER DELETE + ON wire_out + FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger(); + + + +-- ------------------------------ aggregation_transient ---------------------------------------- + +SELECT create_table_aggregation_transient(); + +COMMENT ON TABLE aggregation_transient + IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated'; +COMMENT ON COLUMN aggregation_transient.amount_val + IS 'Sum of all of the aggregated deposits (without deposit fees)'; +COMMENT ON COLUMN aggregation_transient.wtid_raw + IS 'identifier of the wire transfer'; + +CREATE TABLE IF NOT EXISTS aggregation_transient_default + PARTITION OF aggregation_transient + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + +-- ------------------------------ aggregation_tracking ---------------------------------------- + +SELECT create_table_aggregation_tracking(); + +COMMENT ON TABLE aggregation_tracking + IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)'; +COMMENT ON COLUMN aggregation_tracking.wtid_raw + IS 'identifier of the wire transfer'; + +CREATE TABLE IF NOT EXISTS aggregation_tracking_default + PARTITION OF aggregation_tracking + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_aggregation_tracking_partition('default'); + + +-- ------------------------------ wire_fee ---------------------------------------- + +CREATE TABLE IF NOT EXISTS wire_fee + (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,wire_method VARCHAR NOT NULL + ,start_date INT8 NOT NULL + ,end_date INT8 NOT NULL + ,wire_fee_val INT8 NOT NULL + ,wire_fee_frac INT4 NOT NULL + ,closing_fee_val INT8 NOT NULL + ,closing_fee_frac INT4 NOT NULL + ,wad_fee_val INT8 NOT NULL + ,wad_fee_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,PRIMARY KEY (wire_method, start_date) + ); +COMMENT ON TABLE wire_fee + IS 'list of the wire fees of this exchange, by date'; +COMMENT ON COLUMN wire_fee.wire_fee_serial + IS 'needed for exchange-auditor replication logic'; + +CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index + ON wire_fee + (end_date); + + +-- ------------------------------ global_fee ---------------------------------------- + +CREATE TABLE IF NOT EXISTS global_fee + (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,start_date INT8 NOT NULL + ,end_date INT8 NOT NULL + ,history_fee_val INT8 NOT NULL + ,history_fee_frac INT4 NOT NULL + ,kyc_fee_val INT8 NOT NULL + ,kyc_fee_frac INT4 NOT NULL + ,account_fee_val INT8 NOT NULL + ,account_fee_frac INT4 NOT NULL + ,purse_fee_val INT8 NOT NULL + ,purse_fee_frac INT4 NOT NULL + ,purse_timeout INT8 NOT NULL + ,kyc_timeout INT8 NOT NULL + ,history_expiration INT8 NOT NULL + ,purse_account_limit INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,PRIMARY KEY (start_date) + ); +COMMENT ON TABLE global_fee + IS 'list of the global fees of this exchange, by date'; +COMMENT ON COLUMN global_fee.global_fee_serial + IS 'needed for exchange-auditor replication logic'; + +CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index + ON global_fee + (end_date); + + +-- ------------------------------ recoup ---------------------------------------- + +SELECT create_table_recoup(); + +COMMENT ON TABLE recoup + IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.'; +COMMENT ON COLUMN recoup.coin_pub + IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'; +COMMENT ON COLUMN recoup.reserve_out_serial_id + IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.'; +COMMENT ON COLUMN recoup.coin_sig + IS 'Signature by the coin affirming the recoup, of type TALER_SIGNATURE_WALLET_COIN_RECOUP'; +COMMENT ON COLUMN recoup.coin_blind + IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.'; + +CREATE TABLE IF NOT EXISTS recoup_default + PARTITION OF recoup + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_recoup_partition('default'); + + +SELECT create_table_recoup_by_reserve(); + +COMMENT ON TABLE recoup_by_reserve + IS 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.'; + +CREATE TABLE IF NOT EXISTS recoup_by_reserve_default + PARTITION OF recoup_by_reserve + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +CREATE OR REPLACE FUNCTION recoup_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO recoup_by_reserve + (reserve_out_serial_id + ,coin_pub) + VALUES + (NEW.reserve_out_serial_id + ,NEW.coin_pub); + RETURN NEW; +END $$; +COMMENT ON FUNCTION recoup_insert_trigger() + IS 'Replicate recoup inserts into recoup_by_reserve table.'; + +CREATE TRIGGER recoup_on_insert + AFTER INSERT + ON recoup + FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger(); + +CREATE OR REPLACE FUNCTION recoup_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM recoup_by_reserve + WHERE reserve_out_serial_id = OLD.reserve_out_serial_id + AND coin_pub = OLD.coin_pub; + RETURN OLD; +END $$; +COMMENT ON FUNCTION recoup_delete_trigger() + IS 'Replicate recoup deletions into recoup_by_reserve table.'; + +CREATE TRIGGER recoup_on_delete + AFTER DELETE + ON recoup + FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger(); + + +-- ------------------------------ recoup_refresh ---------------------------------------- + +SELECT create_table_recoup_refresh(); + +COMMENT ON TABLE recoup_refresh + IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'; +COMMENT ON COLUMN recoup_refresh.coin_pub + IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!'; +COMMENT ON COLUMN recoup_refresh.known_coin_id + IS 'FIXME: (To be) used for garbage collection (in the future)'; +COMMENT ON COLUMN recoup_refresh.rrc_serial + IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).'; +COMMENT ON COLUMN recoup_refresh.coin_blind + IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.'; + +CREATE TABLE IF NOT EXISTS recoup_refresh_default + PARTITION OF recoup_refresh + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_recoup_refresh_partition('default'); + + +-- ------------------------------ prewire ---------------------------------------- + +SELECT create_table_prewire(); + +COMMENT ON TABLE prewire + IS 'pre-commit data for wire transfers we are about to execute'; +COMMENT ON COLUMN prewire.failed + IS 'set to TRUE if the bank responded with a non-transient failure to our transfer request'; +COMMENT ON COLUMN prewire.finished + IS 'set to TRUE once bank confirmed receiving the wire transfer request'; +COMMENT ON COLUMN prewire.buf + IS 'serialized data to send to the bank to execute the wire transfer'; + +CREATE TABLE IF NOT EXISTS prewire_default + PARTITION OF prewire + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + +-- ------------------------------ wire_accounts ---------------------------------------- + +CREATE TABLE IF NOT EXISTS wire_accounts + (payto_uri VARCHAR PRIMARY KEY + ,master_sig BYTEA CHECK (LENGTH(master_sig)=64) + ,is_active BOOLEAN NOT NULL + ,last_change INT8 NOT NULL + ); +COMMENT ON TABLE wire_accounts + IS 'Table with current and historic bank accounts of the exchange. Entries never expire as we need to remember the last_change column indefinitely.'; +COMMENT ON COLUMN wire_accounts.payto_uri + IS 'payto URI (RFC 8905) with the bank account of the exchange.'; +COMMENT ON COLUMN wire_accounts.master_sig + IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS'; +COMMENT ON COLUMN wire_accounts.is_active + IS 'true if we are currently supporting the use of this account.'; +COMMENT ON COLUMN wire_accounts.last_change + IS 'Latest time when active status changed. Used to detect replays of old messages.'; +-- "wire_accounts" has no sequence because it is a 'mutable' table +-- and is of no concern to the auditor + + +-- ------------------------------ cs_nonce_locks ---------------------------------------- + +SELECT create_table_cs_nonce_locks(); + +COMMENT ON TABLE cs_nonce_locks + IS 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash'; +COMMENT ON COLUMN cs_nonce_locks.nonce + IS 'actual nonce submitted by the client'; +COMMENT ON COLUMN cs_nonce_locks.op_hash + IS 'hash (RC for refresh, blind coin hash for withdraw) the nonce may be used with'; +COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial + IS 'Maximum number of a CS denomination serial the nonce could be used with, for GC'; + +CREATE TABLE IF NOT EXISTS cs_nonce_locks_default + PARTITION OF cs_nonce_locks + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_cs_nonce_locks_partition('default'); + + +-- ------------------------------ work_shards ---------------------------------------- + +CREATE TABLE IF NOT EXISTS work_shards + (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,last_attempt INT8 NOT NULL + ,start_row INT8 NOT NULL + ,end_row INT8 NOT NULL + ,completed BOOLEAN NOT NULL DEFAULT FALSE + ,job_name VARCHAR NOT NULL + ,PRIMARY KEY (job_name, start_row) + ); +COMMENT ON TABLE work_shards + IS 'coordinates work between multiple processes working on the same job'; +COMMENT ON COLUMN work_shards.shard_serial_id + IS 'unique serial number identifying the shard'; +COMMENT ON COLUMN work_shards.last_attempt + IS 'last time a worker attempted to work on the shard'; +COMMENT ON COLUMN work_shards.completed + IS 'set to TRUE once the shard is finished by a worker'; +COMMENT ON COLUMN work_shards.start_row + IS 'row at which the shard scope starts, inclusive'; +COMMENT ON COLUMN work_shards.end_row + IS 'row at which the shard scope ends, exclusive'; +COMMENT ON COLUMN work_shards.job_name + IS 'unique name of the job the workers on this shard are performing'; + +CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index + ON work_shards + (job_name + ,completed + ,last_attempt + ); + + +-- ------------------------------ revolving_work_shards ---------------------------------------- + +CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards + (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,last_attempt INT8 NOT NULL + ,start_row INT4 NOT NULL + ,end_row INT4 NOT NULL + ,active BOOLEAN NOT NULL DEFAULT FALSE + ,job_name VARCHAR NOT NULL + ,PRIMARY KEY (job_name, start_row) + ); +COMMENT ON TABLE revolving_work_shards + IS 'coordinates work between multiple processes working on the same job with partitions that need to be repeatedly processed; unlogged because on system crashes the locks represented by this table will have to be cleared anyway, typically using "taler-exchange-dbinit -s"'; +COMMENT ON COLUMN revolving_work_shards.shard_serial_id + IS 'unique serial number identifying the shard'; +COMMENT ON COLUMN revolving_work_shards.last_attempt + IS 'last time a worker attempted to work on the shard'; +COMMENT ON COLUMN revolving_work_shards.active + IS 'set to TRUE when a worker is active on the shard'; +COMMENT ON COLUMN revolving_work_shards.start_row + IS 'row at which the shard scope starts, inclusive'; +COMMENT ON COLUMN revolving_work_shards.end_row + IS 'row at which the shard scope ends, exclusive'; +COMMENT ON COLUMN revolving_work_shards.job_name + IS 'unique name of the job the workers on this shard are performing'; + +CREATE INDEX IF NOT EXISTS revolving_work_shards_by_job_name_active_last_attempt_index + ON revolving_work_shards + (job_name + ,active + ,last_attempt + ); + +-------------------------------------------------------------------------- +-- Tables for P2P payments +-------------------------------------------------------------------------- + +-- ------------------------------ partners ---------------------------------------- + +CREATE TABLE IF NOT EXISTS partners + (partner_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32) + ,start_date INT8 NOT NULL + ,end_date INT8 NOT NULL + ,wad_frequency INT8 NOT NULL + ,wad_fee_val INT8 NOT NULL + ,wad_fee_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,partner_base_url TEXT NOT NULL + ); +COMMENT ON TABLE partners + IS 'exchanges we do wad transfers to'; +COMMENT ON COLUMN partners.partner_master_pub + IS 'offline master public key of the partner'; +COMMENT ON COLUMN partners.start_date + IS 'starting date of the partnership'; +COMMENT ON COLUMN partners.end_date + IS 'end date of the partnership'; +COMMENT ON COLUMN partners.wad_frequency + IS 'how often do we promise to do wad transfers'; +COMMENT ON COLUMN partners.wad_fee_val + IS 'how high is the fee for a wallet to be added to a wad to this partner'; +COMMENT ON COLUMN partners.partner_base_url + IS 'base URL of the REST API for this partner'; +COMMENT ON COLUMN partners.master_sig + IS 'signature of our master public key affirming the partnership, of purpose TALER_SIGNATURE_MASTER_PARTNER_DETAILS'; + + +-- ------------------------------ 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); +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 + IS 'Public key of the purse'; +COMMENT ON COLUMN purse_requests.purse_expiration + IS 'When the purse is set to expire'; +COMMENT ON COLUMN purse_requests.h_contract_terms + IS 'Hash of the contract the parties are to agree to'; +COMMENT ON COLUMN purse_requests.amount_with_fee_val + IS 'Total amount expected to be in the purse'; +COMMENT ON COLUMN purse_requests.balance_val + IS 'Total amount actually in the purse'; +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); +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 + IS 'identifies the partner exchange, NULL in case the target reserve lives at this exchange'; +COMMENT ON COLUMN purse_merges.reserve_pub + IS 'public key of the target reserve'; +COMMENT ON COLUMN purse_merges.purse_pub + IS 'public key of the purse'; +COMMENT ON COLUMN purse_merges.merge_sig + IS 'signature by the purse private key affirming the merge, of type TALER_SIGNATURE_WALLET_PURSE_MERGE'; +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); +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 + IS 'public key of the target reserve'; +COMMENT ON COLUMN account_merges.purse_pub + IS 'public key of the purse'; +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); +COMMENT ON TABLE contracts + IS 'encrypted contracts associated with purses'; +COMMENT ON COLUMN contracts.purse_pub + IS 'public key of the purse that the contract is associated with'; +COMMENT ON COLUMN contracts.pub_ckey + IS 'Public ECDH key used to encrypt the contract, to be used with the purse private key for decryption'; +COMMENT ON COLUMN contracts.e_contract + IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after decryption)'; + +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); +COMMENT ON TABLE history_requests + IS 'Paid history requests issued by a client against a reserve'; +COMMENT ON COLUMN history_requests.request_timestamp + IS 'When was the history request made'; +COMMENT ON COLUMN history_requests.reserve_sig + IS 'Signature approving payment for the history request'; +COMMENT ON COLUMN history_requests.history_fee_val + IS 'History fee approved by the signature'; + +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); +COMMENT ON TABLE close_requests + IS 'Explicit requests by a reserve owner to close a reserve immediately'; +COMMENT ON COLUMN close_requests.close_timestamp + IS 'When the request was created by the client'; +COMMENT ON COLUMN close_requests.reserve_sig + IS 'Signature affirming that the reserve is to be closed'; +COMMENT ON COLUMN close_requests.close_val + IS 'Balance of the reserve at the time of closing, to be wired to the associated bank account (minus the closing fee)'; + +CREATE TABLE IF NOT EXISTS close_requests_default + PARTITION OF close_requests + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + +-- ------------------------------ 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); +COMMENT ON TABLE purse_deposits + IS 'Requests depositing coins into a purse'; +COMMENT ON COLUMN purse_deposits.partner_serial_id + IS 'identifies the partner exchange, NULL in case the target purse lives at this exchange'; +COMMENT ON COLUMN purse_deposits.purse_pub + IS 'Public key of the purse'; +COMMENT ON COLUMN purse_deposits.coin_pub + IS 'Public key of the coin being deposited'; +COMMENT ON COLUMN purse_deposits.amount_with_fee_val + IS 'Total amount being deposited'; +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); +COMMENT ON TABLE wads_out + IS 'Wire transfers made to another exchange to transfer purse funds'; +COMMENT ON COLUMN wads_out.wad_id + IS 'Unique identifier of the wad, part of the wire transfer subject'; +COMMENT ON COLUMN wads_out.partner_serial_id + IS 'target exchange of the wad'; +COMMENT ON COLUMN wads_out.amount_val + IS 'Amount that was wired'; +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); +COMMENT ON TABLE wad_out_entries + IS 'Purses combined into a wad'; +COMMENT ON COLUMN wad_out_entries.wad_out_serial_id + IS 'Wad the purse was part of'; +COMMENT ON COLUMN wad_out_entries.reserve_pub + IS 'Target reserve for the purse'; +COMMENT ON COLUMN wad_out_entries.purse_pub + IS 'Public key of the purse'; +COMMENT ON COLUMN wad_out_entries.h_contract + IS 'Hash of the contract associated with the purse'; +COMMENT ON COLUMN wad_out_entries.purse_expiration + IS 'Time when the purse expires'; +COMMENT ON COLUMN wad_out_entries.merge_timestamp + IS 'Time when the merge was approved'; +COMMENT ON COLUMN wad_out_entries.amount_with_fee_val + IS 'Total amount in the purse'; +COMMENT ON COLUMN wad_out_entries.wad_fee_val + IS 'Wat fee charged to the purse'; +COMMENT ON COLUMN wad_out_entries.deposit_fees_val + IS 'Total deposit fees charged to the purse'; +COMMENT ON COLUMN wad_out_entries.reserve_sig + IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE'; +COMMENT ON COLUMN wad_out_entries.purse_sig + IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE'; + +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); +COMMENT ON TABLE wads_in + IS 'Incoming exchange-to-exchange wad wire transfers'; +COMMENT ON COLUMN wads_in.wad_id + IS 'Unique identifier of the wad, part of the wire transfer subject'; +COMMENT ON COLUMN wads_in.origin_exchange_url + IS 'Base URL of the originating URL, also part of the wire transfer subject'; +COMMENT ON COLUMN wads_in.amount_val + IS 'Actual amount that was received by our exchange'; +COMMENT ON COLUMN wads_in.arrival_time + IS 'Time when the wad was received'; + +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); +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 + IS 'wad for which the given purse was included in the aggregation'; +COMMENT ON COLUMN wad_in_entries.reserve_pub + IS 'target account of the purse (must be at the local exchange)'; +COMMENT ON COLUMN wad_in_entries.purse_pub + IS 'public key of the purse that was merged'; +COMMENT ON COLUMN wad_in_entries.h_contract + IS 'hash of the contract terms of the purse'; +COMMENT ON COLUMN wad_in_entries.purse_expiration + IS 'Time when the purse was set to expire'; +COMMENT ON COLUMN wad_in_entries.merge_timestamp + IS 'Time when the merge was approved'; +COMMENT ON COLUMN wad_in_entries.amount_with_fee_val + IS 'Total amount in the purse'; +COMMENT ON COLUMN wad_in_entries.wad_fee_val + IS 'Total wad fees paid by the purse'; +COMMENT ON COLUMN wad_in_entries.deposit_fees_val + IS 'Total deposit fees paid when depositing coins into the purse'; +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'); + + +-- ------------------------------ partner_accounts ---------------------------------------- + +CREATE TABLE IF NOT EXISTS partner_accounts + (payto_uri VARCHAR PRIMARY KEY + ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64) + ,last_seen INT8 NOT NULL + ); +CREATE INDEX IF NOT EXISTS partner_accounts_index_by_partner_and_time + ON partner_accounts (partner_serial_id,last_seen); +COMMENT ON TABLE partner_accounts + IS 'Table with bank accounts of the partner exchange. Entries never expire as we need to remember the signature for the auditor.'; +COMMENT ON COLUMN partner_accounts.payto_uri + IS 'payto URI (RFC 8905) with the bank account of the partner exchange.'; +COMMENT ON COLUMN partner_accounts.partner_master_sig + IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS by the partner master public key'; +COMMENT ON COLUMN partner_accounts.last_seen + IS 'Last time we saw this account as being active at the partner exchange. Used to select the most recent entry, and to detect when we should check again.'; + + +--------------------------------------------------------------------------- +-- Stored procedures +--------------------------------------------------------------------------- + +CREATE OR REPLACE FUNCTION exchange_do_withdraw( + IN cs_nonce BYTEA, + IN amount_val INT8, + IN amount_frac INT4, + IN h_denom_pub BYTEA, + IN rpub BYTEA, + IN reserve_sig BYTEA, + IN h_coin_envelope BYTEA, + IN denom_sig BYTEA, + IN now INT8, + IN min_reserve_gc INT8, + OUT reserve_found BOOLEAN, + OUT balance_ok BOOLEAN, + OUT kycok BOOLEAN, + OUT account_uuid INT8, + OUT ruuid INT8) +LANGUAGE plpgsql +AS $$ +DECLARE + reserve_gc INT8; +DECLARE + denom_serial INT8; +DECLARE + reserve_val INT8; +DECLARE + reserve_frac INT4; +BEGIN +-- Shards: reserves by reserve_pub (SELECT) +-- reserves_out (INSERT, with CONFLICT detection) by wih +-- reserves by reserve_pub (UPDATE) +-- reserves_in by reserve_pub (SELECT) +-- wire_targets by wire_target_h_payto + +SELECT denominations_serial + INTO denom_serial + FROM denominations + WHERE denom_pub_hash=h_denom_pub; + +IF NOT FOUND +THEN + -- denomination unknown, should be impossible! + reserve_found=FALSE; + balance_ok=FALSE; + kycok=FALSE; + account_uuid=0; + ruuid=0; + ASSERT false, 'denomination unknown'; + RETURN; +END IF; + + +SELECT + current_balance_val + ,current_balance_frac + ,gc_date + ,reserve_uuid + INTO + reserve_val + ,reserve_frac + ,reserve_gc + ,ruuid + FROM reserves + WHERE reserves.reserve_pub=rpub; + +IF NOT FOUND +THEN + -- reserve unknown + reserve_found=FALSE; + balance_ok=FALSE; + kycok=FALSE; + account_uuid=0; + ruuid=2; + RETURN; +END IF; + +-- We optimistically insert, and then on conflict declare +-- the query successful due to idempotency. +INSERT INTO reserves_out + (h_blind_ev + ,denominations_serial + ,denom_sig + ,reserve_uuid + ,reserve_sig + ,execution_date + ,amount_with_fee_val + ,amount_with_fee_frac) +VALUES + (h_coin_envelope + ,denom_serial + ,denom_sig + ,ruuid + ,reserve_sig + ,now + ,amount_val + ,amount_frac) +ON CONFLICT DO NOTHING; + +IF NOT FOUND +THEN + -- idempotent query, all constraints must be satisfied + reserve_found=TRUE; + balance_ok=TRUE; + kycok=TRUE; + account_uuid=0; + RETURN; +END IF; + +-- Check reserve balance is sufficient. +IF (reserve_val > amount_val) +THEN + IF (reserve_frac >= amount_frac) + THEN + reserve_val=reserve_val - amount_val; + reserve_frac=reserve_frac - amount_frac; + ELSE + reserve_val=reserve_val - amount_val - 1; + reserve_frac=reserve_frac + 100000000 - amount_frac; + END IF; +ELSE + IF (reserve_val = amount_val) AND (reserve_frac >= amount_frac) + THEN + reserve_val=0; + reserve_frac=reserve_frac - amount_frac; + ELSE + reserve_found=TRUE; + balance_ok=FALSE; + kycok=FALSE; -- we do not really know or care + account_uuid=0; + RETURN; + END IF; +END IF; + +-- Calculate new expiration dates. +min_reserve_gc=GREATEST(min_reserve_gc,reserve_gc); + +-- Update reserve balance. +UPDATE reserves SET + gc_date=min_reserve_gc + ,current_balance_val=reserve_val + ,current_balance_frac=reserve_frac +WHERE + reserves.reserve_pub=rpub; + +reserve_found=TRUE; +balance_ok=TRUE; + + + +-- Special actions needed for a CS withdraw? +IF NOT NULL cs_nonce +THEN + -- Cache CS signature to prevent replays in the future + -- (and check if cached signature exists at the same time). + INSERT INTO cs_nonce_locks + (nonce + ,max_denomination_serial + ,op_hash) + VALUES + (cs_nonce + ,denom_serial + ,h_coin_envelope) + ON CONFLICT DO NOTHING; + + IF NOT FOUND + THEN + -- See if the existing entry is identical. + SELECT 1 + FROM cs_nonce_locks + WHERE nonce=cs_nonce + AND op_hash=h_coin_envelope; + IF NOT FOUND + THEN + reserve_found=FALSE; + balance_ok=FALSE; + kycok=FALSE; + account_uuid=0; + ruuid=1; -- FIXME: return error message more nicely! + ASSERT false, 'nonce reuse attempted by client'; + END IF; + END IF; +END IF; + + + +-- Obtain KYC status based on the last wire transfer into +-- this reserve. FIXME: likely not adequate for reserves that got P2P transfers! +SELECT + kyc_ok + ,wire_target_serial_id + INTO + kycok + ,account_uuid + FROM reserves_in + JOIN wire_targets ON (wire_source_h_payto = wire_target_h_payto) + WHERE reserve_pub=rpub + LIMIT 1; -- limit 1 should not be required (without p2p transfers) + + +END $$; + +COMMENT ON FUNCTION exchange_do_withdraw(BYTEA, INT8, INT4, BYTEA, BYTEA, BYTEA, BYTEA, BYTEA, INT8, INT8) + IS 'Checks whether the reserve has sufficient balance for a withdraw operation (or the request is repeated and was previously approved) and if so updates the database with the result'; + + + +CREATE OR REPLACE FUNCTION exchange_do_withdraw_limit_check( + IN ruuid INT8, + IN start_time INT8, + IN upper_limit_val INT8, + IN upper_limit_frac INT4, + OUT below_limit BOOLEAN) +LANGUAGE plpgsql +AS $$ +DECLARE + total_val INT8; +DECLARE + total_frac INT8; -- INT4 could overflow during accumulation! +BEGIN +-- NOTE: Read-only, but crosses shards. +-- Shards: reserves by reserve_pub +-- reserves_out by reserve_uuid -- crosses shards!! + + +SELECT + SUM(amount_with_fee_val) -- overflow here is not plausible + ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits + INTO + total_val + ,total_frac + FROM reserves_out + WHERE reserve_uuid=ruuid + AND execution_date > start_time; + +-- normalize result +total_val = total_val + total_frac / 100000000; +total_frac = total_frac % 100000000; + +-- compare to threshold +below_limit = (total_val < upper_limit_val) OR + ( (total_val = upper_limit_val) AND + (total_frac <= upper_limit_frac) ); +END $$; + +COMMENT ON FUNCTION exchange_do_withdraw_limit_check(INT8, INT8, INT8, INT4) + IS 'Check whether the withdrawals from the given reserve since the given time are below the given threshold'; + + +-- NOTE: experiment, currently dead, see postgres_Start_deferred_wire_out; +-- now done inline. FIXME: Remove code here once inline version is confirmed working nicely! +CREATE OR REPLACE PROCEDURE defer_wire_out() +LANGUAGE plpgsql +AS $$ +BEGIN + +IF EXISTS ( + SELECT 1 + FROM information_Schema.constraint_column_usage + WHERE table_name='wire_out' + AND constraint_name='wire_out_ref') +THEN + SET CONSTRAINTS wire_out_ref DEFERRED; +END IF; + +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_deposit( + IN in_amount_with_fee_val INT8, + IN in_amount_with_fee_frac INT4, + IN in_h_contract_terms BYTEA, + IN in_wire_salt BYTEA, + IN in_wallet_timestamp INT8, + IN in_exchange_timestamp INT8, + IN in_refund_deadline INT8, + IN in_wire_deadline INT8, + IN in_merchant_pub BYTEA, + IN in_receiver_wire_account VARCHAR, + IN in_h_payto BYTEA, + IN in_known_coin_id INT8, + IN in_coin_pub BYTEA, + IN in_coin_sig BYTEA, + IN in_shard INT8, + IN in_extension_blocked BOOLEAN, + IN in_extension_details VARCHAR, + OUT out_exchange_timestamp INT8, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +DECLARE + wtsi INT8; -- wire target serial id +DECLARE + xdi INT8; -- eXstension details serial id +BEGIN +-- Shards: INSERT extension_details (by extension_details_serial_id) +-- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING; +-- INSERT deposits (by coin_pub, shard), ON CONFLICT DO NOTHING; +-- UPDATE known_coins (by coin_pub) + +IF NOT NULL in_extension_details +THEN + INSERT INTO extension_details + (extension_options) + VALUES + (in_extension_details) + RETURNING extension_details_serial_id INTO xdi; +ELSE + xdi=NULL; +END IF; + + +INSERT INTO wire_targets + (wire_target_h_payto + ,payto_uri) + VALUES + (in_h_payto + ,in_receiver_wire_account) +ON CONFLICT DO NOTHING -- for CONFLICT ON (wire_target_h_payto) + RETURNING wire_target_serial_id INTO wtsi; + +IF NOT FOUND +THEN + SELECT wire_target_serial_id + INTO wtsi + FROM wire_targets + WHERE wire_target_h_payto=in_h_payto; +END IF; + + +INSERT INTO deposits + (shard + ,coin_pub + ,known_coin_id + ,amount_with_fee_val + ,amount_with_fee_frac + ,wallet_timestamp + ,exchange_timestamp + ,refund_deadline + ,wire_deadline + ,merchant_pub + ,h_contract_terms + ,coin_sig + ,wire_salt + ,wire_target_h_payto + ,extension_blocked + ,extension_details_serial_id + ) + VALUES + (in_shard + ,in_coin_pub + ,in_known_coin_id + ,in_amount_with_fee_val + ,in_amount_with_fee_frac + ,in_wallet_timestamp + ,in_exchange_timestamp + ,in_refund_deadline + ,in_wire_deadline + ,in_merchant_pub + ,in_h_contract_terms + ,in_coin_sig + ,in_wire_salt + ,in_h_payto + ,in_extension_blocked + ,xdi) + ON CONFLICT DO NOTHING; + +IF NOT FOUND +THEN + -- Idempotency check: see if an identical record exists. + -- Note that by checking 'coin_sig', we implicitly check + -- identity over everything that the signature covers. + -- We do select over merchant_pub and wire_target_h_payto + -- primarily here to maximally use the existing index. + SELECT + exchange_timestamp + INTO + out_exchange_timestamp + FROM deposits + WHERE shard=in_shard + AND merchant_pub=in_merchant_pub + AND wire_target_h_payto=in_h_payto + AND coin_pub=in_coin_pub + AND coin_sig=in_coin_sig; + + IF NOT FOUND + THEN + -- Deposit exists, but with differences. Not allowed. + out_balance_ok=FALSE; + out_conflict=TRUE; + RETURN; + END IF; + + -- Idempotent request known, return success. + out_balance_ok=TRUE; + out_conflict=FALSE; + + RETURN; +END IF; + + +out_exchange_timestamp=in_exchange_timestamp; + +-- Check and update balance of the coin. +UPDATE known_coins + SET + remaining_frac=remaining_frac-in_amount_with_fee_frac + + CASE + WHEN remaining_frac < in_amount_with_fee_frac + THEN 100000000 + ELSE 0 + END, + remaining_val=remaining_val-in_amount_with_fee_val + - CASE + WHEN remaining_frac < in_amount_with_fee_frac + THEN 1 + ELSE 0 + END + WHERE coin_pub=in_coin_pub + AND ( (remaining_val > in_amount_with_fee_val) OR + ( (remaining_frac >= in_amount_with_fee_frac) AND + (remaining_val >= in_amount_with_fee_val) ) ); + +IF NOT FOUND +THEN + -- Insufficient balance. + out_balance_ok=FALSE; + out_conflict=FALSE; + RETURN; +END IF; + +-- Everything fine, return success! +out_balance_ok=TRUE; +out_conflict=FALSE; + +END $$; + + + +CREATE OR REPLACE FUNCTION exchange_do_melt( + IN in_cs_rms BYTEA, + IN in_amount_with_fee_val INT8, + IN in_amount_with_fee_frac INT4, + IN in_rc BYTEA, + IN in_old_coin_pub BYTEA, + IN in_old_coin_sig BYTEA, + IN in_known_coin_id INT8, -- not used, but that's OK + IN in_noreveal_index INT4, + IN in_zombie_required BOOLEAN, + OUT out_balance_ok BOOLEAN, + OUT out_zombie_bad BOOLEAN, + OUT out_noreveal_index INT4) +LANGUAGE plpgsql +AS $$ +DECLARE + denom_max INT8; +BEGIN +-- Shards: INSERT refresh_commitments (by rc) +-- (rare:) SELECT refresh_commitments (by old_coin_pub) -- crosses shards! +-- (rare:) SEELCT refresh_revealed_coins (by melt_serial_id) +-- (rare:) PERFORM recoup_refresh (by rrc_serial) -- crosses shards! +-- UPDATE known_coins (by coin_pub) + +INSERT INTO refresh_commitments + (rc + ,old_coin_pub + ,old_coin_sig + ,amount_with_fee_val + ,amount_with_fee_frac + ,noreveal_index + ) + VALUES + (in_rc + ,in_old_coin_pub + ,in_old_coin_sig + ,in_amount_with_fee_val + ,in_amount_with_fee_frac + ,in_noreveal_index) + ON CONFLICT DO NOTHING; + +IF NOT FOUND +THEN + -- Idempotency check: see if an identical record exists. + out_noreveal_index=-1; + SELECT + noreveal_index + INTO + out_noreveal_index + FROM refresh_commitments + WHERE rc=in_rc; + out_balance_ok=FOUND; + out_zombie_bad=FALSE; -- zombie is OK + RETURN; +END IF; + + +IF in_zombie_required +THEN + -- Check if this coin was part of a refresh + -- operation that was subsequently involved + -- in a recoup operation. We begin by all + -- refresh operations our coin was involved + -- with, then find all associated reveal + -- operations, and then see if any of these + -- reveal operations was involved in a recoup. + PERFORM + FROM recoup_refresh + WHERE rrc_serial IN + (SELECT rrc_serial + FROM refresh_revealed_coins + WHERE melt_serial_id IN + (SELECT melt_serial_id + FROM refresh_commitments + WHERE old_coin_pub=in_old_coin_pub)); + IF NOT FOUND + THEN + out_zombie_bad=TRUE; + out_balance_ok=FALSE; + RETURN; + END IF; +END IF; + +out_zombie_bad=FALSE; -- zombie is OK + + +-- Check and update balance of the coin. +UPDATE known_coins + SET + remaining_frac=remaining_frac-in_amount_with_fee_frac + + CASE + WHEN remaining_frac < in_amount_with_fee_frac + THEN 100000000 + ELSE 0 + END, + remaining_val=remaining_val-in_amount_with_fee_val + - CASE + WHEN remaining_frac < in_amount_with_fee_frac + THEN 1 + ELSE 0 + END + WHERE coin_pub=in_old_coin_pub + AND ( (remaining_val > in_amount_with_fee_val) OR + ( (remaining_frac >= in_amount_with_fee_frac) AND + (remaining_val >= in_amount_with_fee_val) ) ); + +IF NOT FOUND +THEN + -- Insufficient balance. + out_noreveal_index=-1; + out_balance_ok=FALSE; + RETURN; +END IF; + + + +-- Special actions needed for a CS melt? +IF NOT NULL in_cs_rms +THEN + -- Get maximum denominations serial value in + -- existence, this will determine how long the + -- nonce will be locked. + SELECT + denominations_serial + INTO + denom_max + FROM denominations + ORDER BY denominations_serial DESC + LIMIT 1; + + -- Cache CS signature to prevent replays in the future + -- (and check if cached signature exists at the same time). + INSERT INTO cs_nonce_locks + (nonce + ,max_denomination_serial + ,op_hash) + VALUES + (cs_rms + ,denom_serial + ,in_rc) + ON CONFLICT DO NOTHING; + + IF NOT FOUND + THEN + -- Record exists, make sure it is the same + SELECT 1 + FROM cs_nonce_locks + WHERE nonce=cs_rms + AND op_hash=in_rc; + + IF NOT FOUND + THEN + -- Nonce reuse detected + out_balance_ok=FALSE; + out_zombie_bad=FALSE; + out_noreveal_index=42; -- FIXME: return error message more nicely! + ASSERT false, 'nonce reuse attempted by client'; + END IF; + END IF; +END IF; + +-- Everything fine, return success! +out_balance_ok=TRUE; +out_noreveal_index=in_noreveal_index; + +END $$; + + + +CREATE OR REPLACE FUNCTION exchange_do_refund( + IN in_amount_with_fee_val INT8, + IN in_amount_with_fee_frac INT4, + IN in_amount_val INT8, + IN in_amount_frac INT4, + IN in_deposit_fee_val INT8, + IN in_deposit_fee_frac INT4, + IN in_h_contract_terms BYTEA, + IN in_rtransaction_id INT8, + IN in_deposit_shard INT8, + IN in_known_coin_id INT8, + IN in_coin_pub BYTEA, + IN in_merchant_pub BYTEA, + IN in_merchant_sig BYTEA, + OUT out_not_found BOOLEAN, + OUT out_refund_ok BOOLEAN, + OUT out_gone BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +DECLARE + dsi INT8; -- ID of deposit being refunded +DECLARE + tmp_val INT8; -- total amount refunded +DECLARE + tmp_frac INT8; -- total amount refunded +DECLARE + deposit_val INT8; -- amount that was originally deposited +DECLARE + deposit_frac INT8; -- amount that was originally deposited +BEGIN +-- Shards: SELECT deposits (coin_pub, shard, h_contract_terms, merchant_pub) +-- INSERT refunds (by coin_pub, rtransaction_id) ON CONFLICT DO NOTHING +-- SELECT refunds (by coin_pub) +-- UPDATE known_coins (by coin_pub) + +SELECT + deposit_serial_id + ,amount_with_fee_val + ,amount_with_fee_frac + ,done +INTO + dsi + ,deposit_val + ,deposit_frac + ,out_gone +FROM deposits + WHERE coin_pub=in_coin_pub + AND shard=in_deposit_shard + AND merchant_pub=in_merchant_pub + AND h_contract_terms=in_h_contract_terms; + +IF NOT FOUND +THEN + -- No matching deposit found! + out_refund_ok=FALSE; + out_conflict=FALSE; + out_not_found=TRUE; + out_gone=FALSE; + RETURN; +END IF; + +INSERT INTO refunds + (deposit_serial_id + ,coin_pub + ,merchant_sig + ,rtransaction_id + ,amount_with_fee_val + ,amount_with_fee_frac + ) + VALUES + (dsi + ,in_coin_pub + ,in_merchant_sig + ,in_rtransaction_id + ,in_amount_with_fee_val + ,in_amount_with_fee_frac) + ON CONFLICT DO NOTHING; + +IF NOT FOUND +THEN + -- Idempotency check: see if an identical record exists. + -- Note that by checking 'coin_sig', we implicitly check + -- identity over everything that the signature covers. + -- We do select over merchant_pub and h_contract_terms + -- primarily here to maximally use the existing index. + PERFORM + FROM refunds + WHERE coin_pub=in_coin_pub + AND deposit_serial_id=dsi + AND rtransaction_id=in_rtransaction_id + AND amount_with_fee_val=in_amount_with_fee_val + AND amount_with_fee_frac=in_amount_with_fee_frac; + + IF NOT FOUND + THEN + -- Deposit exists, but have conflicting refund. + out_refund_ok=FALSE; + out_conflict=TRUE; + out_not_found=FALSE; + RETURN; + END IF; + + -- Idempotent request known, return success. + out_refund_ok=TRUE; + out_conflict=FALSE; + out_not_found=FALSE; + out_gone=FALSE; + RETURN; +END IF; + +IF out_gone +THEN + -- money already sent to the merchant. Tough luck. + out_refund_ok=FALSE; + out_conflict=FALSE; + out_not_found=FALSE; + RETURN; +END IF; + +-- Check refund balance invariant. +SELECT + SUM(amount_with_fee_val) -- overflow here is not plausible + ,SUM(CAST(amount_with_fee_frac AS INT8)) -- compute using 64 bits + INTO + tmp_val + ,tmp_frac + FROM refunds + WHERE coin_pub=in_coin_pub + AND deposit_serial_id=dsi; +IF tmp_val IS NULL +THEN + RAISE NOTICE 'failed to sum up existing refunds'; + out_refund_ok=FALSE; + out_conflict=FALSE; + out_not_found=FALSE; + RETURN; +END IF; + +-- Normalize result before continuing +tmp_val = tmp_val + tmp_frac / 100000000; +tmp_frac = tmp_frac % 100000000; + +-- Actually check if the deposits are sufficient for the refund. Verbosely. ;-) +IF (tmp_val < deposit_val) +THEN + out_refund_ok=TRUE; +ELSE + IF (tmp_val = deposit_val) AND (tmp_frac <= deposit_frac) + THEN + out_refund_ok=TRUE; + ELSE + out_refund_ok=FALSE; + END IF; +END IF; + +IF (tmp_val = deposit_val) AND (tmp_frac = deposit_frac) +THEN + -- Refunds have reached the full value of the original + -- deposit. Also refund the deposit fee. + in_amount_frac = in_amount_frac + in_deposit_fee_frac; + in_amount_val = in_amount_val + in_deposit_fee_val; + + -- Normalize result before continuing + in_amount_val = in_amount_val + in_amount_frac / 100000000; + in_amount_frac = in_amount_frac % 100000000; +END IF; + +-- Update balance of the coin. +UPDATE known_coins + SET + remaining_frac=remaining_frac+in_amount_frac + - CASE + WHEN remaining_frac+in_amount_frac >= 100000000 + THEN 100000000 + ELSE 0 + END, + remaining_val=remaining_val+in_amount_val + + CASE + WHEN remaining_frac+in_amount_frac >= 100000000 + THEN 1 + ELSE 0 + END + WHERE coin_pub=in_coin_pub; + + +out_conflict=FALSE; +out_not_found=FALSE; + +END $$; + +-- COMMENT ON FUNCTION exchange_do_refund(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) +-- IS 'Executes a refund operation, checking that the corresponding deposit was sufficient to cover the refunded amount'; + + + + +CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve( + IN in_reserve_pub BYTEA, + IN in_reserve_out_serial_id INT8, + IN in_coin_blind BYTEA, + IN in_coin_pub BYTEA, + IN in_known_coin_id INT8, + IN in_coin_sig BYTEA, + IN in_reserve_gc INT8, + IN in_reserve_expiration INT8, + IN in_recoup_timestamp INT8, + OUT out_recoup_ok BOOLEAN, + OUT out_internal_failure BOOLEAN, + OUT out_recoup_timestamp INT8) +LANGUAGE plpgsql +AS $$ +DECLARE + tmp_val INT8; -- amount recouped +DECLARE + tmp_frac INT8; -- amount recouped +BEGIN +-- Shards: SELECT known_coins (by coin_pub) +-- SELECT recoup (by coin_pub) +-- UPDATE known_coins (by coin_pub) +-- UPDATE reserves (by reserve_pub) +-- INSERT recoup (by coin_pub) + +out_internal_failure=FALSE; + + +-- Check remaining balance of the coin. +SELECT + remaining_frac + ,remaining_val + INTO + tmp_frac + ,tmp_val +FROM known_coins + WHERE coin_pub=in_coin_pub; + +IF NOT FOUND +THEN + out_internal_failure=TRUE; + out_recoup_ok=FALSE; + RETURN; +END IF; + +IF tmp_val + tmp_frac = 0 +THEN + -- Check for idempotency + SELECT + recoup_timestamp + INTO + out_recoup_timestamp + FROM recoup + WHERE coin_pub=in_coin_pub; + + out_recoup_ok=FOUND; + RETURN; +END IF; + + +-- Update balance of the coin. +UPDATE known_coins + SET + remaining_frac=0 + ,remaining_val=0 + WHERE coin_pub=in_coin_pub; + + +-- Credit the reserve and update reserve timers. +UPDATE reserves + SET + current_balance_frac=current_balance_frac+tmp_frac + - CASE + WHEN current_balance_frac+tmp_frac >= 100000000 + THEN 100000000 + ELSE 0 + END, + current_balance_val=current_balance_val+tmp_val + + CASE + WHEN current_balance_frac+tmp_frac >= 100000000 + THEN 1 + ELSE 0 + END, + gc_date=GREATEST(gc_date, in_reserve_gc), + expiration_date=GREATEST(expiration_date, in_reserve_expiration) + WHERE reserve_pub=in_reserve_pub; + + +IF NOT FOUND +THEN + RAISE NOTICE 'failed to increase reserve balance from recoup'; + out_recoup_ok=TRUE; + out_internal_failure=TRUE; + RETURN; +END IF; + + +INSERT INTO recoup + (coin_pub + ,coin_sig + ,coin_blind + ,amount_val + ,amount_frac + ,recoup_timestamp + ,reserve_out_serial_id + ) +VALUES + (in_coin_pub + ,in_coin_sig + ,in_coin_blind + ,tmp_val + ,tmp_frac + ,in_recoup_timestamp + ,in_reserve_out_serial_id); + +-- Normal end, everything is fine. +out_recoup_ok=TRUE; +out_recoup_timestamp=in_recoup_timestamp; + +END $$; + +-- COMMENT ON FUNCTION exchange_do_recoup_to_reserve(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) +-- IS 'Executes a recoup of a coin that was withdrawn from a reserve'; + + + + + + +CREATE OR REPLACE FUNCTION exchange_do_recoup_to_coin( + IN in_old_coin_pub BYTEA, + IN in_rrc_serial INT8, + IN in_coin_blind BYTEA, + IN in_coin_pub BYTEA, + IN in_known_coin_id INT8, + IN in_coin_sig BYTEA, + IN in_recoup_timestamp INT8, + OUT out_recoup_ok BOOLEAN, + OUT out_internal_failure BOOLEAN, + OUT out_recoup_timestamp INT8) +LANGUAGE plpgsql +AS $$ +DECLARE + tmp_val INT8; -- amount recouped +DECLARE + tmp_frac INT8; -- amount recouped +BEGIN + +-- Shards: UPDATE known_coins (by coin_pub) +-- SELECT recoup_refresh (by coin_pub) +-- UPDATE known_coins (by coin_pub) +-- INSERT recoup_refresh (by coin_pub) + + +out_internal_failure=FALSE; + + +-- Check remaining balance of the coin. +SELECT + remaining_frac + ,remaining_val + INTO + tmp_frac + ,tmp_val +FROM known_coins + WHERE coin_pub=in_coin_pub; + +IF NOT FOUND +THEN + out_internal_failure=TRUE; + out_recoup_ok=FALSE; + RETURN; +END IF; + +IF tmp_val + tmp_frac = 0 +THEN + -- Check for idempotency + SELECT + recoup_timestamp + INTO + out_recoup_timestamp + FROM recoup_refresh + WHERE coin_pub=in_coin_pub; + out_recoup_ok=FOUND; + RETURN; +END IF; + +-- Update balance of the coin. +UPDATE known_coins + SET + remaining_frac=0 + ,remaining_val=0 + WHERE coin_pub=in_coin_pub; + + +-- Credit the old coin. +UPDATE known_coins + SET + remaining_frac=remaining_frac+tmp_frac + - CASE + WHEN remaining_frac+tmp_frac >= 100000000 + THEN 100000000 + ELSE 0 + END, + remaining_val=remaining_val+tmp_val + + CASE + WHEN remaining_frac+tmp_frac >= 100000000 + THEN 1 + ELSE 0 + END + WHERE coin_pub=in_old_coin_pub; + + +IF NOT FOUND +THEN + RAISE NOTICE 'failed to increase old coin balance from recoup'; + out_recoup_ok=TRUE; + out_internal_failure=TRUE; + RETURN; +END IF; + + +INSERT INTO recoup_refresh + (coin_pub + ,known_coin_id + ,coin_sig + ,coin_blind + ,amount_val + ,amount_frac + ,recoup_timestamp + ,rrc_serial + ) +VALUES + (in_coin_pub + ,in_known_coin_id + ,in_coin_sig + ,in_coin_blind + ,tmp_val + ,tmp_frac + ,in_recoup_timestamp + ,in_rrc_serial); + +-- Normal end, everything is fine. +out_recoup_ok=TRUE; +out_recoup_timestamp=in_recoup_timestamp; + +END $$; + + +-- COMMENT ON FUNCTION exchange_do_recoup_to_coin(INT8, INT4, BYTEA, BOOLEAN, BOOLEAN) +-- IS 'Executes a recoup-refresh of a coin that was obtained from a refresh-reveal process'; + + + +CREATE OR REPLACE PROCEDURE exchange_do_gc( + IN in_ancient_date INT8, + IN in_now INT8) +LANGUAGE plpgsql +AS $$ +DECLARE + reserve_uuid_min INT8; -- minimum reserve UUID still alive +DECLARE + melt_min INT8; -- minimum melt still alive +DECLARE + coin_min INT8; -- minimum known_coin still alive +DECLARE + deposit_min INT8; -- minimum deposit still alive +DECLARE + reserve_out_min INT8; -- minimum reserve_out still alive +DECLARE + denom_min INT8; -- minimum denomination still alive +BEGIN + +DELETE FROM prewire + WHERE finished=TRUE; + +DELETE FROM wire_fee + WHERE end_date < in_ancient_date; + +-- TODO: use closing fee as threshold? +DELETE FROM reserves + WHERE gc_date < in_now + AND current_balance_val = 0 + AND current_balance_frac = 0; + +SELECT + reserve_out_serial_id + INTO + reserve_out_min + FROM reserves_out + ORDER BY reserve_out_serial_id ASC + LIMIT 1; + +DELETE FROM recoup + WHERE reserve_out_serial_id < reserve_out_min; +-- FIXME: recoup_refresh lacks GC! + +SELECT + reserve_uuid + INTO + reserve_uuid_min + FROM reserves + ORDER BY reserve_uuid ASC + LIMIT 1; + +DELETE FROM reserves_out + WHERE reserve_uuid < reserve_uuid_min; + +-- FIXME: this query will be horribly slow; +-- need to find another way to formulate it... +DELETE FROM denominations + WHERE expire_legal < in_now + AND denominations_serial NOT IN + (SELECT DISTINCT denominations_serial + FROM reserves_out) + AND denominations_serial NOT IN + (SELECT DISTINCT denominations_serial + FROM known_coins + WHERE coin_pub IN + (SELECT DISTINCT coin_pub + FROM recoup)) + AND denominations_serial NOT IN + (SELECT DISTINCT denominations_serial + FROM known_coins + WHERE coin_pub IN + (SELECT DISTINCT coin_pub + FROM recoup_refresh)); + +SELECT + melt_serial_id + INTO + melt_min + FROM refresh_commitments + ORDER BY melt_serial_id ASC + LIMIT 1; + +DELETE FROM refresh_revealed_coins + WHERE melt_serial_id < melt_min; + +DELETE FROM refresh_transfer_keys + WHERE melt_serial_id < melt_min; + +SELECT + known_coin_id + INTO + coin_min + FROM known_coins + ORDER BY known_coin_id ASC + LIMIT 1; + +DELETE FROM deposits + WHERE known_coin_id < coin_min; + +SELECT + deposit_serial_id + INTO + deposit_min + FROM deposits + ORDER BY deposit_serial_id ASC + LIMIT 1; + +DELETE FROM refunds + WHERE deposit_serial_id < deposit_min; + +DELETE FROM aggregation_tracking + WHERE deposit_serial_id < deposit_min; + +SELECT + denominations_serial + INTO + denom_min + FROM denominations + ORDER BY denominations_serial ASC + LIMIT 1; + +DELETE FROM cs_nonce_locks + WHERE max_denomination_serial <= denom_min; + +END $$; + + + + + + + + + +CREATE OR REPLACE FUNCTION exchange_do_purse_deposit( + IN in_purse_pub BYTEA, + IN in_amount_with_fee_val INT8, + IN in_amount_with_fee_frac INT4, + IN in_coin_pub BYTEA, + IN in_coin_sig BYTEA, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +BEGIN + -- FIXME +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_purse_merge( + IN in_purse_pub BYTEA, + IN in_merge_sig BYTEA, + IN in_merge_timestamp INT8, + IN in_partner_url VARCHAR, + IN in_reserve_pub BYTEA, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +BEGIN + -- FIXME +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_account_merge( + IN in_purse_pub BYTEA, + IN in_reserve_pub BYTEA, + IN in_reserve_sig BYTEA, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +BEGIN + -- FIXME +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_history_request( + IN in_reserve_pub BYTEA, + IN in_reserve_sig BYTEA, + IN in_request_timestamp INT8, + IN in_history_fee_val INT8, + IN in_history_fee_frac INT4, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +BEGIN + -- FIXME +END $$; + + +CREATE OR REPLACE FUNCTION exchange_do_close_request( + IN in_reserve_pub BYTEA, + IN in_reserve_sig BYTEA, + OUT out_final_balance_val INT8, + OUT out_final_balance_frac INT4, + OUT out_balance_ok BOOLEAN, + OUT out_conflict BOOLEAN) +LANGUAGE plpgsql +AS $$ +BEGIN + -- FIXME +END $$; + + +------------------------------------------------------------- +-- THE END +------------------------------------------------------------- + +-- Complete transaction +COMMIT; diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 2955663ac..1176faec0 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -20,6 +20,1877 @@ BEGIN; -- Check patch versioning is in place. SELECT _v.register_patch('exchange-0001', NULL, NULL); +-------------------- Tables ---------------------------- + +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 +$$; + +----------------------- wire_targets --------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_targets( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' + ',payto_uri VARCHAR NOT NULL' + ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' + ',external_id VARCHAR' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +-- We need a seperate function for this, as we call create_table only once but need to add +-- those constraints to each partition which gets created +CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + + EXECUTE FORMAT ( + 'ALTER TABLE wire_targets_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_targets_' || partition_suffix || '_wire_target_serial_id_key ' + 'UNIQUE (wire_target_serial_id)' + ); +END +$$; + +------------------------ reserves ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' + ',current_balance_val INT8 NOT NULL' + ',current_balance_frac INT4 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'ON ' || table_name || ' ' + '(expiration_date' + ',current_balance_val' + ',current_balance_frac' + ');' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' + 'IS ' || quote_literal('used in get_expired_reserves') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'ON ' || table_name || ' ' + '(gc_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' + 'IS ' || quote_literal('for reserve garbage collection') || ';' + ); + +END +$$; + +----------------------- reserves_in ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_reserves_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',wire_reference INT8 NOT NULL' + ',credit_val INT8 NOT NULL' + ',credit_frac INT4 NOT NULL' + ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_in_serial_id);' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section ' + ',execution_date' + ');' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section,' + 'reserve_in_serial_id DESC' + ');' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' + 'UNIQUE (reserve_in_serial_id)' + ); +END +$$; + +--------------------------- reserves_close ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',execution_date INT8 NOT NULL' + ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' + 'ON ' || table_name || ' ' + '(close_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' + 'PRIMARY KEY (close_uuid)' + ); +END +$$; + +---------------------------- reserves_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denom_sig BYTEA NOT NULL' + ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',execution_date INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,'reserves_out' + ,'PARTITION BY HASH (h_blind_ev)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid, execution_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' + ') %s ' + ,table_name + ,'PARTITION BY HASH (reserve_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + +END +$$; + +---------------------------- known_coins ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_known_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'known_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' + ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' + ',denom_sig BYTEA NOT NULL' + ',remaining_val INT8 NOT NULL' + ',remaining_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE known_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' + 'UNIQUE (known_coin_id)' + ); +END +$$; + +---------------------------- refresh_commitments ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_commitments'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' + ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',noreveal_index INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (rc)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- Note: index spans partitions, may need to be materialized. + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' + 'ON ' || table_name || ' ' + '(old_coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' + 'UNIQUE (melt_serial_id)' + ); +END +$$; + +------------------------------ refresh_revealed_coins -------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',freshcoin_index INT4 NOT NULL' + ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_ev BYTEA NOT NULL' -- UNIQUE' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' + ',ev_sig BYTEA NOT NULL' + ',ewv BYTEA NOT NULL' + -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'ON ' || table_name || ' ' + '(melt_serial_id);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' + 'UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' + 'UNIQUE (coin_ev) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' + 'UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + ); +END +$$; + +----------------------------- refresh_transfer_keys ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_transfer_keys'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' + ',transfer_privs BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' + 'UNIQUE (rtc_serial)' + ); +END +$$; + +---------------------------- deposits ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wallet_timestamp INT8 NOT NULL' + ',exchange_timestamp INT8 NOT NULL' + ',refund_deadline INT8 NOT NULL' + ',wire_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_details_serial_id INT8' -- REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE' + ',UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_by_ready'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_deadline INT8 NOT NULL' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (wire_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(wire_deadline ASC, shard ASC, coin_pub);' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_for_matching'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (refund_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(refund_deadline ASC, merchant_pub, coin_pub);' + ); + +END +$$; + +----------------------------- refunds ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' + ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' + ',rtransaction_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refunds_' || partition_suffix || ' ' + 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' + 'UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); +END +$$; + +---------------------------- wire_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wire_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',execution_date INT8 NOT NULL' + ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wtid_raw)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' + 'ON ' || table_name || ' ' + '(wire_target_h_payto);' + ); + + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wire_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' + 'PRIMARY KEY (wireout_uuid)' + ); +END +$$; + +---------------------------- aggregation_transient ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_aggregation_transient( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_transient'; +BEGIN + + EXECUTE FORMAT ( + 'CREATE TABLE IF NOT EXISTS %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +---------------------------- aggregation_tracking ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME chnage to coint_pub + deposit_serial_id for more efficient depost -- or something else ??? + ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (deposit_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'ON ' || table_name || ' ' + '(wtid_raw);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' + 'IS ' || quote_literal('for lookup_transactions') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' + 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' + 'UNIQUE (aggregation_serial_id) ' + ); +END +$$; + +----------------------------- recoup ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub);' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' + 'UNIQUE (recoup_uuid) ' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + +END +$$; + +---------------------------- recoup_refresh ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: any query using this index will be slow. Materialize index or change query? + -- Also: which query uses this index? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' + 'ON ' || table_name || ' ' + '(rrc_serial);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' + 'UNIQUE (recoup_refresh_uuid) ' + ); +END +$$; + +----------------------------- prewire ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' + ',wire_method TEXT NOT NULL' + ',finished BOOLEAN NOT NULL DEFAULT false' + ',failed BOOLEAN NOT NULL DEFAULT false' + ',buf BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (prewire_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'ON ' || table_name || ' ' + '(finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' + 'IS ' || quote_literal('for gc_prewire') || ';' + ); + -- FIXME: find a way to combine these two indices? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'ON ' || table_name || ' ' + '(failed,finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' + 'IS ' || quote_literal('for wire_prepare_data_get') || ';' + ); + +END +$$; + +----------------------------- cs_nonce_locks ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( + shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' + ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' + ',max_denomination_serial INT8 NOT NULL' + ') %s ;' + ,'cs_nonce_locks' + ,'PARTITION BY HASH (nonce)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' + 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' + 'UNIQUE (cs_nonce_lock_serial_id)' + ); +END +$$; + +------------------------- Partitions ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_partition( + 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 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 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; + +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 shardig 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_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; + +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_table_partition( + 'wire_targets' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'reserves_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_close' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'known_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_commitments' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_revealed_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_transfer_keys' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); + + PERFORM create_table_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_table_partition( +-- 'deposits_by_ready' +-- ,modulus +-- ,num_partitions +-- ); +-- +-- PERFORM create_table_partition( +-- 'deposits_for_matching' +-- ,modulus +-- ,num_partitions +-- ); + + PERFORM create_table_partition( + 'refunds' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'wire_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'aggregation_transient' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'aggregation_tracking' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'recoup_refresh' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'prewire' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'cs_nonce_locks' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + + num_partitions=num_partitions-1; + EXIT WHEN num_partitions=0; + + END LOOP; + + PERFORM drop_default_partitions(); + +END +$$; + +--------------------- Sharding --------------------------- + +---------------------- Shards ---------------------------- +CREATE OR REPLACE FUNCTION setup_shard( + shard_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_table_wire_targets(shard_suffix); + PERFORM add_constraints_to_wire_targets_partition(shard_suffix); + + PERFORM create_table_reserves(shard_suffix); + + PERFORM create_table_reserves_in(shard_suffix); + PERFORM add_constraints_to_reserves_in_partition(shard_suffix); + + PERFORM create_table_reserves_close(shard_suffix); + + PERFORM create_table_reserves_out(shard_suffix); + + PERFORM create_table_reserves_out_by_reserve(shard_suffix); + + PERFORM create_table_known_coins(shard_suffix); + PERFORM add_constraints_to_known_coins_partition(shard_suffix); + + PERFORM create_table_refresh_commitments(shard_suffix); + PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); + + PERFORM create_table_refresh_revealed_coins(shard_suffix); + PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); + + PERFORM create_table_refresh_transfer_keys(shard_suffix); + PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); + + PERFORM create_table_deposits(shard_suffix); + PERFORM add_constraints_to_deposits_partition(shard_suffix); + + PERFORM create_table_deposits_by_ready(shard_suffix); + + PERFORM create_table_deposits_for_matching(shard_suffix); + + PERFORM create_table_refunds(shard_suffix); + PERFORM add_constraints_to_refunds_partition(shard_suffix); + + PERFORM create_table_wire_out(shard_suffix); + PERFORM add_constraints_to_wire_out_partition(shard_suffix); + + PERFORM create_table_aggregation_transient(shard_suffix); + + PERFORM create_table_aggregation_tracking(shard_suffix); + PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); + + PERFORM create_table_recoup(shard_suffix); + PERFORM add_constraints_to_recoup_partition(shard_suffix); + + PERFORM create_table_recoup_by_reserve(shard_suffix); + + PERFORM create_table_recoup_refresh(shard_suffix); + PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); + + PERFORM create_table_prewire(shard_suffix); + + PERFORM create_table_cs_nonce_locks(shard_suffix); + PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); + +END +$$; + +------------------------------ Master ---------------------------------- +CREATE OR REPLACE FUNCTION create_foreign_table( + source_table_name VARCHAR + ,modulus INTEGER + ,shard_suffix VARCHAR + ,current_shard_num INTEGER + ) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, shard_suffix; + + EXECUTE FORMAT( + 'CREATE FOREIGN TABLE IF NOT EXISTS %I ' + 'PARTITION OF %I ' + 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) ' + 'SERVER %I' + ,source_table_name || '_' || shard_suffix + ,source_table_name + ,modulus + ,current_shard_num-1 + ,shard_suffix + ); + + EXECUTE FORMAT( + 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"', + source_table_name || '_' || shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION master_prepare_sharding() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + CREATE EXTENSION IF NOT EXISTS postgres_fdw; + + PERFORM detach_default_partitions(); + + ALTER TABLE IF EXISTS wire_targets + DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves + DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_in + DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_close + DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_out + DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey + ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key + ; + + ALTER TABLE IF EXISTS known_coins + DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_commitments + DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey + ; + + ALTER TABLE IF EXISTS refresh_revealed_coins + DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_transfer_keys + DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE + ; + + ALTER TABLE IF EXISTS deposits + DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS deposits_extension_details_serial_id_fkey + ,DROP CONSTRAINT IF EXISTS deposits_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE + ; + + ALTER TABLE IF EXISTS refunds + DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE + ; + + ALTER TABLE IF EXISTS wire_out + DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE + ; + + ALTER TABLE IF EXISTS aggregation_tracking + DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey + ; + + ALTER TABLE IF EXISTS recoup + DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE + ; + + ALTER TABLE IF EXISTS recoup_refresh + DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE + ; + + ALTER TABLE IF EXISTS prewire + DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE + ; + + ALTER TABLE IF EXISTS cs_nonce_locks + DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE + ; + +END +$$; + + +CREATE OR REPLACE FUNCTION create_shard_server( + shard_suffix VARCHAR + ,total_num_shards INTEGER + ,current_shard_num INTEGER + ,remote_host VARCHAR + ,remote_user VARCHAR + ,remote_user_password VARCHAR + ,remote_db_name VARCHAR DEFAULT 'taler-exchange' + ,remote_port INTEGER DEFAULT '5432' + ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating server %s', remote_host; + + EXECUTE FORMAT( + 'CREATE SERVER IF NOT EXISTS %I ' + 'FOREIGN DATA WRAPPER postgres_fdw ' + 'OPTIONS (dbname %L, host %L, port %L)' + ,shard_suffix + ,remote_db_name + ,remote_host + ,remote_port + ); + + EXECUTE FORMAT( + 'CREATE USER MAPPING IF NOT EXISTS ' + 'FOR %s SERVER %I ' + 'OPTIONS (user %L, password %L)' + ,local_user + ,shard_suffix + ,remote_user + ,remote_user_password + ); + + PERFORM create_foreign_table( + 'wire_targets' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_in' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_close' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'known_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_commitments' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_revealed_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_transfer_keys' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'deposits' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); +-- PERFORM create_foreign_table( +-- 'deposits_by_ready' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); +-- PERFORM create_foreign_table( +-- 'deposits_for_matching' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); + PERFORM create_foreign_table( + 'refunds' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'wire_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'aggregation_tracking' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_refresh' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'prewire' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'cs_nonce_locks' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + +END +$$; + +COMMENT ON FUNCTION create_shard_server + IS 'Create a shard server on the master + node with all foreign tables and user mappings'; + +CREATE OR REPLACE FUNCTION create_shards( + num_shards INTEGER + ,domain VARCHAR DEFAULT 'perf.taler' +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + FOR i IN 1..num_shards LOOP + PERFORM create_shard_server( + i + ,num_shards + ,i + ,'shard-' || i::varchar || '.' || domain + ,'taler' + ,'taler' + ,'taler-exchange' + ,'5432' + ,'taler-exchange-httpd' + ); + END LOOP; +END +$$; + +COMMIT;-- +-- 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; -- ------------------------------ denominations ---------------------------------------- @@ -72,14 +1943,8 @@ COMMENT ON TABLE denomination_revocations -- ------------------------------ wire_targets ---------------------------------------- -CREATE TABLE IF NOT EXISTS wire_targets - (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32) - ,payto_uri VARCHAR NOT NULL - ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE) - ,external_id VARCHAR - ) - PARTITION BY HASH (wire_target_h_payto); +SELECT create_table_wire_targets(); + COMMENT ON TABLE wire_targets IS 'All senders and recipients of money via the exchange'; COMMENT ON COLUMN wire_targets.payto_uri @@ -95,39 +1960,12 @@ CREATE TABLE IF NOT EXISTS wire_targets_default PARTITION OF wire_targets FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wire_targets_' || partition_suffix || ' ' - 'ADD CONSTRAINT wire_targets_' || partition_suffix || '_wire_target_serial_id_key ' - 'UNIQUE (wire_target_serial_id)' - ); -END -$$; - SELECT add_constraints_to_wire_targets_partition('default'); -CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index - ON wire_targets - (wire_target_serial_id); - - -- ------------------------------ reserves ---------------------------------------- -CREATE TABLE IF NOT EXISTS reserves - (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY - ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32) - ,current_balance_val INT8 NOT NULL - ,current_balance_frac INT4 NOT NULL - ,expiration_date INT8 NOT NULL - ,gc_date INT8 NOT NULL - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_reserves(); + COMMENT ON TABLE reserves IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.'; COMMENT ON COLUMN reserves.reserve_pub @@ -138,40 +1976,15 @@ COMMENT ON COLUMN reserves.expiration_date IS 'Used to trigger closing of reserves that have not been drained after some time'; COMMENT ON COLUMN reserves.gc_date IS 'Used to forget all information about a reserve during garbage collection'; + CREATE TABLE IF NOT EXISTS reserves_default PARTITION OF reserves FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE INDEX IF NOT EXISTS reserves_by_expiration_index - ON reserves - (expiration_date - ,current_balance_val - ,current_balance_frac - ); -COMMENT ON INDEX reserves_by_expiration_index - IS 'used in get_expired_reserves'; -CREATE INDEX IF NOT EXISTS reserves_by_reserve_uuid_index - ON reserves - (reserve_uuid); -CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index - ON reserves - (gc_date); -COMMENT ON INDEX reserves_by_gc_date_index - IS 'for reserve garbage collection'; - -- ------------------------------ reserves_in ---------------------------------------- -CREATE TABLE IF NOT EXISTS reserves_in - (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ,wire_reference INT8 NOT NULL - ,credit_val INT8 NOT NULL - ,credit_frac INT4 NOT NULL - ,wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32) - ,exchange_account_section TEXT NOT NULL - ,execution_date INT8 NOT NULL - ) - PARTITION BY HASH (reserve_pub); +SELECT create_table_reserves_in(); + COMMENT ON TABLE reserves_in IS 'list of transfers of funds into the reserves, one per incoming wire transfer'; COMMENT ON COLUMN reserves_in.wire_source_h_payto @@ -185,101 +1998,28 @@ CREATE TABLE IF NOT EXISTS reserves_in_default PARTITION OF reserves_in FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_in_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' - 'UNIQUE (reserve_in_serial_id)' - ); -END -$$; - SELECT add_constraints_to_reserves_in_partition('default'); -CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index - ON reserves_in - (reserve_in_serial_id); --- FIXME: where do we need this index? Can we do better? -CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_section_execution_date_index - ON reserves_in - (exchange_account_section - ,execution_date - ); --- FIXME: where do we need this index? Can we do better? -CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_reserve_in_serial_id_index - ON reserves_in - (exchange_account_section - ,reserve_in_serial_id DESC - ); - - -- ------------------------------ reserves_close ---------------------------------------- -CREATE TABLE IF NOT EXISTS reserves_close - (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY - ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ,execution_date INT8 NOT NULL - ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32) - ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,closing_fee_val INT8 NOT NULL - ,closing_fee_frac INT4 NOT NULL) - PARTITION BY HASH (reserve_pub); +SELECT create_table_reserves_close(); + COMMENT ON TABLE reserves_close IS 'wire transfers executed by the reserve to close reserves'; COMMENT ON COLUMN reserves_close.wire_target_h_payto IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.'; -CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index - ON reserves_close - (close_uuid); -CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index - ON reserves_close - (reserve_pub); - CREATE TABLE IF NOT EXISTS reserves_close_default PARTITION OF reserves_close FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_close_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' - 'PRIMARY KEY (close_uuid)' - ); -END -$$; - SELECT add_constraints_to_reserves_close_partition('default'); -- ------------------------------ reserves_out ---------------------------------------- -CREATE TABLE IF NOT EXISTS reserves_out - (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE - ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) - ,denom_sig BYTEA NOT NULL - ,reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE - ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) - ,execution_date INT8 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ) - PARTITION BY HASH (h_blind_ev); +SELECT create_table_reserves_out(); + COMMENT ON TABLE reserves_out IS 'Withdraw operations performed on reserves.'; COMMENT ON COLUMN reserves_out.h_blind_ev @@ -287,50 +2027,18 @@ COMMENT ON COLUMN reserves_out.h_blind_ev COMMENT ON COLUMN reserves_out.denominations_serial IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive'; -CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index - ON reserves_out - (reserve_out_serial_id); --- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? -CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index - ON reserves_out - (reserve_uuid, execution_date); -COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index - IS 'for get_reserves_out and exchange_do_withdraw_limit_check'; - CREATE TABLE IF NOT EXISTS reserves_out_default PARTITION OF reserves_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE reserves_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' - 'UNIQUE (reserve_out_serial_id)' - ); -END -$$; - SELECT add_constraints_to_reserves_out_partition('default'); -CREATE TABLE IF NOT EXISTS reserves_out_by_reserve - (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE - ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) - ) - PARTITION BY HASH (reserve_uuid); +SELECT create_table_reserves_out_by_reserve(); + COMMENT ON TABLE reserves_out_by_reserve IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.'; -CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index - ON reserves_out_by_reserve - (reserve_uuid); - CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default PARTITION OF reserves_out_by_reserve FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -467,16 +2175,8 @@ COMMENT ON COLUMN extensions.config -- ------------------------------ known_coins ---------------------------------------- -CREATE TABLE IF NOT EXISTS known_coins - (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE - ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32) - ,age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32) - ,denom_sig BYTEA NOT NULL - ,remaining_val INT8 NOT NULL - ,remaining_frac INT4 NOT NULL - ) - PARTITION BY HASH (coin_pub); +SELECT create_table_known_coins(); + COMMENT ON TABLE known_coins IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations'; COMMENT ON COLUMN known_coins.denominations_serial @@ -494,36 +2194,13 @@ CREATE TABLE IF NOT EXISTS known_coins_default PARTITION OF known_coins FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE known_coins_' || partition_suffix || ' ' - 'ADD CONSTRAINT known_coins_' || partition_suffix || 'k_nown_coin_id_key ' - 'UNIQUE (known_coin_id)' - ); -END -$$; - SELECT add_constraints_to_known_coins_partition('default'); -- ------------------------------ refresh_commitments ---------------------------------------- -CREATE TABLE IF NOT EXISTS refresh_commitments - (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64) - ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64) - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,noreveal_index INT4 NOT NULL - ) - PARTITION BY HASH (rc); +SELECT create_table_refresh_commitments(); + COMMENT ON TABLE refresh_commitments IS 'Commitments made when melting coins and the gamma value chosen by the exchange.'; COMMENT ON COLUMN refresh_commitments.noreveal_index @@ -533,48 +2210,17 @@ COMMENT ON COLUMN refresh_commitments.rc COMMENT ON COLUMN refresh_commitments.old_coin_pub IS 'Coin being melted in the refresh process.'; --- Note: index spans partitions, may need to be materialized. -CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index - ON refresh_commitments - (old_coin_pub); - CREATE TABLE IF NOT EXISTS refresh_commitments_default PARTITION OF refresh_commitments FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' - 'UNIQUE (melt_serial_id)' - ); -END -$$; - SELECT add_constraints_to_refresh_commitments_partition('default'); -- ------------------------------ refresh_revealed_coins ---------------------------------------- -CREATE TABLE IF NOT EXISTS refresh_revealed_coins - (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,melt_serial_id INT8 NOT NULL -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE - ,freshcoin_index INT4 NOT NULL - ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64) - ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE - ,coin_ev BYTEA NOT NULL -- UNIQUE - ,h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64) -- UNIQUE - ,ev_sig BYTEA NOT NULL - ,ewv BYTEA NOT NULL - -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard - ) - PARTITION BY HASH (melt_serial_id); +SELECT create_table_refresh_revealed_coins(); + COMMENT ON TABLE refresh_revealed_coins IS 'Revelations about the new coins that are to be created during a melting session.'; COMMENT ON COLUMN refresh_revealed_coins.rrc_serial @@ -592,46 +2238,17 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev COMMENT ON COLUMN refresh_revealed_coins.ev_sig IS 'exchange signature over the envelope'; -CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index - ON refresh_revealed_coins - (melt_serial_id); - CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default PARTITION OF refresh_revealed_coins FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' - 'UNIQUE (rrc_serial) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' - 'UNIQUE (coin_ev) ' - ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' - 'UNIQUE (h_coin_ev) ' - ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' - ); -END -$$; - SELECT add_constraints_to_refresh_revealed_coins_partition('default'); -- ------------------------------ refresh_transfer_keys ---------------------------------------- -CREATE TABLE IF NOT EXISTS refresh_transfer_keys - (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,melt_serial_id INT8 PRIMARY KEY -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE - ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32) - ,transfer_privs BYTEA NOT NULL - ) - PARTITION BY HASH (melt_serial_id); +SELECT create_table_refresh_transfer_keys(); + COMMENT ON TABLE refresh_transfer_keys IS 'Transfer keys of a refresh operation (the data revealed to the exchange).'; COMMENT ON COLUMN refresh_transfer_keys.rtc_serial @@ -647,21 +2264,6 @@ CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default PARTITION OF refresh_transfer_keys FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' - 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' - 'UNIQUE (rtc_serial)' - ); -END -$$; - SELECT add_constraints_to_refresh_transfer_keys_partition('default'); @@ -683,28 +2285,8 @@ CREATE TABLE IF NOT EXISTS extension_details_default -- ------------------------------ deposits ---------------------------------------- -CREATE TABLE IF NOT EXISTS deposits - (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY - ,shard INT8 NOT NULL - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,wallet_timestamp INT8 NOT NULL - ,exchange_timestamp INT8 NOT NULL - ,refund_deadline INT8 NOT NULL - ,wire_deadline INT8 NOT NULL - ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) - ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) - ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) - ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) - ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) - ,done BOOLEAN NOT NULL DEFAULT FALSE - ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE - ,extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE - ,UNIQUE (coin_pub, merchant_pub, h_contract_terms) - ) - PARTITION BY HASH (coin_pub); +SELECT create_table_deposits(); + COMMENT ON TABLE deposits IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).'; COMMENT ON COLUMN deposits.shard @@ -722,65 +2304,28 @@ COMMENT ON COLUMN deposits.extension_blocked COMMENT ON COLUMN deposits.extension_details_serial_id IS 'References extensions table, NULL if extensions are not used'; -CREATE INDEX IF NOT EXISTS deposits_by_coin_pub_index - ON deposits - (coin_pub); - CREATE TABLE IF NOT EXISTS deposits_default PARTITION OF deposits FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' - 'PRIMARY KEY (deposit_serial_id)' - ); -END -$$; - SELECT add_constraints_to_deposits_partition('default'); -CREATE TABLE IF NOT EXISTS deposits_by_ready - (wire_deadline INT8 NOT NULL - ,shard INT8 NOT NULL - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,deposit_serial_id INT8 - ) - PARTITION BY RANGE (wire_deadline); +SELECT create_table_deposits_by_ready(); + COMMENT ON TABLE deposits_by_ready IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER below'; -CREATE INDEX IF NOT EXISTS deposits_by_ready_main_index - ON deposits_by_ready - (wire_deadline ASC, shard ASC, coin_pub); - CREATE TABLE IF NOT EXISTS deposits_by_ready_default PARTITION OF deposits_by_ready DEFAULT; -CREATE TABLE IF NOT EXISTS deposits_for_matching - (refund_deadline INT8 NOT NULL - ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,deposit_serial_id INT8 - ) - PARTITION BY RANGE (refund_deadline); +SELECT create_table_deposits_for_matching(); + COMMENT ON TABLE deposits_for_matching IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via TRIGGER below'; -CREATE INDEX IF NOT EXISTS deposits_for_matching_main_index - ON deposits_for_matching - (refund_deadline ASC, merchant_pub, coin_pub); - CREATE TABLE IF NOT EXISTS deposits_for_matching_default PARTITION OF deposits_for_matching DEFAULT; @@ -920,17 +2465,8 @@ CREATE TRIGGER deposits_on_delete -- ------------------------------ refunds ---------------------------------------- -CREATE TABLE IF NOT EXISTS refunds - (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE - ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE - ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64) - ,rtransaction_id INT8 NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! - ) - PARTITION BY HASH (coin_pub); +SELECT create_table_refunds(); + COMMENT ON TABLE refunds IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.'; COMMENT ON COLUMN refunds.deposit_serial_id @@ -938,46 +2474,17 @@ COMMENT ON COLUMN refunds.deposit_serial_id COMMENT ON COLUMN refunds.rtransaction_id IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund'; -CREATE INDEX IF NOT EXISTS refunds_by_coin_pub_index - ON refunds - (coin_pub); - CREATE TABLE IF NOT EXISTS refunds_default PARTITION OF refunds FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE refunds_' || partition_suffix || ' ' - 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' - 'UNIQUE (refund_serial_id) ' - ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' - ); -END -$$; - SELECT add_constraints_to_refunds_partition('default'); - -- ------------------------------ wire_out ---------------------------------------- -CREATE TABLE IF NOT EXISTS wire_out - (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY - ,execution_date INT8 NOT NULL - ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32) - ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) - ,exchange_account_section TEXT NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ) - PARTITION BY HASH (wtid_raw); +SELECT create_table_wire_out(); + COMMENT ON TABLE wire_out IS 'wire transfers the exchange has executed'; COMMENT ON COLUMN wire_out.exchange_account_section @@ -985,32 +2492,10 @@ COMMENT ON COLUMN wire_out.exchange_account_section COMMENT ON COLUMN wire_out.wire_target_h_payto IS 'Identifies the credited bank account and KYC status'; -CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index - ON wire_out - (wireout_uuid); -CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index - ON wire_out - (wire_target_h_payto); - CREATE TABLE IF NOT EXISTS wire_out_default PARTITION OF wire_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wire_out_' || partition_suffix || ' ' - 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' - 'PRIMARY KEY (wireout_uuid)' - ); -END -$$; - SELECT add_constraints_to_wire_out_partition('default'); CREATE OR REPLACE FUNCTION wire_out_delete_trigger() @@ -1034,14 +2519,8 @@ CREATE TRIGGER wire_out_on_delete -- ------------------------------ aggregation_transient ---------------------------------------- -CREATE TABLE IF NOT EXISTS aggregation_transient - (amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) - ,exchange_account_section TEXT NOT NULL - ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32) - ) - PARTITION BY HASH (wire_target_h_payto); +SELECT create_table_aggregation_transient(); + COMMENT ON TABLE aggregation_transient IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated'; COMMENT ON COLUMN aggregation_transient.amount_val @@ -1054,15 +2533,10 @@ CREATE TABLE IF NOT EXISTS aggregation_transient_default FOR VALUES WITH (MODULUS 1, REMAINDER 0); - -- ------------------------------ aggregation_tracking ---------------------------------------- -CREATE TABLE IF NOT EXISTS aggregation_tracking - (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE - ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32) - ) - PARTITION BY HASH (deposit_serial_id); +SELECT create_table_aggregation_tracking(); + COMMENT ON TABLE aggregation_tracking IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)'; COMMENT ON COLUMN aggregation_tracking.wtid_raw @@ -1072,29 +2546,8 @@ CREATE TABLE IF NOT EXISTS aggregation_tracking_default PARTITION OF aggregation_tracking FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' - 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' - 'UNIQUE (aggregation_serial_id);' - ); -END -$$; - SELECT add_constraints_to_aggregation_tracking_partition('default'); -CREATE INDEX IF NOT EXISTS aggregation_tracking_by_wtid_raw_index - ON aggregation_tracking - (wtid_raw); -COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index - IS 'for lookup_transactions'; - -- ------------------------------ wire_fee ---------------------------------------- @@ -1155,17 +2608,8 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index -- ------------------------------ recoup ---------------------------------------- -CREATE TABLE IF NOT EXISTS recoup - (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) - ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) - ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,recoup_timestamp INT8 NOT NULL - ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE - ) - PARTITION BY HASH (coin_pub); +SELECT create_table_recoup(); + COMMENT ON TABLE recoup IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.'; COMMENT ON COLUMN recoup.coin_pub @@ -1177,43 +2621,18 @@ COMMENT ON COLUMN recoup.coin_sig COMMENT ON COLUMN recoup.coin_blind IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.'; -CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index - ON recoup - (coin_pub); - CREATE TABLE IF NOT EXISTS recoup_default PARTITION OF recoup FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE recoup_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' - 'UNIQUE (recoup_uuid) ' - ); -END -$$; - SELECT add_constraints_to_recoup_partition('default'); -CREATE TABLE IF NOT EXISTS recoup_by_reserve - (reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE - ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) - ) - PARTITION BY HASH (reserve_out_serial_id); + +SELECT create_table_recoup_by_reserve(); + COMMENT ON TABLE recoup_by_reserve IS 'Information in this table is strictly redundant with that of recoup, but saved by a different primary key for fast lookups by reserve_out_serial_id.'; -CREATE INDEX IF NOT EXISTS recoup_by_reserve_main_index - ON recoup_by_reserve - (reserve_out_serial_id); - CREATE TABLE IF NOT EXISTS recoup_by_reserve_default PARTITION OF recoup_by_reserve FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -1260,18 +2679,8 @@ CREATE TRIGGER recoup_on_delete -- ------------------------------ recoup_refresh ---------------------------------------- -CREATE TABLE IF NOT EXISTS recoup_refresh - (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) - ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) - ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) - ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,recoup_timestamp INT8 NOT NULL - ,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE - ) - PARTITION BY HASH (coin_pub); +SELECT create_table_recoup_refresh(); + COMMENT ON TABLE recoup_refresh IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.'; COMMENT ON COLUMN recoup_refresh.coin_pub @@ -1283,47 +2692,17 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial COMMENT ON COLUMN recoup_refresh.coin_blind IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.'; -CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index - ON recoup_refresh - (coin_pub); --- FIXME: any query using this index will be slow. Materialize index or change query? --- Also: which query uses this index? -CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index - ON recoup_refresh - (rrc_serial); - CREATE TABLE IF NOT EXISTS recoup_refresh_default PARTITION OF recoup_refresh FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' - 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' - 'UNIQUE (recoup_refresh_uuid) ' - ); -END -$$; - SELECT add_constraints_to_recoup_refresh_partition('default'); -- ------------------------------ prewire ---------------------------------------- -CREATE TABLE IF NOT EXISTS prewire - (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY - ,wire_method TEXT NOT NULL - ,finished BOOLEAN NOT NULL DEFAULT false - ,failed BOOLEAN NOT NULL DEFAULT false - ,buf BYTEA NOT NULL - ) - PARTITION BY HASH (prewire_uuid); +SELECT create_table_prewire(); + COMMENT ON TABLE prewire IS 'pre-commit data for wire transfers we are about to execute'; COMMENT ON COLUMN prewire.failed @@ -1333,18 +2712,6 @@ COMMENT ON COLUMN prewire.finished COMMENT ON COLUMN prewire.buf IS 'serialized data to send to the bank to execute the wire transfer'; -CREATE INDEX IF NOT EXISTS prewire_by_finished_index - ON prewire - (finished); -COMMENT ON INDEX prewire_by_finished_index - IS 'for gc_prewire'; --- FIXME: find a way to combine these two indices? -CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index - ON prewire - (failed,finished); -COMMENT ON INDEX prewire_by_failed_finished_index - IS 'for wire_prepare_data_get'; - CREATE TABLE IF NOT EXISTS prewire_default PARTITION OF prewire FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -1374,13 +2741,8 @@ COMMENT ON COLUMN wire_accounts.last_change -- ------------------------------ cs_nonce_locks ---------------------------------------- -CREATE TABLE IF NOT EXISTS cs_nonce_locks - (cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32) - ,op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64) - ,max_denomination_serial INT8 NOT NULL - ) - PARTITION BY HASH (nonce); +SELECT create_table_cs_nonce_locks(); + COMMENT ON TABLE cs_nonce_locks IS 'ensures a Clause Schnorr client nonce is locked for use with an operation identified by a hash'; COMMENT ON COLUMN cs_nonce_locks.nonce @@ -1394,21 +2756,6 @@ CREATE TABLE IF NOT EXISTS cs_nonce_locks_default PARTITION OF cs_nonce_locks FOR VALUES WITH (MODULUS 1, REMAINDER 0); -CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( - IN partition_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' - 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' - 'UNIQUE (cs_nonce_lock_serial_id)' - ); -END -$$; - SELECT add_constraints_to_cs_nonce_locks_partition('default'); diff --git a/src/exchangedb/partition-0001.sql b/src/exchangedb/partition-0001.sql deleted file mode 100644 index ba3267988..000000000 --- a/src/exchangedb/partition-0001.sql +++ /dev/null @@ -1,312 +0,0 @@ --- --- 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; - --- Check patch versioning is in place. --- SELECT _v.register_patch('partition-0001', NULL, NULL); - -CREATE OR REPLACE FUNCTION create_table_partition( - 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 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 reserves_out - DETACH PARTITION reserves_out_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; - - 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_tracking - DETACH PARTITION aggregation_tracking_default; - - ALTER TABLE IF EXISTS recoup - DETACH PARTITION recoup_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; - -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 shardig 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_out_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 refunds_default; - DROP TABLE IF EXISTS wire_out_default; - DROP TABLE IF EXISTS aggregation_tracking_default; - DROP TABLE IF EXISTS recoup_default; - DROP TABLE IF EXISTS recoup_refresh_default; - DROP TABLE IF EXISTS prewire_default; - DROP TABLE IF EXISTS cs_nonce_locks_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_table_partition( - 'wire_targets' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'reserves' - ,modulus - ,num_partitions - ); - - PERFORM create_table_partition( - 'reserves_in' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'reserves_close' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'reserves_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'known_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'refresh_commitments' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'refresh_revealed_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'refresh_transfer_keys' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); - - PERFORM create_table_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. - - - PERFORM create_table_partition( - 'refunds' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'wire_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'aggregation_tracking' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'recoup' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'recoup_refresh' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); - - PERFORM create_table_partition( - 'prewire' - ,modulus - ,num_partitions - ); - - PERFORM create_table_partition( - 'cs_nonce_locks' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); - - num_partitions=num_partitions-1; - EXIT WHEN num_partitions=0; - - END LOOP; - - PERFORM drop_default_partitions(); - -END -$$; - -COMMIT; diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index a157c64f2..3097878ef 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -227,7 +227,7 @@ postgres_setup_partitions (void *cls, conn = GNUNET_PQ_connect_with_cfg (pg->cfg, "exchangedb-postgres", - "partition-", + "exchage-", NULL, ps); if (NULL == conn) @@ -243,6 +243,59 @@ postgres_setup_partitions (void *cls, /** + * Setup shards for already existing tables + * + * @param cls the `struct PostgresClosure` with the plugin-specific state + * @param num the number of shard servers to create for each partitioned table + * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure + */ +static enum GNUNET_GenericReturnValue +postgres_setup_shards (void *cls, + const uint32_t num) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_Context *conn; + enum GNUNET_GenericReturnValue ret; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_uint32 (&num), + GNUNET_PQ_query_param_end + }; + struct GNUNET_PQ_PreparedStatement ps[] = { + GNUNET_PQ_make_prepare ("setup_shards", + "SELECT" + " master_prepare_sharding" + " ();", + 0), + GNUNET_PQ_make_prepare ("create_shards", + "SELECT" + " create_shards" + " ($1);", + 1), + GNUNET_PQ_PREPARED_STATEMENT_END + }; + + conn = GNUNET_PQ_connect_with_cfg (pg->cfg, + "exchangedb-postgres", + "exchage-", + NULL, + ps); + if (NULL == conn) + return GNUNET_SYSERR; + ret = GNUNET_OK; + if (0 > GNUNET_PQ_eval_prepared_non_select (conn, + "setup_shards", + NULL)) + ret = GNUNET_SYSERR; + if (0 > GNUNET_PQ_eval_prepared_non_select (conn, + "create_shards", + params)) + ret = GNUNET_SYSERR; + GNUNET_PQ_disconnect (conn); + return ret; +} + + +/** * Initialize prepared statements for @a pg. * * @param[in,out] pg connection to initialize @@ -13062,6 +13115,7 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) plugin->drop_tables = &postgres_drop_tables; plugin->create_tables = &postgres_create_tables; plugin->setup_partitions = &postgres_setup_partitions; + plugin->setup_shards = &postgres_setup_shards; plugin->start = &postgres_start; plugin->start_read_committed = &postgres_start_read_committed; plugin->commit = &postgres_commit; diff --git a/src/exchangedb/shard-0001.sql b/src/exchangedb/shard-0001.sql new file mode 100644 index 000000000..6a91cfbaa --- /dev/null +++ b/src/exchangedb/shard-0001.sql @@ -0,0 +1,1876 @@ +-- +-- 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; + +-- Check patch versioning is in place. +SELECT _v.register_patch('exchange-0001', NULL, NULL); + +-------------------- Tables ---------------------------- + +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 +$$; + +----------------------- wire_targets --------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_targets( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' + ',payto_uri VARCHAR NOT NULL' + ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' + ',external_id VARCHAR' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +-- We need a seperate function for this, as we call create_table only once but need to add +-- those constraints to each partition which gets created +CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + + EXECUTE FORMAT ( + 'ALTER TABLE wire_targets_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_targets_' || partition_suffix || '_wire_target_serial_id_key ' + 'UNIQUE (wire_target_serial_id)' + ); +END +$$; + +------------------------ reserves ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)' + ',current_balance_val INT8 NOT NULL' + ',current_balance_frac INT4 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',gc_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index ' + 'ON ' || table_name || ' ' + '(expiration_date' + ',current_balance_val' + ',current_balance_frac' + ');' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_expiration_index ' + 'IS ' || quote_literal('used in get_expired_reserves') || ';' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index ' + 'ON ' || table_name || ' ' + '(gc_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index ' + 'IS ' || quote_literal('for reserve garbage collection') || ';' + ); + +END +$$; + +----------------------- reserves_in ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_reserves_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',wire_reference INT8 NOT NULL' + ',credit_val INT8 NOT NULL' + ',credit_frac INT4 NOT NULL' + ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',execution_date INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_in_serial_id);' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section ' + ',execution_date' + ');' + ); + -- FIXME: where do we need this index? Can we do better? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx ' + 'ON ' || table_name || ' ' + '(exchange_account_section,' + 'reserve_in_serial_id DESC' + ');' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key ' + 'UNIQUE (reserve_in_serial_id)' + ); +END +$$; + +--------------------------- reserves_close ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_close( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_close'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',execution_date INT8 NOT NULL' + ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',closing_fee_val INT8 NOT NULL' + ',closing_fee_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index ' + 'ON ' || table_name || ' ' + '(close_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_close_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey ' + 'PRIMARY KEY (close_uuid)' + ); +END +$$; + +---------------------------- reserves_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_reserves_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denom_sig BYTEA NOT NULL' + ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',execution_date INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ') %s ;' + ,'reserves_out' + ,'PARTITION BY HASH (h_blind_ev)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well??? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid, execution_date);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index ' + 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key ' + 'UNIQUE (reserve_out_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'reserves_out_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE + ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)' + ') %s ' + ,table_name + ,'PARTITION BY HASH (reserve_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_uuid);' + ); + +END +$$; + +---------------------------- known_coins ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_known_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'known_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' + ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' + ',denom_sig BYTEA NOT NULL' + ',remaining_val INT8 NOT NULL' + ',remaining_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE known_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key ' + 'UNIQUE (known_coin_id)' + ); +END +$$; + +---------------------------- refresh_commitments ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_commitments( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_commitments'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' + ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',noreveal_index INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (rc)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- Note: index spans partitions, may need to be materialized. + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index ' + 'ON ' || table_name || ' ' + '(old_coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_commitments_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key ' + 'UNIQUE (melt_serial_id)' + ); +END +$$; + +------------------------------ refresh_revealed_coins -------------------------------- + +CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_revealed_coins'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',freshcoin_index INT4 NOT NULL' + ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',coin_ev BYTEA NOT NULL' -- UNIQUE' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' + ',ev_sig BYTEA NOT NULL' + ',ewv BYTEA NOT NULL' + -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index ' + 'ON ' || table_name || ' ' + '(melt_serial_id);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key ' + 'UNIQUE (rrc_serial) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key ' + 'UNIQUE (coin_ev) ' + ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key ' + 'UNIQUE (h_coin_ev) ' + ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) ' + ); +END +$$; + +----------------------------- refresh_transfer_keys ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refresh_transfer_keys'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' + ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' + ',transfer_privs BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (melt_serial_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' ' + 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key ' + 'UNIQUE (rtc_serial)' + ); +END +$$; + +---------------------------- deposits ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed??? + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wallet_timestamp INT8 NOT NULL' + ',exchange_timestamp INT8 NOT NULL' + ',refund_deadline INT8 NOT NULL' + ',wire_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',done BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',extension_details_serial_id INT8' -- REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE' + ',UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_by_ready'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wire_deadline INT8 NOT NULL' + ',shard INT8 NOT NULL' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (wire_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(wire_deadline ASC, shard ASC, coin_pub);' + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_table_deposits_for_matching( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'deposits_for_matching'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_deadline INT8 NOT NULL' + ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8' + ') %s ;' + ,table_name + ,'PARTITION BY RANGE (refund_deadline)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(refund_deadline ASC, merchant_pub, coin_pub);' + ); + +END +$$; + +----------------------------- refunds ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_refunds( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'refunds'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' + ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)' + ',rtransaction_id INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard! + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE refunds_' || partition_suffix || ' ' + 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key ' + 'UNIQUE (refund_serial_id) ' + ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) ' + ); +END +$$; + +---------------------------- wire_out ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wire_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY' + ',execution_date INT8 NOT NULL' + ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wtid_raw)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index ' + 'ON ' || table_name || ' ' + '(wire_target_h_payto);' + ); + + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wire_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey ' + 'PRIMARY KEY (wireout_uuid)' + ); +END +$$; + +---------------------------- aggregation_transient ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_aggregation_transient( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_transient'; +BEGIN + + EXECUTE FORMAT ( + 'CREATE TABLE IF NOT EXISTS %I ' + '(amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' + ',exchange_account_section TEXT NOT NULL' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +---------------------------- aggregation_tracking ------------------------------- + +CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME chnage to coint_pub + deposit_serial_id for more efficient depost -- or something else ??? + ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (deposit_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'ON ' || table_name || ' ' + '(wtid_raw);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' + 'IS ' || quote_literal('for lookup_transactions') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' + 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' + 'UNIQUE (aggregation_serial_id) ' + ); +END +$$; + +----------------------------- recoup ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub);' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key ' + 'UNIQUE (recoup_uuid) ' + ); +END +$$; + +CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_by_reserve'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_out_serial_id)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index ' + 'ON ' || table_name || ' ' + '(reserve_out_serial_id);' + ); + +END +$$; + +---------------------------- recoup_refresh ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_recoup_refresh( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'recoup_refresh'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) + ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',recoup_timestamp INT8 NOT NULL' + ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (coin_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: any query using this index will be slow. Materialize index or change query? + -- Also: which query uses this index? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index ' + 'ON ' || table_name || ' ' + '(rrc_serial);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE recoup_refresh_' || partition_suffix || ' ' + 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key ' + 'UNIQUE (recoup_refresh_uuid) ' + ); +END +$$; + +----------------------------- prewire ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_prewire( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'prewire'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY' + ',wire_method TEXT NOT NULL' + ',finished BOOLEAN NOT NULL DEFAULT false' + ',failed BOOLEAN NOT NULL DEFAULT false' + ',buf BYTEA NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (prewire_uuid)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index ' + 'ON ' || table_name || ' ' + '(finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_finished_index ' + 'IS ' || quote_literal('for gc_prewire') || ';' + ); + -- FIXME: find a way to combine these two indices? + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index ' + 'ON ' || table_name || ' ' + '(failed,finished);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index ' + 'IS ' || quote_literal('for wire_prepare_data_get') || ';' + ); + +END +$$; + +----------------------------- cs_nonce_locks ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks( + shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' + ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' + ',max_denomination_serial INT8 NOT NULL' + ') %s ;' + ,'cs_nonce_locks' + ,'PARTITION BY HASH (nonce)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' ' + 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key ' + 'UNIQUE (cs_nonce_lock_serial_id)' + ); +END +$$; + +------------------------- Partitions ------------------------------ + +CREATE OR REPLACE FUNCTION create_table_partition( + 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 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 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; + +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 shardig 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_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; + +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_table_partition( + 'wire_targets' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'reserves_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_close' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'reserves_out_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'known_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_commitments' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_revealed_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'refresh_transfer_keys' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); + + PERFORM create_table_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_table_partition( +-- 'deposits_by_ready' +-- ,modulus +-- ,num_partitions +-- ); +-- +-- PERFORM create_table_partition( +-- 'deposits_for_matching' +-- ,modulus +-- ,num_partitions +-- ); + + PERFORM create_table_partition( + 'refunds' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'wire_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'aggregation_transient' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'aggregation_tracking' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'recoup_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'recoup_refresh' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); + + PERFORM create_table_partition( + 'prewire' + ,modulus + ,num_partitions + ); + + PERFORM create_table_partition( + 'cs_nonce_locks' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + + num_partitions=num_partitions-1; + EXIT WHEN num_partitions=0; + + END LOOP; + + PERFORM drop_default_partitions(); + +END +$$; + +--------------------- Sharding --------------------------- + +---------------------- Shards ---------------------------- +CREATE OR REPLACE FUNCTION setup_shard( + shard_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_table_wire_targets(shard_suffix); + PERFORM add_constraints_to_wire_targets_partition(shard_suffix); + + PERFORM create_table_reserves(shard_suffix); + + PERFORM create_table_reserves_in(shard_suffix); + PERFORM add_constraints_to_reserves_in_partition(shard_suffix); + + PERFORM create_table_reserves_close(shard_suffix); + + PERFORM create_table_reserves_out(shard_suffix); + + PERFORM create_table_reserves_out_by_reserve(shard_suffix); + + PERFORM create_table_known_coins(shard_suffix); + PERFORM add_constraints_to_known_coins_partition(shard_suffix); + + PERFORM create_table_refresh_commitments(shard_suffix); + PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); + + PERFORM create_table_refresh_revealed_coins(shard_suffix); + PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); + + PERFORM create_table_refresh_transfer_keys(shard_suffix); + PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); + + PERFORM create_table_deposits(shard_suffix); + PERFORM add_constraints_to_deposits_partition(shard_suffix); + + PERFORM create_table_deposits_by_ready(shard_suffix); + + PERFORM create_table_deposits_for_matching(shard_suffix); + + PERFORM create_table_refunds(shard_suffix); + PERFORM add_constraints_to_refunds_partition(shard_suffix); + + PERFORM create_table_wire_out(shard_suffix); + PERFORM add_constraints_to_wire_out_partition(shard_suffix); + + PERFORM create_table_aggregation_transient(shard_suffix); + + PERFORM create_table_aggregation_tracking(shard_suffix); + PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); + + PERFORM create_table_recoup(shard_suffix); + PERFORM add_constraints_to_recoup_partition(shard_suffix); + + PERFORM create_table_recoup_by_reserve(shard_suffix); + + PERFORM create_table_recoup_refresh(shard_suffix); + PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); + + PERFORM create_table_prewire(shard_suffix); + + PERFORM create_table_cs_nonce_locks(shard_suffix); + PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); + +END +$$; + +------------------------------ Master ---------------------------------- +CREATE OR REPLACE FUNCTION create_foreign_table( + source_table_name VARCHAR + ,modulus INTEGER + ,shard_suffix VARCHAR + ,current_shard_num INTEGER + ) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, shard_suffix; + + EXECUTE FORMAT( + 'CREATE FOREIGN TABLE IF NOT EXISTS %I ' + 'PARTITION OF %I ' + 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) ' + 'SERVER %I' + ,source_table_name || '_' || shard_suffix + ,source_table_name + ,modulus + ,current_shard_num-1 + ,shard_suffix + ); + + EXECUTE FORMAT( + 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"', + source_table_name || '_' || shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION master_prepare_sharding() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + CREATE EXTENSION IF NOT EXISTS postgres_fdw; + + PERFORM detach_default_partitions(); + + ALTER TABLE IF EXISTS wire_targets + DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves + DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_in + DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_close + DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE + ; + + ALTER TABLE IF EXISTS reserves_out + DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey + ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key + ; + + ALTER TABLE IF EXISTS known_coins + DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_commitments + DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey + ; + + ALTER TABLE IF EXISTS refresh_revealed_coins + DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey + ; + + ALTER TABLE IF EXISTS refresh_transfer_keys + DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE + ; + + ALTER TABLE IF EXISTS deposits + DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS deposits_extension_details_serial_id_fkey + ,DROP CONSTRAINT IF EXISTS deposits_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE + ; + + ALTER TABLE IF EXISTS refunds + DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE + ; + + ALTER TABLE IF EXISTS wire_out + DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE + ; + + ALTER TABLE IF EXISTS aggregation_tracking + DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE + ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey + ; + + ALTER TABLE IF EXISTS recoup + DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE + ; + + ALTER TABLE IF EXISTS recoup_refresh + DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE + ; + + ALTER TABLE IF EXISTS prewire + DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE + ; + + ALTER TABLE IF EXISTS cs_nonce_locks + DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE + ; + +END +$$; + + +CREATE OR REPLACE FUNCTION create_shard_server( + shard_suffix VARCHAR + ,total_num_shards INTEGER + ,current_shard_num INTEGER + ,remote_host VARCHAR + ,remote_user VARCHAR + ,remote_user_password VARCHAR + ,remote_db_name VARCHAR DEFAULT 'taler-exchange' + ,remote_port INTEGER DEFAULT '5432' + ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating server %s', remote_host; + + EXECUTE FORMAT( + 'CREATE SERVER IF NOT EXISTS %I ' + 'FOREIGN DATA WRAPPER postgres_fdw ' + 'OPTIONS (dbname %L, host %L, port %L)' + ,shard_suffix + ,remote_db_name + ,remote_host + ,remote_port + ); + + EXECUTE FORMAT( + 'CREATE USER MAPPING IF NOT EXISTS ' + 'FOR %s SERVER %I ' + 'OPTIONS (user %L, password %L)' + ,local_user + ,shard_suffix + ,remote_user + ,remote_user_password + ); + + PERFORM create_foreign_table( + 'wire_targets' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_in' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_close' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'known_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_commitments' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_revealed_coins' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'refresh_transfer_keys' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'deposits' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); +-- PERFORM create_foreign_table( +-- 'deposits_by_ready' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); +-- PERFORM create_foreign_table( +-- 'deposits_for_matching' +-- ,total_num_shards +-- ,shard_suffix +-- ,current_shard_num +-- ); + PERFORM create_foreign_table( + 'refunds' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'wire_out' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'aggregation_tracking' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'reserves_out_by_reserve' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'recoup_refresh' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'prewire' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + PERFORM create_foreign_table( + 'cs_nonce_locks' + ,total_num_shards + ,shard_suffix + ,current_shard_num + ); + +END +$$; + +COMMENT ON FUNCTION create_shard_server + IS 'Create a shard server on the master + node with all foreign tables and user mappings'; + +CREATE OR REPLACE FUNCTION create_shards( + num_shards INTEGER + ,domain VARCHAR DEFAULT 'perf.taler' +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + FOR i IN 1..num_shards LOOP + PERFORM create_shard_server( + i + ,num_shards + ,i + ,'shard-' || i::varchar || '.' || domain + ,'taler' + ,'taler' + ,'taler-exchange' + ,'5432' + ,'taler-exchange-httpd' + ); + END LOOP; +END +$$; + +COMMIT;
\ No newline at end of file diff --git a/src/include/taler_exchangedb_plugin.h b/src/include/taler_exchangedb_plugin.h index 260fab3ca..b3e51c107 100644 --- a/src/include/taler_exchangedb_plugin.h +++ b/src/include/taler_exchangedb_plugin.h @@ -2242,6 +2242,21 @@ struct TALER_EXCHANGEDB_Plugin const uint32_t num); /** + * Change already present tables of the database to num shards + * Only has an effect if there are default partitions only + * + * @param cls the @e cls of this struct with the plugin-specific state + * @param num the number of shard servers to create. The shard servers + * must follow the numbering of 1-N, have the same user as + * the master and have tables named <table>_n where n is the same + * as the servers index of N. + * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure + */ + enum GNUNET_GenericReturnValue + (*setup_shards)(void *cls, + const uint32_t num); + + /** * Start a transaction. * * @param cls the @e cls of this struct with the plugin-specific state |