diff options
50 files changed, 3994 insertions, 1725 deletions
diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index 540724b8..5afbccdf 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -5,10 +5,6 @@ test-exchangedb-postgres test-exchangedb-signkeys test-perf-taler-exchangedb bench-db-postgres -exchange-0001.sql -shard-0000.sql -shard-0001.sql -drop0001.sql shard-drop0001.sqltest-exchangedb-by-j-postgres test-exchangedb-by-j-postgres perf-exchangedb-reserves-in-insert-postgres diff --git a/src/exchangedb/0002-account_merges.sql b/src/exchangedb/0002-account_merges.sql new file mode 100644 index 00000000..88cbea13 --- /dev/null +++ b/src/exchangedb/0002-account_merges.sql @@ -0,0 +1,66 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_account_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'account_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' -- REFERENCES purse_requests (purse_pub) + ',wallet_h_payto BYTEA NOT NULL CHECK (LENGTH(wallet_h_payto)=32)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE account_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key ' + 'UNIQUE (account_merge_request_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-aggregation_tracking.sql b/src/exchangedb/0002-aggregation_tracking.sql new file mode 100644 index 00000000..7e6977f9 --- /dev/null +++ b/src/exchangedb/0002-aggregation_tracking.sql @@ -0,0 +1,66 @@ +-- +-- 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/> +-- + +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 change to coin_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 +$$; diff --git a/src/exchangedb/0002-aggregation_transient.sql b/src/exchangedb/0002-aggregation_transient.sql new file mode 100644 index 00000000..c2731049 --- /dev/null +++ b/src/exchangedb/0002-aggregation_transient.sql @@ -0,0 +1,43 @@ +-- +-- 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/> +-- + +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 + + PERFORM create_partitioned_table( + '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)' + ',merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)' + ',exchange_account_section TEXT NOT NULL' + ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' + ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; diff --git a/src/exchangedb/0002-close_requests.sql b/src/exchangedb/0002-close_requests.sql new file mode 100644 index 00000000..8168b799 --- /dev/null +++ b/src/exchangedb/0002-close_requests.sql @@ -0,0 +1,85 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_close_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(close_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 + ',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' + ',close_fee_val INT8 NOT NULL' + ',close_fee_frac INT4 NOT NULL' + ',payto_uri VARCHAR NOT NULL' + ',done BOOL NOT NULL DEFAULT(FALSE)' + ',PRIMARY KEY (reserve_pub,close_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); +END +$$; + + +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'close_requests'; +BEGIN + + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' + 'ON ' || table_name || ' ' + '(close_request_serial_id);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_done_index ' + 'ON ' || table_name || ' ' + '(done);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE close_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT close_requests_' || partition_suffix || '_close_request_uuid_pkey ' + 'UNIQUE (close_request_serial_id)' + ); +END +$$; diff --git a/src/exchangedb/0002-contracts.sql b/src/exchangedb/0002-contracts.sql new file mode 100644 index 00000000..d51ced57 --- /dev/null +++ b/src/exchangedb/0002-contracts.sql @@ -0,0 +1,59 @@ +-- +-- 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/> +-- + + +CREATE OR REPLACE FUNCTION create_table_contracts( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'contracts'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' + ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' + ',e_contract BYTEA NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE contracts_' || partition_suffix || ' ' + 'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key ' + 'UNIQUE (contract_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-cs_nonce_locks.sql b/src/exchangedb/0002-cs_nonce_locks.sql new file mode 100644 index 00000000..e651fe15 --- /dev/null +++ b/src/exchangedb/0002-cs_nonce_locks.sql @@ -0,0 +1,53 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/exchange-0002-part.sql b/src/exchangedb/0002-denomination_revocations.sql index 2381d8b2..57668b35 100644 --- a/src/exchangedb/exchange-0002-part.sql +++ b/src/exchangedb/0002-denomination_revocations.sql @@ -14,23 +14,24 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- ------------------------------ 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'; - -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'); - - - -SELECT create_tables('exchange-0002'); +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'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denomination_revocations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-denominations.sql b/src/exchangedb/0002-denominations.sql new file mode 100644 index 00000000..a18a752b --- /dev/null +++ b/src/exchangedb/0002-denominations.sql @@ -0,0 +1,72 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_denominations() +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + +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); + +END +$$; + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denominations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-deposits.sql b/src/exchangedb/0002-deposits.sql new file mode 100644 index 00000000..404deeba --- /dev/null +++ b/src/exchangedb/0002-deposits.sql @@ -0,0 +1,146 @@ +-- +-- 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/> +-- + +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' + ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' + ',policy_details_serial_id INT8' -- REFERENCES policy_details (policy_details_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_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) ' + ',ADD CONSTRAINT deposits_' || partition_suffix || '_coin_pub_merchant_pub_h_contract_terms_key ' + 'UNIQUE (coin_pub, merchant_pub, h_contract_terms)' + ); +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 +$$; diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql new file mode 100644 index 00000000..0b579759 --- /dev/null +++ b/src/exchangedb/0002-history_requests.sql @@ -0,0 +1,44 @@ +-- +-- 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/> +-- + + +CREATE OR REPLACE FUNCTION create_table_history_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'history_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(history_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 + ',request_timestamp INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',history_fee_val INT8 NOT NULL' + ',history_fee_frac INT4 NOT NULL' + ',PRIMARY KEY (reserve_pub,request_timestamp)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (reserve_pub)' + ,shard_suffix + ); + +END +$$; diff --git a/src/exchangedb/0002-known_coins.sql b/src/exchangedb/0002-known_coins.sql new file mode 100644 index 00000000..786d2a6c --- /dev/null +++ b/src/exchangedb/0002-known_coins.sql @@ -0,0 +1,61 @@ +-- +-- 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/> +-- + + +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 DEFAULT(0)' + ',remaining_frac INT4 NOT NULL DEFAULT(0)' + ') %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 +$$; diff --git a/src/exchangedb/0002-kyc_alerts.sql b/src/exchangedb/0002-kyc_alerts.sql new file mode 100644 index 00000000..74872a9c --- /dev/null +++ b/src/exchangedb/0002-kyc_alerts.sql @@ -0,0 +1,41 @@ +-- +-- 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/> +-- + +CREATE TABLE IF NOT EXISTS kyc_alerts + (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) + ,trigger_type INT4 NOT NULL + ,UNIQUE(trigger_type,h_payto) + ); +COMMENT ON TABLE kyc_alerts + IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; +COMMENT ON COLUMN kyc_alerts.h_payto + IS 'hash of the payto://-URI for which the KYC status changed'; +COMMENT ON COLUMN kyc_alerts.trigger_type + IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_alerts' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-legitimization_processes.sql b/src/exchangedb/0002-legitimization_processes.sql new file mode 100644 index 00000000..598603c7 --- /dev/null +++ b/src/exchangedb/0002-legitimization_processes.sql @@ -0,0 +1,72 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_legitimization_processes( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',expiration_time INT8 NOT NULL DEFAULT (0)' + ',provider_section VARCHAR NOT NULL' + ',provider_user_id VARCHAR DEFAULT NULL' + ',provider_legitimization_id VARCHAR DEFAULT NULL' + ',UNIQUE (h_payto, provider_section)' + ') %s ;' + ,'legitimization_processes' + ,'PARTITION BY HASH (h_payto)' + ,shard_suffix + ); + +END +$$; + +-- We need a separate 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_legitimization_processes_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + + partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_key ' + 'UNIQUE (legitimization_process_serial_id)'); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index ' + 'ON '|| partition_name || ' ' + '(provider_section,provider_legitimization_id)' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index ' + 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';' + ); +END +$$; diff --git a/src/exchangedb/0002-legitimization_requirements.sql b/src/exchangedb/0002-legitimization_requirements.sql new file mode 100644 index 00000000..34655f65 --- /dev/null +++ b/src/exchangedb/0002-legitimization_requirements.sql @@ -0,0 +1,60 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_legitimization_requirements( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',required_checks VARCHAR NOT NULL' + ',UNIQUE (h_payto, required_checks)' + ') %s ;' + ,'legitimization_requirements' + ,'PARTITION BY HASH (h_payto)' + ,shard_suffix + ); + +END +$$; + +-- We need a separate 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_legitimization_requirements_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + + partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name + || ' ' + 'ADD CONSTRAINT ' || partition_name || '_serial_id_key ' + 'UNIQUE (legitimization_requirement_serial_id)'); +END +$$; diff --git a/src/exchangedb/0002-prewire.sql b/src/exchangedb/0002-prewire.sql new file mode 100644 index 00000000..451545e6 --- /dev/null +++ b/src/exchangedb/0002-prewire.sql @@ -0,0 +1,63 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-profit_drains.sql b/src/exchangedb/0002-profit_drains.sql new file mode 100644 index 00000000..bb713cd2 --- /dev/null +++ b/src/exchangedb/0002-profit_drains.sql @@ -0,0 +1,57 @@ +-- +-- 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/> +-- + +CREATE TABLE IF NOT EXISTS profit_drains + (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) + ,account_section VARCHAR NOT NULL + ,payto_uri VARCHAR NOT NULL + ,trigger_date INT8 NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,executed BOOLEAN NOT NULL DEFAULT FALSE + ); +COMMENT ON TABLE profit_drains + IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; +COMMENT ON COLUMN profit_drains.wtid + IS 'randomly chosen nonce, unique to prevent double-submission'; +COMMENT ON COLUMN profit_drains.account_section + IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; +COMMENT ON COLUMN profit_drains.payto_uri + IS 'specifies the account to be credited'; +COMMENT ON COLUMN profit_drains.trigger_date + IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; +COMMENT ON COLUMN profit_drains.amount_val + IS 'amount to be transferred'; +COMMENT ON COLUMN profit_drains.master_sig + IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; +COMMENT ON COLUMN profit_drains.executed + IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('profit_drains' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); diff --git a/src/exchangedb/0002-purse_decision.sql b/src/exchangedb/0002-purse_decision.sql new file mode 100644 index 00000000..3905faaf --- /dev/null +++ b/src/exchangedb/0002-purse_decision.sql @@ -0,0 +1,59 @@ +-- +-- 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/> +-- + + +CREATE OR REPLACE FUNCTION create_table_purse_decision( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_decision'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',action_timestamp INT8 NOT NULL' + ',refunded BOOL NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_decision_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_decision_' || partition_suffix || '_purse_action_serial_id_key ' + 'UNIQUE (purse_decision_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_deposits.sql b/src/exchangedb/0002-purse_deposits.sql new file mode 100644 index 00000000..375b0b69 --- /dev/null +++ b/src/exchangedb/0002-purse_deposits.sql @@ -0,0 +1,68 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_purse_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' + ',PRIMARY KEY (purse_pub,coin_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by coin_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' + 'ON ' || table_name || ' ' + '(coin_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key ' + 'UNIQUE (purse_deposit_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_merges.sql b/src/exchangedb/0002-purse_merges.sql new file mode 100644 index 00000000..1e861cc5 --- /dev/null +++ b/src/exchangedb/0002-purse_merges.sql @@ -0,0 +1,71 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_purse_merges( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_merges'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY '-- UNIQUE + ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)'--REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE + ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' + ',merge_timestamp INT8 NOT NULL' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_merges_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key ' + 'UNIQUE (purse_merge_request_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-purse_requests.sql b/src/exchangedb/0002-purse_requests.sql new file mode 100644 index 00000000..135b36df --- /dev/null +++ b/src/exchangedb/0002-purse_requests.sql @@ -0,0 +1,85 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_purse_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'purse_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' + ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' + ',purse_creation INT8 NOT NULL' + ',purse_expiration INT8 NOT NULL' + ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)' + ',age_limit INT4 NOT NULL' + ',flags INT4 NOT NULL' + ',in_reserve_quota BOOLEAN NOT NULL DEFAULT(FALSE)' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',purse_fee_val INT8 NOT NULL' + ',purse_fee_frac INT4 NOT NULL' + ',balance_val INT8 NOT NULL DEFAULT (0)' + ',balance_frac INT4 NOT NULL DEFAULT (0)' + ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)' + ',PRIMARY KEY (purse_pub)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by merge_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' + 'ON ' || table_name || ' ' + '(merge_pub);' + ); + + -- FIXME: drop index on master (crosses shards)? + -- Or use materialized index? (needed?) + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' + 'ON ' || table_name || ' ' + '(purse_expiration);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key ' + 'UNIQUE (purse_requests_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-recoup.sql b/src/exchangedb/0002-recoup.sql new file mode 100644 index 00000000..1e420c5f --- /dev/null +++ b/src/exchangedb/0002-recoup.sql @@ -0,0 +1,98 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-recoup_refresh.sql b/src/exchangedb/0002-recoup_refresh.sql new file mode 100644 index 00000000..859d6dd8 --- /dev/null +++ b/src/exchangedb/0002-recoup_refresh.sql @@ -0,0 +1,76 @@ +-- +-- 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/> +-- + + +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 +$$; diff --git a/src/exchangedb/0002-refresh_commitments.sql b/src/exchangedb/0002-refresh_commitments.sql new file mode 100644 index 00000000..c5193bee --- /dev/null +++ b/src/exchangedb/0002-refresh_commitments.sql @@ -0,0 +1,67 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-refresh_revealed_coins.sql b/src/exchangedb/0002-refresh_revealed_coins.sql new file mode 100644 index 00000000..64f7027a --- /dev/null +++ b/src/exchangedb/0002-refresh_revealed_coins.sql @@ -0,0 +1,74 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-refresh_transfer_keys.sql b/src/exchangedb/0002-refresh_transfer_keys.sql new file mode 100644 index 00000000..425a0b14 --- /dev/null +++ b/src/exchangedb/0002-refresh_transfer_keys.sql @@ -0,0 +1,55 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-refunds.sql b/src/exchangedb/0002-refunds.sql new file mode 100644 index 00000000..daaae384 --- /dev/null +++ b/src/exchangedb/0002-refunds.sql @@ -0,0 +1,72 @@ +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 constrain0002_table_refunds ( + IN partition_suffix VARCHAR DEFAULT NULL +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + -- FIXME: '_' issue if partition_suffix is NULL + -- => solve with general ALTER TABLE helper function! + '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 +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('refunds' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('refunds' + ,'exchange-0002' + ,'constrain0002' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0002-reserves.sql b/src/exchangedb/0002-reserves.sql new file mode 100644 index 00000000..7cb561a9 --- /dev/null +++ b/src/exchangedb/0002-reserves.sql @@ -0,0 +1,74 @@ +-- +-- 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/> +-- + +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 DEFAULT(0)' + ',current_balance_frac INT4 NOT NULL DEFAULT(0)' + ',purses_active INT8 NOT NULL DEFAULT(0)' + ',purses_allowed INT8 NOT NULL DEFAULT(0)' + ',max_age INT4 NOT NULL DEFAULT(120)' + ',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 +$$; diff --git a/src/exchangedb/0002-reserves_close.sql b/src/exchangedb/0002-reserves_close.sql new file mode 100644 index 00000000..fbb0b86c --- /dev/null +++ b/src/exchangedb/0002-reserves_close.sql @@ -0,0 +1,73 @@ +-- +-- 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/> +-- + +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' + ',close_request_row INT8 NOT NULL DEFAULT(0)' + ') %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 +$$; diff --git a/src/exchangedb/0002-reserves_in.sql b/src/exchangedb/0002-reserves_in.sql new file mode 100644 index 00000000..0b3c37e5 --- /dev/null +++ b/src/exchangedb/0002-reserves_in.sql @@ -0,0 +1,83 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-reserves_open_deposits.sql b/src/exchangedb/0002-reserves_open_deposits.sql new file mode 100644 index 00000000..380034b6 --- /dev/null +++ b/src/exchangedb/0002-reserves_open_deposits.sql @@ -0,0 +1,70 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_deposits'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',contribution_val INT8 NOT NULL' + ',contribution_frac INT4 NOT NULL' + ') %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_uuid ' + 'ON ' || table_name || ' ' + '(reserve_open_deposit_uuid);' + ); + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' + 'PRIMARY KEY (coin_pub,coin_sig)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_open_requests.sql b/src/exchangedb/0002-reserves_open_requests.sql new file mode 100644 index 00000000..a9ef5f86 --- /dev/null +++ b/src/exchangedb/0002-reserves_open_requests.sql @@ -0,0 +1,73 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR default 'reserves_open_requests'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',request_timestamp INT8 NOT NULL' + ',expiration_date INT8 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',reserve_payment_val INT8 NOT NULL' + ',reserve_payment_frac INT4 NOT NULL' + ',requested_purse_limit 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_reserve_open_uuid_index ' + 'ON ' || table_name || ' ' + '(open_request_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_open_request_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'PRIMARY KEY (open_request_uuid),' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'UNIQUE (reserve_pub,request_timestamp)' + ); +END +$$; diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql new file mode 100644 index 00000000..ea8cabbc --- /dev/null +++ b/src/exchangedb/0002-reserves_out.sql @@ -0,0 +1,111 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-wad_out_entries.sql b/src/exchangedb/0002-wad_out_entries.sql new file mode 100644 index 00000000..a475c6fb --- /dev/null +++ b/src/exchangedb/0002-wad_out_entries.sql @@ -0,0 +1,76 @@ +-- +-- 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/> +-- + + +CREATE OR REPLACE FUNCTION create_table_wad_out_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_out_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_out_serial_id INT8' -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' + ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' + ',purse_expiration INT8 NOT NULL' + ',merge_timestamp INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wad_fee_val INT8 NOT NULL' + ',wad_fee_frac INT4 NOT NULL' + ',deposit_fees_val INT8 NOT NULL' + ',deposit_fees_frac INT4 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_out_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key ' + 'UNIQUE (wad_out_entry_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wads_in.sql b/src/exchangedb/0002-wads_in.sql new file mode 100644 index 00000000..48036641 --- /dev/null +++ b/src/exchangedb/0002-wads_in.sql @@ -0,0 +1,60 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_wads_in( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_in'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',origin_exchange_url TEXT NOT NULL' + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',arrival_time INT8 NOT NULL' + ',UNIQUE (wad_id, origin_exchange_url)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' + 'UNIQUE (wad_in_serial_id) ' + ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' + 'UNIQUE (wad_id, origin_exchange_url) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wads_in_entries.sql b/src/exchangedb/0002-wads_in_entries.sql new file mode 100644 index 00000000..addd2272 --- /dev/null +++ b/src/exchangedb/0002-wads_in_entries.sql @@ -0,0 +1,79 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_wad_in_entries( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wad_in_entries'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_in_serial_id INT8' -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE + ',reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)' + ',purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)' + ',h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)' + ',purse_expiration INT8 NOT NULL' + ',merge_timestamp INT8 NOT NULL' + ',amount_with_fee_val INT8 NOT NULL' + ',amount_with_fee_frac INT4 NOT NULL' + ',wad_fee_val INT8 NOT NULL' + ',wad_fee_frac INT4 NOT NULL' + ',deposit_fees_val INT8 NOT NULL' + ',deposit_fees_frac INT4 NOT NULL' + ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' + ',purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (purse_pub)' + ,shard_suffix + ); + + table_name = concat_ws('_', table_name, shard_suffix); + + -- FIXME: change to materialized index by reserve_pub! + EXECUTE FORMAT ( + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'ON ' || table_name || ' ' + '(reserve_pub);' + ); + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' + 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' + 'UNIQUE (wad_in_entry_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wads_out.sql b/src/exchangedb/0002-wads_out.sql new file mode 100644 index 00000000..afa56fbb --- /dev/null +++ b/src/exchangedb/0002-wads_out.sql @@ -0,0 +1,57 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_wads_out( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'wads_out'; +BEGIN + + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I ' + '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' + ',partner_serial_id INT8 NOT NULL' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',amount_val INT8 NOT NULL' + ',amount_frac INT4 NOT NULL' + ',execution_time INT8 NOT NULL' + ') %s ;' + ,table_name + ,'PARTITION BY HASH (wad_id)' + ,shard_suffix + ); + +END +$$; + +CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE wads_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' + 'UNIQUE (wad_out_serial_id) ' + ); +END +$$; diff --git a/src/exchangedb/0002-wire_out.sql b/src/exchangedb/0002-wire_out.sql new file mode 100644 index 00000000..9d04cf78 --- /dev/null +++ b/src/exchangedb/0002-wire_out.sql @@ -0,0 +1,67 @@ +-- +-- 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/> +-- + +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 +$$; diff --git a/src/exchangedb/0002-wire_targets.sql b/src/exchangedb/0002-wire_targets.sql new file mode 100644 index 00000000..5e8f1905 --- /dev/null +++ b/src/exchangedb/0002-wire_targets.sql @@ -0,0 +1,67 @@ +-- +-- 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/> +-- + +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' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); +END +$$; + +-- We need a separate 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 +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_targets' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0003-partner_accounts.sql b/src/exchangedb/0003-partner_accounts.sql new file mode 100644 index 00000000..6ed372f5 --- /dev/null +++ b/src/exchangedb/0003-partner_accounts.sql @@ -0,0 +1,33 @@ +-- +-- 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/> +-- + + +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.'; diff --git a/src/exchangedb/0003-purse_actions.sql b/src/exchangedb/0003-purse_actions.sql new file mode 100644 index 00000000..df8eecea --- /dev/null +++ b/src/exchangedb/0003-purse_actions.sql @@ -0,0 +1,60 @@ +-- +-- 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/> +-- + + +CREATE TABLE IF NOT EXISTS purse_actions + (purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32) + ,action_date INT8 NOT NULL + ,partner_serial_id INT8 + ); +COMMENT ON TABLE purse_actions + IS 'purses awaiting some action by the router'; +COMMENT ON COLUMN purse_actions.purse_pub + IS 'public (contract) key of the purse'; +COMMENT ON COLUMN purse_actions.action_date + IS 'when is the purse ready for action'; +COMMENT ON COLUMN purse_actions.partner_serial_id + IS 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown'; + +CREATE INDEX IF NOT EXISTS purse_action_by_target + ON purse_actions + (partner_serial_id,action_date); + + +CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + INSERT INTO + purse_actions + (purse_pub + ,action_date) + VALUES + (NEW.purse_pub + ,NEW.purse_expiration); + RETURN NEW; +END $$; +COMMENT ON FUNCTION purse_requests_insert_trigger() + IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; + +CREATE TRIGGER purse_requests_on_insert + AFTER INSERT + ON purse_requests + FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); +COMMENT ON TRIGGER purse_requests_on_insert + ON purse_requests + IS 'Here we install an entry for the purse expiration.'; diff --git a/src/exchangedb/0003-purse_deletion.sql b/src/exchangedb/0003-purse_deletion.sql new file mode 100644 index 00000000..e655ee61 --- /dev/null +++ b/src/exchangedb/0003-purse_deletion.sql @@ -0,0 +1,94 @@ +-- +-- 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/> +-- + +CREATE OR REPLACE FUNCTION create_table_purse_deletion( + IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE IF NOT EXISTS %I' + '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' + ',XXX VARCHAR NOT NULL' + ') %s ;' + ,'purse_deletion' + ,'PARTITION BY HASH (XXX)' + ,shard_suffix + ); + COMMENT ON TABLE purse_deletion + IS 'signatures affirming explicit purse deletions'; + COMMENT ON COLUMN purse_deletion.purse_sig + IS 'signature of type XXX'; +END +$$; +COMMENT ON FUNCTION create_table_purse_deletion + IS 'Creates the purse_deletion table'; + +CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_deletion_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deletion_' || partition_suffix || '_XXX ' + 'UNIQUE (XXX)' + ); +END +$$; + +CREATE OR REPLACE FUNCTION alter_create_was_deleted_table_purse_requests ( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || + ' ADD COLUMN' + ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' + ); +END +$$; + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('purse_deletion' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('purse_deletion' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('purse_requests' + ,'exchange-0002' + ,'alter_create_was_deleted' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 595bb903..d7dd0895 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -15,37 +15,37 @@ pkgcfg_DATA = \ sqldir = $(prefix)/share/taler/sql/exchange/ sqlinputs = \ - common-0001.sql \ - exchange-0001.sql.in \ - exchange-0001-part.sql \ - common-0002.sql \ + 0002-*.sql \ + 0003-*.sql \ exchange-0002.sql.in \ - exchange-0002-part.sql + exchange-0003.sql.in sql_DATA = \ benchmark-0001.sql \ versioning.sql \ exchange-0001.sql \ + exchange-0002.sql \ drop.sql \ procedures.sql BUILT_SOURCES = \ - exchange-0001.sql \ + benchmark-0001.sql \ drop.sql \ + exchange-0001.sql \ procedures.sql CLEANFILES = \ - exchange-0001.sql \ - exchange-0002.sql + exchange-0002.sql \ + exchange-0003.sql -exchange-0001.sql: common-0001.sql exchange-0001-part.sql exchange-0001.sql.in +exchange-0002.sql: exchange-0002.sql.in 0002-*.sql chmod +w $@ || true - gcc -E -P -undef - < exchange-0001.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ -exchange-0002.sql: common-0002.sql exchange-0002-part.sql exchange-0002.sql.in +exchange-0003.sql: exchange-0003.sql.in 0003-*.sql chmod +w $@ || true - gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + gcc -E -P -undef - < exchange-0003.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ EXTRA_DIST = \ diff --git a/src/exchangedb/common-0002.sql b/src/exchangedb/common-0002.sql index 388d5547..649efa11 100644 --- a/src/exchangedb/common-0002.sql +++ b/src/exchangedb/common-0002.sql @@ -16,27 +16,6 @@ -------------------------------- -CREATE TABLE IF NOT EXISTS exchange_tables - (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY - ,name VARCHAR NOT NULL - ,version VARCHAR NOT NULL - ,action VARCHAR NOT NULL - ,partitioned BOOL NOT NULL - ,by_range BOOL NOT NULL); -COMMENT ON TABLE exchange_tables - IS 'Tables of the exchange and their status'; -COMMENT ON COLUMN exchange_tables.name - IS 'Base name of the table (without partition/shard)'; -COMMENT ON COLUMN exchange_tables.version - IS 'Version of the DB in which the given action happened'; -COMMENT ON COLUMN exchange_tables.action - IS 'Action to take on the table (e.g. create, alter, constrain, unconstrain, or drop)'; -COMMENT ON COLUMN exchange_tables.partitioned - IS 'TRUE if the table is partitioned'; -COMMENT ON COLUMN exchange_tables.by_range - IS 'TRUE if the table is partitioned by range'; - - INSERT INTO exchange_tables (name ,version @@ -44,10 +23,6 @@ INSERT INTO exchange_tables ,partitioned ,by_range) VALUES - ('denominations', 'exchange-0001', 'create', FALSE, FALSE) - ,('denomination_revocations', 'exchange-0001', 'create', FALSE, FALSE) - ,('kyc_alerts', 'exchange-0001', 'create', FALSE, FALSE) - ,('profit_drains', 'exchange-0001', 'create', FALSE, FALSE) ,('auditors', 'exchange-0001', 'create', FALSE, FALSE) ,('auditor_denom_sigs', 'exchange-0001', 'create', FALSE, FALSE) ,('exchange_sign_keys', 'exchange-0001', 'create', FALSE, FALSE) @@ -106,82 +81,3 @@ INSERT INTO exchange_tables -------------------- Tables ---------------------------- - -CREATE OR REPLACE FUNCTION create_table_purse_deletion( - IN shard_suffix VARCHAR DEFAULT NULL -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - PERFORM create_partitioned_table( - 'CREATE TABLE IF NOT EXISTS %I' - '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' - ',XXX VARCHAR NOT NULL' - ') %s ;' - ,'purse_deletion' - ,'PARTITION BY HASH (XXX)' - ,shard_suffix - ); - COMMENT ON TABLE purse_deletion - IS 'signatures affirming explicit purse deletions'; - COMMENT ON COLUMN purse_deletion.purse_sig - IS 'signature of type XXX'; -END -$$; -COMMENT ON FUNCTION create_table_purse_deletion - IS 'Creates the purse_deletion table'; - -CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_deletion_' || partition_suffix || ' ' - 'ADD CONSTRAINT purse_deletion_' || partition_suffix || '_XXX ' - 'UNIQUE (XXX)' - ); -END -$$; - -CREATE OR REPLACE FUNCTION alter_create_was_deleted_table_purse_requests ( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE purse_requests_' || partition_suffix || - ' ADD COLUMN' - ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' - ); -END -$$; - -INSERT INTO exchange_tables - (name - ,version - ,action - ,partitioned - ,by_range) - VALUES - ('purse_deletion' - ,'exchange-0002' - ,'create' - ,TRUE - ,FALSE), - ('purse_deletion' - ,'exchange-0002' - ,'constrain' - ,TRUE - ,FALSE), - ('purse_requests' - ,'exchange-0002' - ,'alter_create_was_deleted' - ,TRUE - ,FALSE); diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index 59e684e1..7855c996 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -14,68 +14,9 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- ------------------------------ denominations ---------------------------------------- --- ------------------------------ 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'; - - - --- -------------------------- kyc_alerts ---------------------------------------- - -CREATE TABLE IF NOT EXISTS kyc_alerts - (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) - ,trigger_type INT4 NOT NULL - ,UNIQUE(trigger_type,h_payto) - ); -COMMENT ON TABLE kyc_alerts - IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; -COMMENT ON COLUMN kyc_alerts.h_payto - IS 'hash of the payto://-URI for which the KYC status changed'; -COMMENT ON COLUMN kyc_alerts.trigger_type - IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; - - --- ------------------------------ profit drains ---------------------------------------- - -CREATE TABLE IF NOT EXISTS profit_drains - (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) - ,account_section VARCHAR NOT NULL - ,payto_uri VARCHAR NOT NULL - ,trigger_date INT8 NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ,executed BOOLEAN NOT NULL DEFAULT FALSE - ); -COMMENT ON TABLE profit_drains - IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; -COMMENT ON COLUMN profit_drains.wtid - IS 'randomly chosen nonce, unique to prevent double-submission'; -COMMENT ON COLUMN profit_drains.account_section - IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; -COMMENT ON COLUMN profit_drains.payto_uri - IS 'specifies the account to be credited'; -COMMENT ON COLUMN profit_drains.trigger_date - IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; -COMMENT ON COLUMN profit_drains.amount_val - IS 'amount to be transferred'; -COMMENT ON COLUMN profit_drains.master_sig - IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; -COMMENT ON COLUMN profit_drains.executed - IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; - - -- ------------------------------ wire_targets ---------------------------------------- SELECT create_table_wire_targets(); @@ -87,6 +28,7 @@ COMMENT ON COLUMN wire_targets.payto_uri COMMENT ON COLUMN wire_targets.wire_target_h_payto IS 'Unsalted hash of payto_uri'; + CREATE TABLE IF NOT EXISTS wire_targets_default PARTITION OF wire_targets FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -1423,66 +1365,4 @@ 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.'; - - ----------------------- router helper table (not synchronzied) ------------------------ - -CREATE TABLE IF NOT EXISTS purse_actions - (purse_pub BYTEA NOT NULL PRIMARY KEY CHECK(LENGTH(purse_pub)=32) - ,action_date INT8 NOT NULL - ,partner_serial_id INT8 - ); -COMMENT ON TABLE purse_actions - IS 'purses awaiting some action by the router'; -COMMENT ON COLUMN purse_actions.purse_pub - IS 'public (contract) key of the purse'; -COMMENT ON COLUMN purse_actions.action_date - IS 'when is the purse ready for action'; -COMMENT ON COLUMN purse_actions.partner_serial_id - IS 'wad target of an outgoing wire transfer, 0 for local, NULL if the purse is unmerged and thus the target is still unknown'; - -CREATE INDEX IF NOT EXISTS purse_action_by_target - ON purse_actions - (partner_serial_id,action_date); - - -CREATE OR REPLACE FUNCTION purse_requests_insert_trigger() - RETURNS trigger - LANGUAGE plpgsql - AS $$ -BEGIN - INSERT INTO - purse_actions - (purse_pub - ,action_date) - VALUES - (NEW.purse_pub - ,NEW.purse_expiration); - RETURN NEW; -END $$; -COMMENT ON FUNCTION purse_requests_insert_trigger() - IS 'When a purse is created, insert it into the purse_action table to take action when the purse expires.'; - -CREATE TRIGGER purse_requests_on_insert - AFTER INSERT - ON purse_requests - FOR EACH ROW EXECUTE FUNCTION purse_requests_insert_trigger(); -COMMENT ON TRIGGER purse_requests_on_insert - ON purse_requests - IS 'Here we install an entry for the purse expiration.'; diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql new file mode 100644 index 00000000..6998320c --- /dev/null +++ b/src/exchangedb/exchange-0001.sql @@ -0,0 +1,783 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +-- Everything in one big transaction +BEGIN; + +SET search_path TO exchange; + +--------------------------------------------------------------------------- +-- General procedures for DB setup +--------------------------------------------------------------------------- + +CREATE TABLE IF NOT EXISTS exchange_tables + (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,name VARCHAR NOT NULL + ,version VARCHAR NOT NULL + ,action VARCHAR NOT NULL + ,partitioned BOOL NOT NULL + ,by_range BOOL NOT NULL + ,finished BOOL NOT NULL DEFAULT(FALSE)); +COMMENT ON TABLE exchange_tables + IS 'Tables of the exchange and their status'; +COMMENT ON COLUMN exchange_tables.name + IS 'Base name of the table (without partition/shard)'; +COMMENT ON COLUMN exchange_tables.version + IS 'Version of the DB in which the given action happened'; +COMMENT ON COLUMN exchange_tables.action + IS 'Action to take on the table (e.g. create, alter, constrain, unconstrain, or drop)'; +COMMENT ON COLUMN exchange_tables.partitioned + IS 'TRUE if the table is partitioned'; +COMMENT ON COLUMN exchange_tables.by_range + IS 'TRUE if the table is partitioned by range'; +COMMENT ON COLUMN exchange_tables.finished + IS 'TRUE if the respective migration has been run'; + + + +CREATE OR REPLACE FUNCTION create_partitioned_table( + IN table_definition VARCHAR + ,IN table_name VARCHAR + ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables + ,IN shard_suffix VARCHAR DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + IF shard_suffix IS NOT NULL THEN + table_name=table_name || '_' || shard_suffix; + main_table_partition_str = ''; + END IF; + + EXECUTE FORMAT( + table_definition, + table_name, + main_table_partition_str + ); + +END +$$; + + + + + +CREATE OR REPLACE FUNCTION create_tables( + num_partitions INTEGER + ,shard_domain VARCHAR +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,by_range + FROM exchange_tables + WHERE NOT finished + ORDER BY table_serial_id ASC; +BEGIN + FOR rec IN tc + LOOP + -- First create the master table, either + -- completely unpartitioned, or with one + -- master and the 'default' partition + IF IS NULL num_partitions + THEN + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + ELSE + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,0 + ); + END IF + + IF NOT NULL shard_domain + THEN + -- FIXME: attach shards! + -- FIXME: how will migration work with shards!? + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_XXX_%s (%s)'::text + ,rec.action + ,rec.name + ,i::varchar + ); + END LOOP; + ELSE + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,i::varchar + ); + END LOOP; + END IF + + IF 0 < num_partitions + THEN + -- FIXME: detach default partition! + END IF + + UPDATE exchange_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; + END LOOP; +END +$$; + +COMMENT ON FUNCTION create_tables + IS 'Creates all tables for the given number of partitions that need creating.'; + + + +-- This is run last by dbinit, if partitions exist +-- or if 'force_create_partitions' is set (otherwise, +-- we are not expected to create partitions if there +-- is only 1). +CREATE OR REPLACE FUNCTION create_partitions( + IN part_idx INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + ,action + ,partitioned + ,by_range + FROM exchange_tables + WHERE version=in_version + AND partitioned + ORDER BY table_seria_id ASC; +BEGIN + FOR rec IN tc + LOOP + EXECUTE FORMAT( + 'PERFORM %s_table_%s (%s)' + ,rec.action + ,rec.name + ,shard_idx::varchar + ); + END LOOP; +END +$$; + +COMMENT ON FUNCTION create_partitions + IS 'Creates all partitions that need creating.'; + + + + +CREATE OR REPLACE FUNCTION drop_default_partitions_NG() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +BEGIN + RAISE NOTICE 'Dropping default tables of partitioned tables'; + FOR rec IN tc + LOOP + EXECUTE FORMAT ( + 'DROP TABLE IF EXISTS %s_default ;'::text, + rec.name; +END +$$; + +COMMENT ON FUNCTION drop_default_partitions + IS 'Drop all default partitions once other partitions are attached. + Might be needed in sharding too.'; + + +CREATE OR REPLACE FUNCTION detach_default_partitions_NG() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +BEGIN + RAISE NOTICE 'Detaching all default table partitions'; + FOR rec IN tc + LOOP + EXECUTE FORMAT ( + 'ALTER TABLE IF EXISTS %s DETACH PARTITION %s_default;'::text, + rec.name, + rec.name + ); + END LOOP; +END +$$; + +COMMENT ON FUNCTION detach_default_partitions + IS 'We need to drop default and create new one before deleting the default partitions + otherwise constraints get lost too. Might be needed in sharding too'; + + +CREATE OR REPLACE FUNCTION create_hash_partition_NG( + source_table_name VARCHAR + ,modulus INTEGER + ,partition_num INTEGER + ) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num; + + EXECUTE FORMAT( + 'CREATE TABLE IF NOT EXISTS %I ' + 'PARTITION OF %I ' + 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,source_table_name || '_' || partition_num + ,source_table_name + ,modulus + ,partition_num-1 + ); + +END +$$; + + +CREATE OR REPLACE FUNCTION create_partitions_NG( + num_partitions INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT name + FROM exchange_tables + WHERE partitioned + AND NOT by_range; +DECLARE + i INTEGER; +BEGIN + PERFORM detach_default_partitions(); + FOR rec IN tc + LOOP + i := num_partitions + LOOP + + PERFORM create_hash_partition( + quote_literal (rec.name) + ,num_partitions + ,i + ); + + i=i-1; + EXIT WHEN i=0; + END LOOP; -- i = num_partitions ... 0 + END LOOP; -- for all partitioned tables + + PERFORM drop_default_partitions(); + +END +$$; + + +-- OLD LOGIC: +------------------------------------------------------------------- +------------------------- Partitions ------------------------------ +------------------------------------------------------------------- + + +CREATE OR REPLACE FUNCTION create_range_partition( + source_table_name VARCHAR + ,partition_num INTEGER +) + RETURNS void + LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'TODO'; +END +$$; + +CREATE OR REPLACE FUNCTION detach_default_partitions() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Detaching all default table partitions'; + + ALTER TABLE IF EXISTS wire_targets + DETACH PARTITION wire_targets_default; + + ALTER TABLE IF EXISTS reserves + DETACH PARTITION reserves_default; + + ALTER TABLE IF EXISTS reserves_in + DETACH PARTITION reserves_in_default; + + ALTER TABLE IF EXISTS reserves_close + DETACH PARTITION reserves_close_default; + + ALTER TABLE IF EXISTS history_requests + DETACH partition history_requests_default; + + ALTER TABLE IF EXISTS close_requests + DETACH partition close_requests_default; + + ALTER TABLE IF EXISTS reserves_open_requests + DETACH partition reserves_open_requests_default; + + ALTER TABLE IF EXISTS reserves_out + DETACH PARTITION reserves_out_default; + + ALTER TABLE IF EXISTS reserves_out_by_reserve + DETACH PARTITION reserves_out_by_reserve_default; + + ALTER TABLE IF EXISTS known_coins + DETACH PARTITION known_coins_default; + + ALTER TABLE IF EXISTS refresh_commitments + DETACH PARTITION refresh_commitments_default; + + ALTER TABLE IF EXISTS refresh_revealed_coins + DETACH PARTITION refresh_revealed_coins_default; + + ALTER TABLE IF EXISTS refresh_transfer_keys + DETACH PARTITION refresh_transfer_keys_default; + + ALTER TABLE IF EXISTS deposits + DETACH PARTITION deposits_default; + +--- TODO range partitioning +-- ALTER TABLE IF EXISTS deposits_by_ready +-- DETACH PARTITION deposits_by_ready_default; +-- +-- ALTER TABLE IF EXISTS deposits_for_matching +-- DETACH PARTITION deposits_default_for_matching_default; + + ALTER TABLE IF EXISTS refunds + DETACH PARTITION refunds_default; + + ALTER TABLE IF EXISTS wire_out + DETACH PARTITION wire_out_default; + + ALTER TABLE IF EXISTS aggregation_transient + DETACH PARTITION aggregation_transient_default; + + ALTER TABLE IF EXISTS aggregation_tracking + DETACH PARTITION aggregation_tracking_default; + + ALTER TABLE IF EXISTS recoup + DETACH PARTITION recoup_default; + + ALTER TABLE IF EXISTS recoup_by_reserve + DETACH PARTITION recoup_by_reserve_default; + + ALTER TABLE IF EXISTS recoup_refresh + DETACH PARTITION recoup_refresh_default; + + ALTER TABLE IF EXISTS prewire + DETACH PARTITION prewire_default; + + ALTER TABLE IF EXISTS cs_nonce_locks + DETACH partition cs_nonce_locks_default; + + ALTER TABLE IF EXISTS purse_requests + DETACH partition purse_requests_default; + + ALTER TABLE IF EXISTS purse_decision + DETACH partition purse_decision_default; + + ALTER TABLE IF EXISTS purse_merges + DETACH partition purse_merges_default; + + ALTER TABLE IF EXISTS account_merges + DETACH partition account_merges_default; + + ALTER TABLE IF EXISTS contracts + DETACH partition contracts_default; + + ALTER TABLE IF EXISTS purse_deposits + DETACH partition purse_deposits_default; + + ALTER TABLE IF EXISTS wad_out_entries + DETACH partition wad_out_entries_default; + + ALTER TABLE IF EXISTS wads_in + DETACH partition wads_in_default; + + ALTER TABLE IF EXISTS wad_in_entries + DETACH partition wad_in_entries_default; +END +$$; + +COMMENT ON FUNCTION detach_default_partitions + IS 'We need to drop default and create new one before deleting the default partitions + otherwise constraints get lost too. Might be needed in sharding too'; + + +CREATE OR REPLACE FUNCTION drop_default_partitions() + RETURNS VOID + LANGUAGE plpgsql +AS $$ +BEGIN + + RAISE NOTICE 'Dropping default table partitions'; + + DROP TABLE IF EXISTS wire_targets_default; + DROP TABLE IF EXISTS reserves_default; + DROP TABLE IF EXISTS reserves_in_default; + DROP TABLE IF EXISTS reserves_close_default; + DROP TABLE IF EXISTS reserves_open_requests_default; + DROP TABLE IF EXISTS history_requests_default; + DROP TABLE IF EXISTS close_requests_default; + + DROP TABLE IF EXISTS reserves_out_default; + DROP TABLE IF EXISTS reserves_out_by_reserve_default; + DROP TABLE IF EXISTS known_coins_default; + DROP TABLE IF EXISTS refresh_commitments_default; + DROP TABLE IF EXISTS refresh_revealed_coins_default; + DROP TABLE IF EXISTS refresh_transfer_keys_default; + DROP TABLE IF EXISTS deposits_default; +--DROP TABLE IF EXISTS deposits_by_ready_default; +--DROP TABLE IF EXISTS deposits_for_matching_default; + DROP TABLE IF EXISTS refunds_default; + DROP TABLE IF EXISTS wire_out_default; + DROP TABLE IF EXISTS aggregation_transient_default; + DROP TABLE IF EXISTS aggregation_tracking_default; + DROP TABLE IF EXISTS recoup_default; + DROP TABLE IF EXISTS recoup_by_reserve_default; + DROP TABLE IF EXISTS recoup_refresh_default; + DROP TABLE IF EXISTS prewire_default; + DROP TABLE IF EXISTS cs_nonce_locks_default; + + DROP TABLE IF EXISTS purse_requests_default; + DROP TABLE IF EXISTS purse_decision_default; + DROP TABLE IF EXISTS purse_merges_default; + DROP TABLE IF EXISTS account_merges_default; + DROP TABLE IF EXISTS purse_deposits_default; + DROP TABLE IF EXISTS contracts_default; + + DROP TABLE IF EXISTS wad_out_entries_default; + DROP TABLE IF EXISTS wads_in_default; + DROP TABLE IF EXISTS wad_in_entries_default; + +END +$$; + +COMMENT ON FUNCTION drop_default_partitions + IS 'Drop all default partitions once other partitions are attached. + Might be needed in sharding too.'; + +CREATE OR REPLACE FUNCTION create_partitions( + num_partitions INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + modulus INTEGER; +BEGIN + + modulus := num_partitions; + + PERFORM detach_default_partitions(); + + LOOP + + PERFORM create_hash_partition( + 'wire_targets' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'reserves_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_close' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'reserves_out_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'known_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_commitments' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_revealed_coins' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'refresh_transfer_keys' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'deposits' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_deposits_partition(num_partitions::varchar); + +-- TODO: dynamically (!) creating/deleting deposits partitions: +-- create new partitions 'as needed', drop old ones once the aggregator has made +-- them empty; as 'new' deposits will always have deadlines in the future, this +-- would basically guarantee no conflict between aggregator and exchange service! +-- SEE also: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/ +-- (article is slightly wrong, as this works:) +--CREATE TABLE tab ( +-- id bigint GENERATED ALWAYS AS IDENTITY, +-- ts timestamp NOT NULL, +-- data text +-- PARTITION BY LIST ((ts::date)); +-- CREATE TABLE tab_def PARTITION OF tab DEFAULT; +-- BEGIN +-- CREATE TABLE tab_part2 (LIKE tab); +-- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo'); +-- alter table tab attach partition tab_part2 for values in ('2022-03-21'); +-- commit; +-- Naturally, to ensure this is actually 100% conflict-free, we'd +-- need to create tables at the granularity of the wire/refund deadlines; +-- that is right now configurable via AGGREGATOR_SHIFT option. + +-- FIXME: range partitioning +-- PERFORM create_range_partition( +-- 'deposits_by_ready' +-- ,modulus +-- ,num_partitions +-- ); +-- +-- PERFORM create_range_partition( +-- 'deposits_for_matching' +-- ,modulus +-- ,num_partitions +-- ); + + PERFORM create_hash_partition( + 'refunds' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wire_out' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'aggregation_transient' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'aggregation_tracking' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'recoup' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'recoup_by_reserve' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'recoup_refresh' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'prewire' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'cs_nonce_locks' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); + + + PERFORM create_hash_partition( + 'close_requests' + ,modulus + ,num_partitions + ); + + PERFORM create_hash_partition( + 'reserves_open_requests' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_reserves_open_request_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'history_requests' + ,modulus + ,num_partitions + ); + + + ---------------- P2P ---------------------- + + PERFORM create_hash_partition( + 'purse_requests' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_requests_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'purse_decision' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_decision_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'purse_merges' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'account_merges' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_account_merges_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'contracts' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_contracts_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'purse_deposits' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_purse_deposits_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wad_out_entries' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wad_out_entries_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wads_in' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar); + + PERFORM create_hash_partition( + 'wad_in_entries' + ,modulus + ,num_partitions + ); + PERFORM add_constraints_to_wad_in_entries_partition(num_partitions::varchar); + + num_partitions=num_partitions-1; + EXIT WHEN num_partitions=0; + + END LOOP; + + PERFORM drop_default_partitions(); + +END +$$; diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/exchange-0002.sql index 3c2a850d..600090bc 100644 --- a/src/exchangedb/common-0001.sql +++ b/src/exchangedb/exchange-0002.sql @@ -1,48 +1,77 @@ --- --- 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/> --- - --------------------- 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 -) +BEGIN; +SELECT _v.register_patch('exchange-0002', NULL, NULL); +SET search_path TO exchange; +CREATE OR REPLACE FUNCTION create_table_denominations() 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 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) + ,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); +END +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denominations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +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'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denomination_revocations' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); CREATE OR REPLACE FUNCTION create_table_wire_targets( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -52,7 +81,7 @@ 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_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' ',payto_uri VARCHAR NOT NULL' ') %s ;' @@ -62,9 +91,6 @@ BEGIN ); END $$; - --- We need a separate 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 ) @@ -72,7 +98,6 @@ 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 ' @@ -80,10 +105,80 @@ BEGIN ); END $$; - - ------------------------ legitimization_processes --------------------------- - +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_targets' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE); +CREATE TABLE IF NOT EXISTS kyc_alerts + (h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32) + ,trigger_type INT4 NOT NULL + ,UNIQUE(trigger_type,h_payto) + ); +COMMENT ON TABLE kyc_alerts + IS 'alerts about completed KYC events reliably notifying other components (even if they are not running)'; +COMMENT ON COLUMN kyc_alerts.h_payto + IS 'hash of the payto://-URI for which the KYC status changed'; +COMMENT ON COLUMN kyc_alerts.trigger_type + IS 'identifies the receiver of the alert, as the same h_payto may require multiple components to be notified'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('kyc_alerts' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); +CREATE TABLE IF NOT EXISTS profit_drains + (profit_drain_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ,wtid BYTEA PRIMARY KEY CHECK (LENGTH(wtid)=32) + ,account_section VARCHAR NOT NULL + ,payto_uri VARCHAR NOT NULL + ,trigger_date INT8 NOT NULL + ,amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) + ,executed BOOLEAN NOT NULL DEFAULT FALSE + ); +COMMENT ON TABLE profit_drains + IS 'transactions to be performed to move profits from the escrow account of the exchange to a regular account'; +COMMENT ON COLUMN profit_drains.wtid + IS 'randomly chosen nonce, unique to prevent double-submission'; +COMMENT ON COLUMN profit_drains.account_section + IS 'specifies the configuration section in the taler-exchange-drain configuration with the wire account to drain'; +COMMENT ON COLUMN profit_drains.payto_uri + IS 'specifies the account to be credited'; +COMMENT ON COLUMN profit_drains.trigger_date + IS 'set by taler-exchange-offline at the time of making the signature; not necessarily the exact date of execution of the wire transfer, just for orientation'; +COMMENT ON COLUMN profit_drains.amount_val + IS 'amount to be transferred'; +COMMENT ON COLUMN profit_drains.master_sig + IS 'EdDSA signature of type TALER_SIGNATURE_MASTER_DRAIN_PROFIT'; +COMMENT ON COLUMN profit_drains.executed + IS 'set to TRUE by taler-exchange-drain on execution of the transaction, not replicated to auditor'; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('profit_drains' + ,'exchange-0002' + ,'create' + ,FALSE + ,FALSE); CREATE OR REPLACE FUNCTION create_table_legitimization_processes( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -91,10 +186,9 @@ RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' - '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' ',expiration_time INT8 NOT NULL DEFAULT (0)' ',provider_section VARCHAR NOT NULL' @@ -106,12 +200,8 @@ BEGIN ,'PARTITION BY HASH (h_payto)' ,shard_suffix ); - END $$; - --- We need a separate 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_legitimization_processes_partition( IN partition_suffix VARCHAR ) @@ -121,9 +211,7 @@ AS $$ DECLARE partition_name VARCHAR; BEGIN - partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); - EXECUTE FORMAT ( 'ALTER TABLE ' || partition_name || ' ' @@ -140,10 +228,6 @@ BEGIN ); END $$; - - ------------------------ legitimization_requirements --------------------------- - CREATE OR REPLACE FUNCTION create_table_legitimization_requirements( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -151,10 +235,9 @@ RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' - '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + '(legitimization_requirement_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' ',required_checks VARCHAR NOT NULL' ',UNIQUE (h_payto, required_checks)' @@ -163,12 +246,8 @@ BEGIN ,'PARTITION BY HASH (h_payto)' ,shard_suffix ); - END $$; - --- We need a separate 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_legitimization_requirements_partition( IN partition_suffix VARCHAR ) @@ -178,9 +257,7 @@ AS $$ DECLARE partition_name VARCHAR; BEGIN - partition_name = concat_ws('_', 'legitimization_requirements', partition_suffix); - EXECUTE FORMAT ( 'ALTER TABLE ' || partition_name || ' ' @@ -188,10 +265,6 @@ BEGIN 'UNIQUE (legitimization_requirement_serial_id)'); END $$; - - ------------------------- reserves ------------------------------- - CREATE OR REPLACE FUNCTION create_table_reserves( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -201,7 +274,6 @@ 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' @@ -218,9 +290,7 @@ BEGIN ,'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 || ' ' @@ -247,12 +317,8 @@ BEGIN '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 ) @@ -262,11 +328,10 @@ 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' + '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA PRIMARY KEY' ',wire_reference INT8 NOT NULL' ',credit_val INT8 NOT NULL' ',credit_frac INT4 NOT NULL' @@ -278,15 +343,12 @@ BEGIN ,'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 || ' ' @@ -294,7 +356,6 @@ BEGIN ',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 || ' ' @@ -302,10 +363,8 @@ BEGIN 'reserve_in_serial_id DESC' ');' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition( IN partition_suffix VARCHAR ) @@ -320,9 +379,6 @@ BEGIN ); END $$; - ---------------------------- reserves_close ------------------------------- - CREATE OR REPLACE FUNCTION create_table_reserves_close( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -332,11 +388,10 @@ 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' + '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL' ',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)' @@ -350,9 +405,7 @@ BEGIN ,'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 || ' ' @@ -365,7 +418,6 @@ BEGIN ); END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition( IN partition_suffix VARCHAR ) @@ -380,10 +432,6 @@ BEGIN ); END $$; - - ---------------------------- close_requests --------------------------- - CREATE OR REPLACE FUNCTION create_table_close_requests( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -393,11 +441,10 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'close_requests'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(close_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 + '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',close_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',close_val INT8 NOT NULL' @@ -414,8 +461,6 @@ BEGIN ); END $$; - - CREATE OR REPLACE FUNCTION add_constraints_to_close_requests( IN partition_suffix VARCHAR ) @@ -425,7 +470,6 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'close_requests'; BEGIN - EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_request_uuid_index ' 'ON ' || table_name || ' ' @@ -438,7 +482,6 @@ BEGIN ); END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_close_requests_partition( IN partition_suffix VARCHAR ) @@ -453,52 +496,43 @@ BEGIN ); END $$; - - ---------------------------- reserves_open_requests ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( +CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR default 'reserves_open_requests'; + table_name VARCHAR default 'reserves_open_deposits'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' - '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' - ',request_timestamp INT8 NOT NULL' - ',expiration_date INT8 NOT NULL' + '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',reserve_payment_val INT8 NOT NULL' - ',reserve_payment_frac INT4 NOT NULL' - ',requested_purse_limit INT4 NOT NULL' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' + ',contribution_val INT8 NOT NULL' + ',contribution_frac INT4 NOT NULL' ') %s ;' ,table_name - ,'PARTITION BY HASH (reserve_pub)' + ,'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_reserve_open_uuid_index ' + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_uuid ' 'ON ' || table_name || ' ' - '(open_request_uuid);' + '(reserve_open_deposit_uuid);' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index ' + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' 'ON ' || table_name || ' ' '(reserve_pub);' ); END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_deposits_partition( IN partition_suffix VARCHAR ) RETURNS void @@ -506,59 +540,50 @@ LANGUAGE plpgsql AS $$ BEGIN EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' - 'PRIMARY KEY (open_request_uuid),' - 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' - 'UNIQUE (reserve_pub,request_timestamp)' + 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' + 'PRIMARY KEY (coin_pub,coin_sig)' ); END $$; - - ---------------------------- reserves_open_deposits ------------------------------- - -CREATE OR REPLACE FUNCTION create_table_reserves_open_deposits( +CREATE OR REPLACE FUNCTION create_table_reserves_open_requests( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR default 'reserves_open_deposits'; + table_name VARCHAR default 'reserves_open_requests'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' - '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' + '(open_request_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL' + ',request_timestamp INT8 NOT NULL' + ',expiration_date INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' - ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' - ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)' - ',contribution_val INT8 NOT NULL' - ',contribution_frac INT4 NOT NULL' + ',reserve_payment_val INT8 NOT NULL' + ',reserve_payment_frac INT4 NOT NULL' + ',requested_purse_limit INT4 NOT NULL' ') %s ;' ,table_name - ,'PARTITION BY HASH (coin_pub)' + ,'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_uuid ' + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_open_uuid_index ' 'ON ' || table_name || ' ' - '(reserve_open_deposit_uuid);' + '(open_request_uuid);' ); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve ' + '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_open_deposits_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_reserves_open_request_partition( IN partition_suffix VARCHAR ) RETURNS void @@ -566,16 +591,14 @@ LANGUAGE plpgsql AS $$ BEGIN EXECUTE FORMAT ( - 'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique ' - 'PRIMARY KEY (coin_pub,coin_sig)' + 'ALTER TABLE reserves_open_requests_' || partition_suffix || ' ' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_uuid ' + 'PRIMARY KEY (open_request_uuid),' + 'ADD CONSTRAINT reserves_open_requests_' || partition_suffix || '_by_time ' + 'UNIQUE (reserve_pub,request_timestamp)' ); END $$; - - ----------------------------- reserves_out ------------------------------- - CREATE OR REPLACE FUNCTION create_table_reserves_out( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -585,14 +608,13 @@ 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' + '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' - ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' + ',denominations_serial INT8 NOT NULL' ',denom_sig BYTEA NOT NULL' - ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' + ',reserve_uuid INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',execution_date INT8 NOT NULL' ',amount_with_fee_val INT8 NOT NULL' @@ -602,15 +624,12 @@ BEGIN ,'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 || ' ' @@ -620,11 +639,8 @@ BEGIN '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 ) @@ -639,7 +655,6 @@ BEGIN ); END $$; - CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -649,30 +664,23 @@ 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 + '(reserve_uuid INT8 NOT NULL' ',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 ) @@ -682,11 +690,10 @@ 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' + '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',denominations_serial INT8 NOT NULL' ',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' @@ -694,15 +701,12 @@ BEGIN ',remaining_frac INT4 NOT NULL DEFAULT(0)' ') %s ;' ,table_name - ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial? or multi-level partitioning?; + ,'PARTITION BY HASH (coin_pub)' ,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 ) @@ -717,9 +721,6 @@ BEGIN ); END $$; - ----------------------------- refresh_commitments ------------------------------- - CREATE OR REPLACE FUNCTION create_table_refresh_commitments( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -729,12 +730,11 @@ 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' + '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' - ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_pub BYTEA NOT NULL' ',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' @@ -744,19 +744,14 @@ BEGIN ,'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 ) @@ -771,9 +766,6 @@ BEGIN ); END $$; - ------------------------------- refresh_revealed_coins -------------------------------- - CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -783,36 +775,30 @@ 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' + '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',melt_serial_id INT8 NOT NULL' ',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' + ',denominations_serial INT8 NOT NULL' + ',coin_ev BYTEA NOT NULL' + ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' ',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 ) @@ -832,9 +818,6 @@ BEGIN ); END $$; - ------------------------------ refresh_transfer_keys ------------------------------ - CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -844,11 +827,10 @@ 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' + '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',melt_serial_id INT8 PRIMARY KEY' ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)' ',transfer_privs BYTEA NOT NULL' ') %s ;' @@ -856,10 +838,8 @@ BEGIN ,'PARTITION BY HASH (melt_serial_id)' ,shard_suffix ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition( IN partition_suffix VARCHAR ) @@ -874,9 +854,6 @@ BEGIN ); END $$; - ----------------------------- deposits ------------------------------- - CREATE OR REPLACE FUNCTION create_table_deposits( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -886,13 +863,12 @@ 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' + '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',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??? + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',known_coin_id INT8 NOT NULL' ',amount_with_fee_val INT8 NOT NULL' ',amount_with_fee_frac INT4 NOT NULL' ',wallet_timestamp INT8 NOT NULL' @@ -906,24 +882,20 @@ BEGIN ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)' ',done BOOLEAN NOT NULL DEFAULT FALSE' ',policy_blocked BOOLEAN NOT NULL DEFAULT FALSE' - ',policy_details_serial_id INT8' -- REFERENCES policy_details (policy_details_serial_id) ON DELETE CASCADE' + ',policy_details_serial_id INT8' ') %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 ) @@ -940,7 +912,6 @@ BEGIN ); END $$; - CREATE OR REPLACE FUNCTION create_table_deposits_by_ready( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -950,7 +921,6 @@ 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' @@ -962,19 +932,14 @@ BEGIN ,'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 ) @@ -984,32 +949,88 @@ 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 + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' ',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 $$; - ----------------------------- wire_out ------------------------------- - +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' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',deposit_serial_id INT8 NOT NULL' + ',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' + ') %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 constrain0002_table_refunds ( + IN partition_suffix VARCHAR DEFAULT NULL +) +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 +$$; +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('refunds' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('refunds' + ,'exchange-0002' + ,'constrain0002' + ,TRUE + ,FALSE); CREATE OR REPLACE FUNCTION create_table_wire_out( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1019,10 +1040,9 @@ 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' + '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' ',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)' @@ -1034,19 +1054,14 @@ BEGIN ,'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 ) @@ -1061,9 +1076,6 @@ BEGIN ); END $$; - ----------------------------- aggregation_transient ------------------------------ - CREATE OR REPLACE FUNCTION create_table_aggregation_transient( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1073,7 +1085,6 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'aggregation_transient'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' '(amount_val INT8 NOT NULL' @@ -1088,12 +1099,8 @@ BEGIN ,'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 ) @@ -1103,20 +1110,17 @@ 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 change to coin_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' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',deposit_serial_id INT8 PRIMARY KEY' + ',wtid_raw BYTEA NOT NULL' ') %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 || ' ' @@ -1126,10 +1130,8 @@ BEGIN '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 ) @@ -1144,9 +1146,6 @@ BEGIN ); END $$; - ------------------------------ recoup ------------------------------ - CREATE OR REPLACE FUNCTION create_table_recoup( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1156,34 +1155,29 @@ 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) + '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' ',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' + ',reserve_out_serial_id INT8 NOT NULL' ') %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 ) @@ -1198,7 +1192,6 @@ BEGIN ); END $$; - CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1208,30 +1201,23 @@ 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) + '(reserve_out_serial_id INT8 NOT NULL' + ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' ') %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 ) @@ -1241,28 +1227,23 @@ 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 + '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' + ',known_coin_id BIGINT NOT NULL' ',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' + ',rrc_serial INT8 NOT NULL' ') %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 || ' ' @@ -1273,10 +1254,8 @@ BEGIN 'ON ' || table_name || ' ' '(coin_pub);' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition( IN partition_suffix VARCHAR ) @@ -1291,9 +1270,6 @@ BEGIN ); END $$; - ------------------------------ prewire ------------------------------ - CREATE OR REPLACE FUNCTION create_table_prewire( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1303,7 +1279,6 @@ 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' @@ -1316,9 +1291,7 @@ BEGIN ,'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 || ' ' @@ -1328,7 +1301,6 @@ BEGIN '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 || ' ' @@ -1338,12 +1310,8 @@ BEGIN '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 ) @@ -1351,10 +1319,9 @@ 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' + '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)' ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)' ',max_denomination_serial INT8 NOT NULL' @@ -1363,10 +1330,8 @@ BEGIN ,'PARTITION BY HASH (nonce)' ,shard_suffix ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition( IN partition_suffix VARCHAR ) @@ -1381,13 +1346,6 @@ BEGIN ); END $$; - --------------------------------------------------------------------------- --- Tables for P2P payments --------------------------------------------------------------------------- - -------------------------------- purse_requests ---------------------------------------- - CREATE OR REPLACE FUNCTION create_table_purse_requests( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1397,10 +1355,9 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_requests'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)' ',purse_creation INT8 NOT NULL' @@ -1422,27 +1379,19 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by merge_pub! EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub ' 'ON ' || table_name || ' ' '(merge_pub);' ); - - -- FIXME: drop index on master (crosses shards)? - -- Or use materialized index? (needed?) EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_purse_expiration ' 'ON ' || table_name || ' ' '(purse_expiration);' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition( IN partition_suffix VARCHAR ) @@ -1457,10 +1406,6 @@ BEGIN ); END $$; - - ----------------------------- purse_merges ----------------------------- - CREATE OR REPLACE FUNCTION create_table_purse_merges( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1470,13 +1415,12 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_merges'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY '-- UNIQUE - ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE - ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)'--REFERENCES reserves (reserve_pub) ON DELETE CASCADE - ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE + '(purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY ' + ',partner_serial_id INT8' + ',reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)' + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)' ',merge_timestamp INT8 NOT NULL' ',PRIMARY KEY (purse_pub)' @@ -1485,10 +1429,7 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by reserve_pub! EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' 'ON ' || table_name || ' ' @@ -1498,10 +1439,8 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' 'IS ' || quote_literal('needed in reserve history computation') || ';' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition( IN partition_suffix VARCHAR ) @@ -1516,9 +1455,6 @@ BEGIN ); END $$; - -------------------------- account_merges ---------------------------- - CREATE OR REPLACE FUNCTION create_table_account_merges( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1528,13 +1464,12 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'account_merges'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE - ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE + '(account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',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) + ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',wallet_h_payto BYTEA NOT NULL CHECK (LENGTH(wallet_h_payto)=32)' ',PRIMARY KEY (purse_pub)' ') %s ;' @@ -1542,19 +1477,14 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by reserve_pub! EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' 'ON ' || table_name || ' ' '(reserve_pub);' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition( IN partition_suffix VARCHAR ) @@ -1569,10 +1499,6 @@ BEGIN ); END $$; - - -------------------------------- purse_decision ---------------------------------------- - CREATE OR REPLACE FUNCTION create_table_purse_decision( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1582,10 +1508,9 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_decision'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(purse_decision_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',action_timestamp INT8 NOT NULL' ',refunded BOOL NOT NULL' @@ -1595,12 +1520,9 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_purse_decision_partition( IN partition_suffix VARCHAR ) @@ -1615,10 +1537,6 @@ BEGIN ); END $$; - - -------------------------- contracts ------------------------------- - CREATE OR REPLACE FUNCTION create_table_contracts( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1628,10 +1546,9 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'contracts'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' ',pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)' ',contract_sig BYTEA NOT NULL CHECK (LENGTH(contract_sig)=64)' @@ -1643,10 +1560,8 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition( IN partition_suffix VARCHAR ) @@ -1661,10 +1576,6 @@ BEGIN ); END $$; - ---------------------------- history_requests -------------------------- - - CREATE OR REPLACE FUNCTION create_table_history_requests( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1674,11 +1585,10 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'history_requests'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(history_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 + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',request_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',history_fee_val INT8 NOT NULL' @@ -1689,12 +1599,8 @@ BEGIN ,'PARTITION BY HASH (reserve_pub)' ,shard_suffix ); - END $$; - -------------------------------- purse_deposits ------------------------------- - CREATE OR REPLACE FUNCTION create_table_purse_deposits( IN shard_suffix VARCHAR DEFAULT NULL ) @@ -1704,13 +1610,12 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'purse_deposits'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE - ',partner_serial_id INT8' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE' + '(purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',partner_serial_id INT8' ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)' - ',coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',coin_pub BYTEA NOT NULL' ',amount_with_fee_val INT8 NOT NULL' ',amount_with_fee_frac INT4 NOT NULL' ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)' @@ -1720,19 +1625,14 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by coin_pub! EXECUTE FORMAT ( 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub ' 'ON ' || table_name || ' ' '(coin_pub);' ); - END $$; - CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition( IN partition_suffix VARCHAR ) @@ -1747,36 +1647,32 @@ BEGIN ); END $$; - ----------------------------- wads_out ------------------------------- -CREATE OR REPLACE FUNCTION create_table_wads_out( +CREATE OR REPLACE FUNCTION create_table_wads_in( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR DEFAULT 'wads_out'; + table_name VARCHAR DEFAULT 'wads_in'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' - ',partner_serial_id INT8 NOT NULL' -- REFERENCES partners(partner_serial_id) ON DELETE CASCADE + ',origin_exchange_url TEXT NOT NULL' ',amount_val INT8 NOT NULL' ',amount_frac INT4 NOT NULL' - ',execution_time INT8 NOT NULL' + ',arrival_time INT8 NOT NULL' + ',UNIQUE (wad_id, origin_exchange_url)' ') %s ;' ,table_name ,'PARTITION BY HASH (wad_id)' ,shard_suffix ); - END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( IN partition_suffix VARCHAR ) RETURNS VOID @@ -1784,29 +1680,27 @@ 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) ' + 'ALTER TABLE wads_in_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' + 'UNIQUE (wad_in_serial_id) ' + ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' + 'UNIQUE (wad_id, origin_exchange_url) ' ); END $$; - ---------------------------- wad_out_entries -------------------------- - -CREATE OR REPLACE FUNCTION create_table_wad_out_entries( +CREATE OR REPLACE FUNCTION create_table_wad_in_entries( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR DEFAULT 'wad_out_entries'; + table_name VARCHAR DEFAULT 'wad_in_entries'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_out_serial_id INT8' -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE + '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',wad_in_serial_id INT8' ',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)' @@ -1825,20 +1719,19 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by reserve_pub! EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' 'ON ' || table_name || ' ' '(reserve_pub);' ); - + EXECUTE FORMAT ( + 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' + 'IS ' || quote_literal('needed in reserve history computation') || ';' + ); END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( IN partition_suffix VARCHAR ) RETURNS VOID @@ -1846,44 +1739,37 @@ 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) ' + '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 $$; - --------------------------- wads_in -------------------------------- - -CREATE OR REPLACE FUNCTION create_table_wads_in( +CREATE OR REPLACE FUNCTION create_table_wads_out( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR DEFAULT 'wads_in'; + table_name VARCHAR DEFAULT 'wads_out'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)' - ',origin_exchange_url TEXT NOT NULL' + ',partner_serial_id INT8 NOT NULL' ',amount_val INT8 NOT NULL' ',amount_frac INT4 NOT NULL' - ',arrival_time INT8 NOT NULL' - ',UNIQUE (wad_id, origin_exchange_url)' + ',execution_time INT8 NOT NULL' ') %s ;' ,table_name ,'PARTITION BY HASH (wad_id)' ,shard_suffix ); - END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition( IN partition_suffix VARCHAR ) RETURNS VOID @@ -1891,32 +1777,25 @@ LANGUAGE plpgsql AS $$ BEGIN EXECUTE FORMAT ( - 'ALTER TABLE wads_in_' || partition_suffix || ' ' - 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key ' - 'UNIQUE (wad_in_serial_id) ' - ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key ' - 'UNIQUE (wad_id, origin_exchange_url) ' + 'ALTER TABLE wads_out_' || partition_suffix || ' ' + 'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key ' + 'UNIQUE (wad_out_serial_id) ' ); END $$; - - -------------------------- wads_in_entries -------------------------- - -CREATE OR REPLACE FUNCTION create_table_wad_in_entries( +CREATE OR REPLACE FUNCTION create_table_wad_out_entries( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ DECLARE - table_name VARCHAR DEFAULT 'wad_in_entries'; + table_name VARCHAR DEFAULT 'wad_out_entries'; BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE - ',wad_in_serial_id INT8' -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE + '(wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',wad_out_serial_id INT8' ',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)' @@ -1935,24 +1814,15 @@ BEGIN ,'PARTITION BY HASH (purse_pub)' ,shard_suffix ); - table_name = concat_ws('_', table_name, shard_suffix); - - -- FIXME: change to materialized index by reserve_pub! EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub ' + 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub ' 'ON ' || table_name || ' ' '(reserve_pub);' ); - EXECUTE FORMAT ( - 'COMMENT ON INDEX ' || table_name || '_reserve_pub ' - 'IS ' || quote_literal('needed in reserve history computation') || ';' - ); - END $$; - -CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition( +CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition( IN partition_suffix VARCHAR ) RETURNS VOID @@ -1960,995 +1830,10 @@ LANGUAGE plpgsql AS $$ BEGIN EXECUTE FORMAT ( - 'ALTER TABLE wad_in_entries_' || partition_suffix || ' ' - 'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key ' - 'UNIQUE (wad_in_entry_serial_id) ' - ); -END -$$; - -------------------------------------------------------------------- -------------------------- Partitions ------------------------------ -------------------------------------------------------------------- - - -CREATE OR REPLACE FUNCTION create_range_partition( - source_table_name VARCHAR - ,partition_num INTEGER -) - RETURNS void - LANGUAGE plpgsql -AS $$ -BEGIN - RAISE NOTICE 'TODO'; -END -$$; - -CREATE OR REPLACE FUNCTION detach_default_partitions() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Detaching all default table partitions'; - - ALTER TABLE IF EXISTS wire_targets - DETACH PARTITION wire_targets_default; - - ALTER TABLE IF EXISTS reserves - DETACH PARTITION reserves_default; - - ALTER TABLE IF EXISTS reserves_in - DETACH PARTITION reserves_in_default; - - ALTER TABLE IF EXISTS reserves_close - DETACH PARTITION reserves_close_default; - - ALTER TABLE IF EXISTS history_requests - DETACH partition history_requests_default; - - ALTER TABLE IF EXISTS close_requests - DETACH partition close_requests_default; - - ALTER TABLE IF EXISTS reserves_open_requests - DETACH partition reserves_open_requests_default; - - ALTER TABLE IF EXISTS reserves_out - DETACH PARTITION reserves_out_default; - - ALTER TABLE IF EXISTS reserves_out_by_reserve - DETACH PARTITION reserves_out_by_reserve_default; - - ALTER TABLE IF EXISTS known_coins - DETACH PARTITION known_coins_default; - - ALTER TABLE IF EXISTS refresh_commitments - DETACH PARTITION refresh_commitments_default; - - ALTER TABLE IF EXISTS refresh_revealed_coins - DETACH PARTITION refresh_revealed_coins_default; - - ALTER TABLE IF EXISTS refresh_transfer_keys - DETACH PARTITION refresh_transfer_keys_default; - - ALTER TABLE IF EXISTS deposits - DETACH PARTITION deposits_default; - ---- TODO range partitioning --- ALTER TABLE IF EXISTS deposits_by_ready --- DETACH PARTITION deposits_by_ready_default; --- --- ALTER TABLE IF EXISTS deposits_for_matching --- DETACH PARTITION deposits_default_for_matching_default; - - ALTER TABLE IF EXISTS refunds - DETACH PARTITION refunds_default; - - ALTER TABLE IF EXISTS wire_out - DETACH PARTITION wire_out_default; - - ALTER TABLE IF EXISTS aggregation_transient - DETACH PARTITION aggregation_transient_default; - - ALTER TABLE IF EXISTS aggregation_tracking - DETACH PARTITION aggregation_tracking_default; - - ALTER TABLE IF EXISTS recoup - DETACH PARTITION recoup_default; - - ALTER TABLE IF EXISTS recoup_by_reserve - DETACH PARTITION recoup_by_reserve_default; - - ALTER TABLE IF EXISTS recoup_refresh - DETACH PARTITION recoup_refresh_default; - - ALTER TABLE IF EXISTS prewire - DETACH PARTITION prewire_default; - - ALTER TABLE IF EXISTS cs_nonce_locks - DETACH partition cs_nonce_locks_default; - - ALTER TABLE IF EXISTS purse_requests - DETACH partition purse_requests_default; - - ALTER TABLE IF EXISTS purse_decision - DETACH partition purse_decision_default; - - ALTER TABLE IF EXISTS purse_merges - DETACH partition purse_merges_default; - - ALTER TABLE IF EXISTS account_merges - DETACH partition account_merges_default; - - ALTER TABLE IF EXISTS contracts - DETACH partition contracts_default; - - ALTER TABLE IF EXISTS purse_deposits - DETACH partition purse_deposits_default; - - ALTER TABLE IF EXISTS wad_out_entries - DETACH partition wad_out_entries_default; - - ALTER TABLE IF EXISTS wads_in - DETACH partition wads_in_default; - - ALTER TABLE IF EXISTS wad_in_entries - DETACH partition wad_in_entries_default; -END -$$; - -COMMENT ON FUNCTION detach_default_partitions - IS 'We need to drop default and create new one before deleting the default partitions - otherwise constraints get lost too. Might be needed in sharding too'; - - -CREATE OR REPLACE FUNCTION drop_default_partitions() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Dropping default table partitions'; - - DROP TABLE IF EXISTS wire_targets_default; - DROP TABLE IF EXISTS reserves_default; - DROP TABLE IF EXISTS reserves_in_default; - DROP TABLE IF EXISTS reserves_close_default; - DROP TABLE IF EXISTS reserves_open_requests_default; - DROP TABLE IF EXISTS history_requests_default; - DROP TABLE IF EXISTS close_requests_default; - - DROP TABLE IF EXISTS reserves_out_default; - DROP TABLE IF EXISTS reserves_out_by_reserve_default; - DROP TABLE IF EXISTS known_coins_default; - DROP TABLE IF EXISTS refresh_commitments_default; - DROP TABLE IF EXISTS refresh_revealed_coins_default; - DROP TABLE IF EXISTS refresh_transfer_keys_default; - DROP TABLE IF EXISTS deposits_default; ---DROP TABLE IF EXISTS deposits_by_ready_default; ---DROP TABLE IF EXISTS deposits_for_matching_default; - DROP TABLE IF EXISTS refunds_default; - DROP TABLE IF EXISTS wire_out_default; - DROP TABLE IF EXISTS aggregation_transient_default; - DROP TABLE IF EXISTS aggregation_tracking_default; - DROP TABLE IF EXISTS recoup_default; - DROP TABLE IF EXISTS recoup_by_reserve_default; - DROP TABLE IF EXISTS recoup_refresh_default; - DROP TABLE IF EXISTS prewire_default; - DROP TABLE IF EXISTS cs_nonce_locks_default; - - DROP TABLE IF EXISTS purse_requests_default; - DROP TABLE IF EXISTS purse_decision_default; - DROP TABLE IF EXISTS purse_merges_default; - DROP TABLE IF EXISTS account_merges_default; - DROP TABLE IF EXISTS purse_deposits_default; - DROP TABLE IF EXISTS contracts_default; - - DROP TABLE IF EXISTS wad_out_entries_default; - DROP TABLE IF EXISTS wads_in_default; - DROP TABLE IF EXISTS wad_in_entries_default; - -END -$$; - -COMMENT ON FUNCTION drop_default_partitions - IS 'Drop all default partitions once other partitions are attached. - Might be needed in sharding too.'; - -CREATE OR REPLACE FUNCTION create_partitions( - num_partitions INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - modulus INTEGER; -BEGIN - - modulus := num_partitions; - - PERFORM detach_default_partitions(); - - LOOP - - PERFORM create_hash_partition( - 'wire_targets' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'reserves_in' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_close' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'reserves_out_by_reserve' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'known_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_commitments' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_revealed_coins' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'refresh_transfer_keys' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'deposits' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_deposits_partition(num_partitions::varchar); - --- TODO: dynamically (!) creating/deleting deposits partitions: --- create new partitions 'as needed', drop old ones once the aggregator has made --- them empty; as 'new' deposits will always have deadlines in the future, this --- would basically guarantee no conflict between aggregator and exchange service! --- SEE also: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/ --- (article is slightly wrong, as this works:) ---CREATE TABLE tab ( --- id bigint GENERATED ALWAYS AS IDENTITY, --- ts timestamp NOT NULL, --- data text --- PARTITION BY LIST ((ts::date)); --- CREATE TABLE tab_def PARTITION OF tab DEFAULT; --- BEGIN --- CREATE TABLE tab_part2 (LIKE tab); --- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo'); --- alter table tab attach partition tab_part2 for values in ('2022-03-21'); --- commit; --- Naturally, to ensure this is actually 100% conflict-free, we'd --- need to create tables at the granularity of the wire/refund deadlines; --- that is right now configurable via AGGREGATOR_SHIFT option. - --- FIXME: range partitioning --- PERFORM create_range_partition( --- 'deposits_by_ready' --- ,modulus --- ,num_partitions --- ); --- --- PERFORM create_range_partition( --- 'deposits_for_matching' --- ,modulus --- ,num_partitions --- ); - - PERFORM create_hash_partition( - 'refunds' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wire_out' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'aggregation_transient' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'aggregation_tracking' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'recoup' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'recoup_by_reserve' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'recoup_refresh' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'prewire' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'cs_nonce_locks' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar); - - - PERFORM create_hash_partition( - 'close_requests' - ,modulus - ,num_partitions - ); - - PERFORM create_hash_partition( - 'reserves_open_requests' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_reserves_open_request_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'history_requests' - ,modulus - ,num_partitions - ); - - - ---------------- P2P ---------------------- - - PERFORM create_hash_partition( - 'purse_requests' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_requests_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_decision' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_decision_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_merges' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'account_merges' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_account_merges_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'contracts' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_contracts_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'purse_deposits' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_purse_deposits_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wad_out_entries' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wad_out_entries_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wads_in' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar); - - PERFORM create_hash_partition( - 'wad_in_entries' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wad_in_entries_partition(num_partitions::varchar); - - num_partitions=num_partitions-1; - EXIT WHEN num_partitions=0; - - END LOOP; - - PERFORM drop_default_partitions(); - -END -$$; - ---------------------- Sharding --------------------------- - -CREATE OR REPLACE FUNCTION create_foreign_hash_partition( - source_table_name VARCHAR - ,modulus INTEGER - ,shard_suffix VARCHAR - ,current_shard_num INTEGER - ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' - ) - 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 %I' - ,source_table_name || '_' || shard_suffix - ,local_user - ); - -END -$$; - -CREATE OR REPLACE FUNCTION create_foreign_range_partition( - source_table_name VARCHAR - ,partition_num INTEGER -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - RAISE NOTICE 'TODO'; -END -$$; - -CREATE OR REPLACE FUNCTION 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_policy_details_serial_id_fkey - ,DROP CONSTRAINT IF EXISTS deposits_coin_pub_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 - ; - - ALTER TABLE IF EXISTS purse_requests - DROP CONSTRAINT IF EXISTS purse_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_decision - DROP CONSTRAINT IF EXISTS purse_decision_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_merges - DROP CONSTRAINT IF EXISTS purse_merges_pkey CASCADE - ; - - ALTER TABLE IF EXISTS account_merges - DROP CONSTRAINT IF EXISTS account_merges_pkey CASCADE - ; - - ALTER TABLE IF EXISTS contracts - DROP CONSTRAINT IF EXISTS contracts_pkey CASCADE - ; - - ALTER TABLE IF EXISTS history_requests - DROP CONSTRAINT IF EXISTS history_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS close_requests - DROP CONSTRAINT IF EXISTS close_requests_pkey CASCADE - ; - - ALTER TABLE IF EXISTS purse_deposits - DROP CONSTRAINT IF EXISTS purse_deposits_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wads_out - DROP CONSTRAINT IF EXISTS wads_out_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wad_out_entries - DROP CONSTRAINT IF EXISTS wad_out_entries_pkey CASCADE - ; - - ALTER TABLE IF EXISTS wads_in - DROP CONSTRAINT IF EXISTS wads_in_pkey CASCADE - ,DROP CONSTRAINT IF EXISTS wads_in_wad_id_origin_exchange_url_key - ; - - ALTER TABLE IF EXISTS wad_in_entries - DROP CONSTRAINT IF EXISTS wad_in_entries_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 %', 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 %I SERVER %I ' - 'OPTIONS (user %L, password %L)' - ,local_user - ,shard_suffix - ,remote_user - ,remote_user_password - ); - - EXECUTE FORMAT( - 'GRANT ALL PRIVILEGES ' - 'ON FOREIGN SERVER %I ' - 'TO %I;' - ,shard_suffix - ,local_user - ); - - PERFORM create_foreign_hash_partition( - 'wire_targets' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_in' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_out' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_out_by_reserve' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'reserves_close' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'history_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'close_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'open_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'known_coins' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_commitments' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_revealed_coins' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'refresh_transfer_keys' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'deposits' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); --- PERFORM create_foreign_range_partition( --- 'deposits_by_ready' --- ,total_num_shards --- ,shard_suffix --- ,current_shard_num --- ,local_user --- ); --- PERFORM create_foreign_range_partition( --- 'deposits_for_matching' --- ,total_num_shards --- ,shard_suffix --- ,current_shard_num --- ,local_user --- ); - PERFORM create_foreign_hash_partition( - 'refunds' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wire_out' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'aggregation_transient' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'aggregation_tracking' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup_by_reserve' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'recoup_refresh' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'prewire' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'cs_nonce_locks' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - - ------------------- P2P -------------------- - - PERFORM create_foreign_hash_partition( - 'purse_requests' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'purse_decision' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'purse_merges' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'account_merges' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'contracts' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - - PERFORM create_foreign_hash_partition( - 'purse_deposits' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wad_out_entries' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - PERFORM create_foreign_hash_partition( - 'wads_in' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user + '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) ' ); - PERFORM create_foreign_hash_partition( - 'wad_in_entries' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user - ); - -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 - ,remote_user VARCHAR DEFAULT 'taler' - ,remote_user_password VARCHAR DEFAULT 'taler' -) - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM prepare_sharding(); - - FOR i IN 1..amount LOOP - PERFORM create_shard_server( - i::varchar - ,amount - ,i - ,'shard-' || i::varchar || '.' || domain - ,remote_user - ,remote_user_password - ,'taler-exchange' - ,'5432' - ,'taler-exchange-httpd' - ); - END LOOP; - - PERFORM drop_default_partitions(); - END $$; +COMMIT; diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in index a09ea58f..9d2110c8 100644 --- a/src/exchangedb/exchange-0002.sql.in +++ b/src/exchangedb/exchange-0002.sql.in @@ -25,5 +25,42 @@ SELECT _v.register_patch('exchange-0002', NULL, NULL); SET search_path TO exchange; #include "0002-denominations.sql" +#include "0002-denomination_revocations.sql" +#include "0002-wire_targets.sql" +#include "0002-kyc_alerts.sql" +#include "0002-profit_drains.sql" +#include "0002-legitimization_processes.sql" +#include "0002-legitimization_requirements.sql" +#include "0002-reserves.sql" +#include "0002-reserves_in.sql" +#include "0002-reserves_close.sql" +#include "0002-close_requests.sql" +#include "0002-reserves_open_deposits.sql" +#include "0002-reserves_open_requests.sql" +#include "0002-reserves_out.sql" +#include "0002-known_coins.sql" +#include "0002-refresh_commitments.sql" +#include "0002-refresh_revealed_coins.sql" +#include "0002-refresh_transfer_keys.sql" +#include "0002-deposits.sql" +#include "0002-refunds.sql" +#include "0002-wire_out.sql" +#include "0002-aggregation_transient.sql" +#include "0002-aggregation_tracking.sql" +#include "0002-recoup.sql" +#include "0002-recoup_refresh.sql" +#include "0002-prewire.sql" +#include "0002-cs_nonce_locks.sql" +#include "0002-purse_requests.sql" +#include "0002-purse_merges.sql" +#include "0002-account_merges.sql" +#include "0002-purse_decision.sql" +#include "0002-contracts.sql" +#include "0002-history_requests.sql" +#include "0002-purse_deposits.sql" +#include "0002-wads_in.sql" +#include "0002-wads_in_entries.sql" +#include "0002-wads_out.sql" +#include "0002-wad_out_entries.sql" COMMIT; diff --git a/src/exchangedb/exchange-0001.sql.in b/src/exchangedb/exchange-0003.sql.in index a01ac3a8..ee03d440 100644 --- a/src/exchangedb/exchange-0001.sql.in +++ b/src/exchangedb/exchange-0003.sql.in @@ -18,7 +18,7 @@ BEGIN; -- Check patch versioning is in place. -SELECT _v.register_patch('exchange-0001', NULL, NULL); +SELECT _v.register_patch('exchange-0003', NULL, NULL); -------------------- Schema ---------------------------- @@ -27,7 +27,9 @@ COMMENT ON SCHEMA exchange IS 'taler-exchange data'; SET search_path TO exchange; -#include "common-0001.sql" -#include "exchange-0001-part.sql" + +#include "0003-partner_accounts.sql" +#include "0003-purse_actions.sql" +#include "0003-purse_deletion.sql" COMMIT; diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 2f8b9b57..146d9f8c 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -103,7 +103,6 @@ #include "pg_start.h" #include "pg_rollback.h" #include "pg_create_tables.h" -#include "pg_setup_foreign_servers.h" #include "pg_event_listen.h" #include "pg_event_listen_cancel.h" #include "pg_event_notify.h" |