summaryrefslogtreecommitdiff
path: root/src/exchangedb/common-0001.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/exchangedb/common-0001.sql')
-rw-r--r--src/exchangedb/common-0001.sql2804
1 files changed, 0 insertions, 2804 deletions
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
deleted file mode 100644
index 76185572c..000000000
--- a/src/exchangedb/common-0001.sql
+++ /dev/null
@@ -1,2804 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
---
--- TALER is free software; you can redistribute it and/or modify it under the
--- terms of the GNU General Public License as published by the Free Software
--- Foundation; either version 3, or (at your option) any later version.
---
--- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
--- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
--- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
---
--- You should have received a copy of the GNU General Public License along with
--- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
---
-
--------------------- Tables ----------------------------
-
-CREATE OR REPLACE FUNCTION create_partitioned_table(
- IN table_definition VARCHAR
- ,IN table_name VARCHAR
- ,IN main_table_partition_str VARCHAR -- Used only when it is the main table - we do not partition shard tables
- ,IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- IF shard_suffix IS NOT NULL THEN
- table_name=table_name || '_' || shard_suffix;
- main_table_partition_str = '';
- END IF;
-
- EXECUTE FORMAT(
- table_definition,
- table_name,
- main_table_partition_str
- );
-
-END
-$$;
-
------------------------ wire_targets ---------------------------
-
-CREATE OR REPLACE FUNCTION create_table_wire_targets(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)'
- ',payto_uri VARCHAR NOT NULL'
- ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)' -- FIXME: REMOVE!
- ',external_id VARCHAR' -- FIXME: REMOVE!
- ') %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
-$$;
-
-
------------------------ legitimizations ---------------------------
-
-CREATE OR REPLACE FUNCTION create_table_legitimizations(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(legitimization_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=64)'
- ',expiration_time INT8 NOT NULL DEFAULT (0)'
- ',provider_section VARCHAR NOT NULL'
- ',provider_user_id VARCHAR DEFAULT NULL'
- ',provider_legitimization_id VARCHAR DEFAULT NULL'
- ') %s ;'
- ,'legitimizations'
- ,'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_legitimizations_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-DECLARE
- partition_name VARCHAR;
-BEGIN
-
- partition_name = concat_ws('_', 'legitimizations', partition_suffix);
-
- EXECUTE FORMAT (
- 'ALTER TABLE ' || partition_name
- || ' '
- 'ADD CONSTRAINT ' || partition_name || '_legitimization_serial_id_key '
- 'UNIQUE (legitimization_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
-$$;
-
------------------------- reserves -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_reserves(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'reserves';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
- ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)'
- ',current_balance_val INT8 NOT NULL DEFAULT(0)'
- ',current_balance_frac INT4 NOT NULL DEFAULT(0)'
- ',purses_active INT8 NOT NULL DEFAULT(0)'
- ',purses_allowed INT8 NOT NULL DEFAULT(0)'
- -- FIXME: remove:
- ',kyc_required BOOLEAN NOT NULL DEFAULT(FALSE)'
- -- FIXME: remove:
- ',kyc_passed BOOLEAN NOT NULL DEFAULT(FALSE)'
- ',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
-$$;
-
------------------------ reserves_in ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_reserves_in(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR default 'reserves_in';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
- ',wire_reference INT8 NOT NULL'
- ',credit_val INT8 NOT NULL'
- ',credit_frac INT4 NOT NULL'
- ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)'
- ',exchange_account_section TEXT NOT NULL'
- ',execution_date INT8 NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_in_serial_id_index '
- 'ON ' || table_name || ' '
- '(reserve_in_serial_id);'
- );
- -- FIXME: where do we need this index? Can we do better?
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_section_execution_date_idx '
- 'ON ' || table_name || ' '
- '(exchange_account_section '
- ',execution_date'
- ');'
- );
- -- FIXME: where do we need this index? Can we do better?
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_exch_accnt_reserve_in_serial_id_idx '
- 'ON ' || table_name || ' '
- '(exchange_account_section,'
- 'reserve_in_serial_id DESC'
- ');'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_in_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_in_' || partition_suffix || '_reserve_in_serial_id_key '
- 'UNIQUE (reserve_in_serial_id)'
- );
-END
-$$;
-
---------------------------- reserves_close -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_reserves_close(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR default 'reserves_close';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY'
- ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
- ',execution_date INT8 NOT NULL'
- ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
- ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',closing_fee_val INT8 NOT NULL'
- ',closing_fee_frac INT4 NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index '
- 'ON ' || table_name || ' '
- '(close_uuid);'
- );
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index '
- 'ON ' || table_name || ' '
- '(reserve_pub);'
- );
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_close_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_close_' || partition_suffix || '_close_uuid_pkey '
- 'PRIMARY KEY (close_uuid)'
- );
-END
-$$;
-
----------------------------- reserves_out -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_reserves_out(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR default 'reserves_out';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)'
- ',denom_sig BYTEA NOT NULL'
- ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
- ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
- ',execution_date INT8 NOT NULL'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ') %s ;'
- ,'reserves_out'
- ,'PARTITION BY HASH (h_blind_ev)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_out_serial_id_index '
- 'ON ' || table_name || ' '
- '(reserve_out_serial_id);'
- );
- -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well???
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_and_execution_date_index '
- 'ON ' || table_name || ' '
- '(reserve_uuid, execution_date);'
- );
- EXECUTE FORMAT (
- 'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index '
- 'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';'
- );
-
-END
-$$;
-
-
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_out_' || partition_suffix || '_reserve_out_serial_id_key '
- 'UNIQUE (reserve_out_serial_id)'
- );
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'reserves_out_by_reserve';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
- ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)'
- ') %s '
- ,table_name
- ,'PARTITION BY HASH (reserve_uuid)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
- 'ON ' || table_name || ' '
- '(reserve_uuid);'
- );
-
-END
-$$;
-
----------------------------- known_coins -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_known_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR default 'known_coins';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
- ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)'
- ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)'
- ',denom_sig BYTEA NOT NULL'
- ',remaining_val INT8 NOT NULL 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
-$$;
-
----------------------------- refresh_commitments -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_refresh_commitments(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'refresh_commitments';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)'
- ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
- ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ',noreveal_index INT4 NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (rc)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- -- Note: index spans partitions, may need to be materialized.
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index '
- 'ON ' || table_name || ' '
- '(old_coin_pub);'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_commitments_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_commitments_' || partition_suffix || '_melt_serial_id_key '
- 'UNIQUE (melt_serial_id)'
- );
-END
-$$;
-
------------------------------- refresh_revealed_coins --------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'refresh_revealed_coins';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
- ',freshcoin_index INT4 NOT NULL'
- ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)'
- ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
- ',coin_ev BYTEA NOT NULL' -- UNIQUE'
- ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE'
- ',ev_sig BYTEA NOT NULL'
- ',ewv BYTEA NOT NULL'
- -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_coins_by_melt_serial_id_index '
- 'ON ' || table_name || ' '
- '(melt_serial_id);'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_rrc_serial_key '
- 'UNIQUE (rrc_serial) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_coin_ev_key '
- 'UNIQUE (coin_ev) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix || '_h_coin_ev_key '
- 'UNIQUE (h_coin_ev) '
- ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) '
- );
-END
-$$;
-
------------------------------ refresh_transfer_keys ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'refresh_transfer_keys';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
- ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)'
- ',transfer_privs BYTEA NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (melt_serial_id)'
- ,shard_suffix
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix || '_rtc_serial_key '
- 'UNIQUE (rtc_serial)'
- );
-END
-$$;
-
----------------------------- deposits -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_deposits(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'deposits';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
- ',shard INT8 NOT NULL'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE
- ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE' --- FIXME: column needed???
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- ',wallet_timestamp INT8 NOT NULL'
- ',exchange_timestamp INT8 NOT NULL'
- ',refund_deadline INT8 NOT NULL'
- ',wire_deadline INT8 NOT NULL'
- ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
- ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)'
- ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)'
- ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)'
- ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
- ',done BOOLEAN NOT NULL DEFAULT FALSE'
- ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE'
- ',extension_details_serial_id INT8' -- REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE'
- ') %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
-$$;
-
------------------------------ refunds ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_refunds(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'refunds';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE
- ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
- ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
- ',rtransaction_id INT8 NOT NULL'
- ',amount_with_fee_val INT8 NOT NULL'
- ',amount_with_fee_frac INT4 NOT NULL'
- -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
- 'ON ' || table_name || ' '
- '(coin_pub);'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refunds_' || partition_suffix || ' '
- 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key '
- 'UNIQUE (refund_serial_id) '
- ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
- );
-END
-$$;
-
----------------------------- wire_out -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_wire_out(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'wire_out';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
- ',execution_date INT8 NOT NULL'
- ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)'
- ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
- ',exchange_account_section TEXT NOT NULL'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (wtid_raw)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wire_target_h_payto_index '
- 'ON ' || table_name || ' '
- '(wire_target_h_payto);'
- );
-
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE wire_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey '
- 'PRIMARY KEY (wireout_uuid)'
- );
-END
-$$;
-
----------------------------- aggregation_transient ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_aggregation_transient(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'aggregation_transient';
-BEGIN
-
- 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)'
- ',exchange_account_section TEXT NOT NULL'
- ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (wire_target_h_payto)'
- ,shard_suffix
- );
-
-END
-$$;
-
----------------------------- aggregation_tracking -------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_aggregation_tracking(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'aggregation_tracking';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME 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
-$$;
-
------------------------------ recoup ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_recoup(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'recoup';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',recoup_timestamp INT8 NOT NULL'
- ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (coin_pub);'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
- 'ON ' || table_name || ' '
- '(coin_pub);'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE recoup_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key '
- 'UNIQUE (recoup_uuid) '
- );
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'recoup_by_reserve';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE
- ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (reserve_out_serial_id)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
- 'ON ' || table_name || ' '
- '(reserve_out_serial_id);'
- );
-
-END
-$$;
-
----------------------------- recoup_refresh ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'recoup_refresh';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins (coin_pub)
- ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
- ',amount_val INT8 NOT NULL'
- ',amount_frac INT4 NOT NULL'
- ',recoup_timestamp INT8 NOT NULL'
- ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (coin_pub)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- -- FIXME: any query using this index will be slow. Materialize index or change query?
- -- Also: which query uses this index?
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index '
- 'ON ' || table_name || ' '
- '(rrc_serial);'
- );
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
- 'ON ' || table_name || ' '
- '(coin_pub);'
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE recoup_refresh_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_refresh_' || partition_suffix || '_recoup_refresh_uuid_key '
- 'UNIQUE (recoup_refresh_uuid) '
- );
-END
-$$;
-
------------------------------ prewire ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_prewire(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'prewire';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
- ',wire_method TEXT NOT NULL'
- ',finished BOOLEAN NOT NULL DEFAULT false'
- ',failed BOOLEAN NOT NULL DEFAULT false'
- ',buf BYTEA NOT NULL'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (prewire_uuid)'
- ,shard_suffix
- );
-
- table_name = concat_ws('_', table_name, shard_suffix);
-
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index '
- 'ON ' || table_name || ' '
- '(finished);'
- );
- EXECUTE FORMAT (
- 'COMMENT ON INDEX ' || table_name || '_by_finished_index '
- 'IS ' || quote_literal('for gc_prewire') || ';'
- );
- -- FIXME: find a way to combine these two indices?
- EXECUTE FORMAT (
- 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index '
- 'ON ' || table_name || ' '
- '(failed,finished);'
- );
- EXECUTE FORMAT (
- 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index '
- 'IS ' || quote_literal('for wire_prepare_data_get') || ';'
- );
-
-END
-$$;
-
------------------------------ cs_nonce_locks ------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks(
- shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I'
- '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
- ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)'
- ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)'
- ',max_denomination_serial INT8 NOT NULL'
- ') %s ;'
- ,'cs_nonce_locks'
- ,'PARTITION BY HASH (nonce)'
- ,shard_suffix
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' '
- 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix || '_cs_nonce_lock_serial_id_key '
- 'UNIQUE (cs_nonce_lock_serial_id)'
- );
-END
-$$;
-
---------------------------------------------------------------------------
--- Tables for P2P payments
---------------------------------------------------------------------------
-
-------------------------------- purse_requests ----------------------------------------
-
-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'
- ',refunded BOOLEAN NOT NULL DEFAULT(FALSE)'
- ',finished BOOLEAN NOT NULL DEFAULT(FALSE)'
- ',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
-$$;
-
-
-------------------------------- purse_refunds ----------------------------------------
-
-CREATE OR REPLACE FUNCTION create_table_purse_refunds(
- IN shard_suffix VARCHAR DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name VARCHAR DEFAULT 'purse_refunds';
-BEGIN
-
- PERFORM create_partitioned_table(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(purse_refunds_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE
- ',purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)'
- ',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_refunds_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE purse_refunds_' || partition_suffix || ' '
- 'ADD CONSTRAINT purse_refunds_' || partition_suffix || '_purse_refunds_serial_id_key '
- 'UNIQUE (purse_refunds_serial_id) '
- );
-END
-$$;
-
-
-
-
-
----------------------------- purse_merges -----------------------------
-
-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
-$$;
-
-------------------------- account_merges ----------------------------
-
-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
-$$;
-
-------------------------- contracts -------------------------------
-
-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
-$$;
-
---------------------------- history_requests --------------------------
-
-
-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
-$$;
-
---------------------------- close_requests ---------------------------
-
-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'
- ',PRIMARY KEY (reserve_pub,close_timestamp)'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (reserve_pub)'
- ,shard_suffix
- );
-
-END
-$$;
-
-------------------------------- purse_deposits -------------------------------
-
-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
-$$;
-
----------------------------- wads_out -------------------------------
-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
-$$;
-
---------------------------- wad_out_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_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
-$$;
-
--------------------------- wads_in --------------------------------
-
-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
-$$;
-
-
-------------------------- wads_in_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_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
-$$;
-
--------------------------------------------------------------------
-------------------------- Partitions ------------------------------
--------------------------------------------------------------------
-
-CREATE OR REPLACE FUNCTION create_hash_partition(
- source_table_name VARCHAR
- ,modulus INTEGER
- ,partition_num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num;
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
- ,source_table_name || '_' || partition_num
- ,source_table_name
- ,modulus
- ,partition_num-1
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION 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 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_refunds
- DETACH partition purse_refunds_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 history_requests
- DETACH partition history_requests_default;
-
- ALTER TABLE IF EXISTS close_requests
- DETACH partition close_requests_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 shardig too';
-
-
-CREATE OR REPLACE FUNCTION drop_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Dropping default table partitions';
-
- DROP TABLE IF EXISTS wire_targets_default;
- DROP TABLE IF EXISTS reserves_default;
- DROP TABLE IF EXISTS reserves_in_default;
- DROP TABLE IF EXISTS reserves_close_default;
- DROP TABLE IF EXISTS reserves_out_default;
- DROP TABLE IF EXISTS reserves_out_by_reserve_default;
- DROP TABLE IF EXISTS known_coins_default;
- DROP TABLE IF EXISTS refresh_commitments_default;
- DROP TABLE IF EXISTS refresh_revealed_coins_default;
- DROP TABLE IF EXISTS refresh_transfer_keys_default;
- DROP TABLE IF EXISTS deposits_default;
---DROP TABLE IF EXISTS deposits_by_ready_default;
---DROP TABLE IF EXISTS deposits_for_matching_default;
- DROP TABLE IF EXISTS refunds_default;
- DROP TABLE IF EXISTS wire_out_default;
- DROP TABLE IF EXISTS aggregation_transient_default;
- DROP TABLE IF EXISTS aggregation_tracking_default;
- DROP TABLE IF EXISTS recoup_default;
- DROP TABLE IF EXISTS recoup_by_reserve_default;
- DROP TABLE IF EXISTS recoup_refresh_default;
- DROP TABLE IF EXISTS prewire_default;
- DROP TABLE IF EXISTS cs_nonce_locks_default;
-
- DROP TABLE IF EXISTS purse_requests_default;
- DROP TABLE IF EXISTS purse_refunds_default;
- DROP TABLE IF EXISTS purse_merges_default;
- DROP TABLE IF EXISTS account_merges_default;
- DROP TABLE IF EXISTS contracts_default;
- DROP TABLE IF EXISTS history_requests_default;
- DROP TABLE IF EXISTS close_requests_default;
- DROP TABLE IF EXISTS purse_deposits_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);
-
- ---------------- 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_refunds'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_purse_refunds_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(
- 'history_requests'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_hash_partition(
- 'close_requests'
- ,modulus
- ,num_partitions
- );
-
- 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_extension_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_refunds
- DROP CONSTRAINT IF EXISTS purse_refunds_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(
- '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_refunds'
- ,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(
- '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(
- '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
- );
- 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
-$$;