[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] branch master updated (6c66f6f5 -> 1b76ba5e)
|
From: |
gnunet |
|
Subject: |
[taler-exchange] branch master updated (6c66f6f5 -> 1b76ba5e) |
|
Date: |
Sat, 02 Apr 2022 09:29:36 +0200 |
This is an automated email from the git hooks/post-receive script.
marco-boss pushed a change to branch master
in repository exchange.
from 6c66f6f5 rollback transaction to end transaction scope before
releasing the shard (fixes #7216)
new 0cf9a998 add sharding logic
new 75c1e10a add shard init for shard node
new 45916b99 remove built sql files from vc
new 0ba219fe add permissions
new 1b76ba5e Include sharding sql logic
The 5 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
Summary of changes:
src/exchange-tools/taler-exchange-dbinit.c | 52 +
src/exchangedb/.gitignore | 2 +
src/exchangedb/Makefile.am | 16 +-
src/exchangedb/common-0001.sql | 1912 ++++++++++++++++++++
src/exchangedb/drop0001.sql | 28 +-
.../{exchange-0001.sql => exchange-0001-part.sql} | 620 +------
src/exchangedb/partition-0001.sql | 312 ----
src/exchangedb/plugin_exchangedb_postgres.c | 92 +-
src/include/taler_exchangedb_plugin.h | 29 +-
9 files changed, 2170 insertions(+), 893 deletions(-)
create mode 100644 src/exchangedb/common-0001.sql
rename src/exchangedb/{exchange-0001.sql => exchange-0001-part.sql} (81%)
delete mode 100644 src/exchangedb/partition-0001.sql
diff --git a/src/exchange-tools/taler-exchange-dbinit.c
b/src/exchange-tools/taler-exchange-dbinit.c
index 9ec31afc..cf35466c 100644
--- a/src/exchange-tools/taler-exchange-dbinit.c
+++ b/src/exchange-tools/taler-exchange-dbinit.c
@@ -49,6 +49,15 @@ static int gc_db;
*/
static uint32_t num_partitions;
+/**
+ * -F option: setup a sharded database, i.e. create foreign tables/server
+ */
+static uint32_t num_foreign_servers;
+
+/**
+ * -S option: setup a database on a shard server, creates tables with suffix
shard_idx
+ */
+static uint32_t shard_idx;
/**
* Main function that will be run.
@@ -85,6 +94,20 @@ run (void *cls,
"Could not drop tables as requested. Either database was not
yet initialized, or permission denied. Consult the logs. Will still try to
create new tables.\n");
}
}
+ if (1 <
+ shard_idx)
+ {
+ if (GNUNET_OK != plugin->create_shard_tables (plugin->cls, shard_idx))
+ {
+ GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
+ "Could not create shard database\n");
+ global_ret = EXIT_NOTINSTALLED;
+ }
+ /* We do not want to continue if we are on a shard */
+ TALER_EXCHANGEDB_plugin_unload (plugin);
+ plugin = NULL;
+ return;
+ }
if (GNUNET_OK !=
plugin->create_tables (plugin->cls))
{
@@ -113,6 +136,25 @@ run (void *cls,
return;
}
}
+ else if (1 <
+ num_foreign_servers)
+ {
+ if (GNUNET_OK != plugin->setup_foreign_servers (plugin->cls,
+ num_foreign_servers))
+ {
+ GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
+ "Could not setup shards. Aborting\n");
+ if (GNUNET_OK != plugin->drop_tables (plugin->cls))
+ {
+ GNUNET_log (GNUNET_ERROR_TYPE_ERROR,
+ "Could not drop tables after failed shard setup, please
delete the DB manually\n");
+ }
+ TALER_EXCHANGEDB_plugin_unload (plugin);
+ plugin = NULL;
+ global_ret = EXIT_NOTINSTALLED;
+ return;
+ }
+ }
if (gc_db || clear_shards)
{
if (GNUNET_OK !=
@@ -178,6 +220,16 @@ main (int argc,
"NUMBER",
"Setup a partitioned database where each table
which can be partitioned holds NUMBER partitions on a single DB node (NOTE:
this is different from sharding)",
&num_partitions),
+ GNUNET_GETOPT_option_uint ('F',
+ "foreign",
+ "NUMBER",
+ "Setup a sharded database whit N foreign
servers (shards) / tables",
+ &num_foreign_servers),
+ GNUNET_GETOPT_option_uint ('S',
+ "shard",
+ "INDEX",
+ "Setup a shard server, creates tables with
INDEX as suffix",
+ &shard_idx),
GNUNET_GETOPT_OPTION_END
};
enum GNUNET_GenericReturnValue ret;
diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore
index aea9a74e..b5d4ff3f 100644
--- a/src/exchangedb/.gitignore
+++ b/src/exchangedb/.gitignore
@@ -5,3 +5,5 @@ test-exchangedb-postgres
test-exchangedb-signkeys
test-perf-taler-exchangedb
bench-db-postgres
+exchange-0001.sql
+shard-0001.sql
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am
index 2eb1eb0a..305de431 100644
--- a/src/exchangedb/Makefile.am
+++ b/src/exchangedb/Makefile.am
@@ -19,9 +19,23 @@ sql_DATA = \
benchmark-0001.sql \
exchange-0000.sql \
exchange-0001.sql \
- partition-0001.sql \
+ shard-0001.sql \
drop0001.sql
+BUILT_SOURCES = \
+ shard-0001.sql \
+ exchange-0001.sql
+
+CLEANFILES = \
+ shard-0001.sql \
+ exchange-0001.sql
+
+exchange-0001.sql: common-0001.sql exchange-0001-part.sql
+ cat common-0001.sql exchange-0001-part.sql >$@
+
+shard-0001.sql: common-0001.sql
+ cp common-0001.sql $@
+
EXTRA_DIST = \
exchangedb.conf \
exchangedb-postgres.conf \
diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
new file mode 100644
index 00000000..bf2a9925
--- /dev/null
+++ b/src/exchangedb/common-0001.sql
@@ -0,0 +1,1912 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2014--2022 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+-- Everything in one big transaction
+BEGIN;
+
+-- Check patch versioning is in place.
+SELECT _v.register_patch('exchange-0001', NULL, NULL);
+
+-------------------- Tables ----------------------------
+
+CREATE OR REPLACE FUNCTION create_partitioned_table(
+ IN table_definition VARCHAR
+ ,IN table_name VARCHAR
+ ,IN main_table_partition_str VARCHAR -- Used only when it is the main table
- we do not partition shard tables
+ ,IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ IF shard_suffix IS NOT NULL THEN
+ table_name=table_name || '_' || shard_suffix;
+ main_table_partition_str = '';
+ END IF;
+
+ EXECUTE FORMAT(
+ table_definition,
+ table_name,
+ main_table_partition_str
+ );
+
+END
+$$;
+
+----------------------- wire_targets ---------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wire_targets(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',wire_target_h_payto BYTEA PRIMARY KEY CHECK
(LENGTH(wire_target_h_payto)=32)'
+ ',payto_uri VARCHAR NOT NULL'
+ ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)'
+ ',external_id VARCHAR'
+ ') %s ;'
+ ,'wire_targets'
+ ,'PARTITION BY HASH (wire_target_h_payto)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+-- We need a seperate function for this, as we call create_table only once but
need to add
+-- those constraints to each partition which gets created
+CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ EXECUTE FORMAT (
+ 'ALTER TABLE wire_targets_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wire_targets_' || partition_suffix ||
'_wire_target_serial_id_key '
+ 'UNIQUE (wire_target_serial_id)'
+ );
+END
+$$;
+
+------------------------ reserves -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_reserves(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'reserves';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)'
+ ',current_balance_val INT8 NOT NULL'
+ ',current_balance_frac INT4 NOT NULL'
+ ',expiration_date INT8 NOT NULL'
+ ',gc_date INT8 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (reserve_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_expiration_index '
+ 'ON ' || table_name || ' '
+ '(expiration_date'
+ ',current_balance_val'
+ ',current_balance_frac'
+ ');'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name || '_by_expiration_index '
+ 'IS ' || quote_literal('used in get_expired_reserves') || ';'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_uuid_index '
+ 'ON ' || table_name || ' '
+ '(reserve_uuid);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_gc_date_index '
+ 'ON ' || table_name || ' '
+ '(gc_date);'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name || '_by_gc_date_index '
+ 'IS ' || quote_literal('for reserve garbage collection') || ';'
+ );
+
+END
+$$;
+
+----------------------- reserves_in ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_reserves_in(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_in';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
+ ',wire_reference INT8 NOT NULL'
+ ',credit_val INT8 NOT NULL'
+ ',credit_frac INT4 NOT NULL'
+ ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)'
+ ',exchange_account_section TEXT NOT NULL'
+ ',execution_date INT8 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (reserve_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_in_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(reserve_in_serial_id);'
+ );
+ -- FIXME: where do we need this index? Can we do better?
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_exch_accnt_section_execution_date_idx '
+ 'ON ' || table_name || ' '
+ '(exchange_account_section '
+ ',execution_date'
+ ');'
+ );
+ -- FIXME: where do we need this index? Can we do better?
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_exch_accnt_reserve_in_serial_id_idx '
+ 'ON ' || table_name || ' '
+ '(exchange_account_section,'
+ 'reserve_in_serial_id DESC'
+ ');'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE reserves_in_' || partition_suffix || ' '
+ 'ADD CONSTRAINT reserves_in_' || partition_suffix ||
'_reserve_in_serial_id_key '
+ 'UNIQUE (reserve_in_serial_id)'
+ );
+END
+$$;
+
+--------------------------- reserves_close -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_reserves_close(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_close';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE /
PRIMARY KEY'
+ ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON
DELETE CASCADE'
+ ',execution_date INT8 NOT NULL'
+ ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
+ ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
+ ',amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',closing_fee_val INT8 NOT NULL'
+ ',closing_fee_frac INT4 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (reserve_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_close_uuid_index '
+ 'ON ' || table_name || ' '
+ '(close_uuid);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub_index '
+ 'ON ' || table_name || ' '
+ '(reserve_pub);'
+ );
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE reserves_close_' || partition_suffix || ' '
+ 'ADD CONSTRAINT reserves_close_' || partition_suffix ||
'_close_uuid_pkey '
+ 'PRIMARY KEY (close_uuid)'
+ );
+END
+$$;
+
+---------------------------- reserves_out -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_reserves_out(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'reserves_out';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
+ ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial)'
+ ',denom_sig BYTEA NOT NULL'
+ ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON
DELETE CASCADE'
+ ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
+ ',execution_date INT8 NOT NULL'
+ ',amount_with_fee_val INT8 NOT NULL'
+ ',amount_with_fee_frac INT4 NOT NULL'
+ ') %s ;'
+ ,'reserves_out'
+ ,'PARTITION BY HASH (h_blind_ev)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_out_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(reserve_out_serial_id);'
+ );
+ -- FIXME: change query to use reserves_out_by_reserve instead and
materialize execution_date there as well???
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_uuid_and_execution_date_index '
+ 'ON ' || table_name || ' '
+ '(reserve_uuid, execution_date);'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name ||
'_by_reserve_uuid_and_execution_date_index '
+ 'IS ' || quote_literal('for get_reserves_out and
exchange_do_withdraw_limit_check') || ';'
+ );
+
+END
+$$;
+
+
+CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE reserves_out_' || partition_suffix || ' '
+ 'ADD CONSTRAINT reserves_out_' || partition_suffix ||
'_reserve_out_serial_id_key '
+ 'UNIQUE (reserve_out_serial_id)'
+ );
+END
+$$;
+
+CREATE OR REPLACE FUNCTION create_table_reserves_out_by_reserve(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'reserves_out_by_reserve';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON
DELETE CASCADE
+ ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)'
+ ') %s '
+ ,table_name
+ ,'PARTITION BY HASH (reserve_uuid)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'ON ' || table_name || ' '
+ '(reserve_uuid);'
+ );
+
+END
+$$;
+
+---------------------------- known_coins -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_known_coins(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR default 'known_coins';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
+ ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)'
+ ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)'
+ ',denom_sig BYTEA NOT NULL'
+ ',remaining_val INT8 NOT NULL'
+ ',remaining_frac INT4 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (coin_pub)' -- FIXME: or include denominations_serial?
or multi-level partitioning?;
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE known_coins_' || partition_suffix || ' '
+ 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key
'
+ 'UNIQUE (known_coin_id)'
+ );
+END
+$$;
+
+---------------------------- refresh_commitments
-------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_refresh_commitments(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_commitments';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)'
+ ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON
DELETE CASCADE'
+ ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)'
+ ',amount_with_fee_val INT8 NOT NULL'
+ ',amount_with_fee_frac INT4 NOT NULL'
+ ',noreveal_index INT4 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (rc)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ -- Note: index spans partitions, may need to be materialized.
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_old_coin_pub_index '
+ 'ON ' || table_name || ' '
+ '(old_coin_pub);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE refresh_commitments_' || partition_suffix || ' '
+ 'ADD CONSTRAINT refresh_commitments_' || partition_suffix ||
'_melt_serial_id_key '
+ 'UNIQUE (melt_serial_id)'
+ );
+END
+$$;
+
+------------------------------ refresh_revealed_coins
--------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_revealed_coins';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
+ ',freshcoin_index INT4 NOT NULL'
+ ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)'
+ ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations
(denominations_serial) ON DELETE CASCADE'
+ ',coin_ev BYTEA NOT NULL' -- UNIQUE'
+ ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE'
+ ',ev_sig BYTEA NOT NULL'
+ ',ewv BYTEA NOT NULL'
+ -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (melt_serial_id)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_coins_by_melt_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(melt_serial_id);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' '
+ 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_rrc_serial_key '
+ 'UNIQUE (rrc_serial) '
+ ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_coin_ev_key '
+ 'UNIQUE (coin_ev) '
+ ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_h_coin_ev_key '
+ 'UNIQUE (h_coin_ev) '
+ ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) '
+ );
+END
+$$;
+
+----------------------------- refresh_transfer_keys
------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refresh_transfer_keys';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE'
+ ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)'
+ ',transfer_privs BYTEA NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (melt_serial_id)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' '
+ 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix ||
'_rtc_serial_key '
+ 'UNIQUE (rtc_serial)'
+ );
+END
+$$;
+
+---------------------------- deposits -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_deposits(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY
KEY'
+ ',shard INT8 NOT NULL'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
+ ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)
ON DELETE CASCADE' --- FIXME: column needed???
+ ',amount_with_fee_val INT8 NOT NULL'
+ ',amount_with_fee_frac INT4 NOT NULL'
+ ',wallet_timestamp INT8 NOT NULL'
+ ',exchange_timestamp INT8 NOT NULL'
+ ',refund_deadline INT8 NOT NULL'
+ ',wire_deadline INT8 NOT NULL'
+ ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
+ ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)'
+ ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)'
+ ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)'
+ ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
+ ',done BOOLEAN NOT NULL DEFAULT FALSE'
+ ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE'
+ ',extension_details_serial_id INT8' -- REFERENCES extension_details
(extension_details_serial_id) ON DELETE CASCADE'
+ ',UNIQUE (coin_pub, merchant_pub, h_contract_terms)'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (coin_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+ 'ON ' || table_name || ' '
+ '(coin_pub);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE deposits_' || partition_suffix || ' '
+ 'ADD CONSTRAINT deposits_' || partition_suffix ||
'_deposit_serial_id_pkey '
+ 'PRIMARY KEY (deposit_serial_id)'
+ );
+END
+$$;
+
+CREATE OR REPLACE FUNCTION create_table_deposits_by_ready(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits_by_ready';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(wire_deadline INT8 NOT NULL'
+ ',shard INT8 NOT NULL'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+ ',deposit_serial_id INT8'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY RANGE (wire_deadline)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'ON ' || table_name || ' '
+ '(wire_deadline ASC, shard ASC, coin_pub);'
+ );
+
+END
+$$;
+
+
+CREATE OR REPLACE FUNCTION create_table_deposits_for_matching(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'deposits_for_matching';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(refund_deadline INT8 NOT NULL'
+ ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
+ ',deposit_serial_id INT8'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY RANGE (refund_deadline)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'ON ' || table_name || ' '
+ '(refund_deadline ASC, merchant_pub, coin_pub);'
+ );
+
+END
+$$;
+
+----------------------------- refunds ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_refunds(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'refunds';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
+ ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE'
+ ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
+ ',rtransaction_id INT8 NOT NULL'
+ ',amount_with_fee_val INT8 NOT NULL'
+ ',amount_with_fee_frac INT4 NOT NULL'
+ -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (coin_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+ 'ON ' || table_name || ' '
+ '(coin_pub);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE refunds_' || partition_suffix || ' '
+ 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key '
+ 'UNIQUE (refund_serial_id) '
+ ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
+ );
+END
+$$;
+
+---------------------------- wire_out -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wire_out(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'wire_out';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
+ ',execution_date INT8 NOT NULL'
+ ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)'
+ ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
+ ',exchange_account_section TEXT NOT NULL'
+ ',amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (wtid_raw)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_wire_target_h_payto_index '
+ 'ON ' || table_name || ' '
+ '(wire_target_h_payto);'
+ );
+
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wire_out_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey '
+ 'PRIMARY KEY (wireout_uuid)'
+ );
+END
+$$;
+
+---------------------------- aggregation_transient
------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_aggregation_transient(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'aggregation_transient';
+BEGIN
+
+ EXECUTE FORMAT (
+ 'CREATE TABLE IF NOT EXISTS %I '
+ '(amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)'
+ ',exchange_account_section TEXT NOT NULL'
+ ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (wire_target_h_payto)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+---------------------------- aggregation_tracking
-------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_aggregation_tracking(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'aggregation_tracking';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE' -- FIXME chnage to coint_pub +
deposit_serial_id for more efficient depost -- or something else ???
+ ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES
wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (deposit_serial_id)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index '
+ 'ON ' || table_name || ' '
+ '(wtid_raw);'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index '
+ 'IS ' || quote_literal('for lookup_transactions') || ';'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' '
+ 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix ||
'_aggregation_serial_id_key '
+ 'UNIQUE (aggregation_serial_id) '
+ );
+END
+$$;
+
+----------------------------- recoup ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_recoup(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub)
+ ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+ ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+ ',amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',recoup_timestamp INT8 NOT NULL'
+ ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out
(reserve_out_serial_id) ON DELETE CASCADE'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (coin_pub);'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+ 'ON ' || table_name || ' '
+ '(coin_pub);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE recoup_' || partition_suffix || ' '
+ 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key '
+ 'UNIQUE (recoup_uuid) '
+ );
+END
+$$;
+
+CREATE OR REPLACE FUNCTION create_table_recoup_by_reserve(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup_by_reserve';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves
(reserve_out_serial_id) ON DELETE CASCADE
+ ',coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)' -- REFERENCES known_coins
(coin_pub)
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (reserve_out_serial_id)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'ON ' || table_name || ' '
+ '(reserve_out_serial_id);'
+ );
+
+END
+$$;
+
+---------------------------- recoup_refresh ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'recoup_refresh';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)' -- REFERENCES
known_coins (coin_pub)
+ ',known_coin_id BIGINT NOT NULL' -- REFERENCES known_coins
(known_coin_id) ON DELETE CASCADE
+ ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+ ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+ ',amount_val INT8 NOT NULL'
+ ',amount_frac INT4 NOT NULL'
+ ',recoup_timestamp INT8 NOT NULL'
+ ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins
(rrc_serial) ON DELETE CASCADE -- UNIQUE'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (coin_pub)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ -- FIXME: any query using this index will be slow. Materialize index or
change query?
+ -- Also: which query uses this index?
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rrc_serial_index '
+ 'ON ' || table_name || ' '
+ '(rrc_serial);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub_index '
+ 'ON ' || table_name || ' '
+ '(coin_pub);'
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE recoup_refresh_' || partition_suffix || ' '
+ 'ADD CONSTRAINT recoup_refresh_' || partition_suffix ||
'_recoup_refresh_uuid_key '
+ 'UNIQUE (recoup_refresh_uuid) '
+ );
+END
+$$;
+
+----------------------------- prewire ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_prewire(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name VARCHAR DEFAULT 'prewire';
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
+ ',wire_method TEXT NOT NULL'
+ ',finished BOOLEAN NOT NULL DEFAULT false'
+ ',failed BOOLEAN NOT NULL DEFAULT false'
+ ',buf BYTEA NOT NULL'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (prewire_uuid)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_finished_index '
+ 'ON ' || table_name || ' '
+ '(finished);'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name || '_by_finished_index '
+ 'IS ' || quote_literal('for gc_prewire') || ';'
+ );
+ -- FIXME: find a way to combine these two indices?
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_failed_finished_index '
+ 'ON ' || table_name || ' '
+ '(failed,finished);'
+ );
+ EXECUTE FORMAT (
+ 'COMMENT ON INDEX ' || table_name || '_by_failed_finished_index '
+ 'IS ' || quote_literal('for wire_prepare_data_get') || ';'
+ );
+
+END
+$$;
+
+----------------------------- cs_nonce_locks ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks(
+ shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)'
+ ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)'
+ ',max_denomination_serial INT8 NOT NULL'
+ ') %s ;'
+ ,'cs_nonce_locks'
+ ,'PARTITION BY HASH (nonce)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' '
+ 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix ||
'_cs_nonce_lock_serial_id_key '
+ 'UNIQUE (cs_nonce_lock_serial_id)'
+ );
+END
+$$;
+
+------------------------- Partitions ------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_partition(
+ source_table_name VARCHAR
+ ,modulus INTEGER
+ ,partition_num INTEGER
+ )
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num;
+
+ EXECUTE FORMAT(
+ 'CREATE TABLE IF NOT EXISTS %I '
+ 'PARTITION OF %I '
+ 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
+ ,source_table_name || '_' || partition_num
+ ,source_table_name
+ ,modulus
+ ,partition_num-1
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION detach_default_partitions()
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ RAISE NOTICE 'Detaching all default table partitions';
+
+ ALTER TABLE IF EXISTS wire_targets
+ DETACH PARTITION wire_targets_default;
+
+ ALTER TABLE IF EXISTS reserves
+ DETACH PARTITION reserves_default;
+
+ ALTER TABLE IF EXISTS reserves_in
+ DETACH PARTITION reserves_in_default;
+
+ ALTER TABLE IF EXISTS reserves_close
+ DETACH PARTITION reserves_close_default;
+
+ ALTER TABLE IF EXISTS reserves_out
+ DETACH PARTITION reserves_out_default;
+
+ ALTER TABLE IF EXISTS reserves_out_by_reserve
+ DETACH PARTITION reserves_out_by_reserve_default;
+
+ ALTER TABLE IF EXISTS known_coins
+ DETACH PARTITION known_coins_default;
+
+ ALTER TABLE IF EXISTS refresh_commitments
+ DETACH PARTITION refresh_commitments_default;
+
+ ALTER TABLE IF EXISTS refresh_revealed_coins
+ DETACH PARTITION refresh_revealed_coins_default;
+
+ ALTER TABLE IF EXISTS refresh_transfer_keys
+ DETACH PARTITION refresh_transfer_keys_default;
+
+ ALTER TABLE IF EXISTS deposits
+ DETACH PARTITION deposits_default;
+
+--- TODO range partitioning
+-- ALTER TABLE IF EXISTS deposits_by_ready
+-- DETACH PARTITION deposits_by_ready_default;
+--
+-- ALTER TABLE IF EXISTS deposits_for_matching
+-- DETACH PARTITION deposits_default_for_matching_default;
+
+ ALTER TABLE IF EXISTS refunds
+ DETACH PARTITION refunds_default;
+
+ ALTER TABLE IF EXISTS wire_out
+ DETACH PARTITION wire_out_default;
+
+ ALTER TABLE IF EXISTS aggregation_transient
+ DETACH PARTITION aggregation_transient_default;
+
+ ALTER TABLE IF EXISTS aggregation_tracking
+ DETACH PARTITION aggregation_tracking_default;
+
+ ALTER TABLE IF EXISTS recoup
+ DETACH PARTITION recoup_default;
+
+ ALTER TABLE IF EXISTS recoup_by_reserve
+ DETACH PARTITION recoup_by_reserve_default;
+
+ ALTER TABLE IF EXISTS recoup_refresh
+ DETACH PARTITION recoup_refresh_default;
+
+ ALTER TABLE IF EXISTS prewire
+ DETACH PARTITION prewire_default;
+
+ ALTER TABLE IF EXISTS cs_nonce_locks
+ DETACH partition cs_nonce_locks_default;
+
+END
+$$;
+
+COMMENT ON FUNCTION detach_default_partitions
+ IS 'We need to drop default and create new one before deleting the default
partitions
+ otherwise constraints get lost too. Might be needed in shardig too';
+
+
+CREATE OR REPLACE FUNCTION drop_default_partitions()
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ RAISE NOTICE 'Dropping default table partitions';
+
+ DROP TABLE IF EXISTS wire_targets_default;
+ DROP TABLE IF EXISTS reserves_default;
+ DROP TABLE IF EXISTS reserves_in_default;
+ DROP TABLE IF EXISTS reserves_close_default;
+ DROP TABLE IF EXISTS reserves_out_default;
+ DROP TABLE IF EXISTS reserves_out_by_reserve_default;
+ DROP TABLE IF EXISTS known_coins_default;
+ DROP TABLE IF EXISTS refresh_commitments_default;
+ DROP TABLE IF EXISTS refresh_revealed_coins_default;
+ DROP TABLE IF EXISTS refresh_transfer_keys_default;
+ DROP TABLE IF EXISTS deposits_default;
+--DROP TABLE IF EXISTS deposits_by_ready_default;
+--DROP TABLE IF EXISTS deposits_for_matching_default;
+ DROP TABLE IF EXISTS refunds_default;
+ DROP TABLE IF EXISTS wire_out_default;
+ DROP TABLE IF EXISTS aggregation_transient_default;
+ DROP TABLE IF EXISTS aggregation_tracking_default;
+ DROP TABLE IF EXISTS recoup_default;
+ DROP TABLE IF EXISTS recoup_by_reserve_default;
+ DROP TABLE IF EXISTS recoup_refresh_default;
+ DROP TABLE IF EXISTS prewire_default;
+ DROP TABLE IF EXISTS cs_nonce_locks_default;
+
+END
+$$;
+
+COMMENT ON FUNCTION drop_default_partitions
+ IS 'Drop all default partitions once other partitions are attached.
+ Might be needed in sharding too.';
+
+CREATE OR REPLACE FUNCTION create_partitions(
+ num_partitions INTEGER
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ modulus INTEGER;
+BEGIN
+
+ modulus := num_partitions;
+
+ PERFORM detach_default_partitions();
+
+ LOOP
+
+ PERFORM create_table_partition(
+ 'wire_targets'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'reserves'
+ ,modulus
+ ,num_partitions
+ );
+
+ PERFORM create_table_partition(
+ 'reserves_in'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'reserves_close'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_reserves_close_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'reserves_out'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'reserves_out_by_reserve'
+ ,modulus
+ ,num_partitions
+ );
+
+ PERFORM create_table_partition(
+ 'known_coins'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'refresh_commitments'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_refresh_commitments_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'refresh_revealed_coins'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'refresh_transfer_keys'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'deposits'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_deposits_partition(num_partitions::varchar);
+
+-- TODO: dynamically (!) creating/deleting deposits partitions:
+-- create new partitions 'as needed', drop old ones once the aggregator has
made
+-- them empty; as 'new' deposits will always have deadlines in the future,
this
+-- would basically guarantee no conflict between aggregator and exchange
service!
+-- SEE also:
https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
+-- (article is slightly wrong, as this works:)
+--CREATE TABLE tab (
+-- id bigint GENERATED ALWAYS AS IDENTITY,
+-- ts timestamp NOT NULL,
+-- data text
+-- PARTITION BY LIST ((ts::date));
+-- CREATE TABLE tab_def PARTITION OF tab DEFAULT;
+-- BEGIN
+-- CREATE TABLE tab_part2 (LIKE tab);
+-- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo');
+-- alter table tab attach partition tab_part2 for values in ('2022-03-21');
+-- commit;
+-- Naturally, to ensure this is actually 100% conflict-free, we'd
+-- need to create tables at the granularity of the wire/refund deadlines;
+-- that is right now configurable via AGGREGATOR_SHIFT option.
+
+-- FIXME: range partitioning
+-- PERFORM create_table_partition(
+-- 'deposits_by_ready'
+-- ,modulus
+-- ,num_partitions
+-- );
+--
+-- PERFORM create_table_partition(
+-- 'deposits_for_matching'
+-- ,modulus
+-- ,num_partitions
+-- );
+
+ PERFORM create_table_partition(
+ 'refunds'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_refunds_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'wire_out'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'aggregation_transient'
+ ,modulus
+ ,num_partitions
+ );
+
+ PERFORM create_table_partition(
+ 'aggregation_tracking'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_aggregation_tracking_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'recoup'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM add_constraints_to_recoup_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'recoup_by_reserve'
+ ,modulus
+ ,num_partitions
+ );
+
+ PERFORM create_table_partition(
+ 'recoup_refresh'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_recoup_refresh_partition(num_partitions::varchar);
+
+ PERFORM create_table_partition(
+ 'prewire'
+ ,modulus
+ ,num_partitions
+ );
+
+ PERFORM create_table_partition(
+ 'cs_nonce_locks'
+ ,modulus
+ ,num_partitions
+ );
+ PERFORM
add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar);
+
+ num_partitions=num_partitions-1;
+ EXIT WHEN num_partitions=0;
+
+ END LOOP;
+
+ PERFORM drop_default_partitions();
+
+END
+$$;
+
+--------------------- Sharding ---------------------------
+
+---------------------- Shards ----------------------------
+
+CREATE OR REPLACE FUNCTION setup_shard(
+ shard_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM create_table_wire_targets(shard_suffix);
+ PERFORM add_constraints_to_wire_targets_partition(shard_suffix);
+
+ PERFORM create_table_reserves(shard_suffix);
+
+ PERFORM create_table_reserves_in(shard_suffix);
+ PERFORM add_constraints_to_reserves_in_partition(shard_suffix);
+
+ PERFORM create_table_reserves_close(shard_suffix);
+
+ PERFORM create_table_reserves_out(shard_suffix);
+
+ PERFORM create_table_reserves_out_by_reserve(shard_suffix);
+
+ PERFORM create_table_known_coins(shard_suffix);
+ PERFORM add_constraints_to_known_coins_partition(shard_suffix);
+
+ PERFORM create_table_refresh_commitments(shard_suffix);
+ PERFORM add_constraints_to_refresh_commitments_partition(shard_suffix);
+
+ PERFORM create_table_refresh_revealed_coins(shard_suffix);
+ PERFORM add_constraints_to_refresh_revealed_coins_partition(shard_suffix);
+
+ PERFORM create_table_refresh_transfer_keys(shard_suffix);
+ PERFORM add_constraints_to_refresh_transfer_keys_partition(shard_suffix);
+
+ PERFORM create_table_deposits(shard_suffix);
+ PERFORM add_constraints_to_deposits_partition(shard_suffix);
+
+ PERFORM create_table_deposits_by_ready(shard_suffix);
+
+ PERFORM create_table_deposits_for_matching(shard_suffix);
+
+ PERFORM create_table_refunds(shard_suffix);
+ PERFORM add_constraints_to_refunds_partition(shard_suffix);
+
+ PERFORM create_table_wire_out(shard_suffix);
+ PERFORM add_constraints_to_wire_out_partition(shard_suffix);
+
+ PERFORM create_table_aggregation_transient(shard_suffix);
+
+ PERFORM create_table_aggregation_tracking(shard_suffix);
+ PERFORM add_constraints_to_aggregation_tracking_partition(shard_suffix);
+
+ PERFORM create_table_recoup(shard_suffix);
+ PERFORM add_constraints_to_recoup_partition(shard_suffix);
+
+ PERFORM create_table_recoup_by_reserve(shard_suffix);
+
+ PERFORM create_table_recoup_refresh(shard_suffix);
+ PERFORM add_constraints_to_recoup_refresh_partition(shard_suffix);
+
+ PERFORM create_table_prewire(shard_suffix);
+
+ PERFORM create_table_cs_nonce_locks(shard_suffix);
+ PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix);
+
+END
+$$;
+
+------------------------------ Master ----------------------------------
+
+CREATE OR REPLACE FUNCTION create_foreign_table(
+ source_table_name VARCHAR
+ ,modulus INTEGER
+ ,shard_suffix VARCHAR
+ ,current_shard_num INTEGER
+ ,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 %L'
+ ,source_table_name || '_' || shard_suffix
+ ,local_user
+ );
+
+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_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS refunds
+ DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS wire_out
+ DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS aggregation_tracking
+ DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey
+ ;
+
+ ALTER TABLE IF EXISTS recoup
+ DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS recoup_refresh
+ DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS prewire
+ DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS cs_nonce_locks
+ DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE
+ ;
+
+END
+$$;
+
+
+CREATE OR REPLACE FUNCTION create_shard_server(
+ shard_suffix VARCHAR
+ ,total_num_shards INTEGER
+ ,current_shard_num INTEGER
+ ,remote_host VARCHAR
+ ,remote_user VARCHAR
+ ,remote_user_password VARCHAR
+ ,remote_db_name VARCHAR DEFAULT 'taler-exchange'
+ ,remote_port INTEGER DEFAULT '5432'
+ ,local_user VARCHAR DEFAULT 'taler-exchange-httpd'
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ RAISE NOTICE 'Creating server %s', remote_host;
+
+ EXECUTE FORMAT(
+ 'CREATE SERVER IF NOT EXISTS %I '
+ 'FOREIGN DATA WRAPPER postgres_fdw '
+ 'OPTIONS (dbname %L, host %L, port %L)'
+ ,shard_suffix
+ ,remote_db_name
+ ,remote_host
+ ,remote_port
+ );
+
+ EXECUTE FORMAT(
+ 'CREATE USER MAPPING IF NOT EXISTS '
+ 'FOR %s SERVER %I '
+ 'OPTIONS (user %L, password %L)'
+ ,local_user
+ ,shard_suffix
+ ,remote_user
+ ,remote_user_password
+ );
+
+ EXECUTE FORMAT(
+ 'GRANT ALL PRIVILEGES '
+ 'ON FOREIGN SERVER %I '
+ 'TO %L;'
+ ,shard_suffix
+ ,local_user
+ );
+
+ PERFORM create_foreign_table(
+ 'wire_targets'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'reserves'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'reserves_in'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'reserves_out'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'reserves_close'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'known_coins'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'refresh_commitments'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'refresh_revealed_coins'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'refresh_transfer_keys'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'deposits'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+-- PERFORM create_foreign_table(
+-- 'deposits_by_ready'
+-- ,total_num_shards
+-- ,shard_suffix
+-- ,current_shard_num
+-- ,local_user
+-- );
+-- PERFORM create_foreign_table(
+-- 'deposits_for_matching'
+-- ,total_num_shards
+-- ,shard_suffix
+-- ,current_shard_num
+-- ,local_user
+-- );
+ PERFORM create_foreign_table(
+ 'refunds'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'wire_out'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'aggregation_tracking'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'recoup'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'recoup_by_reserve'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'reserves_out_by_reserve'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'recoup_refresh'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'prewire'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ ,local_user
+ );
+ PERFORM create_foreign_table(
+ 'cs_nonce_locks'
+ ,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 DEFAULT 'perf.taler'
+)
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM prepare_sharding();
+
+ FOR i IN 1..amount LOOP
+ PERFORM create_shard_server(
+ i
+ ,amount
+ ,i
+ ,'shard-' || i::varchar || '.' || domain
+ ,'taler'
+ ,'taler'
+ ,'taler-exchange'
+ ,'5432'
+ ,'taler-exchange-httpd'
+ );
+ END LOOP;
+END
+$$;
+
+COMMIT;
\ No newline at end of file
diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql
index 3f43a569..36457851 100644
--- a/src/exchangedb/drop0001.sql
+++ b/src/exchangedb/drop0001.sql
@@ -43,43 +43,65 @@ DROP TABLE IF EXISTS wire_accounts CASCADE;
DROP TABLE IF EXISTS signkey_revocations CASCADE;
DROP TABLE IF EXISTS work_shards CASCADE;
DROP TABLE IF EXISTS prewire CASCADE;
+DROP FUNCTION IF EXISTS create_table_prewire;
DROP TABLE IF EXISTS recoup CASCADE;
+DROP FUNCTION IF EXISTS create_table_recoup;
DROP FUNCTION IF EXISTS add_constraints_to_recoup_partition;
DROP TABLE IF EXISTS recoup_refresh CASCADE;
+DROP FUNCTION IF EXISTS create_table_recoup_refresh;
DROP FUNCTION IF EXISTS add_constraints_to_recoup_refresh_partition;
+DROP TABLE IF EXISTS aggregation_transient CASCADE;
+DROP FUNCTION IF EXISTS create_table_aggregation_transient;
DROP TABLE IF EXISTS aggregation_tracking CASCADE;
+DROP FUNCTION IF EXISTS create_table_aggregation_tracking;
DROP FUNCTION IF EXISTS add_constraints_to_aggregation_tracking_partition;
DROP TABLE IF EXISTS wire_out CASCADE;
+DROP FUNCTION IF EXISTS create_table_wire_out;
DROP FUNCTION IF EXISTS add_constraints_to_wire_out_partition;
DROP TABLE IF EXISTS wire_targets CASCADE;
+DROP FUNCTION IF EXISTS create_table_wire_targets;
DROP FUNCTION IF EXISTS add_constraints_to_wire_targets_partition;
DROP TABLE IF EXISTS wire_fee CASCADE;
DROP TABLE IF EXISTS deposits CASCADE;
+DROP FUNCTION IF EXISTS create_table_deposits;
DROP TABLE IF EXISTS deposits_by_ready CASCADE;
+DROP FUNCTION IF EXISTS create_table_deposits_by_ready;
DROP TABLE IF EXISTS deposits_for_matching CASCADE;
+DROP FUNCTION IF EXISTS create_table_deposits_for_matching;
DROP FUNCTION IF EXISTS add_constraints_to_deposits_partition;
DROP TABLE IF EXISTS extension_details CASCADE;
DROP TABLE IF EXISTS refunds CASCADE;
+DROP FUNCTION IF EXISTS create_table_refunds;
DROP FUNCTION IF EXISTS add_constraints_to_refunds_partition;
DROP TABLE IF EXISTS refresh_commitments CASCADE;
+DROP FUNCTION IF EXISTS create_table_refresh_commitments;
DROP FUNCTION IF EXISTS add_constraints_to_refresh_commitments_partition;
DROP TABLE IF EXISTS refresh_revealed_coins CASCADE;
+DROP FUNCTION IF EXISTS create_table_refresh_revealed_coins;
DROP FUNCTION IF EXISTS add_constraints_to_refresh_revealed_coins_partition;
DROP TABLE IF EXISTS refresh_transfer_keys CASCADE;
+DROP FUNCTION IF EXISTS create_table_refresh_transfer_keys;
DROP FUNCTION IF EXISTS add_constraints_to_refresh_transfer_keys_partition;
DROP TABLE IF EXISTS known_coins CASCADE;
+DROP FUNCTION IF EXISTS create_table_known_coins;
DROP FUNCTION IF EXISTS add_constraints_to_known_coins_partition;
DROP TABLE IF EXISTS reserves_close CASCADE;
+DROP FUNCTION IF EXISTS create_table_reserves_close;
DROP FUNCTION IF EXISTS add_constraints_to_reserves_close_partition;
DROP TABLE IF EXISTS reserves_out CASCADE;
+DROP FUNCTION IF EXISTS create_table_reserves_out;
DROP TABLE IF EXISTS reserves_out_by_reserve CASCADE;
+DROP FUNCTION IF EXISTS create_table_reserves_out_by_reserve;
DROP FUNCTION IF EXISTS add_constraints_to_reserves_out_partition;
DROP TABLE IF EXISTS reserves_in CASCADE;
+DROP FUNCTION IF EXISTS create_table_reserves_in;
DROP FUNCTION IF EXISTS add_constraints_to_reserves_in_partition;
DROP TABLE IF EXISTS reserves CASCADE;
+DROP FUNCTION IF EXISTS create_table_reserves;
DROP TABLE IF EXISTS denomination_revocations CASCADE;
DROP TABLE IF EXISTS denominations CASCADE;
DROP TABLE IF EXISTS cs_nonce_locks CASCADE;
+DROP FUNCTION IF EXISTS create_table_cs_nonce_locks;
DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition;
DROP TABLE IF EXISTS global_fee CASCADE;
@@ -122,14 +144,14 @@ DROP FUNCTION IF EXISTS exchange_do_account_merge;
DROP FUNCTION IF EXISTS exchange_do_history_request;
DROP FUNCTION IF EXISTS exchange_do_close_request;
--- Unregister patch (partition-0001.sql)
--- SELECT _v.unregister_patch('partition-0001');
--- Drops for partition-0001.sql
DROP FUNCTION IF EXISTS create_table_partition;
DROP FUNCTION IF EXISTS create_partitions;
DROP FUNCTION IF EXISTS detach_default_partitions;
DROP FUNCTION IF EXISTS drop_default_partitions;
+DROP FUNCTION IF EXISTS master_prepare_sharding;
+DROP FUNCTION IF EXISTS create_foreign_table;
+DROP FUNCTION IF EXISTS create_shard_server;
-- And we're out of here...
diff --git a/src/exchangedb/exchange-0001.sql
b/src/exchangedb/exchange-0001-part.sql
similarity index 81%
rename from src/exchangedb/exchange-0001.sql
rename to src/exchangedb/exchange-0001-part.sql
index 2955663a..359fe9f8 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -17,10 +17,6 @@
-- 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
@@ -72,14 +68,8 @@ COMMENT ON TABLE denomination_revocations
-- ------------------------------ wire_targets
----------------------------------------
-CREATE TABLE IF NOT EXISTS wire_targets
- (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)
- ,payto_uri VARCHAR NOT NULL
- ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
- ,external_id VARCHAR
- )
- PARTITION BY HASH (wire_target_h_payto);
+SELECT create_table_wire_targets();
+
COMMENT ON TABLE wire_targets
IS 'All senders and recipients of money via the exchange';
COMMENT ON COLUMN wire_targets.payto_uri
@@ -95,39 +85,12 @@ CREATE TABLE IF NOT EXISTS wire_targets_default
PARTITION OF wire_targets
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE wire_targets_' || partition_suffix || ' '
- 'ADD CONSTRAINT wire_targets_' || partition_suffix ||
'_wire_target_serial_id_key '
- 'UNIQUE (wire_target_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_wire_targets_partition('default');
-CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index
- ON wire_targets
- (wire_target_serial_id);
-
-
-- ------------------------------ reserves
----------------------------------------
-CREATE TABLE IF NOT EXISTS reserves
- (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
- ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
- ,current_balance_val INT8 NOT NULL
- ,current_balance_frac INT4 NOT NULL
- ,expiration_date INT8 NOT NULL
- ,gc_date INT8 NOT NULL
- )
- PARTITION BY HASH (reserve_pub);
+SELECT create_table_reserves();
+
COMMENT ON TABLE reserves
IS 'Summarizes the balance of a reserve. Updated when new funds are added or
withdrawn.';
COMMENT ON COLUMN reserves.reserve_pub
@@ -138,40 +101,15 @@ COMMENT ON COLUMN reserves.expiration_date
IS 'Used to trigger closing of reserves that have not been drained after
some time';
COMMENT ON COLUMN reserves.gc_date
IS 'Used to forget all information about a reserve during garbage
collection';
+
CREATE TABLE IF NOT EXISTS reserves_default
PARTITION OF reserves
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS reserves_by_expiration_index
- ON reserves
- (expiration_date
- ,current_balance_val
- ,current_balance_frac
- );
-COMMENT ON INDEX reserves_by_expiration_index
- IS 'used in get_expired_reserves';
-CREATE INDEX IF NOT EXISTS reserves_by_reserve_uuid_index
- ON reserves
- (reserve_uuid);
-CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index
- ON reserves
- (gc_date);
-COMMENT ON INDEX reserves_by_gc_date_index
- IS 'for reserve garbage collection';
-
-- ------------------------------ reserves_in
----------------------------------------
-CREATE TABLE IF NOT EXISTS reserves_in
- (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
- ,wire_reference INT8 NOT NULL
- ,credit_val INT8 NOT NULL
- ,credit_frac INT4 NOT NULL
- ,wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)
- ,exchange_account_section TEXT NOT NULL
- ,execution_date INT8 NOT NULL
- )
- PARTITION BY HASH (reserve_pub);
+SELECT create_table_reserves_in();
+
COMMENT ON TABLE reserves_in
IS 'list of transfers of funds into the reserves, one per incoming wire
transfer';
COMMENT ON COLUMN reserves_in.wire_source_h_payto
@@ -185,101 +123,28 @@ CREATE TABLE IF NOT EXISTS reserves_in_default
PARTITION OF reserves_in
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_in_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_in_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_in_' || partition_suffix ||
'_reserve_in_serial_id_key '
- 'UNIQUE (reserve_in_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_reserves_in_partition('default');
-CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
- ON reserves_in
- (reserve_in_serial_id);
--- FIXME: where do we need this index? Can we do better?
-CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_section_execution_date_index
- ON reserves_in
- (exchange_account_section
- ,execution_date
- );
--- FIXME: where do we need this index? Can we do better?
-CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_reserve_in_serial_id_index
- ON reserves_in
- (exchange_account_section
- ,reserve_in_serial_id DESC
- );
-
-
-- ------------------------------ reserves_close
----------------------------------------
-CREATE TABLE IF NOT EXISTS reserves_close
- (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
- ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
- ,execution_date INT8 NOT NULL
- ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)
- ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
- ,amount_val INT8 NOT NULL
- ,amount_frac INT4 NOT NULL
- ,closing_fee_val INT8 NOT NULL
- ,closing_fee_frac INT4 NOT NULL)
- PARTITION BY HASH (reserve_pub);
+SELECT create_table_reserves_close();
+
COMMENT ON TABLE reserves_close
IS 'wire transfers executed by the reserve to close reserves';
COMMENT ON COLUMN reserves_close.wire_target_h_payto
IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
-CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
- ON reserves_close
- (close_uuid);
-CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
- ON reserves_close
- (reserve_pub);
-
CREATE TABLE IF NOT EXISTS reserves_close_default
PARTITION OF reserves_close
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_close_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_close_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_close_' || partition_suffix ||
'_close_uuid_pkey '
- 'PRIMARY KEY (close_uuid)'
- );
-END
-$$;
-
SELECT add_constraints_to_reserves_close_partition('default');
-- ------------------------------ reserves_out
----------------------------------------
-CREATE TABLE IF NOT EXISTS reserves_out
- (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE
- ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial)
- ,denom_sig BYTEA NOT NULL
- ,reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE
CASCADE
- ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
- ,execution_date INT8 NOT NULL
- ,amount_with_fee_val INT8 NOT NULL
- ,amount_with_fee_frac INT4 NOT NULL
- )
- PARTITION BY HASH (h_blind_ev);
+SELECT create_table_reserves_out();
+
COMMENT ON TABLE reserves_out
IS 'Withdraw operations performed on reserves.';
COMMENT ON COLUMN reserves_out.h_blind_ev
@@ -287,50 +152,18 @@ COMMENT ON COLUMN reserves_out.h_blind_ev
COMMENT ON COLUMN reserves_out.denominations_serial
IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
- ON reserves_out
- (reserve_out_serial_id);
--- FIXME: change query to use reserves_out_by_reserve instead and materialize
execution_date there as well???
-CREATE INDEX IF NOT EXISTS
reserves_out_by_reserve_uuid_and_execution_date_index
- ON reserves_out
- (reserve_uuid, execution_date);
-COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
- IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
-
CREATE TABLE IF NOT EXISTS reserves_out_default
PARTITION OF reserves_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE reserves_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT reserves_out_' || partition_suffix ||
'_reserve_out_serial_id_key '
- 'UNIQUE (reserve_out_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_reserves_out_partition('default');
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
- (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE
CASCADE
- ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)
- )
- PARTITION BY HASH (reserve_uuid);
+SELECT create_table_reserves_out_by_reserve();
+
COMMENT ON TABLE reserves_out_by_reserve
IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
- ON reserves_out_by_reserve
- (reserve_uuid);
-
CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
PARTITION OF reserves_out_by_reserve
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -467,16 +300,8 @@ COMMENT ON COLUMN extensions.config
-- ------------------------------ known_coins
----------------------------------------
-CREATE TABLE IF NOT EXISTS known_coins
- (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)
- ,age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)
- ,denom_sig BYTEA NOT NULL
- ,remaining_val INT8 NOT NULL
- ,remaining_frac INT4 NOT NULL
- )
- PARTITION BY HASH (coin_pub);
+SELECT create_table_known_coins();
+
COMMENT ON TABLE known_coins
IS 'information about coins and their signatures, so we do not have to store
the signatures more than once if a coin is involved in multiple operations';
COMMENT ON COLUMN known_coins.denominations_serial
@@ -494,36 +319,13 @@ CREATE TABLE IF NOT EXISTS known_coins_default
PARTITION OF known_coins
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_known_coins_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE known_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT known_coins_' || partition_suffix || 'k_nown_coin_id_key
'
- 'UNIQUE (known_coin_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_known_coins_partition('default');
-- ------------------------------ refresh_commitments
----------------------------------------
-CREATE TABLE IF NOT EXISTS refresh_commitments
- (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)
- ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE
CASCADE
- ,old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)
- ,amount_with_fee_val INT8 NOT NULL
- ,amount_with_fee_frac INT4 NOT NULL
- ,noreveal_index INT4 NOT NULL
- )
- PARTITION BY HASH (rc);
+SELECT create_table_refresh_commitments();
+
COMMENT ON TABLE refresh_commitments
IS 'Commitments made when melting coins and the gamma value chosen by the
exchange.';
COMMENT ON COLUMN refresh_commitments.noreveal_index
@@ -533,48 +335,17 @@ COMMENT ON COLUMN refresh_commitments.rc
COMMENT ON COLUMN refresh_commitments.old_coin_pub
IS 'Coin being melted in the refresh process.';
--- Note: index spans partitions, may need to be materialized.
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
- ON refresh_commitments
- (old_coin_pub);
-
CREATE TABLE IF NOT EXISTS refresh_commitments_default
PARTITION OF refresh_commitments
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_commitments_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_commitments_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_commitments_' || partition_suffix ||
'_melt_serial_id_key '
- 'UNIQUE (melt_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_refresh_commitments_partition('default');
-- ------------------------------ refresh_revealed_coins
----------------------------------------
-CREATE TABLE IF NOT EXISTS refresh_revealed_coins
- (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,melt_serial_id INT8 NOT NULL -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE
- ,freshcoin_index INT4 NOT NULL
- ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)
- ,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
- ,coin_ev BYTEA NOT NULL -- UNIQUE
- ,h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64) -- UNIQUE
- ,ev_sig BYTEA NOT NULL
- ,ewv BYTEA NOT NULL
- -- ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
- )
- PARTITION BY HASH (melt_serial_id);
+SELECT create_table_refresh_revealed_coins();
+
COMMENT ON TABLE refresh_revealed_coins
IS 'Revelations about the new coins that are to be created during a melting
session.';
COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
@@ -592,46 +363,17 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
COMMENT ON COLUMN refresh_revealed_coins.ev_sig
IS 'exchange signature over the envelope';
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
- ON refresh_revealed_coins
- (melt_serial_id);
-
CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
PARTITION OF refresh_revealed_coins
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_revealed_coins_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_revealed_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_rrc_serial_key '
- 'UNIQUE (rrc_serial) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_coin_ev_key '
- 'UNIQUE (coin_ev) '
- ',ADD CONSTRAINT refresh_revealed_coins_' || partition_suffix ||
'_h_coin_ev_key '
- 'UNIQUE (h_coin_ev) '
- ',ADD PRIMARY KEY (melt_serial_id, freshcoin_index) '
- );
-END
-$$;
-
SELECT add_constraints_to_refresh_revealed_coins_partition('default');
-- ------------------------------ refresh_transfer_keys
----------------------------------------
-CREATE TABLE IF NOT EXISTS refresh_transfer_keys
- (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,melt_serial_id INT8 PRIMARY KEY -- REFERENCES refresh_commitments
(melt_serial_id) ON DELETE CASCADE
- ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)
- ,transfer_privs BYTEA NOT NULL
- )
- PARTITION BY HASH (melt_serial_id);
+SELECT create_table_refresh_transfer_keys();
+
COMMENT ON TABLE refresh_transfer_keys
IS 'Transfer keys of a refresh operation (the data revealed to the
exchange).';
COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
@@ -647,21 +389,6 @@ CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
PARTITION OF refresh_transfer_keys
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_refresh_transfer_keys_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refresh_transfer_keys_' || partition_suffix || ' '
- 'ADD CONSTRAINT refresh_transfer_keys_' || partition_suffix ||
'_rtc_serial_key '
- 'UNIQUE (rtc_serial)'
- );
-END
-$$;
-
SELECT add_constraints_to_refresh_transfer_keys_partition('default');
@@ -683,28 +410,8 @@ CREATE TABLE IF NOT EXISTS extension_details_default
-- ------------------------------ deposits
----------------------------------------
-CREATE TABLE IF NOT EXISTS deposits
- (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
- ,shard INT8 NOT NULL
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub) ON DELETE CASCADE
- ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON
DELETE CASCADE
- ,amount_with_fee_val INT8 NOT NULL
- ,amount_with_fee_frac INT4 NOT NULL
- ,wallet_timestamp INT8 NOT NULL
- ,exchange_timestamp INT8 NOT NULL
- ,refund_deadline INT8 NOT NULL
- ,wire_deadline INT8 NOT NULL
- ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
- ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
- ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
- ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
- ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
- ,done BOOLEAN NOT NULL DEFAULT FALSE
- ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE
- ,extension_details_serial_id INT8 REFERENCES extension_details
(extension_details_serial_id) ON DELETE CASCADE
- ,UNIQUE (coin_pub, merchant_pub, h_contract_terms)
- )
- PARTITION BY HASH (coin_pub);
+SELECT create_table_deposits();
+
COMMENT ON TABLE deposits
IS 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
COMMENT ON COLUMN deposits.shard
@@ -722,65 +429,28 @@ COMMENT ON COLUMN deposits.extension_blocked
COMMENT ON COLUMN deposits.extension_details_serial_id
IS 'References extensions table, NULL if extensions are not used';
-CREATE INDEX IF NOT EXISTS deposits_by_coin_pub_index
- ON deposits
- (coin_pub);
-
CREATE TABLE IF NOT EXISTS deposits_default
PARTITION OF deposits
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE deposits_' || partition_suffix || ' '
- 'ADD CONSTRAINT deposits_' || partition_suffix ||
'_deposit_serial_id_pkey '
- 'PRIMARY KEY (deposit_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_deposits_partition('default');
-CREATE TABLE IF NOT EXISTS deposits_by_ready
- (wire_deadline INT8 NOT NULL
- ,shard INT8 NOT NULL
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub) ON DELETE CASCADE
- ,deposit_serial_id INT8
- )
- PARTITION BY RANGE (wire_deadline);
+SELECT create_table_deposits_by_ready();
+
COMMENT ON TABLE deposits_by_ready
IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER
below';
-CREATE INDEX IF NOT EXISTS deposits_by_ready_main_index
- ON deposits_by_ready
- (wire_deadline ASC, shard ASC, coin_pub);
-
CREATE TABLE IF NOT EXISTS deposits_by_ready_default
PARTITION OF deposits_by_ready
DEFAULT;
-CREATE TABLE IF NOT EXISTS deposits_for_matching
- (refund_deadline INT8 NOT NULL
- ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub) ON DELETE CASCADE
- ,deposit_serial_id INT8
- )
- PARTITION BY RANGE (refund_deadline);
+SELECT create_table_deposits_for_matching();
+
COMMENT ON TABLE deposits_for_matching
IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via
TRIGGER below';
-CREATE INDEX IF NOT EXISTS deposits_for_matching_main_index
- ON deposits_for_matching
- (refund_deadline ASC, merchant_pub, coin_pub);
-
CREATE TABLE IF NOT EXISTS deposits_for_matching_default
PARTITION OF deposits_for_matching
DEFAULT;
@@ -920,17 +590,8 @@ CREATE TRIGGER deposits_on_delete
-- ------------------------------ refunds
----------------------------------------
-CREATE TABLE IF NOT EXISTS refunds
- (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub) ON DELETE CASCADE
- ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id)
ON DELETE CASCADE
- ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)
- ,rtransaction_id INT8 NOT NULL
- ,amount_with_fee_val INT8 NOT NULL
- ,amount_with_fee_frac INT4 NOT NULL
- -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
- )
- PARTITION BY HASH (coin_pub);
+SELECT create_table_refunds();
+
COMMENT ON TABLE refunds
IS 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.';
COMMENT ON COLUMN refunds.deposit_serial_id
@@ -938,46 +599,17 @@ COMMENT ON COLUMN refunds.deposit_serial_id
COMMENT ON COLUMN refunds.rtransaction_id
IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
-CREATE INDEX IF NOT EXISTS refunds_by_coin_pub_index
- ON refunds
- (coin_pub);
-
CREATE TABLE IF NOT EXISTS refunds_default
PARTITION OF refunds
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_refunds_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE refunds_' || partition_suffix || ' '
- 'ADD CONSTRAINT refunds_' || partition_suffix || '_refund_serial_id_key '
- 'UNIQUE (refund_serial_id) '
- ',ADD PRIMARY KEY (deposit_serial_id, rtransaction_id) '
- );
-END
-$$;
-
SELECT add_constraints_to_refunds_partition('default');
-
-- ------------------------------ wire_out
----------------------------------------
-CREATE TABLE IF NOT EXISTS wire_out
- (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
- ,execution_date INT8 NOT NULL
- ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)
- ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
- ,exchange_account_section TEXT NOT NULL
- ,amount_val INT8 NOT NULL
- ,amount_frac INT4 NOT NULL
- )
- PARTITION BY HASH (wtid_raw);
+SELECT create_table_wire_out();
+
COMMENT ON TABLE wire_out
IS 'wire transfers the exchange has executed';
COMMENT ON COLUMN wire_out.exchange_account_section
@@ -985,32 +617,10 @@ COMMENT ON COLUMN wire_out.exchange_account_section
COMMENT ON COLUMN wire_out.wire_target_h_payto
IS 'Identifies the credited bank account and KYC status';
-CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index
- ON wire_out
- (wireout_uuid);
-CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index
- ON wire_out
- (wire_target_h_payto);
-
CREATE TABLE IF NOT EXISTS wire_out_default
PARTITION OF wire_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_wire_out_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE wire_out_' || partition_suffix || ' '
- 'ADD CONSTRAINT wire_out_' || partition_suffix || '_wireout_uuid_pkey '
- 'PRIMARY KEY (wireout_uuid)'
- );
-END
-$$;
-
SELECT add_constraints_to_wire_out_partition('default');
CREATE OR REPLACE FUNCTION wire_out_delete_trigger()
@@ -1034,14 +644,8 @@ CREATE TRIGGER wire_out_on_delete
-- ------------------------------ aggregation_transient
----------------------------------------
-CREATE TABLE IF NOT EXISTS aggregation_transient
- (amount_val INT8 NOT NULL
- ,amount_frac INT4 NOT NULL
- ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
- ,exchange_account_section TEXT NOT NULL
- ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)
- )
- PARTITION BY HASH (wire_target_h_payto);
+SELECT create_table_aggregation_transient();
+
COMMENT ON TABLE aggregation_transient
IS 'aggregations currently happening (lacking wire_out, usually because the
amount is too low); this table is not replicated';
COMMENT ON COLUMN aggregation_transient.amount_val
@@ -1054,15 +658,10 @@ CREATE TABLE IF NOT EXISTS aggregation_transient_default
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-- ------------------------------ aggregation_tracking
----------------------------------------
-CREATE TABLE IF NOT EXISTS aggregation_tracking
- (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits
(deposit_serial_id) ON DELETE CASCADE
- ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)
- )
- PARTITION BY HASH (deposit_serial_id);
+SELECT create_table_aggregation_tracking();
+
COMMENT ON TABLE aggregation_tracking
IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
COMMENT ON COLUMN aggregation_tracking.wtid_raw
@@ -1072,29 +671,8 @@ CREATE TABLE IF NOT EXISTS aggregation_tracking_default
PARTITION OF aggregation_tracking
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' '
- 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix ||
'_aggregation_serial_id_key '
- 'UNIQUE (aggregation_serial_id);'
- );
-END
-$$;
-
SELECT add_constraints_to_aggregation_tracking_partition('default');
-CREATE INDEX IF NOT EXISTS aggregation_tracking_by_wtid_raw_index
- ON aggregation_tracking
- (wtid_raw);
-COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
- IS 'for lookup_transactions';
-
-- ------------------------------ wire_fee
----------------------------------------
@@ -1155,17 +733,8 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
-- ------------------------------ recoup
----------------------------------------
-CREATE TABLE IF NOT EXISTS recoup
- (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub)
- ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
- ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
- ,amount_val INT8 NOT NULL
- ,amount_frac INT4 NOT NULL
- ,recoup_timestamp INT8 NOT NULL
- ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out
(reserve_out_serial_id) ON DELETE CASCADE
- )
- PARTITION BY HASH (coin_pub);
+SELECT create_table_recoup();
+
COMMENT ON TABLE recoup
IS 'Information about recoups that were executed between a coin and a
reserve. In this type of recoup, the amount is credited back to the reserve
from which the coin originated.';
COMMENT ON COLUMN recoup.coin_pub
@@ -1177,43 +746,18 @@ COMMENT ON COLUMN recoup.coin_sig
COMMENT ON COLUMN recoup.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the withdraw operation.';
-CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index
- ON recoup
- (coin_pub);
-
CREATE TABLE IF NOT EXISTS recoup_default
PARTITION OF recoup
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE recoup_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_' || partition_suffix || '_recoup_uuid_key '
- 'UNIQUE (recoup_uuid) '
- );
-END
-$$;
-
SELECT add_constraints_to_recoup_partition('default');
-CREATE TABLE IF NOT EXISTS recoup_by_reserve
- (reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves
(reserve_out_serial_id) ON DELETE CASCADE
- ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub)
- )
- PARTITION BY HASH (reserve_out_serial_id);
+
+SELECT create_table_recoup_by_reserve();
+
COMMENT ON TABLE recoup_by_reserve
IS 'Information in this table is strictly redundant with that of recoup, but
saved by a different primary key for fast lookups by reserve_out_serial_id.';
-CREATE INDEX IF NOT EXISTS recoup_by_reserve_main_index
- ON recoup_by_reserve
- (reserve_out_serial_id);
-
CREATE TABLE IF NOT EXISTS recoup_by_reserve_default
PARTITION OF recoup_by_reserve
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1260,18 +804,8 @@ CREATE TRIGGER recoup_on_delete
-- ------------------------------ recoup_refresh
----------------------------------------
-CREATE TABLE IF NOT EXISTS recoup_refresh
- (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins
(coin_pub)
- ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id)
- ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
- ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
- ,amount_val INT8 NOT NULL
- ,amount_frac INT4 NOT NULL
- ,recoup_timestamp INT8 NOT NULL
- ,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial)
ON DELETE CASCADE -- UNIQUE
- )
- PARTITION BY HASH (coin_pub);
+SELECT create_table_recoup_refresh();
+
COMMENT ON TABLE recoup_refresh
IS 'Table of coins that originated from a refresh operation and that were
recouped. Links the (fresh) coin to the melted operation (and thus the old
coin). A recoup on a refreshed coin credits the old coin and debits the fresh
coin.';
COMMENT ON COLUMN recoup_refresh.coin_pub
@@ -1283,47 +817,17 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
COMMENT ON COLUMN recoup_refresh.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the refresh operation.';
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
- ON recoup_refresh
- (coin_pub);
--- FIXME: any query using this index will be slow. Materialize index or change
query?
--- Also: which query uses this index?
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
- ON recoup_refresh
- (rrc_serial);
-
CREATE TABLE IF NOT EXISTS recoup_refresh_default
PARTITION OF recoup_refresh
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_recoup_refresh_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE recoup_refresh_' || partition_suffix || ' '
- 'ADD CONSTRAINT recoup_refresh_' || partition_suffix ||
'_recoup_refresh_uuid_key '
- 'UNIQUE (recoup_refresh_uuid) '
- );
-END
-$$;
-
SELECT add_constraints_to_recoup_refresh_partition('default');
-- ------------------------------ prewire
----------------------------------------
-CREATE TABLE IF NOT EXISTS prewire
- (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
- ,wire_method TEXT NOT NULL
- ,finished BOOLEAN NOT NULL DEFAULT false
- ,failed BOOLEAN NOT NULL DEFAULT false
- ,buf BYTEA NOT NULL
- )
- PARTITION BY HASH (prewire_uuid);
+SELECT create_table_prewire();
+
COMMENT ON TABLE prewire
IS 'pre-commit data for wire transfers we are about to execute';
COMMENT ON COLUMN prewire.failed
@@ -1333,18 +837,6 @@ COMMENT ON COLUMN prewire.finished
COMMENT ON COLUMN prewire.buf
IS 'serialized data to send to the bank to execute the wire transfer';
-CREATE INDEX IF NOT EXISTS prewire_by_finished_index
- ON prewire
- (finished);
-COMMENT ON INDEX prewire_by_finished_index
- IS 'for gc_prewire';
--- FIXME: find a way to combine these two indices?
-CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index
- ON prewire
- (failed,finished);
-COMMENT ON INDEX prewire_by_failed_finished_index
- IS 'for wire_prepare_data_get';
-
CREATE TABLE IF NOT EXISTS prewire_default
PARTITION OF prewire
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1374,13 +866,8 @@ COMMENT ON COLUMN wire_accounts.last_change
-- ------------------------------ cs_nonce_locks
----------------------------------------
-CREATE TABLE IF NOT EXISTS cs_nonce_locks
- (cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)
- ,op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)
- ,max_denomination_serial INT8 NOT NULL
- )
- PARTITION BY HASH (nonce);
+SELECT create_table_cs_nonce_locks();
+
COMMENT ON TABLE cs_nonce_locks
IS 'ensures a Clause Schnorr client nonce is locked for use with an
operation identified by a hash';
COMMENT ON COLUMN cs_nonce_locks.nonce
@@ -1394,21 +881,6 @@ CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
PARTITION OF cs_nonce_locks
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition(
- IN partition_suffix VARCHAR
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' '
- 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix ||
'_cs_nonce_lock_serial_id_key '
- 'UNIQUE (cs_nonce_lock_serial_id)'
- );
-END
-$$;
-
SELECT add_constraints_to_cs_nonce_locks_partition('default');
diff --git a/src/exchangedb/partition-0001.sql
b/src/exchangedb/partition-0001.sql
deleted file mode 100644
index ba326798..00000000
--- a/src/exchangedb/partition-0001.sql
+++ /dev/null
@@ -1,312 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
---
--- TALER is free software; you can redistribute it and/or modify it under the
--- terms of the GNU General Public License as published by the Free Software
--- Foundation; either version 3, or (at your option) any later version.
---
--- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
--- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
FOR
--- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
---
--- You should have received a copy of the GNU General Public License along with
--- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
---
-
--- Everything in one big transaction
-BEGIN;
-
--- Check patch versioning is in place.
--- SELECT _v.register_patch('partition-0001', NULL, NULL);
-
-CREATE OR REPLACE FUNCTION create_table_partition(
- source_table_name VARCHAR
- ,modulus INTEGER
- ,partition_num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating partition %_%', source_table_name, partition_num;
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)'
- ,source_table_name || '_' || partition_num
- ,source_table_name
- ,modulus
- ,partition_num-1
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION detach_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Detaching all default table partitions';
-
- ALTER TABLE IF EXISTS wire_targets
- DETACH PARTITION wire_targets_default;
-
- ALTER TABLE IF EXISTS reserves
- DETACH PARTITION reserves_default;
-
- ALTER TABLE IF EXISTS reserves_in
- DETACH PARTITION reserves_in_default;
-
- ALTER TABLE IF EXISTS reserves_close
- DETACH PARTITION reserves_close_default;
-
- ALTER TABLE IF EXISTS reserves_out
- DETACH PARTITION reserves_out_default;
-
- ALTER TABLE IF EXISTS known_coins
- DETACH PARTITION known_coins_default;
-
- ALTER TABLE IF EXISTS refresh_commitments
- DETACH PARTITION refresh_commitments_default;
-
- ALTER TABLE IF EXISTS refresh_revealed_coins
- DETACH PARTITION refresh_revealed_coins_default;
-
- ALTER TABLE IF EXISTS refresh_transfer_keys
- DETACH PARTITION refresh_transfer_keys_default;
-
- ALTER TABLE IF EXISTS deposits
- DETACH PARTITION deposits_default;
-
- ALTER TABLE IF EXISTS refunds
- DETACH PARTITION refunds_default;
-
- ALTER TABLE IF EXISTS wire_out
- DETACH PARTITION wire_out_default;
-
- ALTER TABLE IF EXISTS aggregation_tracking
- DETACH PARTITION aggregation_tracking_default;
-
- ALTER TABLE IF EXISTS recoup
- DETACH PARTITION recoup_default;
-
- ALTER TABLE IF EXISTS recoup_refresh
- DETACH PARTITION recoup_refresh_default;
-
- ALTER TABLE IF EXISTS prewire
- DETACH PARTITION prewire_default;
-
- ALTER TABLE IF EXISTS cs_nonce_locks
- DETACH partition cs_nonce_locks_default;
-
-END
-$$;
-
-COMMENT ON FUNCTION detach_default_partitions
- IS 'We need to drop default and create new one before deleting the default
partitions
- otherwise constraints get lost too. Might be needed in shardig too';
-
-
-CREATE OR REPLACE FUNCTION drop_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Dropping default table partitions';
-
- DROP TABLE IF EXISTS wire_targets_default;
- DROP TABLE IF EXISTS reserves_default;
- DROP TABLE IF EXISTS reserves_in_default;
- DROP TABLE IF EXISTS reserves_close_default;
- DROP TABLE IF EXISTS reserves_out_default;
- DROP TABLE IF EXISTS known_coins_default;
- DROP TABLE IF EXISTS refresh_commitments_default;
- DROP TABLE IF EXISTS refresh_revealed_coins_default;
- DROP TABLE IF EXISTS refresh_transfer_keys_default;
- DROP TABLE IF EXISTS deposits_default;
- DROP TABLE IF EXISTS refunds_default;
- DROP TABLE IF EXISTS wire_out_default;
- DROP TABLE IF EXISTS aggregation_tracking_default;
- DROP TABLE IF EXISTS recoup_default;
- DROP TABLE IF EXISTS recoup_refresh_default;
- DROP TABLE IF EXISTS prewire_default;
- DROP TABLE IF EXISTS cs_nonce_locks_default;
-
-END
-$$;
-
-COMMENT ON FUNCTION drop_default_partitions
- IS 'Drop all default partitions once other partitions are attached.
- Might be needed in sharding too.';
-
-CREATE OR REPLACE FUNCTION create_partitions(
- num_partitions INTEGER
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- modulus INTEGER;
-BEGIN
-
- modulus := num_partitions;
-
- PERFORM detach_default_partitions();
-
- LOOP
- PERFORM create_table_partition(
- 'wire_targets'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'reserves'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_table_partition(
- 'reserves_in'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_reserves_in_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'reserves_close'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_reserves_close_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'reserves_out'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_reserves_out_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'known_coins'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_known_coins_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'refresh_commitments'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_commitments_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'refresh_revealed_coins'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_revealed_coins_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'refresh_transfer_keys'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_refresh_transfer_keys_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'deposits'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_deposits_partition(num_partitions::varchar);
-
--- TODO: dynamically (!) creating/deleting deposits partitions:
--- create new partitions 'as needed', drop old ones once the aggregator has
made
--- them empty; as 'new' deposits will always have deadlines in the future,
this
--- would basically guarantee no conflict between aggregator and exchange
service!
--- SEE also:
https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
--- (article is slightly wrong, as this works:)
---CREATE TABLE tab (
--- id bigint GENERATED ALWAYS AS IDENTITY,
--- ts timestamp NOT NULL,
--- data text
--- PARTITION BY LIST ((ts::date));
--- CREATE TABLE tab_def PARTITION OF tab DEFAULT;
--- BEGIN
--- CREATE TABLE tab_part2 (LIKE tab);
--- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo');
--- alter table tab attach partition tab_part2 for values in ('2022-03-21');
--- commit;
--- Naturally, to ensure this is actually 100% conflict-free, we'd
--- need to create tables at the granularity of the wire/refund deadlines;
--- that is right now configurable via AGGREGATOR_SHIFT option.
-
-
- PERFORM create_table_partition(
- 'refunds'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_refunds_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'wire_out'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_wire_out_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'aggregation_tracking'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_aggregation_tracking_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'recoup'
- ,modulus
- ,num_partitions
- );
- PERFORM add_constraints_to_recoup_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'recoup_refresh'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_recoup_refresh_partition(num_partitions::varchar);
-
- PERFORM create_table_partition(
- 'prewire'
- ,modulus
- ,num_partitions
- );
-
- PERFORM create_table_partition(
- 'cs_nonce_locks'
- ,modulus
- ,num_partitions
- );
- PERFORM
add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar);
-
- num_partitions=num_partitions-1;
- EXIT WHEN num_partitions=0;
-
- END LOOP;
-
- PERFORM drop_default_partitions();
-
-END
-$$;
-
-COMMIT;
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c
b/src/exchangedb/plugin_exchangedb_postgres.c
index 8f0f5e22..1d14cb73 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -198,6 +198,49 @@ postgres_create_tables (void *cls)
}
+/**
+ * Create tables of a shard node with index idx
+ *
+ * @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param idx the shards index, will be appended as suffix to all tables
+ * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
+ */
+static enum GNUNET_GenericReturnValue
+postgres_create_shard_tables (void *cls,
+ uint32_t idx)
+{
+ struct PostgresClosure *pg = cls;
+ struct GNUNET_PQ_Context *conn;
+ enum GNUNET_GenericReturnValue ret;
+ struct GNUNET_PQ_QueryParam params[] = {
+ GNUNET_PQ_query_param_uint32 (&idx),
+ GNUNET_PQ_query_param_end
+ };
+ struct GNUNET_PQ_PreparedStatement ps[] = {
+ GNUNET_PQ_make_prepare ("create_shard_tables",
+ "SELECT"
+ " setup_shard"
+ " ($1);",
+ 1),
+ GNUNET_PQ_PREPARED_STATEMENT_END
+ };
+
+ conn = GNUNET_PQ_connect_with_cfg (pg->cfg,
+ "exchangedb-postgres",
+ "shard-",
+ NULL,
+ ps);
+ if (NULL == conn)
+ return GNUNET_SYSERR;
+ if (0 > GNUNET_PQ_eval_prepared_non_select (conn,
+ "create_shard_tables",
+ params))
+ ret = GNUNET_SYSERR;
+ GNUNET_PQ_disconnect (conn);
+ return ret;
+}
+
+
/**
* Setup partitions of already existing tables
*
@@ -207,7 +250,7 @@ postgres_create_tables (void *cls)
*/
static enum GNUNET_GenericReturnValue
postgres_setup_partitions (void *cls,
- const uint32_t num)
+ uint32_t num)
{
struct PostgresClosure *pg = cls;
struct GNUNET_PQ_Context *conn;
@@ -227,7 +270,7 @@ postgres_setup_partitions (void *cls,
conn = GNUNET_PQ_connect_with_cfg (pg->cfg,
"exchangedb-postgres",
- "partition-",
+ NULL,
NULL,
ps);
if (NULL == conn)
@@ -242,6 +285,49 @@ postgres_setup_partitions (void *cls,
}
+/**
+ * Setup foreign servers (shards) for already existing tables
+ *
+ * @param cls the `struct PostgresClosure` with the plugin-specific state
+ * @param num the number of foreign servers (shards) to create for each
partitioned table
+ * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
+ */
+static enum GNUNET_GenericReturnValue
+postgres_setup_foreign_servers (void *cls,
+ uint32_t num)
+{
+ struct PostgresClosure *pg = cls;
+ struct GNUNET_PQ_Context *conn;
+ enum GNUNET_GenericReturnValue ret;
+ struct GNUNET_PQ_QueryParam params[] = {
+ GNUNET_PQ_query_param_uint32 (&num),
+ GNUNET_PQ_query_param_end
+ };
+ struct GNUNET_PQ_PreparedStatement ps[] = {
+ GNUNET_PQ_make_prepare ("create_foreign_servers",
+ "SELECT"
+ " create_foreign_servers"
+ " ($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,
+ "create_foreign_servers",
+ params))
+ ret = GNUNET_SYSERR;
+ GNUNET_PQ_disconnect (conn);
+ return ret;
+}
+
+
/**
* Initialize prepared statements for @a pg.
*
@@ -13055,7 +13141,9 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
plugin->cls = pg;
plugin->drop_tables = &postgres_drop_tables;
plugin->create_tables = &postgres_create_tables;
+ plugin->create_shard_tables = &postgres_create_shard_tables;
plugin->setup_partitions = &postgres_setup_partitions;
+ plugin->setup_foreign_servers = &postgres_setup_foreign_servers;
plugin->start = &postgres_start;
plugin->start_read_committed = &postgres_start_read_committed;
plugin->commit = &postgres_commit;
diff --git a/src/include/taler_exchangedb_plugin.h
b/src/include/taler_exchangedb_plugin.h
index 488ffd51..7383913a 100644
--- a/src/include/taler_exchangedb_plugin.h
+++ b/src/include/taler_exchangedb_plugin.h
@@ -2254,6 +2254,17 @@ struct TALER_EXCHANGEDB_Plugin
enum GNUNET_GenericReturnValue
(*create_tables)(void *cls);
+ /**
+ * Initialize the database of a shard node
+ *
+ * @param cls the @e cls of this struct with the plugin-specific state
+ * @param idx the current shard index, will be appended to tables as suffix
+ * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
+ */
+ enum GNUNET_GenericReturnValue
+ (*create_shard_tables)(void *cls,
+ uint32_t idx);
+
/**
* Change already present tables of the database to num partitions
* Only has an effect if there are default partitions only
@@ -2264,7 +2275,23 @@ struct TALER_EXCHANGEDB_Plugin
*/
enum GNUNET_GenericReturnValue
(*setup_partitions)(void *cls,
- const uint32_t num);
+ uint32_t num);
+
+ /**
+ * Change already present tables of the database to num foreign tables on
+ * num foreign servers (shards).
+ * Only has an effect if there are default partitions only
+ *
+ * @param cls the @e cls of this struct with the plugin-specific state
+ * @param num the number of shard servers to create. The shard servers
+ * must follow the numbering of 1-N, have the same user as
+ * the master and have tables named <table>_n where n is the same
+ * as the servers index of N.
+ * @return #GNUNET_OK upon success; #GNUNET_SYSERR upon failure
+ */
+ enum GNUNET_GenericReturnValue
+ (*setup_foreign_servers)(void *cls,
+ uint32_t num);
/**
* Start a transaction.
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-exchange] branch master updated (6c66f6f5 -> 1b76ba5e),
gnunet <=
- [taler-exchange] 04/05: add permissions, gnunet, 2022/04/02
- [taler-exchange] 03/05: remove built sql files from vc, gnunet, 2022/04/02
- [taler-exchange] 02/05: add shard init for shard node, gnunet, 2022/04/02
- [taler-exchange] 05/05: Include sharding sql logic, gnunet, 2022/04/02
- [taler-exchange] 01/05: add sharding logic, gnunet, 2022/04/02