diff options
author | Marco Boss <bossm8@bfh.ch> | 2022-03-31 21:49:35 +0200 |
---|---|---|
committer | Marco Boss <bossm8@bfh.ch> | 2022-03-31 21:49:35 +0200 |
commit | 45916b992b2a8e680aa1e53ec6fab4846a17e650 (patch) | |
tree | 7acadae3d96174ab8598ffc230cb8bd8255958a6 /src/exchangedb | |
parent | 75c1e10a67d94f43d0c06433efc2b78c10bae5c5 (diff) | |
download | exchange-45916b992b2a8e680aa1e53ec6fab4846a17e650.tar.xz |
remove built sql files from vc
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/.gitignore | 2 | ||||
-rw-r--r-- | src/exchangedb/Makefile.am | 4 | ||||
-rw-r--r-- | src/exchangedb/exchange-0001.sql | 4706 | ||||
-rw-r--r-- | src/exchangedb/shard-0001.sql | 1881 |
4 files changed, 6 insertions, 6587 deletions
diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index aea9a74e4..b5d4ff3f7 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -5,3 +5,5 @@ test-exchangedb-postgres test-exchangedb-signkeys test-perf-taler-exchangedb bench-db-postgres +exchange-0001.sql +shard-0001.sql diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 3df0653ad..305de431c 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -26,6 +26,10 @@ BUILT_SOURCES = \ shard-0001.sql \ exchange-0001.sql +CLEANFILES = \ + shard-0001.sql \ + exchange-0001.sql + exchange-0001.sql: common-0001.sql exchange-0001-part.sql cat common-0001.sql exchange-0001-part.sql >$@ diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql deleted file mode 100644 index 1bf2d3079..000000000 --- a/src/exchangedb/exchange-0001.sql +++ /dev/null @@ -1,4706 +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('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_foreign_servers( - amount INTEGER - ,domain VARCHAR DEFAULT 'perf.taler' -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM master_prepare_sharding(); - - FOR i IN 1..amount LOOP - PERFORM create_shard_server( - i - ,amount - ,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 ---------------------------------------- - -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/shard-0001.sql b/src/exchangedb/shard-0001.sql deleted file mode 100644 index 873141b82..000000000 --- a/src/exchangedb/shard-0001.sql +++ /dev/null @@ -1,1881 +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('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_foreign_servers( - amount INTEGER - ,domain VARCHAR DEFAULT 'perf.taler' -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM master_prepare_sharding(); - - FOR i IN 1..amount LOOP - PERFORM create_shard_server( - i - ,amount - ,i - ,'shard-' || i::varchar || '.' || domain - ,'taler' - ,'taler' - ,'taler-exchange' - ,'5432' - ,'taler-exchange-httpd' - ); - END LOOP; -END -$$; - -COMMIT;
\ No newline at end of file |