From ae75ff2cb4f0b7322a9f0451acaadfb7b7514b54 Mon Sep 17 00:00:00 2001 From: Marco Boss Date: Sat, 2 Apr 2022 18:43:49 +0200 Subject: extend sharding logic that shard nodes can be initialied and dropped --- src/exchangedb/.gitignore | 2 + src/exchangedb/Makefile.am | 19 ++- src/exchangedb/common-0001.sql | 208 +++++++++++----------------- src/exchangedb/drop-common.sql | 76 ++++++++++ src/exchangedb/drop0001-exchange-part.sql | 105 ++++++++++++++ src/exchangedb/drop0001-shard-part.sql | 27 ++++ src/exchangedb/drop0001.sql | 160 --------------------- src/exchangedb/exchange-0001-part.sql | 3 + src/exchangedb/plugin_exchangedb_postgres.c | 58 ++++++++ src/exchangedb/shard-0001-part.sql | 199 ++++++++++++++++++++++++++ 10 files changed, 567 insertions(+), 290 deletions(-) create mode 100644 src/exchangedb/drop-common.sql create mode 100644 src/exchangedb/drop0001-exchange-part.sql create mode 100644 src/exchangedb/drop0001-shard-part.sql delete mode 100644 src/exchangedb/drop0001.sql create mode 100644 src/exchangedb/shard-0001-part.sql (limited to 'src/exchangedb') diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index c9ed55470..881bbe53d 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -8,3 +8,5 @@ bench-db-postgres exchange-0001.sql shard-0000.sql shard-0001.sql +drop0001.sql +shard-drop0001.sql \ No newline at end of file diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index f525f341a..0470887de 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -21,27 +21,38 @@ sql_DATA = \ exchange-0001.sql \ shard-0000.sql \ shard-0001.sql \ + shard-drop0001.sql \ drop0001.sql BUILT_SOURCES = \ shard-0000.sql \ shard-0001.sql \ - exchange-0001.sql + exchange-0001.sql \ + drop0001.sql \ + shard-drop0001.sql CLEANFILES = \ shard-0000.sql \ shard-0001.sql \ - exchange-0001.sql + exchange-0001.sql \ + drop0001.sql \ + shard-drop0001.sql exchange-0001.sql: common-0001.sql exchange-0001-part.sql cat common-0001.sql exchange-0001-part.sql >$@ -shard-0001.sql: common-0001.sql - cp common-0001.sql $@ +shard-0001.sql: common-0001.sql shard-0001-part.sql + cat common-0001.sql shard-0001-part.sql >$@ shard-0000.sql: exchange-0000.sql cp exchange-0000.sql $@ +drop0001.sql: drop-common.sql drop0001-exchange-part.sql + cat drop-common.sql drop0001-exchange-part.sql >$@ + +shard-drop0001.sql: drop-common.sql drop0001-shard-part.sql + cat drop-common.sql drop0001-shard-part.sql >$@ + EXTRA_DIST = \ exchangedb.conf \ exchangedb-postgres.conf \ diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql index 20fddd499..b00c4488b 100644 --- a/src/exchangedb/common-0001.sql +++ b/src/exchangedb/common-0001.sql @@ -17,9 +17,6 @@ -- Everything in one big transaction BEGIN; --- Check patch versioning is in place. -SELECT _v.register_patch('exchange-0001', NULL, NULL); - -------------------- Tables ---------------------------- CREATE OR REPLACE FUNCTION create_partitioned_table( @@ -833,7 +830,7 @@ DECLARE table_name VARCHAR DEFAULT 'aggregation_transient'; BEGIN - EXECUTE FORMAT ( + PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' '(amount_val INT8 NOT NULL' ',amount_frac INT4 NOT NULL' @@ -1141,7 +1138,7 @@ $$; ------------------------- Partitions ------------------------------ -CREATE OR REPLACE FUNCTION create_table_partition( +CREATE OR REPLACE FUNCTION create_hash_partition( source_table_name VARCHAR ,modulus INTEGER ,partition_num INTEGER @@ -1166,6 +1163,18 @@ BEGIN END $$; +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 @@ -1303,75 +1312,75 @@ BEGIN LOOP - PERFORM create_table_partition( + PERFORM create_hash_partition( 'wire_targets' ,modulus ,num_partitions ); PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'reserves' ,modulus ,num_partitions ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'reserves_in' ,modulus ,num_partitions ); PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'reserves_close' ,modulus ,num_partitions ); PERFORM add_constraints_to_reserves_close_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'reserves_out' ,modulus ,num_partitions ); PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'reserves_out_by_reserve' ,modulus ,num_partitions ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'known_coins' ,modulus ,num_partitions ); PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'refresh_commitments' ,modulus ,num_partitions ); PERFORM add_constraints_to_refresh_commitments_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'refresh_revealed_coins' ,modulus ,num_partitions ); PERFORM add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'refresh_transfer_keys' ,modulus ,num_partitions ); PERFORM add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'deposits' ,modulus ,num_partitions @@ -1400,72 +1409,72 @@ BEGIN -- that is right now configurable via AGGREGATOR_SHIFT option. -- FIXME: range partitioning --- PERFORM create_table_partition( +-- PERFORM create_range_partition( -- 'deposits_by_ready' -- ,modulus -- ,num_partitions -- ); -- --- PERFORM create_table_partition( +-- PERFORM create_range_partition( -- 'deposits_for_matching' -- ,modulus -- ,num_partitions -- ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'refunds' ,modulus ,num_partitions ); PERFORM add_constraints_to_refunds_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'wire_out' ,modulus ,num_partitions ); PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'aggregation_transient' ,modulus ,num_partitions ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'aggregation_tracking' ,modulus ,num_partitions ); PERFORM add_constraints_to_aggregation_tracking_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'recoup' ,modulus ,num_partitions ); PERFORM add_constraints_to_recoup_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'recoup_by_reserve' ,modulus ,num_partitions ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'recoup_refresh' ,modulus ,num_partitions ); PERFORM add_constraints_to_recoup_refresh_partition(num_partitions::varchar); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'prewire' ,modulus ,num_partitions ); - PERFORM create_table_partition( + PERFORM create_hash_partition( 'cs_nonce_locks' ,modulus ,num_partitions @@ -1484,79 +1493,7 @@ $$; --------------------- Sharding --------------------------- ----------------------- Shards ---------------------------- - -CREATE OR REPLACE FUNCTION setup_shard( - shard_suffix VARCHAR -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM create_table_wire_targets(shard_suffix); - PERFORM add_constraints_to_wire_targets_partition(shard_suffix); - - PERFORM create_table_reserves(shard_suffix); - - PERFORM create_table_reserves_in(shard_suffix); - PERFORM add_constraints_to_reserves_in_partition(shard_suffix); - - PERFORM create_table_reserves_close(shard_suffix); - - PERFORM create_table_reserves_out(shard_suffix); - - PERFORM create_table_reserves_out_by_reserve(shard_suffix); - - PERFORM create_table_known_coins(shard_suffix); - PERFORM add_constraints_to_known_coins_partition(shard_suffix); - - PERFORM create_table_refresh_commitments(shard_suffix); - PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); - - PERFORM create_table_refresh_revealed_coins(shard_suffix); - PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); - - PERFORM create_table_refresh_transfer_keys(shard_suffix); - PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); - - PERFORM create_table_deposits(shard_suffix); - PERFORM add_constraints_to_deposits_partition(shard_suffix); - - PERFORM create_table_deposits_by_ready(shard_suffix); - - PERFORM create_table_deposits_for_matching(shard_suffix); - - PERFORM create_table_refunds(shard_suffix); - PERFORM add_constraints_to_refunds_partition(shard_suffix); - - PERFORM create_table_wire_out(shard_suffix); - PERFORM add_constraints_to_wire_out_partition(shard_suffix); - - PERFORM create_table_aggregation_transient(shard_suffix); - - PERFORM create_table_aggregation_tracking(shard_suffix); - PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); - - PERFORM create_table_recoup(shard_suffix); - PERFORM add_constraints_to_recoup_partition(shard_suffix); - - PERFORM create_table_recoup_by_reserve(shard_suffix); - - PERFORM create_table_recoup_refresh(shard_suffix); - PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); - - PERFORM create_table_prewire(shard_suffix); - - PERFORM create_table_cs_nonce_locks(shard_suffix); - PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); - -END -$$; - ------------------------------- Master ---------------------------------- - -CREATE OR REPLACE FUNCTION create_foreign_table( +CREATE OR REPLACE FUNCTION create_foreign_hash_partition( source_table_name VARCHAR ,modulus INTEGER ,shard_suffix VARCHAR @@ -1591,6 +1528,18 @@ BEGIN 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 @@ -1683,7 +1632,7 @@ $$; CREATE OR REPLACE FUNCTION create_shard_server( - shard_suffix VARCHAR + shard_idx VARCHAR ,total_num_shards INTEGER ,current_shard_num INTEGER ,remote_host VARCHAR @@ -1728,147 +1677,154 @@ BEGIN ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'wire_targets' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'reserves' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'reserves_in' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'reserves_out' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + 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_table( + PERFORM create_foreign_hash_partition( 'known_coins' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'refresh_commitments' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'refresh_revealed_coins' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'refresh_transfer_keys' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'deposits' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); --- PERFORM create_foreign_table( +-- PERFORM create_foreign_range_partition( -- 'deposits_by_ready' -- ,total_num_shards -- ,shard_suffix -- ,current_shard_num -- ,local_user -- ); --- PERFORM create_foreign_table( +-- PERFORM create_foreign_range_partition( -- 'deposits_for_matching' -- ,total_num_shards -- ,shard_suffix -- ,current_shard_num -- ,local_user -- ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'refunds' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'wire_out' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( - 'aggregation_tracking' + PERFORM create_foreign_hash_partition( + 'aggregation_transient' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( - 'recoup' + PERFORM create_foreign_hash_partition( + 'aggregation_tracking' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( - 'recoup_by_reserve' + PERFORM create_foreign_hash_partition( + 'recoup' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( - 'reserves_out_by_reserve' + PERFORM create_foreign_hash_partition( + 'recoup_by_reserve' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'recoup_refresh' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'prewire' ,total_num_shards ,shard_suffix ,current_shard_num ,local_user ); - PERFORM create_foreign_table( + PERFORM create_foreign_hash_partition( 'cs_nonce_locks' ,total_num_shards ,shard_suffix diff --git a/src/exchangedb/drop-common.sql b/src/exchangedb/drop-common.sql new file mode 100644 index 000000000..5c4bddfdb --- /dev/null +++ b/src/exchangedb/drop-common.sql @@ -0,0 +1,76 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2021 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 +-- + +-- Everything in one big transaction +BEGIN; + +-- This script DROPs all of the common functions we create. +-- +-- Unlike the other SQL files, it SHOULD be updated to reflect the +-- latest requirements for dropping tables. + + +DROP FUNCTION IF EXISTS create_table_prewire; +DROP FUNCTION IF EXISTS create_table_recoup; +DROP FUNCTION IF EXISTS add_constraints_to_recoup_partition; +DROP FUNCTION IF EXISTS create_table_recoup_by_reserve; +DROP FUNCTION IF EXISTS create_table_recoup_refresh; +DROP FUNCTION IF EXISTS add_constraints_to_recoup_refresh_partition; +DROP FUNCTION IF EXISTS create_table_aggregation_transient; +DROP FUNCTION IF EXISTS create_table_aggregation_tracking; +DROP FUNCTION IF EXISTS add_constraints_to_aggregation_tracking_partition; +DROP FUNCTION IF EXISTS create_table_wire_out; +DROP FUNCTION IF EXISTS add_constraints_to_wire_out_partition; +DROP FUNCTION IF EXISTS create_table_wire_targets; +DROP FUNCTION IF EXISTS add_constraints_to_wire_targets_partition; +DROP FUNCTION IF EXISTS create_table_deposits; +DROP FUNCTION IF EXISTS create_table_deposits_by_ready; +DROP FUNCTION IF EXISTS create_table_deposits_for_matching; +DROP FUNCTION IF EXISTS add_constraints_to_deposits_partition; +DROP FUNCTION IF EXISTS create_table_refunds; +DROP FUNCTION IF EXISTS add_constraints_to_refunds_partition; +DROP FUNCTION IF EXISTS create_table_refresh_commitments; +DROP FUNCTION IF EXISTS add_constraints_to_refresh_commitments_partition; +DROP FUNCTION IF EXISTS create_table_refresh_revealed_coins; +DROP FUNCTION IF EXISTS add_constraints_to_refresh_revealed_coins_partition; +DROP FUNCTION IF EXISTS create_table_refresh_transfer_keys; +DROP FUNCTION IF EXISTS add_constraints_to_refresh_transfer_keys_partition; +DROP FUNCTION IF EXISTS create_table_known_coins; +DROP FUNCTION IF EXISTS add_constraints_to_known_coins_partition; +DROP FUNCTION IF EXISTS create_table_reserves_close; +DROP FUNCTION IF EXISTS add_constraints_to_reserves_close_partition; +DROP FUNCTION IF EXISTS create_table_reserves_out; +DROP FUNCTION IF EXISTS create_table_reserves_out_by_reserve; +DROP FUNCTION IF EXISTS add_constraints_to_reserves_out_partition; +DROP FUNCTION IF EXISTS create_table_reserves_in; +DROP FUNCTION IF EXISTS add_constraints_to_reserves_in_partition; +DROP FUNCTION IF EXISTS create_table_reserves; +DROP FUNCTION IF EXISTS create_table_cs_nonce_locks; +DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition; + +DROP FUNCTION IF EXISTS create_partitioned_table; +DROP FUNCTION IF EXISTS create_hash_partition; +DROP FUNCTION IF EXISTS create_range_partition; +DROP FUNCTION IF EXISTS create_partitions; +DROP FUNCTION IF EXISTS detach_default_partitions; +DROP FUNCTION IF EXISTS drop_default_partitions; +DROP FUNCTION IF EXISTS prepare_sharding; +DROP FUNCTION IF EXISTS create_foreign_hash_partition; +DROP FUNCTION IF EXISTS create_foreign_range_partition; +DROP FUNCTION IF EXISTS create_foreign_servers; +DROP FUNCTION IF EXISTS create_shard_server; + +COMMIT; \ No newline at end of file diff --git a/src/exchangedb/drop0001-exchange-part.sql b/src/exchangedb/drop0001-exchange-part.sql new file mode 100644 index 000000000..a5a399eb4 --- /dev/null +++ b/src/exchangedb/drop0001-exchange-part.sql @@ -0,0 +1,105 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2021 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 +-- + +BEGIN; + +-- Unregister patch (exchange-0001.sql) +SELECT _v.unregister_patch('exchange-0001'); + + +-- Drops for exchange-0001-part.sql +DROP TRIGGER IF EXISTS reserves_out_on_insert ON reserves_out; +DROP TRIGGER IF EXISTS reserves_out_on_delete ON reserves_out; +DROP TRIGGER IF EXISTS deposits_on_insert ON deposits; +DROP TRIGGER IF EXISTS deposits_on_delete ON deposits; +DROP TRIGGER IF EXISTS recoup_on_insert ON recoup; +DROP TRIGGER IF EXISTS recoup_on_delete ON recoup; + +DROP TABLE IF EXISTS revolving_work_shards CASCADE; +DROP TABLE IF EXISTS extensions CASCADE; +DROP TABLE IF EXISTS auditors CASCADE; +DROP TABLE IF EXISTS auditor_denom_sigs CASCADE; +DROP TABLE IF EXISTS exchange_sign_keys CASCADE; +DROP TABLE IF EXISTS wire_accounts CASCADE; +DROP TABLE IF EXISTS signkey_revocations CASCADE; +DROP TABLE IF EXISTS work_shards CASCADE; +DROP TABLE IF EXISTS prewire CASCADE; +DROP TABLE IF EXISTS recoup CASCADE; +DROP TABLE IF EXISTS recoup_refresh CASCADE; +DROP TABLE IF EXISTS aggregation_transient CASCADE; +DROP TABLE IF EXISTS aggregation_tracking CASCADE; +DROP TABLE IF EXISTS wire_out CASCADE; +DROP TABLE IF EXISTS wire_targets CASCADE; +DROP TABLE IF EXISTS wire_fee CASCADE; +DROP TABLE IF EXISTS deposits CASCADE; +DROP TABLE IF EXISTS deposits_by_ready CASCADE; +DROP TABLE IF EXISTS deposits_for_matching CASCADE; +DROP TABLE IF EXISTS extension_details CASCADE; +DROP TABLE IF EXISTS refunds CASCADE; +DROP TABLE IF EXISTS refresh_commitments CASCADE; +DROP TABLE IF EXISTS refresh_revealed_coins CASCADE; +DROP TABLE IF EXISTS refresh_transfer_keys CASCADE; +DROP TABLE IF EXISTS known_coins CASCADE; +DROP TABLE IF EXISTS reserves_close CASCADE; +DROP TABLE IF EXISTS reserves_out CASCADE; +DROP TABLE IF EXISTS reserves_out_by_reserve CASCADE; +DROP TABLE IF EXISTS reserves_in CASCADE; +DROP TABLE IF EXISTS reserves CASCADE; +DROP TABLE IF EXISTS denomination_revocations CASCADE; +DROP TABLE IF EXISTS denominations CASCADE; +DROP TABLE IF EXISTS cs_nonce_locks CASCADE; +DROP TABLE IF EXISTS global_fee CASCADE; +DROP TABLE IF EXISTS recoup_by_reserve CASCADE; + + +DROP TABLE IF EXISTS partners CASCADE; +DROP TABLE IF EXISTS account_merges CASCADE; +DROP TABLE IF EXISTS purse_merges CASCADE; +DROP TABLE IF EXISTS purse_deposits CASCADE; +DROP TABLE IF EXISTS contracts CASCADE; +DROP TABLE IF EXISTS history_requests CASCADE; +DROP TABLE IF EXISTS close_requests CASCADE; +DROP TABLE IF EXISTS purse_requests CASCADE; +DROP TABLE IF EXISTS wads_out CASCADE; +DROP TABLE IF EXISTS wad_out_entries CASCADE; +DROP TABLE IF EXISTS wads_in CASCADE; +DROP TABLE IF EXISTS wad_in_entries CASCADE; +DROP TABLE IF EXISTS partner_accounts CASCADE; + + +DROP FUNCTION IF EXISTS exchange_do_withdraw; +DROP FUNCTION IF EXISTS exchange_do_withdraw_limit_check; +DROP FUNCTION IF EXISTS recoup_insert_trigger; +DROP FUNCTION IF EXISTS recoup_delete_trigger; +DROP FUNCTION IF EXISTS deposits_insert_trigger; +DROP FUNCTION IF EXISTS deposits_update_trigger; +DROP FUNCTION IF EXISTS deposits_delete_trigger; +DROP FUNCTION IF EXISTS reserves_out_by_reserve_insert_trigger; +DROP FUNCTION IF EXISTS reserves_out_by_reserve_delete_trigger; +DROP FUNCTION IF EXISTS exchange_do_deposit; +DROP FUNCTION IF EXISTS exchange_do_melt; +DROP FUNCTION IF EXISTS exchange_do_refund; +DROP FUNCTION IF EXISTS exchange_do_recoup_to_coin; +DROP FUNCTION IF EXISTS exchange_do_recoup_to_reserve; +DROP FUNCTION IF EXISTS exchange_do_purse_deposit; +DROP FUNCTION IF EXISTS exchange_do_purse_merge; +DROP FUNCTION IF EXISTS exchange_do_account_merge; +DROP FUNCTION IF EXISTS exchange_do_history_request; +DROP FUNCTION IF EXISTS exchange_do_close_request; + +-- And we're out of here... + +COMMIT; diff --git a/src/exchangedb/drop0001-shard-part.sql b/src/exchangedb/drop0001-shard-part.sql new file mode 100644 index 000000000..9cf3eeb3a --- /dev/null +++ b/src/exchangedb/drop0001-shard-part.sql @@ -0,0 +1,27 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2021 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 +-- + +BEGIN; + +-- Unregister patch (shard-0001.sql) +SELECT _v.unregister_patch('shard-0001'); + +-- Drops for shard-0001-part.sql + +DROP FUNCTION IF EXISTS drop_shard; +DROP FUNCTION IF EXISTS setup_shard; + +COMMIT; diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql deleted file mode 100644 index ab0e75234..000000000 --- a/src/exchangedb/drop0001.sql +++ /dev/null @@ -1,160 +0,0 @@ --- --- This file is part of TALER --- Copyright (C) 2014--2021 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 --- - --- Everything in one big transaction -BEGIN; - --- This script DROPs all of the tables we create. --- --- Unlike the other SQL files, it SHOULD be updated to reflect the --- latest requirements for dropping tables. - - --- Unregister patch (exchange-0001.sql) -SELECT _v.unregister_patch('exchange-0001'); - --- Drops for exchange-0001.sql -DROP TRIGGER IF EXISTS reserves_out_on_insert ON reserves_out; -DROP TRIGGER IF EXISTS reserves_out_on_delete ON reserves_out; -DROP TRIGGER IF EXISTS deposits_on_insert ON deposits; -DROP TRIGGER IF EXISTS deposits_on_delete ON deposits; -DROP TRIGGER IF EXISTS recoup_on_insert ON recoup; -DROP TRIGGER IF EXISTS recoup_on_delete ON recoup; - -DROP TABLE IF EXISTS revolving_work_shards CASCADE; -DROP TABLE IF EXISTS extensions CASCADE; -DROP TABLE IF EXISTS auditors CASCADE; -DROP TABLE IF EXISTS auditor_denom_sigs CASCADE; -DROP TABLE IF EXISTS exchange_sign_keys CASCADE; -DROP TABLE IF EXISTS wire_accounts CASCADE; -DROP TABLE IF EXISTS signkey_revocations CASCADE; -DROP TABLE IF EXISTS work_shards CASCADE; -DROP TABLE IF EXISTS prewire CASCADE; -DROP FUNCTION IF EXISTS create_table_prewire; -DROP TABLE IF EXISTS recoup CASCADE; -DROP FUNCTION IF EXISTS create_table_recoup; -DROP FUNCTION IF EXISTS add_constraints_to_recoup_partition; -DROP TABLE IF EXISTS recoup_refresh CASCADE; -DROP FUNCTION IF EXISTS create_table_recoup_refresh; -DROP FUNCTION IF EXISTS add_constraints_to_recoup_refresh_partition; -DROP TABLE IF EXISTS aggregation_transient CASCADE; -DROP FUNCTION IF EXISTS create_table_aggregation_transient; -DROP TABLE IF EXISTS aggregation_tracking CASCADE; -DROP FUNCTION IF EXISTS create_table_aggregation_tracking; -DROP FUNCTION IF EXISTS add_constraints_to_aggregation_tracking_partition; -DROP TABLE IF EXISTS wire_out CASCADE; -DROP FUNCTION IF EXISTS create_table_wire_out; -DROP FUNCTION IF EXISTS add_constraints_to_wire_out_partition; -DROP TABLE IF EXISTS wire_targets CASCADE; -DROP FUNCTION IF EXISTS create_table_wire_targets; -DROP FUNCTION IF EXISTS add_constraints_to_wire_targets_partition; -DROP TABLE IF EXISTS wire_fee CASCADE; -DROP TABLE IF EXISTS deposits CASCADE; -DROP FUNCTION IF EXISTS create_table_deposits; -DROP TABLE IF EXISTS deposits_by_ready CASCADE; -DROP FUNCTION IF EXISTS create_table_deposits_by_ready; -DROP TABLE IF EXISTS deposits_for_matching CASCADE; -DROP FUNCTION IF EXISTS create_table_deposits_for_matching; -DROP FUNCTION IF EXISTS add_constraints_to_deposits_partition; -DROP TABLE IF EXISTS extension_details CASCADE; -DROP TABLE IF EXISTS refunds CASCADE; -DROP FUNCTION IF EXISTS create_table_refunds; -DROP FUNCTION IF EXISTS add_constraints_to_refunds_partition; -DROP TABLE IF EXISTS refresh_commitments CASCADE; -DROP FUNCTION IF EXISTS create_table_refresh_commitments; -DROP FUNCTION IF EXISTS add_constraints_to_refresh_commitments_partition; -DROP TABLE IF EXISTS refresh_revealed_coins CASCADE; -DROP FUNCTION IF EXISTS create_table_refresh_revealed_coins; -DROP FUNCTION IF EXISTS add_constraints_to_refresh_revealed_coins_partition; -DROP TABLE IF EXISTS refresh_transfer_keys CASCADE; -DROP FUNCTION IF EXISTS create_table_refresh_transfer_keys; -DROP FUNCTION IF EXISTS add_constraints_to_refresh_transfer_keys_partition; -DROP TABLE IF EXISTS known_coins CASCADE; -DROP FUNCTION IF EXISTS create_table_known_coins; -DROP FUNCTION IF EXISTS add_constraints_to_known_coins_partition; -DROP TABLE IF EXISTS reserves_close CASCADE; -DROP FUNCTION IF EXISTS create_table_reserves_close; -DROP FUNCTION IF EXISTS add_constraints_to_reserves_close_partition; -DROP TABLE IF EXISTS reserves_out CASCADE; -DROP FUNCTION IF EXISTS create_table_reserves_out; -DROP TABLE IF EXISTS reserves_out_by_reserve CASCADE; -DROP FUNCTION IF EXISTS create_table_reserves_out_by_reserve; -DROP FUNCTION IF EXISTS add_constraints_to_reserves_out_partition; -DROP TABLE IF EXISTS reserves_in CASCADE; -DROP FUNCTION IF EXISTS create_table_reserves_in; -DROP FUNCTION IF EXISTS add_constraints_to_reserves_in_partition; -DROP TABLE IF EXISTS reserves CASCADE; -DROP FUNCTION IF EXISTS create_table_reserves; -DROP TABLE IF EXISTS denomination_revocations CASCADE; -DROP TABLE IF EXISTS denominations CASCADE; -DROP TABLE IF EXISTS cs_nonce_locks CASCADE; -DROP FUNCTION IF EXISTS create_table_cs_nonce_locks; -DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition; - -DROP TABLE IF EXISTS global_fee CASCADE; -DROP TABLE IF EXISTS recoup_by_reserve CASCADE; -DROP TABLE IF EXISTS aggregation_transient CASCADE; - - -DROP TABLE IF EXISTS partners CASCADE; -DROP TABLE IF EXISTS account_merges CASCADE; -DROP TABLE IF EXISTS purse_merges CASCADE; -DROP TABLE IF EXISTS purse_deposits CASCADE; -DROP TABLE IF EXISTS contracts CASCADE; -DROP TABLE IF EXISTS history_requests CASCADE; -DROP TABLE IF EXISTS close_requests CASCADE; -DROP TABLE IF EXISTS purse_requests CASCADE; -DROP TABLE IF EXISTS wads_out CASCADE; -DROP TABLE IF EXISTS wad_out_entries CASCADE; -DROP TABLE IF EXISTS wads_in CASCADE; -DROP TABLE IF EXISTS wad_in_entries CASCADE; -DROP TABLE IF EXISTS partner_accounts CASCADE; - - -DROP FUNCTION IF EXISTS exchange_do_withdraw; -DROP FUNCTION IF EXISTS exchange_do_withdraw_limit_check; -DROP FUNCTION IF EXISTS recoup_insert_trigger; -DROP FUNCTION IF EXISTS recoup_delete_trigger; -DROP FUNCTION IF EXISTS deposits_insert_trigger; -DROP FUNCTION IF EXISTS deposits_update_trigger; -DROP FUNCTION IF EXISTS deposits_delete_trigger; -DROP FUNCTION IF EXISTS reserves_out_by_reserve_insert_trigger; -DROP FUNCTION IF EXISTS reserves_out_by_reserve_delete_trigger; -DROP FUNCTION IF EXISTS exchange_do_deposit; -DROP FUNCTION IF EXISTS exchange_do_melt; -DROP FUNCTION IF EXISTS exchange_do_refund; -DROP FUNCTION IF EXISTS exchange_do_recoup_to_coin; -DROP FUNCTION IF EXISTS exchange_do_recoup_to_reserve; -DROP FUNCTION IF EXISTS exchange_do_purse_deposit; -DROP FUNCTION IF EXISTS exchange_do_purse_merge; -DROP FUNCTION IF EXISTS exchange_do_account_merge; -DROP FUNCTION IF EXISTS exchange_do_history_request; -DROP FUNCTION IF EXISTS exchange_do_close_request; - - -DROP FUNCTION IF EXISTS create_table_partition; -DROP FUNCTION IF EXISTS create_partitions; -DROP FUNCTION IF EXISTS detach_default_partitions; -DROP FUNCTION IF EXISTS drop_default_partitions; -DROP FUNCTION IF EXISTS prepare_sharding; -DROP FUNCTION IF EXISTS create_foreign_table; -DROP FUNCTION IF EXISTS create_foreign_servers; -DROP FUNCTION IF EXISTS create_shard_server; - - --- And we're out of here... - -COMMIT; diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index 359fe9f81..a478a0d16 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -17,6 +17,9 @@ -- Everything in one big transaction BEGIN; +-- Check patch versioning is in place. +SELECT _v.register_patch('exchange-0001', NULL, NULL); + -- ------------------------------ denominations ---------------------------------------- CREATE TABLE IF NOT EXISTS denominations diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 53b84ce80..9ba373707 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -174,6 +174,63 @@ postgres_drop_tables (void *cls) } +/** + * Drop all Taler shard tables. This should only be used by testcases. + * + * @param cls the `struct PostgresClosure` with the plugin-specific state + * @param old_idx the index which was used when the shard database was initialized + * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure + */ +static enum GNUNET_GenericReturnValue +postgres_drop_shard_tables (void *cls, + uint32_t old_idx) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_Context *conn; + enum GNUNET_GenericReturnValue ret = GNUNET_OK; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_uint32 (&old_idx), + GNUNET_PQ_query_param_end + }; + struct GNUNET_PQ_PreparedStatement ps[] = { + GNUNET_PQ_make_prepare ("drop_shard_tables", + "SELECT" + " drop_shard" + " ($1);", + 1), + GNUNET_PQ_PREPARED_STATEMENT_END + }; + conn = GNUNET_PQ_connect_with_cfg (pg->cfg, + "exchangedb-postgres", + NULL, + NULL, + ps); + if (NULL == conn) + return GNUNET_SYSERR; + if (0 > GNUNET_PQ_eval_prepared_non_select (conn, + "drop_shard_tables", + params)) + ret = GNUNET_SYSERR; + GNUNET_PQ_disconnect (conn); + + conn = GNUNET_PQ_connect_with_cfg (pg->cfg, + "exchangedb-postgres", + "shard-drop", + NULL, + NULL); + if (NULL == conn) + return GNUNET_SYSERR; + GNUNET_PQ_disconnect (conn); + if (NULL != pg->conn) + { + GNUNET_PQ_disconnect (pg->conn); + pg->conn = NULL; + pg->init = false; + } + return ret; +} + + /** * Create the necessary tables if they are not present * @@ -13140,6 +13197,7 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) plugin = GNUNET_new (struct TALER_EXCHANGEDB_Plugin); plugin->cls = pg; plugin->drop_tables = &postgres_drop_tables; + plugin->drop_shard_tables = &postgres_drop_shard_tables; plugin->create_tables = &postgres_create_tables; plugin->create_shard_tables = &postgres_create_shard_tables; plugin->setup_partitions = &postgres_setup_partitions; diff --git a/src/exchangedb/shard-0001-part.sql b/src/exchangedb/shard-0001-part.sql new file mode 100644 index 000000000..d971b0c7a --- /dev/null +++ b/src/exchangedb/shard-0001-part.sql @@ -0,0 +1,199 @@ +-- +-- 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 +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('shard-0001', NULL, NULL); + +CREATE OR REPLACE FUNCTION setup_shard( + shard_idx INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + shard_suffix VARCHAR; +BEGIN + + shard_suffix = shard_idx::varchar; + + PERFORM create_table_wire_targets(shard_suffix); + PERFORM add_constraints_to_wire_targets_partition(shard_suffix); + + PERFORM create_table_reserves(shard_suffix); + + PERFORM create_table_reserves_in(shard_suffix); + PERFORM add_constraints_to_reserves_in_partition(shard_suffix); + + PERFORM create_table_reserves_close(shard_suffix); + + PERFORM create_table_reserves_out(shard_suffix); + + PERFORM create_table_reserves_out_by_reserve(shard_suffix); + + PERFORM create_table_known_coins(shard_suffix); + PERFORM add_constraints_to_known_coins_partition(shard_suffix); + + PERFORM create_table_refresh_commitments(shard_suffix); + PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix); + + PERFORM create_table_refresh_revealed_coins(shard_suffix); + PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix); + + PERFORM create_table_refresh_transfer_keys(shard_suffix); + PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix); + + PERFORM create_table_deposits(shard_suffix); + PERFORM add_constraints_to_deposits_partition(shard_suffix); + + PERFORM create_table_deposits_by_ready(shard_suffix); + + PERFORM create_table_deposits_for_matching(shard_suffix); + + PERFORM create_table_refunds(shard_suffix); + PERFORM add_constraints_to_refunds_partition(shard_suffix); + + PERFORM create_table_wire_out(shard_suffix); + PERFORM add_constraints_to_wire_out_partition(shard_suffix); + + PERFORM create_table_aggregation_transient(shard_suffix); + + PERFORM create_table_aggregation_tracking(shard_suffix); + PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix); + + PERFORM create_table_recoup(shard_suffix); + PERFORM add_constraints_to_recoup_partition(shard_suffix); + + PERFORM create_table_recoup_by_reserve(shard_suffix); + + PERFORM create_table_recoup_refresh(shard_suffix); + PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix); + + PERFORM create_table_prewire(shard_suffix); + + PERFORM create_table_cs_nonce_locks(shard_suffix); + PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix); + +END +$$; + + +CREATE OR REPLACE FUNCTION drop_shard( + shard_idx INTEGER +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + shard_suffix VARCHAR; +BEGIN + + shard_suffix = shard_idx::varchar; + + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'wire_targets_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'reserves_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'reserves_in_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'reserves_out_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'reserves_close_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'known_coins_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'refresh_commitments_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'refresh_revealed_coins_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'refresh_transfer_keys_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'deposits_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'deposits_by_ready_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'deposits_for_matching_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'refunds_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'wire_out_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'aggregation_transient_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'aggregation_tracking_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'recoup_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'recoup_by_reserve_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'reserves_out_by_reserve_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'recoup_refresh_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'prewire_' || shard_suffix + ); + EXECUTE FORMAT( + 'DROP TABLE IF EXISTS %I CASCADE' + ,'cs_nonce_locks_' || shard_suffix + ); +END +$$; + +COMMIT; -- cgit v1.2.3