[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-grid5k] 114/189: test with sharding
|
From: |
gnunet |
|
Subject: |
[taler-grid5k] 114/189: test with sharding |
|
Date: |
Thu, 28 Apr 2022 10:48:04 +0200 |
This is an automated email from the git hooks/post-receive script.
marco-boss pushed a commit to branch master
in repository grid5k.
commit 4fb7a844d572bfebe3ec3c28216ae074d12cc0b4
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Mon Mar 28 21:48:24 2022 +0200
test with sharding
---
experiment/scripts/database.sh | 17 +-
experiment/scripts/shard.sh | 11 +-
sql/exchange-0001.sql | 1291 ++++++++++++++++++++++++++++------------
sql/exchange-0002.sql | 257 --------
sql/exchange-shard-0000.sql | 227 -------
sql/exchange-tables.sql | 1139 +++++++++++++++++++++++++++++++++++
sql/partition-0001.sql | 601 +++++++++++++++++++
sql/shard-0001.sql | 69 +++
8 files changed, 2725 insertions(+), 887 deletions(-)
diff --git a/experiment/scripts/database.sh b/experiment/scripts/database.sh
index 16c28ec..468b366 100755
--- a/experiment/scripts/database.sh
+++ b/experiment/scripts/database.sh
@@ -108,7 +108,7 @@ function setup_config() {
shared_preload_libraries='pg_stat_statements,auto_explain'
# Should be set locally
- # join_collapse_limit=1
+ join_collapse_limit=1
# Large tables perform bad with the default settings
# However, they could also be set on each table indiviudally
@@ -236,27 +236,26 @@ function enable_remote_access() {
fi
}
-function setup_distributed_db() {
+function setup_shards() {
+ su postgres << EOF
+psql -d "${DB_NAME}" -f ${G5K_HOME}/sql/exchange-tables.sql
+EOF
cp ${G5K_HOME}/sql/exchange-0001.sql /usr/share/taler/sql/exchange/
- cp ${G5K_HOME}/sql/exchange-0002.sql /usr/share/taler/sql/exchange/
+
chmod o+r /usr/share/taler/sql/exchange/exchange-0001.sql
- chmod o+r /usr/share/taler/sql/exchange/exchange-0002.sql
sudo -u taler-exchange-httpd taler-exchange-dbinit -r || true
sudo -u taler-exchange-httpd taler-exchange-dbinit -s || true
sudo -u taler-exchange-httpd taler-exchange-dbinit
-}
-
-function setup_shards() {
- setup_distributed_db
su postgres << EOF
+psql -d "${DB_NAME}" -f ${G5K_HOME}/sql/partition-0001.sql
psql -d "${DB_NAME}" -tAc "CREATE EXTENSION IF NOT EXISTS postgres_fdw;"
EOF
su taler-exchange-httpd -s /bin/bash << EOF
-psql -d "${DB_NAME}" -tAc "SELECT prepare_sharding();"
+psql -d "${DB_NAME}" -tAc "SELECT master_prepare_sharding();"
EOF
let "i=1"
diff --git a/experiment/scripts/shard.sh b/experiment/scripts/shard.sh
index bf72819..4f6ad9b 100755
--- a/experiment/scripts/shard.sh
+++ b/experiment/scripts/shard.sh
@@ -152,14 +152,19 @@ psql -tAc "SELECT 1 FROM pg_database WHERE
datname='${DB_NAME}'" | \
createdb -O "${DB_USER}" "${DB_NAME}"
EOF
- cp ${G5K_HOME}/sql/exchange-shard-0000.sql /tmp
- chmod o+r /tmp/exchange-shard-0000.sql
+ cp ${G5K_HOME}/sql/shard-0001.sql ${G5K_HOME$/sql/exchange-tables.sql /tmp
+ chmod o+r /tmp/shard-0001.sql /tmp/exchange-tables.sql
PGPASSWORD=${DB_PASSWORD} psql -tA \
-U ${DB_USER} \
-h localhost \
-d ${DB_NAME} \
- -f /tmp/exchange-shard-0000.sql
+ -f /tmp/exchange-tables.sql
+ PGPASSWORD=${DB_PASSWORD} psql -tA \
+ -U ${DB_USER} \
+ -h localhost \
+ -d ${DB_NAME} \
+ -f /tmp/shard-0000.sql
PGPASSWORD=${DB_PASSWORD} psql -tA \
-U ${DB_USER} \
-h localhost \
diff --git a/sql/exchange-0001.sql b/sql/exchange-0001.sql
index 54cc8af..eb5860a 100644
--- a/sql/exchange-0001.sql
+++ b/sql/exchange-0001.sql
@@ -20,7 +20,6 @@ BEGIN;
-- Check patch versioning is in place.
SELECT _v.register_patch('exchange-0001', NULL, NULL);
-
CREATE TABLE IF NOT EXISTS denominations
(denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
@@ -66,45 +65,28 @@ COMMENT ON TABLE denomination_revocations
IS 'remembering which denomination keys have been revoked';
-CREATE TABLE IF NOT EXISTS wire_targets
- (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64)
- ,payto_uri VARCHAR NOT NULL
- ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
- ,external_id VARCHAR
- )
- PARTITION BY HASH (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
IS 'Can be a regular bank account, or also be a URI identifying a
reserve-account (for P2P payments)';
-COMMENT ON COLUMN wire_targets.h_payto
+COMMENT ON COLUMN wire_targets.wire_target_h_payto
IS 'Unsalted hash of payto_uri';
COMMENT ON COLUMN wire_targets.kyc_ok
IS 'true if the KYC check was passed successfully';
COMMENT ON COLUMN wire_targets.external_id
IS 'Name of the user that was used for OAuth 2.0-based legitimization';
+
CREATE TABLE IF NOT EXISTS wire_targets_default
PARTITION OF wire_targets
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
--- FIXME partition by serial_id rather than h_payto,
--- it is used more in join conditions - crucial for sharding to select this.
--- Author: (Boss Marco)
-CREATE INDEX IF NOT EXISTS wire_targets_wire_target_serial_id
- ON wire_targets
- (wire_target_serial_id
- );
+SELECT add_constraints_to_wire_targets_partition('default');
+
+
+SELECT create_table_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);
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
@@ -115,84 +97,38 @@ 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';
-
-
-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_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
- ,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_serial_id
+COMMENT ON COLUMN reserves_in.wire_source_h_payto
IS 'Identifies the debited bank account and KYC status';
COMMENT ON COLUMN reserves_in.reserve_pub
IS 'Public key of the reserve. Private key signifies ownership of the
remaining balance.';
COMMENT ON COLUMN reserves_in.credit_val
IS 'Amount that was transferred into the reserve';
+
CREATE TABLE IF NOT EXISTS reserves_in_default
PARTITION OF reserves_in
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
- ON reserves_in
- (reserve_in_serial_id);
-CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_section_execution_date_index
- ON reserves_in
- (exchange_account_section
- ,execution_date
- );
-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
- );
+SELECT add_constraints_to_reserves_in_partition('default');
-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_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
- ,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_serial_id
+COMMENT ON COLUMN reserves_close.wire_target_h_payto
IS 'Identifies the credited bank account (and KYC status). Note that closing
does not depend on KYC.';
-CREATE TABLE IF NOT EXISTS reserves_close_default
- PARTITION OF reserves_close
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index
ON reserves_close
@@ -201,37 +137,27 @@ 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);
+
+SELECT add_constraints_to_reserves_close_partition('default');
+
+
+SELECT create_table_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);
COMMENT ON TABLE reserves_out
IS 'Withdraw operations performed on reserves.';
COMMENT ON COLUMN reserves_out.h_blind_ev
IS 'Hash of the blinded coin, used as primary key here so that broken
clients that use a non-random coin or blinding factor fail to withdraw
(otherwise they would fail on deposit when the coin is not unique there).';
COMMENT ON COLUMN reserves_out.denominations_serial
IS 'We do not CASCADE ON DELETE here, we may keep the denomination data
alive';
+
CREATE TABLE IF NOT EXISTS reserves_out_default
PARTITION OF reserves_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
- ON reserves_out
- (reserve_out_serial_id);
-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';
+SELECT add_constraints_to_reserves_out_partition('default');
CREATE TABLE IF NOT EXISTS auditors
@@ -315,16 +241,8 @@ COMMENT ON COLUMN extensions.config
IS 'Configuration of the extension as JSON-blob, maybe NULL';
-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); -- FIXME: or include denominations_serial? or
multi-level partitioning?
+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
@@ -337,26 +255,16 @@ COMMENT ON COLUMN known_coins.age_commitment_hash
IS 'Optional hash of the age commitment for age restrictions as per DD 24
(active if denom_type has the respective bit set)';
COMMENT ON COLUMN known_coins.denom_sig
IS 'This is the signature of the exchange that affirms that the coin is a
valid coin. The specific signature type depends on denom_type of the
denomination.';
+
CREATE TABLE IF NOT EXISTS known_coins_default
PARTITION OF known_coins
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
- ON known_coins
- (known_coin_id);
+SELECT add_constraints_to_known_coins_partition('default');
-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
- ,h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)
- ,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
@@ -365,33 +273,16 @@ COMMENT ON COLUMN refresh_commitments.rc
IS 'Commitment made by the client, hash over the various client inputs in
the cut-and-choose protocol';
COMMENT ON COLUMN refresh_commitments.old_coin_pub
IS 'Coin being melted in the refresh process.';
-COMMENT ON COLUMN refresh_commitments.h_age_commitment
- IS 'The (optional) age commitment that was involved in the minting process
of the coin, may be NULL.';
+
CREATE TABLE IF NOT EXISTS refresh_commitments_default
PARTITION OF refresh_commitments
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_melt_serial_id_index
- ON refresh_commitments
- (melt_serial_id);
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
- ON refresh_commitments
- (old_coin_pub);
+SELECT add_constraints_to_refresh_commitments_partition('default');
-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
@@ -408,28 +299,16 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
IS 'hash of the envelope of the new coin to be signed (for lookups)';
COMMENT ON COLUMN refresh_revealed_coins.ev_sig
IS 'exchange signature over the envelope';
+
CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default
PARTITION OF refresh_revealed_coins
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
--- We do require this primary key on each shard!
-ALTER TABLE refresh_revealed_coins_default
- ADD PRIMARY KEY (melt_serial_id, freshcoin_index);
-
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
- ON refresh_revealed_coins
- (rrc_serial);
-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_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 add_constraints_to_refresh_revealed_coins_partition('default');
+
+
+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
@@ -440,13 +319,12 @@ COMMENT ON COLUMN refresh_transfer_keys.transfer_pub
IS 'transfer public key for the gamma index';
COMMENT ON COLUMN refresh_transfer_keys.transfer_privs
IS 'array of TALER_CNC_KAPPA - 1 transfer private keys that have been
revealed, with the gamma entry being skipped';
+
CREATE TABLE IF NOT EXISTS refresh_transfer_keys_default
PARTITION OF refresh_transfer_keys
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
- ON refresh_transfer_keys
- (rtc_serial);
+SELECT add_constraints_to_refresh_transfer_keys_partition('default');
CREATE TABLE IF NOT EXISTS extension_details
@@ -458,37 +336,38 @@ COMMENT ON COLUMN extension_details.extension_options
IS 'JSON object with options set that the exchange needs to consider when
executing a deposit. Supported details depend on the extensions supported by
the exchange.';
-CREATE TABLE IF NOT EXISTS deposits
- (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
- ,shard INT8 NOT NULL
- ,known_coin_id INT8 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_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
- ,tiny BOOLEAN NOT NULL DEFAULT FALSE
- ,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 (shard, known_coin_id, merchant_pub, h_contract_terms)
- )
- PARTITION BY HASH (shard);
-CREATE TABLE IF NOT EXISTS deposits_default
- PARTITION OF deposits
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+SELECT create_table_deposits();
+-- FIXME:
+-- TODO: dynamically (!) creating/deleting 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 seconds (!). But I see no problem with changing the
+-- aggregator to basically always run 1 minute behind and use minutes instead!
+
COMMENT ON TABLE deposits
IS 'Deposits we have received and for which we need to make (aggregate) wire
transfers (and manage refunds).';
COMMENT ON COLUMN deposits.shard
- IS 'Used for load sharding. Should be set based on h_payto and merchant_pub.
64-bit value because we need an *unsigned* 32-bit value.';
-COMMENT ON COLUMN deposits.wire_target_serial_id
+ IS 'Used for load sharding. Should be set based on merchant_pub. 64-bit
value because we need an *unsigned* 32-bit value.';
+COMMENT ON COLUMN deposits.known_coin_id
+ IS 'Used for garbage collection';
+COMMENT ON COLUMN deposits.wire_target_h_payto
IS 'Identifies the target bank account and KYC status';
COMMENT ON COLUMN deposits.wire_salt
IS 'Salt used when hashing the payto://-URI to get the h_wire';
@@ -501,113 +380,233 @@ COMMENT ON COLUMN deposits.extension_details_serial_id
COMMENT ON COLUMN deposits.tiny
IS 'Set to TRUE if we decided that the amount is too small to ever trigger a
wire transfer by itself (requires real aggregation)';
--- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
--- thereby resulting in a much better use of the index: we could do
(shard,deposit_serial_id)!
-CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
- ON deposits
- (deposit_serial_id);
-CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index
- ON deposits
- (shard ASC
- ,done
- ,extension_blocked
- ,tiny
- ,wire_deadline ASC
- );
-COMMENT ON INDEX deposits_for_get_ready_index
- IS 'for deposits_get_ready';
--- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
--- thereby resulting in a much better use of the index: we could do
(shard,merchant_pub, ...)!
-CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index
- ON deposits
- (merchant_pub
- ,wire_target_serial_id
- ,done
- ,extension_blocked
- ,refund_deadline ASC
- );
-COMMENT ON INDEX deposits_for_iterate_matching_index
- IS 'for deposits_iterate_matching';
+CREATE TABLE IF NOT EXISTS deposits_default
+ PARTITION OF deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+SELECT add_constraints_to_deposits_partition('default');
+
+
+SELECT create_table_deposits_by_ready();
+
+COMMENT ON TABLE deposits_by_ready
+ IS 'Enables fast lookups for deposits_get_ready, auto-populated via TRIGGER
below';
+
+CREATE TABLE IF NOT EXISTS deposits_by_ready_default
+ PARTITION OF deposits_by_ready
+ DEFAULT;
+
+
+SELECT create_table_deposits_for_matching();
+
+COMMENT ON TABLE deposits_for_matching
+ IS 'Enables fast lookups for deposits_iterate_matching, auto-populated via
TRIGGER below';
+
+CREATE TABLE IF NOT EXISTS deposits_for_matching_default
+ PARTITION OF deposits_for_matching
+ DEFAULT;
+
+
+CREATE OR REPLACE FUNCTION deposits_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ is_ready BOOLEAN;
+DECLARE
+ is_tready BOOLEAN; -- is ready, but may be tiny
+BEGIN
+ is_ready = NOT (NEW.done OR NEW.tiny OR NEW.extension_blocked);
+ is_tready = NOT (NEW.done OR NEW.extension_blocked);
+
+ IF (is_ready)
+ THEN
+ INSERT INTO deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ IF (is_tready)
+ THEN
+ INSERT INTO deposits_for_matching
+ (refund_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_insert_trigger()
+ IS 'Replicate deposit inserts into materialized indices.';
+
+CREATE TRIGGER deposits_on_insert
+ AFTER INSERT
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_insert_trigger();
+
+CREATE OR REPLACE FUNCTION deposits_update_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+DECLARE
+ is_ready BOOLEAN;
+DECLARE
+ was_tready BOOLEAN; -- was ready, but may be tiny
+DECLARE
+ is_tready BOOLEAN; -- is ready, but may be tiny
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.tiny OR OLD.extension_blocked);
+ is_ready = NOT (NEW.done OR NEW.tiny OR NEW.extension_blocked);
+ was_tready = NOT (OLD.done OR OLD.extension_blocked);
+ is_tready = NOT (NEW.done OR NEW.extension_blocked);
+ IF (was_ready AND NOT is_ready)
+ THEN
+ DELETE FROM deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ IF (was_tready AND NOT is_tready)
+ THEN
+ DELETE FROM deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ IF (is_ready AND NOT was_ready)
+ THEN
+ INSERT INTO deposits_by_ready
+ (wire_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.wire_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ IF (is_tready AND NOT was_tready)
+ THEN
+ INSERT INTO deposits_for_matching
+ (refund_deadline
+ ,shard
+ ,coin_pub
+ ,deposit_serial_id)
+ VALUES
+ (NEW.refund_deadline
+ ,NEW.shard
+ ,NEW.coin_pub
+ ,NEW.deposit_serial_id);
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_update_trigger()
+ IS 'Replicate deposits changes into materialized indices.';
+
+CREATE TRIGGER deposits_on_update
+ AFTER UPDATE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_update_trigger();
+
+CREATE OR REPLACE FUNCTION deposits_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+DECLARE
+ was_ready BOOLEAN;
+DECLARE
+ was_tready BOOLEAN; -- is ready, but may be tiny
+BEGIN
+ was_ready = NOT (OLD.done OR OLD.tiny OR OLD.extension_blocked);
+ was_tready = NOT (OLD.done OR OLD.extension_blocked);
+
+ IF (was_ready)
+ THEN
+ DELETE FROM deposits_by_ready
+ WHERE wire_deadline = OLD.wire_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ IF (was_tready)
+ THEN
+ DELETE FROM deposits_for_matching
+ WHERE refund_deadline = OLD.refund_deadline
+ AND shard = OLD.shard
+ AND coin_pub = OLD.coin_pub
+ AND deposit_serial_id = OLD.deposit_serial_id;
+ END IF;
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION deposits_delete_trigger()
+ IS 'Replicate deposit deletions into materialized indices.';
+
+CREATE TRIGGER deposits_on_delete
+ AFTER DELETE
+ ON deposits
+ FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
+
+
+SELECT create_table_refunds();
-CREATE TABLE IF NOT EXISTS refunds
- (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,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 (deposit_serial_id);
COMMENT ON TABLE refunds
IS 'Data on coins that were refunded. Technically, refunds always apply
against specific deposit operations involving a coin. The combination of
coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique,
and we usually select by coin_pub so that one goes first.';
COMMENT ON COLUMN refunds.deposit_serial_id
- IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id.
Multiple deposits may match a refund, this only identifies one of them.';
+ IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub.
Multiple deposits may match a refund, this only identifies one of them.';
COMMENT ON COLUMN refunds.rtransaction_id
IS 'used by the merchant to make refunds unique in case the same coin for
the same deposit gets a subsequent (higher) refund';
+
CREATE TABLE IF NOT EXISTS refunds_default
PARTITION OF refunds
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-ALTER TABLE refunds_default
- ADD PRIMARY KEY (deposit_serial_id, rtransaction_id);
-CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
- ON refunds
- (refund_serial_id);
+SELECT add_constraints_to_refunds_partition('default');
-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_serial_id INT8 NOT NULL -- REFERENCES wire_targets
(wire_target_serial_id)
- ,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
IS 'identifies the configuration section with the debit account of this
payment';
-COMMENT ON COLUMN wire_out.wire_target_serial_id
+COMMENT ON COLUMN wire_out.wire_target_h_payto
IS 'Identifies the credited bank account and KYC status';
+
CREATE TABLE IF NOT EXISTS wire_out_default
PARTITION OF wire_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-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_serial_id_index
- ON wire_out
- (wire_target_serial_id);
+SELECT add_constraints_to_wire_out_partition('default');
+SELECT create_table_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 CONSTRAINT wire_out_ref REFERENCES
wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
- )
- PARTITION BY HASH (deposit_serial_id);
COMMENT ON TABLE aggregation_tracking
IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
COMMENT ON COLUMN aggregation_tracking.wtid_raw
IS 'We first create entries in the aggregation_tracking table and then
finally the wire_out entry once we know the total amount. Hence the constraint
must be deferrable and we cannot use a wireout_uuid here, because we do not
have it when these rows are created. Changing the logic to first INSERT a dummy
row into wire_out and then UPDATEing that row in the same transaction would
theoretically reduce per-deposit storage costs by 5 percent (24/~460 bytes).';
+
CREATE TABLE IF NOT EXISTS aggregation_tracking_default
PARTITION OF aggregation_tracking
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS aggregation_tracking_by_aggregation_serial_id_index
- ON aggregation_tracking
- (aggregation_serial_id);
-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';
+SELECT add_constraints_to_aggregation_tracking_partition('default');
CREATE TABLE IF NOT EXISTS wire_fee
@@ -619,6 +618,8 @@ CREATE TABLE IF NOT EXISTS wire_fee
,wire_fee_frac INT4 NOT NULL
,closing_fee_val INT8 NOT NULL
,closing_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
,PRIMARY KEY (wire_method, start_date)
);
@@ -632,20 +633,40 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
(end_date);
-CREATE TABLE IF NOT EXISTS recoup
- (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,known_coin_id INT8 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
- ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out
(reserve_out_serial_id) ON DELETE CASCADE
- )
- PARTITION BY HASH (known_coin_id);
+CREATE TABLE IF NOT EXISTS global_fee
+ (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,history_fee_val INT8 NOT NULL
+ ,history_fee_frac INT4 NOT NULL
+ ,kyc_fee_val INT8 NOT NULL
+ ,kyc_fee_frac INT4 NOT NULL
+ ,account_fee_val INT8 NOT NULL
+ ,account_fee_frac INT4 NOT NULL
+ ,purse_fee_val INT8 NOT NULL
+ ,purse_fee_frac INT4 NOT NULL
+ ,purse_timeout INT8 NOT NULL
+ ,kyc_timeout INT8 NOT NULL
+ ,history_expiration INT8 NOT NULL
+ ,purse_account_limit INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,PRIMARY KEY (start_date)
+ );
+COMMENT ON TABLE global_fee
+ IS 'list of the global fees of this exchange, by date';
+COMMENT ON COLUMN global_fee.global_fee_serial
+ IS 'needed for exchange-auditor replication logic';
+
+CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
+ ON global_fee
+ (end_date);
+
+
+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.known_coin_id
+COMMENT ON COLUMN recoup.coin_pub
IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the
coin_pub, as we may keep the coin alive!';
COMMENT ON COLUMN recoup.reserve_out_serial_id
IS 'Identifies the h_blind_ev of the recouped coin and provides the link to
the credited reserve.';
@@ -653,63 +674,138 @@ COMMENT ON COLUMN recoup.coin_sig
IS 'Signature by the coin affirming the recoup, of type
TALER_SIGNATURE_WALLET_COIN_RECOUP';
COMMENT ON COLUMN recoup.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the withdraw operation.';
+
CREATE TABLE IF NOT EXISTS recoup_default
PARTITION OF recoup
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
- ON recoup
- (recoup_uuid);
-CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
- ON recoup
- (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
- ON recoup
- (known_coin_id);
+SELECT add_constraints_to_recoup_partition('default');
-CREATE TABLE IF NOT EXISTS recoup_refresh
- (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,known_coin_id INT8 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 (known_coin_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 TABLE IF NOT EXISTS recoup_by_reserve_default
+ PARTITION OF recoup_by_reserve
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION recoup_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO recoup_by_reserve
+ (reserve_out_serial_id
+ ,coin_pub)
+ VALUES
+ (NEW.reserve_out_serial_id
+ ,NEW.coin_pub);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION recoup_insert_trigger()
+ IS 'Replicate recoup inserts into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_insert
+ AFTER INSERT
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
+
+
+CREATE OR REPLACE FUNCTION recoup_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM recoup_by_reserve
+ WHERE reserve_out_serial_id = OLD.reserve_out_serial_id
+ AND coin_pub = OLD.coin_pub;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION recoup_delete_trigger()
+ IS 'Replicate recoup deletions into recoup_by_reserve table.';
+
+CREATE TRIGGER recoup_on_delete
+ AFTER DELETE
+ ON recoup
+ FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
+
+
+
+SELECT create_table_reserves_out_by_reserve();
+
+COMMENT ON TABLE reserves_out_by_reserve
+ IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
+
+
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
+ PARTITION OF reserves_out_by_reserve
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ INSERT INTO reserves_out_by_reserve
+ (reserve_uuid
+ ,h_blind_ev)
+ VALUES
+ (NEW.reserve_uuid
+ ,NEW.h_blind_ev);
+ RETURN NEW;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
+ IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_insert
+ AFTER INSERT
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
+
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
+ RETURNS trigger
+ LANGUAGE plpgsql
+ AS $$
+BEGIN
+ DELETE FROM reserves_out_by_reserve
+ WHERE reserve_uuid = OLD.reserve_uuid;
+ RETURN OLD;
+END $$;
+COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
+ IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_delete
+ AFTER DELETE
+ ON reserves_out
+ FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+
+
+
+SELECT create_table_recoup_refresh();
+
COMMENT ON TABLE recoup_refresh
IS 'Table of coins that originated from a refresh operation and that were
recouped. Links the (fresh) coin to the melted operation (and thus the old
coin). A recoup on a refreshed coin credits the old coin and debits the fresh
coin.';
+COMMENT ON COLUMN recoup_refresh.coin_pub
+ IS 'Refreshed coin of a revoked denomination where the residual value is
credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may
keep the coin alive!';
COMMENT ON COLUMN recoup_refresh.known_coin_id
- IS 'Refreshed coin of a revoked denomination where the residual value is
credited to the old coin. Do not CASCADE ON DROP on the known_coin_id, as we
may keep the coin alive!';
+ IS 'FIXME: (To be) used for garbage collection (in the future)';
COMMENT ON COLUMN recoup_refresh.rrc_serial
IS 'Link to the refresh operation. Also identifies the h_blind_ev of the
recouped coin (as h_coin_ev).';
COMMENT ON COLUMN recoup_refresh.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the refresh operation.';
+
CREATE TABLE IF NOT EXISTS recoup_refresh_default
PARTITION OF recoup_refresh
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
- ON recoup_refresh
- (recoup_refresh_uuid);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
- ON recoup_refresh
- (rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
- ON recoup_refresh
- (known_coin_id);
-
-
-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 add_constraints_to_recoup_refresh_partition('default');
+
+
+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
@@ -718,21 +814,11 @@ COMMENT ON COLUMN prewire.finished
IS 'set to TRUE once bank confirmed receiving the wire transfer request';
COMMENT ON COLUMN prewire.buf
IS 'serialized data to send to the bank to execute the wire transfer';
+
CREATE TABLE IF NOT EXISTS prewire_default
PARTITION OF prewire
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-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 wire_accounts
@@ -755,13 +841,8 @@ COMMENT ON COLUMN wire_accounts.last_change
-- and is of no concern to the auditor
-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
@@ -771,6 +852,12 @@ COMMENT ON COLUMN cs_nonce_locks.op_hash
COMMENT ON COLUMN cs_nonce_locks.max_denomination_serial
IS 'Maximum number of a CS denomination serial the nonce could be used with,
for GC';
+CREATE TABLE IF NOT EXISTS cs_nonce_locks_default
+ PARTITION OF cs_nonce_locks
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+SELECT add_constraints_to_cs_nonce_locks_partition('default');
+
CREATE TABLE IF NOT EXISTS work_shards
(shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
@@ -835,6 +922,344 @@ CREATE INDEX IF NOT EXISTS
revolving_work_shards_by_job_name_active_last_attempt
,last_attempt
);
+-- Tables for P2P payments
+
+CREATE TABLE IF NOT EXISTS partners
+ (partner_serial_id BIGSERIAL UNIQUE
+ ,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32)
+ ,start_date INT8 NOT NULL
+ ,end_date INT8 NOT NULL
+ ,wad_frequency INT8 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+ ,partner_base_url TEXT NOT NULL
+ );
+COMMENT ON TABLE partners
+ IS 'exchanges we do wad transfers to';
+COMMENT ON COLUMN partners.partner_master_pub
+ IS 'offline master public key of the partner';
+COMMENT ON COLUMN partners.start_date
+ IS 'starting date of the partnership';
+COMMENT ON COLUMN partners.end_date
+ IS 'end date of the partnership';
+COMMENT ON COLUMN partners.wad_frequency
+ IS 'how often do we promise to do wad transfers';
+COMMENT ON COLUMN partners.wad_fee_val
+ IS 'how high is the fee for a wallet to be added to a wad to this partner';
+COMMENT ON COLUMN partners.partner_base_url
+ IS 'base URL of the REST API for this partner';
+COMMENT ON COLUMN partners.master_sig
+ IS 'signature of our master public key affirming the partnership, of purpose
TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
+
+
+CREATE TABLE IF NOT EXISTS purse_requests
+ (purse_deposit_serial_id BIGSERIAL UNIQUE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)
+ ,purse_expiration INT8 NOT NULL
+ ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
+ ,age_limit INT4 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,balance_val INT8 NOT NULL DEFAULT (0)
+ ,balance_frac INT4 NOT NULL DEFAULT (0)
+ ,purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)
+ ,PRIMARY KEY (purse_pub)
+ ); -- partition by purse_pub
+COMMENT ON TABLE purse_requests
+ IS 'Requests establishing purses, associating them with a contract but
without a target reserve';
+COMMENT ON COLUMN purse_requests.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN purse_requests.purse_expiration
+ IS 'When the purse is set to expire';
+COMMENT ON COLUMN purse_requests.h_contract_terms
+ IS 'Hash of the contract the parties are to agree to';
+COMMENT ON COLUMN purse_requests.amount_with_fee_val
+ IS 'Total amount expected to be in the purse';
+COMMENT ON COLUMN purse_requests.balance_val
+ IS 'Total amount actually in the purse';
+COMMENT ON COLUMN purse_requests.purse_sig
+ IS 'Signature of the purse affirming the purse parameters, of type
TALER_SIGNATURE_PURSE_REQUEST';
+
+-- FIXME: create purse_by_merge materialized index table
+-- for merge_pub => purse_pub mapping!
+
+
+CREATE TABLE IF NOT EXISTS purse_merges
+ (purse_merge_request_serial_id BIGSERIAL -- UNIQUE
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)--REFERENCES
reserves (reserve_pub) ON DELETE CASCADE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) --REFERENCES
purse_requests (purse_pub) ON DELETE CASCADE
+ ,merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)
+ ,merge_timestamp INT8 NOT NULL
+ ,PRIMARY KEY (purse_pub)
+ ); -- partition by purse_pub; plus materialized index by reserve_pub!
+COMMENT ON TABLE purse_merges
+ IS 'Merge requests where a purse-owner requested merging the purse into the
account';
+COMMENT ON COLUMN purse_merges.partner_serial_id
+ IS 'identifies the partner exchange, NULL in case the target reserve lives
at this exchange';
+COMMENT ON COLUMN purse_merges.reserve_pub
+ IS 'public key of the target reserve';
+COMMENT ON COLUMN purse_merges.purse_pub
+ IS 'public key of the purse';
+COMMENT ON COLUMN purse_merges.merge_sig
+ IS 'signature by the purse private key affirming the merge, of type
TALER_SIGNATURE_WALLET_PURSE_MERGE';
+COMMENT ON COLUMN purse_merges.merge_timestamp
+ IS 'when was the merge message signed';
+CREATE INDEX IF NOT EXISTS purse_merges_reserve_pub
+ ON purse_merges (reserve_pub);
+COMMENT ON INDEX purse_merges_reserve_pub
+ IS 'needed in reserve history computation';
+
+
+CREATE TABLE IF NOT EXISTS account_mergers
+ (account_merge_request_serial_id BIGSERIAL -- UNIQUE
+ ,reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) -- REFERENCES
reserves (reserve_pub) ON DELETE CASCADE
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) -- REFERENCES
purse_requests (purse_pub)
+ ,PRIMARY KEY (reserve_pub)
+ ); -- partition by purse_pub; plus materialized index by reserve_pub!
+COMMENT ON TABLE account_mergers
+ IS 'Merge requests where a purse- and account-owner requested merging the
purse into the account';
+COMMENT ON COLUMN account_mergers.reserve_pub
+ IS 'public key of the target reserve';
+COMMENT ON COLUMN account_mergers.purse_pub
+ IS 'public key of the purse';
+COMMENT ON COLUMN account_mergers.reserve_sig
+ IS 'signature by the reserve private key affirming the merge, of type
TALER_SIGNATURE_WALLET_ACCOUNT_MERGE';
+
+CREATE INDEX IF NOT EXISTS account_mergers_purse_pub
+ ON account_mergers (purse_pub);
+COMMENT ON INDEX account_mergers_purse_pub
+ IS 'needed when checking for a purse merge status';
+
+
+CREATE TABLE IF NOT EXISTS contracts
+ (contract_serial_id BIGSERIAL UNIQUE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)
+ ,e_contract BYTEA NOT NULL
+ ,purse_expiration INT8 NOT NULL
+ ,PRIMARY KEY (purse_pub)
+ ); -- partition by purse_pub
+COMMENT ON TABLE contracts
+ IS 'encrypted contracts associated with purses';
+COMMENT ON COLUMN contracts.purse_pub
+ IS 'public key of the purse that the contract is associated with';
+COMMENT ON COLUMN contracts.pub_ckey
+ IS 'Public ECDH key used to encrypt the contract, to be used with the purse
private key for decryption';
+COMMENT ON COLUMN contracts.e_contract
+ IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after
decryption)';
+
+CREATE TABLE IF NOT EXISTS history_requests
+ (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES
reserves(reserve_pub) ON DELETE CASCADE
+ ,request_timestamp INT8 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,history_fee_val INT8 NOT NULL
+ ,history_fee_frac INT4 NOT NULL
+ ,PRIMARY KEY (reserve_pub,request_timestamp)
+ ); -- partition by reserve_pub
+COMMENT ON TABLE history_requests
+ IS 'Paid history requests issued by a client against a reserve';
+COMMENT ON COLUMN history_requests.request_timestamp
+ IS 'When was the history request made';
+COMMENT ON COLUMN history_requests.reserve_sig
+ IS 'Signature approving payment for the history request';
+COMMENT ON COLUMN history_requests.history_fee_val
+ IS 'History fee approved by the signature';
+
+CREATE TABLE IF NOT EXISTS close_requests
+ (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES
reserves(reserve_pub) ON DELETE CASCADE
+ ,close_timestamp INT8 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,close_val INT8 NOT NULL
+ ,close_frac INT4 NOT NULL
+ ,PRIMARY KEY (reserve_pub,close_timestamp)
+ ); -- partition by reserve_pub
+COMMENT ON TABLE close_requests
+ IS 'Explicit requests by a reserve owner to close a reserve immediately';
+COMMENT ON COLUMN close_requests.close_timestamp
+ IS 'When the request was created by the client';
+COMMENT ON COLUMN close_requests.reserve_sig
+ IS 'Signature affirming that the reserve is to be closed';
+COMMENT ON COLUMN close_requests.close_val
+ IS 'Balance of the reserve at the time of closing, to be wired to the
associated bank account (minus the closing fee)';
+
+
+CREATE TABLE IF NOT EXISTS purse_deposits
+ (purse_deposit_serial_id BIGSERIAL UNIQUE
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
+ ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
+ ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
+ ,PRIMARY KEY (purse_pub,coin_pub)
+ ); -- partition by purse_pub, plus a materialized index by coin_pub!
+COMMENT ON TABLE purse_deposits
+ IS 'Requests depositing coins into a purse';
+COMMENT ON COLUMN purse_deposits.partner_serial_id
+ IS 'identifies the partner exchange, NULL in case the target purse lives at
this exchange';
+COMMENT ON COLUMN purse_deposits.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN purse_deposits.coin_pub
+ IS 'Public key of the coin being deposited';
+COMMENT ON COLUMN purse_deposits.amount_with_fee_val
+ IS 'Total amount being deposited';
+COMMENT ON COLUMN purse_deposits.coin_sig
+ IS 'Signature of the coin affirming the deposit into the purse, of type
TALER_SIGNATURE_PURSE_DEPOSIT';
+
+CREATE TABLE IF NOT EXISTS wads_out
+ (wad_out_serial_id BIGSERIAL UNIQUE
+ ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
+ ,partner_serial_id INT8 NOT NULL REFERENCES partners(partner_serial_id) ON
DELETE CASCADE
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,execution_time INT8 NOT NULL
+ ); -- partition by wad_id
+COMMENT ON TABLE wads_out
+ IS 'Wire transfers made to another exchange to transfer purse funds';
+COMMENT ON COLUMN wads_out.wad_id
+ IS 'Unique identifier of the wad, part of the wire transfer subject';
+COMMENT ON COLUMN wads_out.partner_serial_id
+ IS 'target exchange of the wad';
+COMMENT ON COLUMN wads_out.amount_val
+ IS 'Amount that was wired';
+COMMENT ON COLUMN wads_out.execution_time
+ IS 'Time when the wire transfer was scheduled';
+
+CREATE TABLE IF NOT EXISTS wad_out_entries
+ (wad_out_entry_serial_id BIGSERIAL UNIQUE
+ ,wad_out_serial_id INT8 REFERENCES wads_out (wad_out_serial_id) ON DELETE
CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
+ ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
+ ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
+ ,purse_expiration INT8 NOT NULL
+ ,merge_timestamp INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,deposit_fees_val INT8 NOT NULL
+ ,deposit_fees_frac INT4 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
+ ); -- partition by purse_pub? do we need a materialized index by reserve_pub?
+CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_wad
+ ON wad_out_entries (wad_out_serial_id);
+COMMENT ON TABLE wad_out_entries
+ IS 'Purses combined into a wad';
+COMMENT ON COLUMN wad_out_entries.wad_out_serial_id
+ IS 'Wad the purse was part of';
+COMMENT ON COLUMN wad_out_entries.reserve_pub
+ IS 'Target reserve for the purse';
+COMMENT ON COLUMN wad_out_entries.purse_pub
+ IS 'Public key of the purse';
+COMMENT ON COLUMN wad_out_entries.h_contract
+ IS 'Hash of the contract associated with the purse';
+COMMENT ON COLUMN wad_out_entries.purse_expiration
+ IS 'Time when the purse expires';
+COMMENT ON COLUMN wad_out_entries.merge_timestamp
+ IS 'Time when the merge was approved';
+COMMENT ON COLUMN wad_out_entries.amount_with_fee_val
+ IS 'Total amount in the purse';
+COMMENT ON COLUMN wad_out_entries.wad_fee_val
+ IS 'Wat fee charged to the purse';
+COMMENT ON COLUMN wad_out_entries.deposit_fees_val
+ IS 'Total deposit fees charged to the purse';
+COMMENT ON COLUMN wad_out_entries.reserve_sig
+ IS 'Signature by the receiving reserve, of purpose
TALER_SIGNATURE_ACCOUNT_MERGE';
+COMMENT ON COLUMN wad_out_entries.purse_sig
+ IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
+
+CREATE TABLE IF NOT EXISTS wads_in
+ (wad_in_serial_id BIGSERIAL UNIQUE
+ ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
+ ,origin_exchange_url TEXT NOT NULL
+ ,amount_val INT8 NOT NULL
+ ,amount_frac INT4 NOT NULL
+ ,arrival_time INT8 NOT NULL
+ ,UNIQUE (wad_id, origin_exchange_url)
+ ); -- partition by wad_id
+COMMENT ON TABLE wads_in
+ IS 'Incoming exchange-to-exchange wad wire transfers';
+COMMENT ON COLUMN wads_in.wad_id
+ IS 'Unique identifier of the wad, part of the wire transfer subject';
+COMMENT ON COLUMN wads_in.origin_exchange_url
+ IS 'Base URL of the originating URL, also part of the wire transfer subject';
+COMMENT ON COLUMN wads_in.amount_val
+ IS 'Actual amount that was received by our exchange';
+COMMENT ON COLUMN wads_in.arrival_time
+ IS 'Time when the wad was received';
+
+CREATE TABLE IF NOT EXISTS wad_in_entries
+ (wad_in_entry_serial_id BIGSERIAL UNIQUE
+ ,wad_in_serial_id INT8 REFERENCES wads_in (wad_in_serial_id) ON DELETE
CASCADE
+ ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
+ ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
+ ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
+ ,purse_expiration INT8 NOT NULL
+ ,merge_timestamp INT8 NOT NULL
+ ,amount_with_fee_val INT8 NOT NULL
+ ,amount_with_fee_frac INT4 NOT NULL
+ ,wad_fee_val INT8 NOT NULL
+ ,wad_fee_frac INT4 NOT NULL
+ ,deposit_fees_val INT8 NOT NULL
+ ,deposit_fees_frac INT4 NOT NULL
+ ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
+ ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
+ ); -- partition by purse or reserve? likely need both (so extra table?)
+COMMENT ON TABLE wad_in_entries
+ IS 'list of purses aggregated in a wad according to the sending exchange';
+COMMENT ON COLUMN wad_in_entries.wad_in_serial_id
+ IS 'wad for which the given purse was included in the aggregation';
+COMMENT ON COLUMN wad_in_entries.reserve_pub
+ IS 'target account of the purse (must be at the local exchange)';
+COMMENT ON COLUMN wad_in_entries.purse_pub
+ IS 'public key of the purse that was merged';
+COMMENT ON COLUMN wad_in_entries.h_contract
+ IS 'hash of the contract terms of the purse';
+COMMENT ON COLUMN wad_in_entries.purse_expiration
+ IS 'Time when the purse was set to expire';
+COMMENT ON COLUMN wad_in_entries.merge_timestamp
+ IS 'Time when the merge was approved';
+COMMENT ON COLUMN wad_in_entries.amount_with_fee_val
+ IS 'Total amount in the purse';
+COMMENT ON COLUMN wad_in_entries.wad_fee_val
+ IS 'Total wad fees paid by the purse';
+COMMENT ON COLUMN wad_in_entries.deposit_fees_val
+ IS 'Total deposit fees paid when depositing coins into the purse';
+COMMENT ON COLUMN wad_in_entries.reserve_sig
+ IS 'Signature by the receiving reserve, of purpose
TALER_SIGNATURE_ACCOUNT_MERGE';
+COMMENT ON COLUMN wad_in_entries.purse_sig
+ IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
+CREATE INDEX IF NOT EXISTS wad_in_entries_wad_in_serial
+ ON wad_in_entries (wad_in_serial_id);
+CREATE INDEX IF NOT EXISTS wad_in_entries_reserve_pub
+ ON wad_in_entries (reserve_pub);
+COMMENT ON INDEX wad_in_entries_wad_in_serial
+ IS 'needed to lookup all transfers associated with a wad';
+COMMENT ON INDEX wad_in_entries_reserve_pub
+ IS 'needed to compute reserve history';
+
+CREATE TABLE IF NOT EXISTS partner_accounts
+ (payto_uri VARCHAR PRIMARY KEY
+ ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
+ ,partner_master_sig BYTEA CHECK (LENGTH(partner_master_sig)=64)
+ ,last_seen INT8 NOT NULL
+ );
+CREATE INDEX IF NOT EXISTS partner_accounts_index_by_partner_and_time
+ ON partner_accounts (partner_serial_id,last_seen);
+COMMENT ON TABLE partner_accounts
+ IS 'Table with bank accounts of the partner exchange. Entries never expire
as we need to remember the signature for the auditor.';
+COMMENT ON COLUMN partner_accounts.payto_uri
+ IS 'payto URI (RFC 8905) with the bank account of the partner exchange.';
+COMMENT ON COLUMN partner_accounts.partner_master_sig
+ IS 'Signature of purpose TALER_SIGNATURE_MASTER_WIRE_DETAILS by the partner
master public key';
+COMMENT ON COLUMN partner_accounts.last_seen
+ IS 'Last time we saw this account as being active at the partner exchange.
Used to select the most recent entry, and to detect when we should check
again.';
+
-- Stored procedures
@@ -870,7 +1295,7 @@ BEGIN
-- reserves_out (INSERT, with CONFLICT detection) by wih
-- reserves by reserve_pub (UPDATE)
-- reserves_in by reserve_pub (SELECT)
--- wire_targets by wire_target_serial_id
+-- wire_targets by wire_target_h_payto
SELECT denominations_serial
INTO denom_serial
@@ -1027,12 +1452,12 @@ END IF;
-- this reserve. FIXME: likely not adequate for reserves that got P2P
transfers!
SELECT
kyc_ok
- ,wire_source_serial_id
+ ,wire_target_serial_id
INTO
kycok
,account_uuid
FROM reserves_in
- JOIN wire_targets ON (wire_source_serial_id = wire_target_serial_id)
+ JOIN wire_targets ON (wire_source_h_payto = wire_target_h_payto)
WHERE reserve_pub=rpub
LIMIT 1; -- limit 1 should not be required (without p2p transfers)
@@ -1097,8 +1522,8 @@ IF EXISTS (
SELECT 1
FROM information_Schema.constraint_column_usage
WHERE table_name='wire_out'
- AND constraint_name='wire_out_ref')
-THEN
+ AND constraint_name='wire_out_ref')
+THEN
SET CONSTRAINTS wire_out_ref DEFERRED;
END IF;
@@ -1135,7 +1560,7 @@ DECLARE
BEGIN
-- Shards: INSERT extension_details (by extension_details_serial_id)
-- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
--- INSERT deposits (by shard + known_coin_id, merchant_pub,
h_contract_terms), ON CONFLICT DO NOTHING;
+-- INSERT deposits (by coin_pub, shard), ON CONFLICT DO NOTHING;
-- UPDATE known_coins (by coin_pub)
IF NOT NULL in_extension_details
@@ -1151,12 +1576,12 @@ END IF;
INSERT INTO wire_targets
- (h_payto
+ (wire_target_h_payto
,payto_uri)
VALUES
(in_h_payto
,in_receiver_wire_account)
-ON CONFLICT DO NOTHING -- for CONFLICT ON (h_payto)
+ON CONFLICT DO NOTHING -- for CONFLICT ON (wire_target_h_payto)
RETURNING wire_target_serial_id INTO wtsi;
IF NOT FOUND
@@ -1164,12 +1589,13 @@ THEN
SELECT wire_target_serial_id
INTO wtsi
FROM wire_targets
- WHERE h_payto=in_h_payto;
+ WHERE wire_target_h_payto=in_h_payto;
END IF;
INSERT INTO deposits
(shard
+ ,coin_pub
,known_coin_id
,amount_with_fee_val
,amount_with_fee_frac
@@ -1181,12 +1607,13 @@ INSERT INTO deposits
,h_contract_terms
,coin_sig
,wire_salt
- ,wire_target_serial_id
+ ,wire_target_h_payto
,extension_blocked
,extension_details_serial_id
)
VALUES
(in_shard
+ ,in_coin_pub
,in_known_coin_id
,in_amount_with_fee_val
,in_amount_with_fee_frac
@@ -1198,7 +1625,7 @@ INSERT INTO deposits
,in_h_contract_terms
,in_coin_sig
,in_wire_salt
- ,wtsi
+ ,in_h_payto
,in_extension_blocked
,xdi)
ON CONFLICT DO NOTHING;
@@ -1208,19 +1635,18 @@ THEN
-- Idempotency check: see if an identical record exists.
-- Note that by checking 'coin_sig', we implicitly check
-- identity over everything that the signature covers.
- -- We do select over merchant_pub and h_contract_terms
+ -- We do select over merchant_pub and wire_target_h_payto
-- primarily here to maximally use the existing index.
SELECT
exchange_timestamp
INTO
out_exchange_timestamp
FROM deposits
- WHERE
- shard=in_shard AND
- known_coin_id=in_known_coin_id AND
- merchant_pub=in_merchant_pub AND
- h_contract_terms=in_h_contract_terms AND
- coin_sig=in_coin_sig;
+ WHERE shard=in_shard
+ AND merchant_pub=in_merchant_pub
+ AND wire_target_h_payto=in_h_payto
+ AND coin_pub=in_coin_pub
+ AND coin_sig=in_coin_sig;
IF NOT FOUND
THEN
@@ -1284,7 +1710,6 @@ CREATE OR REPLACE FUNCTION exchange_do_melt(
IN in_old_coin_pub BYTEA,
IN in_old_coin_sig BYTEA,
IN in_known_coin_id INT8, -- not used, but that's OK
- IN in_h_age_commitment BYTEA,
IN in_noreveal_index INT4,
IN in_zombie_required BOOLEAN,
OUT out_balance_ok BOOLEAN,
@@ -1307,7 +1732,6 @@ INSERT INTO refresh_commitments
,old_coin_sig
,amount_with_fee_val
,amount_with_fee_frac
- ,h_age_commitment
,noreveal_index
)
VALUES
@@ -1316,7 +1740,6 @@ INSERT INTO refresh_commitments
,in_old_coin_sig
,in_amount_with_fee_val
,in_amount_with_fee_frac
- ,in_h_age_commitment
,in_noreveal_index)
ON CONFLICT DO NOTHING;
@@ -1482,7 +1905,7 @@ DECLARE
DECLARE
deposit_frac INT8; -- amount that was originally deposited
BEGIN
--- Shards: SELECT deposits (by shard, known_coin_id,h_contract_terms,
merchant_pub)
+-- Shards: SELECT deposits (coin_pub, shard, h_contract_terms, merchant_pub)
-- INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT
DO NOTHING
-- SELECT refunds (by deposit_serial_id)
-- UPDATE known_coins (by coin_pub)
@@ -1498,10 +1921,10 @@ INTO
,deposit_frac
,out_gone
FROM deposits
-WHERE shard=in_deposit_shard
- AND known_coin_id=in_known_coin_id
- AND h_contract_terms=in_h_contract_terms
- AND merchant_pub=in_merchant_pub;
+ WHERE coin_pub=in_coin_pub
+ AND shard=in_deposit_shard
+ AND merchant_pub=in_merchant_pub
+ AND h_contract_terms=in_h_contract_terms;
IF NOT FOUND
THEN
@@ -1516,6 +1939,7 @@ END IF;
INSERT INTO refunds
(deposit_serial_id
+ ,shard
,merchant_sig
,rtransaction_id
,amount_with_fee_val
@@ -1523,6 +1947,7 @@ INSERT INTO refunds
)
VALUES
(dsi
+ ,in_deposit_shard
,in_merchant_sig
,in_rtransaction_id
,in_amount_with_fee_val
@@ -1538,11 +1963,11 @@ THEN
-- primarily here to maximally use the existing index.
PERFORM
FROM refunds
- WHERE
- deposit_serial_id=dsi AND
- rtransaction_id=in_rtransaction_id AND
- amount_with_fee_val=in_amount_with_fee_val AND
- amount_with_fee_frac=in_amount_with_fee_frac;
+ WHERE shard=in_deposit_shard
+ AND deposit_serial_id=dsi
+ AND rtransaction_id=in_rtransaction_id
+ AND amount_with_fee_val=in_amount_with_fee_val
+ AND amount_with_fee_frac=in_amount_with_fee_frac;
IF NOT FOUND
THEN
@@ -1581,8 +2006,8 @@ SELECT
tmp_val
,tmp_frac
FROM refunds
- WHERE
- deposit_serial_id=dsi;
+ WHERE shard=in_deposit_shard
+ AND deposit_serial_id=dsi;
IF tmp_val IS NULL
THEN
RAISE NOTICE 'failed to sum up existing refunds';
@@ -1669,10 +2094,10 @@ DECLARE
tmp_frac INT8; -- amount recouped
BEGIN
-- Shards: SELECT known_coins (by coin_pub)
--- SELECT recoup (by known_coin_id)
+-- SELECT recoup (by coin_pub)
-- UPDATE known_coins (by coin_pub)
-- UPDATE reserves (by reserve_pub)
--- INSERT recoup (by known_coin_id)
+-- INSERT recoup (by coin_pub)
out_internal_failure=FALSE;
@@ -1702,7 +2127,7 @@ THEN
INTO
out_recoup_timestamp
FROM recoup
- WHERE known_coin_id=in_known_coin_id;
+ WHERE coin_pub=in_coin_pub;
out_recoup_ok=FOUND;
RETURN;
@@ -1747,7 +2172,7 @@ END IF;
INSERT INTO recoup
- (known_coin_id
+ (coin_pub
,coin_sig
,coin_blind
,amount_val
@@ -1756,7 +2181,7 @@ INSERT INTO recoup
,reserve_out_serial_id
)
VALUES
- (in_known_coin_id
+ (in_coin_pub
,in_coin_sig
,in_coin_blind
,tmp_val
@@ -1798,9 +2223,9 @@ DECLARE
BEGIN
-- Shards: UPDATE known_coins (by coin_pub)
--- SELECT recoup_refresh (by known_coin_id)
+-- SELECT recoup_refresh (by coin_pub)
-- UPDATE known_coins (by coin_pub)
--- INSERT recoup_refresh (by known_coin_id)
+-- INSERT recoup_refresh (by coin_pub)
out_internal_failure=FALSE;
@@ -1831,7 +2256,7 @@ THEN
INTO
out_recoup_timestamp
FROM recoup_refresh
- WHERE known_coin_id=in_known_coin_id;
+ WHERE coin_pub=in_coin_pub;
out_recoup_ok=FOUND;
RETURN;
END IF;
@@ -1872,7 +2297,8 @@ END IF;
INSERT INTO recoup_refresh
- (known_coin_id
+ (coin_pub
+ ,known_coin_id
,coin_sig
,coin_blind
,amount_val
@@ -1881,7 +2307,8 @@ INSERT INTO recoup_refresh
,rrc_serial
)
VALUES
- (in_known_coin_id
+ (in_coin_pub
+ ,in_known_coin_id
,in_coin_sig
,in_coin_blind
,tmp_val
@@ -1942,7 +2369,7 @@ SELECT
DELETE FROM recoup
WHERE reserve_out_serial_id < reserve_out_min;
-
+-- FIXME: recoup_refresh lacks GC!
SELECT
reserve_uuid
@@ -1955,7 +2382,8 @@ SELECT
DELETE FROM reserves_out
WHERE reserve_uuid < reserve_uuid_min;
-
+-- FIXME: this query will be horribly slow;
+-- need to find another way to formulate it...
DELETE FROM denominations
WHERE expire_legal < in_now
AND denominations_serial NOT IN
@@ -1964,14 +2392,14 @@ DELETE FROM denominations
AND denominations_serial NOT IN
(SELECT DISTINCT denominations_serial
FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
FROM recoup))
AND denominations_serial NOT IN
(SELECT DISTINCT denominations_serial
FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
FROM recoup_refresh));
SELECT
@@ -2027,5 +2455,86 @@ DELETE FROM cs_nonce_locks
END $$;
+
+
+
+
+
+
+
+CREATE OR REPLACE FUNCTION exchange_do_purse_deposit(
+ IN in_purse_pub BYTEA,
+ IN in_amount_with_fee_val INT8,
+ IN in_amount_with_fee_frac INT4,
+ IN in_coin_pub BYTEA,
+ IN in_coin_sig BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_purse_merge(
+ IN in_purse_pub BYTEA,
+ IN in_merge_sig BYTEA,
+ IN in_merge_timestamp INT8,
+ IN in_partner_url VARCHAR,
+ IN in_reserve_pub BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_account_merge(
+ IN in_purse_pub BYTEA,
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_history_request(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ IN in_request_timestamp INT8,
+ IN in_history_fee_val INT8,
+ IN in_history_fee_frac INT4,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+CREATE OR REPLACE FUNCTION exchange_do_close_request(
+ IN in_reserve_pub BYTEA,
+ IN in_reserve_sig BYTEA,
+ OUT out_final_balance_val INT8,
+ OUT out_final_balance_frac INT4,
+ OUT out_balance_ok BOOLEAN,
+ OUT out_conflict BOOLEAN)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ -- FIXME
+END $$;
+
+
+
-- Complete transaction
COMMIT;
+
diff --git a/sql/exchange-0002.sql b/sql/exchange-0002.sql
deleted file mode 100644
index dd55aab..0000000
--- a/sql/exchange-0002.sql
+++ /dev/null
@@ -1,257 +0,0 @@
-CREATE OR REPLACE FUNCTION detach_default_partitions()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Detaching 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;
-END
-$$;
-
-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;
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_partition(
- source_table VARCHAR,
- modulus INTEGER,
- num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating partition %_%', source_table, num;
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s)',
- source_table || '_' || num,
- source_table,
- modulus,
- num-1
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION setup_partitions(
- num_partitions INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-DECLARE
- modulus INTEGER;
-BEGIN
-
- modulus := num_partitions;
-
- PERFORM detach_default_partitions();
-
- LOOP
- -- PERFORM create_partition('wire_targets', modulus, num_partitions);
- PERFORM create_partition('reserves', modulus, num_partitions);
- PERFORM create_partition('reserves_in', modulus, num_partitions);
- PERFORM create_partition('reserves_close', modulus, num_partitions);
- PERFORM create_partition('reserves_out', modulus, num_partitions);
- PERFORM create_partition('known_coins', modulus, num_partitions);
- PERFORM create_partition('refresh_commitments', modulus, num_partitions);
- PERFORM create_partition('refresh_revealed_coins', modulus,
num_partitions);
- PERFORM create_partition('refresh_transfer_keys', modulus, num_partitions);
- PERFORM create_partition('deposits', modulus, num_partitions);
- PERFORM create_partition('refunds', modulus, num_partitions);
- PERFORM create_partition('wire_out', modulus, num_partitions);
- PERFORM create_partition('aggregation_tracking', modulus, num_partitions);
- PERFORM create_partition('recoup', modulus, num_partitions);
- PERFORM create_partition('recoup_refresh', modulus, num_partitions);
- PERFORM create_partition('prewire', modulus, num_partitions);
-
- num_partitions=num_partitions-1;
- EXIT WHEN num_partitions=0;
-
- END LOOP;
-
- PERFORM drop_default_partitions();
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_foreign_table(
- source_table VARCHAR,
- modulus INTEGER,
- suffix VARCHAR,
- num INTEGER
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating %_% on shard_%', source_table, suffix, suffix;
-
- EXECUTE FORMAT(
- 'CREATE FOREIGN TABLE IF NOT EXISTS %I '
- 'PARTITION OF %I '
- 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) '
- 'SERVER %I',
- source_table || '_' || suffix,
- source_table,
- modulus,
- num-1,
- 'shard_' || suffix
- );
-
- EXECUTE FORMAT(
- 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"',
- source_table || '_' || suffix
- );
-
-END
-$$;
-
-CREATE OR REPLACE FUNCTION prepare_sharding()
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- 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;
- ALTER TABLE IF EXISTS reserves_out DROP CONSTRAINT IF EXISTS
reserves_out_denominations_serial_fkey;
-
- ALTER TABLE IF EXISTS known_coins DROP CONSTRAINT IF EXISTS known_coins_pkey
CASCADE;
- ALTER TABLE IF EXISTS known_coins DROP CONSTRAINT IF EXISTS
known_coins_denominations_serial_fkey;
-
- ALTER TABLE IF EXISTS refresh_commitments DROP CONSTRAINT IF EXISTS
refresh_commitments_pkey CASCADE;
- ALTER TABLE IF EXISTS refresh_commitments 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;
- ALTER TABLE IF EXISTS refresh_revealed_coins 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;
- ALTER TABLE IF EXISTS deposits DROP CONSTRAINT IF EXISTS
deposits_extension_details_serial_id_fkey;
- ALTER TABLE IF EXISTS deposits 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;
- ALTER TABLE IF EXISTS wire_out DROP CONSTRAINT IF EXISTS
wire_out_wtid_raw_key CASCADE;
-
- ALTER TABLE IF EXISTS aggregation_tracking DROP CONSTRAINT IF EXISTS
aggregation_tracking_pkey CASCADE;
- ALTER TABLE IF EXISTS aggregation_tracking 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;
-END
-$$;
-
-CREATE OR REPLACE FUNCTION create_shard_server(
- host VARCHAR,
- port INTEGER,
- usr VARCHAR,
- passw VARCHAR,
- suffix VARCHAR,
- num_shards INTEGER,
- shard_idx INTEGER,
- db_name VARCHAR
- )
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- RAISE NOTICE 'Creating server shard_%', suffix;
-
- EXECUTE FORMAT(
- 'CREATE SERVER IF NOT EXISTS %I '
- 'FOREIGN DATA WRAPPER postgres_fdw '
- 'OPTIONS (dbname %L, host %L, port %L)',
- 'shard_' || suffix,
- db_name,
- host,
- port
- );
-
- EXECUTE FORMAT(
- 'CREATE USER MAPPING IF NOT EXISTS FOR "taler-exchange-httpd" SERVER %I '
- 'OPTIONS (user %L, password %L)',
- 'shard_' || suffix,
- usr,
- passw
- );
-
- PERFORM create_foreign_table('wire_targets', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('reserves', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('reserves_in', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('reserves_close', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('reserves_out', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('known_coins', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('refresh_commitments', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('refresh_revealed_coins', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('refresh_transfer_keys', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('deposits', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('refunds', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('wire_out', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('aggregation_tracking', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('recoup', num_shards, suffix, shard_idx);
- PERFORM create_foreign_table('recoup_refresh', num_shards, suffix,
shard_idx);
- PERFORM create_foreign_table('prewire', num_shards, suffix, shard_idx);
-
-END
-$$;
diff --git a/sql/exchange-shard-0000.sql b/sql/exchange-shard-0000.sql
deleted file mode 100644
index 5f16d62..0000000
--- a/sql/exchange-shard-0000.sql
+++ /dev/null
@@ -1,227 +0,0 @@
-CREATE OR REPLACE FUNCTION setup_shard_tables(suffix VARCHAR)
- RETURNS VOID
- LANGUAGE plpgsql
-AS $$
-BEGIN
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(wire_target_serial_id BIGINT UNIQUE '
- ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64) '
- ',payto_uri VARCHAR NOT NULL '
- ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE) '
- ',external_id VARCHAR '
- ')',
- 'wire_targets_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(reserve_uuid BIGINT '
- ',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 '
- ')',
- 'reserves_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(reserve_in_serial_id BIGINT UNIQUE '
- ',reserve_pub BYTEA PRIMARY KEY '
- ',wire_reference INT8 NOT NULL '
- ',credit_val INT8 NOT NULL '
- ',credit_frac INT4 NOT NULL '
- ',wire_source_serial_id INT8 NOT NULL '
- ',exchange_account_section TEXT NOT NULL '
- ',execution_date INT8 NOT NULL '
- ')',
- 'reserves_in_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(close_uuid BIGINT PRIMARY KEY '
- ',reserve_pub BYTEA NOT NULL '
- ',execution_date INT8 NOT NULL '
- ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32) '
- ',wire_target_serial_id INT8 NOT NULL '
- ',amount_val INT8 NOT NULL '
- ',amount_frac INT4 NOT NULL '
- ',closing_fee_val INT8 NOT NULL '
- ',closing_fee_frac INT4 NOT NULL '
- ') ',
- 'reserves_close_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(reserve_out_serial_id BIGINT UNIQUE '
- ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
- ',denominations_serial INT8 NOT NULL '
- ',denom_sig BYTEA NOT NULL '
- ',reserve_uuid INT8 NOT NULL '
- ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64) '
- ',execution_date INT8 NOT NULL '
- ',amount_with_fee_val INT8 NOT NULL '
- ',amount_with_fee_frac INT4 NOT NULL '
- ')',
- 'reserves_out_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(known_coin_id BIGINT UNIQUE '
- ',denominations_serial INT8 NOT NULL '
- ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32) '
- ',age_hash BYTEA CHECK (LENGTH(age_hash)=32) '
- ',denom_sig BYTEA NOT NULL '
- ',remaining_val INT8 NOT NULL '
- ',remaining_frac INT4 NOT NULL '
- ')',
- 'known_coins_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(melt_serial_id BIGINT UNIQUE '
- ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64) '
- ',old_coin_pub BYTEA NOT NULL '
- ',h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32) '
- ',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 '
- ')',
- 'refresh_commitments_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(rrc_serial BIGINT UNIQUE '
- ',melt_serial_id INT8 NOT NULL '
- ',freshcoin_index INT4 NOT NULL '
- ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64) '
- ',denominations_serial INT8 NOT NULL '
- ',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) '
- ')',
- 'refresh_revealed_coins_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(rtc_serial BIGINT UNIQUE '
- ',melt_serial_id INT8 PRIMARY KEY '
- ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32) '
- ',transfer_privs BYTEA NOT NULL '
- ')',
- 'refresh_transfer_keys_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(deposit_serial_id BIGINT PRIMARY KEY '
- ',shard INT8 NOT NULL '
- ',known_coin_id INT8 NOT NULL '
- ',amount_with_fee_val INT8 NOT NULL '
- ',amount_with_fee_frac INT4 NOT NULL '
- ',wallet_timestamp INT8 NOT NULL '
- ',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_serial_id INT8 NOT NULL '
- ',tiny BOOLEAN NOT NULL DEFAULT FALSE '
- ',done BOOLEAN NOT NULL DEFAULT FALSE '
- ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE '
- ',extension_details_serial_id INT8 '
- ',UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms) '
- ')',
- 'deposits_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(refund_serial_id BIGINT UNIQUE'
- ',deposit_serial_id INT8 NOT NULL '
- ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64) '
- ',rtransaction_id INT8 NOT NULL '
- ',amount_with_fee_val INT8 NOT NULL '
- ',amount_with_fee_frac INT4 NOT NULL '
- ',PRIMARY KEY (deposit_serial_id, rtransaction_id) '
- ')',
- 'refunds_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(wireout_uuid BIGINT PRIMARY KEY'
- ',execution_date INT8 NOT NULL '
- ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32) '
- ',wire_target_serial_id INT8 NOT NULL '
- ',exchange_account_section TEXT NOT NULL '
- ',amount_val INT8 NOT NULL '
- ',amount_frac INT4 NOT NULL '
- ')',
- 'wire_out_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(aggregation_serial_id BIGINT UNIQUE '
- ',deposit_serial_id INT8 PRIMARY KEY '
- ',wtid_raw BYTEA '
- ')',
- 'aggregation_tracking_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(recoup_uuid BIGINT UNIQUE '
- ',known_coin_id INT8 NOT NULL '
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) '
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) '
- ',amount_val INT8 NOT NULL '
- ',amount_frac INT4 NOT NULL '
- ',recoup_timestamp INT8 NOT NULL '
- ',reserve_out_serial_id INT8 NOT NULL '
- ')',
- 'recoup_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(recoup_refresh_uuid BIGINT UNIQUE '
- ',known_coin_id INT8 NOT NULL '
- ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64) '
- ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32) '
- ',amount_val INT8 NOT NULL '
- ',amount_frac INT4 NOT NULL '
- ',recoup_timestamp INT8 NOT NULL '
- ',rrc_serial INT8 NOT NULL UNIQUE'
- ')',
- 'recoup_refresh_' || suffix
- );
-
- EXECUTE FORMAT(
- 'CREATE TABLE IF NOT EXISTS %I '
- '(prewire_uuid BIGINT PRIMARY KEY '
- ',wire_method TEXT NOT NULL '
- ',finished BOOLEAN NOT NULL DEFAULT false '
- ',failed BOOLEAN NOT NULL DEFAULT false '
- ',buf BYTEA NOT NULL '
- ')',
- 'prewire_' || suffix
- );
-
-END
-$$;
diff --git a/sql/exchange-tables.sql b/sql/exchange-tables.sql
new file mode 100644
index 0000000..0658594
--- /dev/null
+++ b/sql/exchange-tables.sql
@@ -0,0 +1,1139 @@
+CREATE OR REPLACE FUNCTION create_partitioned_table(
+ IN table_definition VARCHAR
+ ,IN table_name VARCHAR
+ ,IN main_table_partition_str VARCHAR -- Used only when it is the main table
- we do not partition shard tables
+ ,IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ IF shard_suffix IS NOT NULL THEN
+ table_name=table_name || '_' || shard_suffix;
+ main_table_partition_str = '';
+ END IF;
+
+ EXECUTE FORMAT(
+ table_definition,
+ table_name,
+ main_table_partition_str
+ );
+
+END
+$$;
+
+-----------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION create_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
+$$;
+
+-----------------------------------------------------------
+
+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
+$$;
+
+-----------------------------------------------------------
+
+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);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_exch_accnt_section_execution_date_idx '
+ 'ON ' || table_name || ' '
+ '(exchange_account_section '
+ ',execution_date'
+ ');'
+ );
+ 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
+$$;
+
+-----------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_reserves_close(
+ IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+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 ;'
+ ,'reserves_close'
+ ,'PARTITION BY HASH (reserve_pub)'
+ ,shard_suffix
+ );
+
+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
+$$;
+
+-----------------------------------------------------------
+
+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);'
+ );
+ 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_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);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_known_coin_id_index '
+ 'ON ' || table_name || ' '
+ '(known_coin_id);'
+ );
+
+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 || 'k_nown_coin_id_key
'
+ 'UNIQUE (known_coin_id)'
+ );
+END
+$$;
+
+-----------------------------------------------------------
+
+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);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_melt_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(melt_serial_id);'
+ );
+ 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
+$$;
+
+--------------------------------------------------------------
+
+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_rrc_serial_index
'
+ 'ON ' || table_name || ' '
+ '(rrc_serial);'
+ );
+ 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
+$$;
+
+-----------------------------------------------------------
+
+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
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_rtc_serial_index '
+ 'ON ' || table_name || ' '
+ '(rtc_serial);'
+ );
+
+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
+$$;
+
+-----------------------------------------------------------
+
+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)'
+ ',tiny BOOLEAN NOT NULL DEFAULT FALSE'
+ ',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 (shard, known_coin_id, merchant_pub, h_contract_terms)'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (shard)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ -- FIXME: we sometimes go ONLY by 'deposit_serial_id',
+ -- check if queries could be improved by adding shard or adding
another index without shard here, or inverting the order of the index here!
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_deposit_by_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(shard,deposit_serial_id);'
+ );
+ 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'
+ ',shard INT8 NOT NULL'
+ ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
+ ',deposit_serial_id INT8'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY RANGE (refund_deadline)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_main_index '
+ 'ON ' || table_name || ' '
+ '(refund_deadline ASC, shard, coin_pub);'
+ );
+
+END
+$$;
+
+-----------------------------------------------------------
+
+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'
+ ',shard INT8 NOT NULL' -- REFERENCES deposits (shard)
+ ',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 (shard)'
+ ,shard_suffix
+ );
+
+ table_name = concat_ws('_', table_name, shard_suffix);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_refund_serial_id_index
'
+ 'ON ' || table_name || ' '
+ '(refund_serial_id);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_deposit_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(shard,deposit_serial_id);'
+ );
+
+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
+$$;
+
+-----------------------------------------------------------
+
+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_wireout_uuid_index '
+ 'ON ' || table_name || ' '
+ '(wireout_uuid);'
+ );
+ 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
+$$;
+
+-----------------------------------------------------------
+
+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_aggregation_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(aggregation_serial_id);'
+ );
+ 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
+$$;
+
+-----------------------------------------------------------
+
+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 || '_recoup_uuid_index '
+ 'ON ' || table_name || ' '
+ '(recoup_uuid);'
+ );
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_reserve_out_serial_id_index '
+ 'ON ' || table_name || ' '
+ '(reserve_out_serial_id);'
+ );
+ 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)'
+ ') %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
+$$;
+
+-----------------------------------------------------------
+
+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
+$$;
+
+-----------------------------------------------------------
+
+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);
+
+ EXECUTE FORMAT (
+ 'CREATE INDEX IF NOT EXISTS ' || table_name ||
'_by_recoup_refresh_uuid_index '
+ 'ON ' || table_name || ' '
+ '(recoup_refresh_uuid);'
+ );
+ 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
+$$;
+
+-----------------------------------------------------------
+
+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
+$$;
+
+-----------------------------------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_cs_nonce_locks(
+ shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE IF NOT EXISTS %I'
+ '(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --
UNIQUE'
+ ',nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)'
+ ',op_hash BYTEA NOT NULL CHECK (LENGTH(op_hash)=64)'
+ ',max_denomination_serial INT8 NOT NULL'
+ ') %s ;'
+ ,'cs_nonce_locks'
+ ,'PARTITION BY HASH (nonce)'
+ ,shard_suffix
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION add_constraints_to_cs_nonce_locks_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE cs_nonce_locks_' || partition_suffix || ' '
+ 'ADD CONSTRAINT cs_nonce_locks_' || partition_suffix ||
'_cs_nonce_lock_serial_id_key '
+ 'UNIQUE (cs_nonce_lock_serial_id)'
+ );
+END
+$$;
+
+-----------------------------------------------------------
diff --git a/sql/partition-0001.sql b/sql/partition-0001.sql
new file mode 100644
index 0000000..f3910c6
--- /dev/null
+++ b/sql/partition-0001.sql
@@ -0,0 +1,601 @@
+BEGIN;
+
+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 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_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 reserves_out_by_reserve
+ DETACH PARTITION reserves_out_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 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_tracking_default;
+ DROP TABLE IF EXISTS recoup_default;
+ DROP TABLE IF EXISTS recoup_by_reserve_default;
+ DROP TABLE IF EXISTS reserves_out_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(
+ '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);
+
+-- 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_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(
+ 'reserves_out_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
+$$;
+
+
+CREATE OR REPLACE FUNCTION create_foreign_table(
+ source_table_name VARCHAR
+ ,modulus INTEGER
+ ,shard_suffix VARCHAR
+ ,current_shard_num INTEGER
+ )
+ RETURNS VOID
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix,
shard_suffix;
+
+ EXECUTE FORMAT(
+ 'CREATE FOREIGN TABLE IF NOT EXISTS %I '
+ 'PARTITION OF %I '
+ 'FOR VALUES WITH (MODULUS %s, REMAINDER %s) '
+ 'SERVER %I'
+ ,source_table_name || '_' || shard_suffix
+ ,source_table_name
+ ,modulus
+ ,current_shard_num-1
+ ,shard_suffix
+ );
+
+ EXECUTE FORMAT(
+ 'ALTER FOREIGN TABLE %I OWNER TO "taler-exchange-httpd"',
+ source_table_name || '_' || shard_suffix
+ );
+
+END
+$$;
+
+CREATE OR REPLACE FUNCTION master_prepare_sharding()
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+
+ -- CREATE EXTENSION IF NOT EXISTS postgres_fdw;
+
+ PERFORM detach_default_partitions();
+
+ ALTER TABLE IF EXISTS wire_targets
+ DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS reserves
+ DROP CONSTRAINT IF EXISTS reserves_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS reserves_in
+ DROP CONSTRAINT IF EXISTS reserves_in_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS reserves_close
+ DROP CONSTRAINT IF EXISTS reserves_close_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS reserves_out
+ DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey
+ ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key
+ ;
+
+ ALTER TABLE IF EXISTS known_coins
+ DROP CONSTRAINT IF EXISTS known_coins_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS known_coins_denominations_serial_fkey
+ ;
+
+ ALTER TABLE IF EXISTS refresh_commitments
+ DROP CONSTRAINT IF EXISTS refresh_commitments_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS refresh_old_coin_pub_fkey
+ ;
+
+ ALTER TABLE IF EXISTS refresh_revealed_coins
+ DROP CONSTRAINT IF EXISTS refresh_revealed_coins_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS refresh_revealed_coins_denominations_serial_fkey
+ ;
+
+ ALTER TABLE IF EXISTS refresh_transfer_keys
+ DROP CONSTRAINT IF EXISTS refresh_transfer_keys_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS deposits
+ DROP CONSTRAINT IF EXISTS deposits_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS deposits_extension_details_serial_id_fkey
+ ,DROP CONSTRAINT IF EXISTS
deposits_shard_known_coin_id_merchant_pub_h_contract_terms_key CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS refunds
+ DROP CONSTRAINT IF EXISTS refunds_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS wire_out
+ DROP CONSTRAINT IF EXISTS wire_out_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS wire_out_wtid_raw_key CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS aggregation_tracking
+ DROP CONSTRAINT IF EXISTS aggregation_tracking_pkey CASCADE
+ ,DROP CONSTRAINT IF EXISTS aggregation_tracking_wtid_raw_fkey
+ ;
+
+ ALTER TABLE IF EXISTS recoup
+ DROP CONSTRAINT IF EXISTS recoup_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS recoup_refresh
+ DROP CONSTRAINT IF EXISTS recoup_refresh_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS prewire
+ DROP CONSTRAINT IF EXISTS prewire_pkey CASCADE
+ ;
+
+ ALTER TABLE IF EXISTS cs_nonce_locks
+ DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE
+ ;
+
+END
+$$;
+
+
+CREATE OR REPLACE FUNCTION create_shard_server(
+ shard_suffix VARCHAR
+ ,total_num_shards INTEGER
+ ,current_shard_num INTEGER
+ ,remote_host VARCHAR
+ ,remote_user VARCHAR
+ ,remote_user_password VARCHAR
+ ,remote_db_name VARCHAR DEFAULT 'taler-exchange'
+ ,remote_port INTEGER DEFAULT '5432'
+)
+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 "taler-exchange-httpd" SERVER %I '
+ 'OPTIONS (user %L, password %L)'
+ ,shard_suffix
+ ,remote_user
+ ,remote_user_password
+ );
+
+ PERFORM create_foreign_table(
+ 'wire_targets'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'reserves'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'reserves_in'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'reserves_out'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'reserves_close'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'known_coins'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'refresh_commitments'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'refresh_revealed_coins'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'refresh_transfer_keys'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'deposits'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+-- PERFORM create_foreign_table(
+-- 'deposits_by_ready'
+-- ,total_num_shards
+-- ,shard_suffix
+-- ,current_shard_num
+-- );
+-- PERFORM create_foreign_table(
+-- 'deposits_for_matching'
+-- ,total_num_shards
+-- ,shard_suffix
+-- ,current_shard_num
+-- );
+ PERFORM create_foreign_table(
+ 'refunds'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'wire_out'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'aggregation_tracking'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'recoup'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'recoup_by_reserve'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'reserves_out_by_reserve'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'recoup_refresh'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'prewire'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+ PERFORM create_foreign_table(
+ 'cs_nonce_locks'
+ ,total_num_shards
+ ,shard_suffix
+ ,current_shard_num
+ );
+
+END
+$$;
+
+COMMIT;
diff --git a/sql/shard-0001.sql b/sql/shard-0001.sql
new file mode 100644
index 0000000..a5134b3
--- /dev/null
+++ b/sql/shard-0001.sql
@@ -0,0 +1,69 @@
+BEGIN;
+
+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_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_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_reserves_out_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
+$$;
+
+COMMIT;
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-grid5k] 109/189: update promtail message size, (continued)
- [taler-grid5k] 109/189: update promtail message size, gnunet, 2022/04/28
- [taler-grid5k] 117/189: some db config, gnunet, 2022/04/28
- [taler-grid5k] 64/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 96/189: fixes, add lookahead sign as param, gnunet, 2022/04/28
- [taler-grid5k] 70/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 120/189: update postgres exporter to work when there is no merchant, update grafana dashboards, gnunet, 2022/04/28
- [taler-grid5k] 74/189: reduce amount of logging wallets, gnunet, 2022/04/28
- [taler-grid5k] 93/189: possibility to create multiple instances, gnunet, 2022/04/28
- [taler-grid5k] 92/189: configure merchant instance, gnunet, 2022/04/28
- [taler-grid5k] 97/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 114/189: test with sharding,
gnunet <=
- [taler-grid5k] 111/189: explain analyze now working with correct postgres config, gnunet, 2022/04/28
- [taler-grid5k] 98/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 102/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 130/189: fix slow query amount, gnunet, 2022/04/28
- [taler-grid5k] 158/189: additional scripts improved, gnunet, 2022/04/28
- [taler-grid5k] 133/189: cleaner regex, gnunet, 2022/04/28
- [taler-grid5k] 156/189: fix, gnunet, 2022/04/28
- [taler-grid5k] 129/189: slow query time, gnunet, 2022/04/28
- [taler-grid5k] 152/189: deduplicate promtail, gnunet, 2022/04/28
- [taler-grid5k] 169/189: centos add node-exporter, gnunet, 2022/04/28