[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-exchange] branch master updated: add partitions to new p2p tables
|
From: |
gnunet |
|
Subject: |
[taler-exchange] branch master updated: add partitions to new p2p tables |
|
Date: |
Sat, 26 Mar 2022 09:00:26 +0100 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository exchange.
The following commit(s) were added to refs/heads/master by this push:
new 783e2ae4 add partitions to new p2p tables
783e2ae4 is described below
commit 783e2ae424fdd338da142e2e7472ee86b27d4035
Author: Christian Grothoff <grothoff@gnunet.org>
AuthorDate: Sat Mar 26 09:00:19 2022 +0100
add partitions to new p2p tables
---
src/exchangedb/exchange-0001.sql | 642 ++++++++++++++++++--------
src/exchangedb/exchangedb.conf | 4 +
src/exchangedb/partition-0001.sql | 24 +-
src/exchangedb/plugin_exchangedb_postgres.c | 24 +-
src/exchangedb/test-exchange-db-postgres.conf | 3 +
5 files changed, 497 insertions(+), 200 deletions(-)
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 568779f9..a42baa1f 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -21,6 +21,8 @@ BEGIN;
SELECT _v.register_patch('exchange-0001', NULL, NULL);
+-- ------------------------------ denominations
----------------------------------------
+
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)
@@ -57,6 +59,8 @@ CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
(expire_legal);
+-- ------------------------------ denomination_revocations
----------------------------------------
+
CREATE TABLE IF NOT EXISTS denomination_revocations
(denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,denominations_serial INT8 PRIMARY KEY REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
@@ -66,6 +70,8 @@ COMMENT ON TABLE denomination_revocations
IS 'remembering which denomination keys have been revoked';
+-- ------------------------------ wire_targets
----------------------------------------
+
CREATE TABLE IF NOT EXISTS wire_targets
(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)
@@ -106,14 +112,13 @@ $$;
SELECT add_constraints_to_wire_targets_partition('default');
--- 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_serial_id_index
ON wire_targets
(wire_target_serial_id);
+-- ------------------------------ reserves
----------------------------------------
+
CREATE TABLE IF NOT EXISTS reserves
(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
@@ -154,6 +159,7 @@ CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index
COMMENT ON INDEX reserves_by_gc_date_index
IS 'for reserve garbage collection';
+-- ------------------------------ reserves_in
----------------------------------------
CREATE TABLE IF NOT EXISTS reserves_in
(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -199,18 +205,22 @@ SELECT
add_constraints_to_reserves_in_partition('default');
CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
ON reserves_in
(reserve_in_serial_id);
+-- FIXME: where do we need this index? Can we do better?
CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_section_execution_date_index
ON reserves_in
(exchange_account_section
,execution_date
);
+-- FIXME: where do we need this index? Can we do better?
CREATE INDEX IF NOT EXISTS
reserves_in_by_exchange_account_reserve_in_serial_id_index
ON reserves_in
- (exchange_account_section,
- reserve_in_serial_id DESC
+ (exchange_account_section
+ ,reserve_in_serial_id DESC
);
+-- ------------------------------ reserves_close
----------------------------------------
+
CREATE TABLE IF NOT EXISTS reserves_close
(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE
CASCADE
@@ -256,6 +266,7 @@ $$;
SELECT add_constraints_to_reserves_close_partition('default');
+-- ------------------------------ reserves_out
----------------------------------------
CREATE TABLE IF NOT EXISTS reserves_out
(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -279,14 +290,13 @@ COMMENT ON COLUMN reserves_out.denominations_serial
CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
ON reserves_out
(reserve_out_serial_id);
+-- FIXME: change query to use reserves_out_by_reserve instead and materialize
execution_date there as well???
CREATE INDEX IF NOT EXISTS
reserves_out_by_reserve_uuid_and_execution_date_index
ON reserves_out
(reserve_uuid, execution_date);
COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
-
-
CREATE TABLE IF NOT EXISTS reserves_out_default
PARTITION OF reserves_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -309,6 +319,63 @@ $$;
SELECT add_constraints_to_reserves_out_partition('default');
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
+ (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE
CASCADE
+ ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)
+ )
+ PARTITION BY HASH (reserve_uuid);
+COMMENT ON TABLE reserves_out_by_reserve
+ IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
+
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
+ ON reserves_out_by_reserve
+ (reserve_uuid);
+
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
+ PARTITION OF reserves_out_by_reserve
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+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();
+
+
+-- ------------------------------ auditors
----------------------------------------
+
CREATE TABLE IF NOT EXISTS auditors
(auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
@@ -329,6 +396,8 @@ COMMENT ON COLUMN auditors.last_change
IS 'Latest time when active status changed. Used to detect replays of old
messages.';
+-- ------------------------------ auditor_denom_sigs
----------------------------------------
+
CREATE TABLE IF NOT EXISTS auditor_denom_sigs
(auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE
CASCADE
@@ -346,6 +415,8 @@ COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
IS 'Signature of the auditor, of purpose
TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
+-- ------------------------------ exchange_sign_keys
----------------------------------------
+
CREATE TABLE IF NOT EXISTS exchange_sign_keys
(esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
@@ -368,6 +439,8 @@ COMMENT ON COLUMN exchange_sign_keys.expire_legal
IS 'Time when this online signing key legally expires.';
+-- ------------------------------ signkey_revocations
----------------------------------------
+
CREATE TABLE IF NOT EXISTS signkey_revocations
(signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON
DELETE CASCADE
@@ -377,6 +450,8 @@ COMMENT ON TABLE signkey_revocations
IS 'Table storing which online signing keys have been revoked';
+-- ------------------------------ extension
----------------------------------------
+
CREATE TABLE IF NOT EXISTS extensions
(extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,name VARCHAR NOT NULL UNIQUE
@@ -390,6 +465,8 @@ COMMENT ON COLUMN extensions.config
IS 'Configuration of the extension as JSON-blob, maybe NULL';
+-- ------------------------------ known_coins
----------------------------------------
+
CREATE TABLE IF NOT EXISTS known_coins
(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,denominations_serial INT8 NOT NULL REFERENCES denominations
(denominations_serial) ON DELETE CASCADE
@@ -399,7 +476,7 @@ CREATE TABLE IF NOT EXISTS known_coins
,remaining_val INT8 NOT NULL
,remaining_frac INT4 NOT NULL
)
- PARTITION BY HASH (coin_pub); -- FIXME: or include denominations_serial? or
multi-level partitioning?
+ PARTITION BY HASH (coin_pub);
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
@@ -434,10 +511,8 @@ $$;
SELECT add_constraints_to_known_coins_partition('default');
-CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
- ON known_coins
- (known_coin_id);
+-- ------------------------------ refresh_commitments
----------------------------------------
CREATE TABLE IF NOT EXISTS refresh_commitments
(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -458,6 +533,11 @@ COMMENT ON COLUMN refresh_commitments.rc
COMMENT ON COLUMN refresh_commitments.old_coin_pub
IS 'Coin being melted in the refresh process.';
+-- Note: index spans partitions, may need to be materialized.
+CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
+ ON refresh_commitments
+ (old_coin_pub);
+
CREATE TABLE IF NOT EXISTS refresh_commitments_default
PARTITION OF refresh_commitments
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -479,13 +559,8 @@ $$;
SELECT add_constraints_to_refresh_commitments_partition('default');
-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);
+-- ------------------------------ refresh_revealed_coins
----------------------------------------
CREATE TABLE IF NOT EXISTS refresh_revealed_coins
(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -517,9 +592,6 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
COMMENT ON COLUMN refresh_revealed_coins.ev_sig
IS 'exchange signature over the envelope';
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_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);
@@ -551,7 +623,7 @@ $$;
SELECT add_constraints_to_refresh_revealed_coins_partition('default');
-
+-- ------------------------------ refresh_transfer_keys
----------------------------------------
CREATE TABLE IF NOT EXISTS refresh_transfer_keys
(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -592,25 +664,30 @@ $$;
SELECT add_constraints_to_refresh_transfer_keys_partition('default');
-CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
- ON refresh_transfer_keys
- (rtc_serial);
+-- ------------------------------ extension_details
----------------------------------------
CREATE TABLE IF NOT EXISTS extension_details
(extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY
KEY
- ,extension_options VARCHAR);
+ ,extension_options VARCHAR)
+ PARTITION BY HASH (extension_details_serial_id);
COMMENT ON TABLE extension_details
IS 'Extensions that were provided with deposits (not yet used).';
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 extension_details_default
+ PARTITION OF extension_details
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ deposits
----------------------------------------
CREATE TABLE IF NOT EXISTS deposits
(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
,shard INT8 NOT NULL
,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES
known_coins (coin_pub) ON DELETE CASCADE
- ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON
DELETE CASCADE --- FIXME: column needed???
+ ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON
DELETE CASCADE
,amount_with_fee_val INT8 NOT NULL
,amount_with_fee_frac INT4 NOT NULL
,wallet_timestamp INT8 NOT NULL
@@ -629,34 +706,10 @@ CREATE TABLE IF NOT EXISTS deposits
,UNIQUE (coin_pub, merchant_pub, h_contract_terms)
)
PARTITION BY HASH (coin_pub);
--- 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 merchant_pub. 64-bit
value because we need an *unsigned* 32-bit value.';
+ IS 'Used for load sharding in the materialized indices. 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
@@ -672,17 +725,10 @@ 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: 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!
-CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
- ON deposits
- (shard,deposit_serial_id);
-
CREATE INDEX IF NOT EXISTS deposits_by_coin_pub_index
ON deposits
(coin_pub);
-
CREATE TABLE IF NOT EXISTS deposits_default
PARTITION OF deposits
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -889,7 +935,7 @@ BEGIN
AND deposit_serial_id = OLD.deposit_serial_id;
END IF;
RETURN NEW;
-END $$;
+END $$;
COMMENT ON FUNCTION deposits_delete_trigger()
IS 'Replicate deposit deletions into materialized indices.';
@@ -899,9 +945,13 @@ CREATE TRIGGER deposits_on_delete
FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
+-- ------------------------------ refunds
----------------------------------------
+
+-- FIXME-URGENT: very bad structure, should replace 'shard' by 'coin_pub'
+-- as deposits is sharded by that now!
CREATE TABLE IF NOT EXISTS refunds
(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,shard INT8 NOT NULL -- REFERENCES deposits (shard)
+ ,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
@@ -939,14 +989,12 @@ $$;
SELECT add_constraints_to_refunds_partition('default');
-CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
- ON refunds
- (refund_serial_id);
CREATE INDEX IF NOT EXISTS refunds_by_deposit_serial_id_index
ON refunds
(shard,deposit_serial_id);
+-- ------------------------------ wire_out
----------------------------------------
CREATE TABLE IF NOT EXISTS wire_out
(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
@@ -972,7 +1020,6 @@ CREATE INDEX IF NOT EXISTS
wire_out_by_wire_target_h_payto_index
ON wire_out
(wire_target_h_payto);
-
CREATE TABLE IF NOT EXISTS wire_out_default
PARTITION OF wire_out
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -995,10 +1042,13 @@ $$;
SELECT add_constraints_to_wire_out_partition('default');
+-- ------------------------------ aggregation_tracking
----------------------------------------
+-- FIXME-URGENT: add colum coin_pub to select by coin_pub + deposit_serial_id
for more efficient deposit lookup!?
+-- Or which direction(s) is this table used? Is the partitioning sane??
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 -- FIXME: change to coin_pub +
deposit_serial_id for more efficient deposit -- or something else???
+ ,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);
@@ -1028,9 +1078,6 @@ $$;
SELECT add_constraints_to_aggregation_tracking_partition('default');
-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);
@@ -1038,6 +1085,8 @@ COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
IS 'for lookup_transactions';
+-- ------------------------------ wire_fee
----------------------------------------
+
CREATE TABLE IF NOT EXISTS wire_fee
(wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,wire_method VARCHAR NOT NULL
@@ -1062,6 +1111,8 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
(end_date);
+-- ------------------------------ global_fee
----------------------------------------
+
CREATE TABLE IF NOT EXISTS global_fee
(global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,start_date INT8 NOT NULL
@@ -1091,6 +1142,8 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
(end_date);
+-- ------------------------------ recoup
----------------------------------------
+
CREATE TABLE IF NOT EXISTS recoup
(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES
known_coins (coin_pub)
@@ -1113,17 +1166,10 @@ COMMENT ON COLUMN recoup.coin_sig
COMMENT ON COLUMN recoup.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the withdraw operation.';
-CREATE INDEX IF NOT EXISTS recoup_by_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_coin_pub_index
ON recoup
(coin_pub);
-
CREATE TABLE IF NOT EXISTS recoup_default
PARTITION OF recoup
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1145,7 +1191,6 @@ $$;
SELECT add_constraints_to_recoup_partition('default');
-
CREATE TABLE IF NOT EXISTS recoup_by_reserve
(reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves
(reserve_out_serial_id) ON DELETE CASCADE
,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)
@@ -1183,7 +1228,6 @@ CREATE TRIGGER recoup_on_insert
ON recoup
FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
-
CREATE OR REPLACE FUNCTION recoup_delete_trigger()
RETURNS trigger
LANGUAGE plpgsql
@@ -1203,68 +1247,7 @@ CREATE TRIGGER recoup_on_delete
FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
-
-
-
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
- (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE
CASCADE
- ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)
- )
- PARTITION BY HASH (reserve_uuid);
-COMMENT ON TABLE reserves_out_by_reserve
- IS 'Information in this table is strictly redundant with that of
reserves_out, but saved by a different primary key for fast lookups by reserve
public key/uuid.';
-
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
- ON reserves_out_by_reserve
- (reserve_uuid);
-
-
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
- PARTITION OF reserves_out_by_reserve
- FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-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();
-
-
-
-
+-- ------------------------------ recoup_refresh
----------------------------------------
CREATE TABLE IF NOT EXISTS recoup_refresh
(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -1289,16 +1272,14 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
COMMENT ON COLUMN recoup_refresh.coin_blind
IS 'Denomination blinding key used when creating the blinded coin from the
planchet. Secret revealed during the recoup to provide the linkage between the
coin and the refresh operation.';
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
ON recoup_refresh
- (recoup_refresh_uuid);
+ (coin_pub);
+-- FIXME: any query using this index will be slow. Materialize index or change
query?
+-- Also: which query uses this index?
CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
ON recoup_refresh
(rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
- ON recoup_refresh
- (coin_pub);
-
CREATE TABLE IF NOT EXISTS recoup_refresh_default
PARTITION OF recoup_refresh
@@ -1322,6 +1303,7 @@ $$;
SELECT add_constraints_to_recoup_refresh_partition('default');
+-- ------------------------------ prewire
----------------------------------------
CREATE TABLE IF NOT EXISTS prewire
(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
@@ -1357,6 +1339,7 @@ CREATE TABLE IF NOT EXISTS prewire_default
FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+-- ------------------------------ wire_accounts
----------------------------------------
CREATE TABLE IF NOT EXISTS wire_accounts
(payto_uri VARCHAR PRIMARY KEY
@@ -1378,6 +1361,8 @@ COMMENT ON COLUMN wire_accounts.last_change
-- and is of no concern to the auditor
+-- ------------------------------ cs_nonce_locks
----------------------------------------
+
CREATE TABLE IF NOT EXISTS cs_nonce_locks
(cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)
@@ -1415,6 +1400,9 @@ $$;
SELECT add_constraints_to_cs_nonce_locks_partition('default');
+
+-- ------------------------------ work_shards
----------------------------------------
+
CREATE TABLE IF NOT EXISTS work_shards
(shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,last_attempt INT8 NOT NULL
@@ -1447,6 +1435,8 @@ CREATE INDEX IF NOT EXISTS
work_shards_by_job_name_completed_last_attempt_index
);
+-- ------------------------------ revolving_work_shards
----------------------------------------
+
CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
(shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,last_attempt INT8 NOT NULL
@@ -1478,10 +1468,14 @@ CREATE INDEX IF NOT EXISTS
revolving_work_shards_by_job_name_active_last_attempt
,last_attempt
);
--- Tables for P2P payments
+--------------------------------------------------------------------------
+-- Tables for P2P payments
+--------------------------------------------------------------------------
+
+-- ------------------------------ partners
----------------------------------------
CREATE TABLE IF NOT EXISTS partners
- (partner_serial_id BIGSERIAL UNIQUE
+ (partner_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32)
,start_date INT8 NOT NULL
,end_date INT8 NOT NULL
@@ -1509,8 +1503,10 @@ COMMENT ON COLUMN partners.master_sig
IS 'signature of our master public key affirming the partnership, of purpose
TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
+-- ------------------------------ purse_requests
----------------------------------------
+
CREATE TABLE IF NOT EXISTS purse_requests
- (purse_deposit_serial_id BIGSERIAL UNIQUE
+ (purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
,merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)
,purse_expiration INT8 NOT NULL
@@ -1522,7 +1518,8 @@ CREATE TABLE IF NOT EXISTS purse_requests
,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
+ )
+ PARTITION BY HASH (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
@@ -1538,19 +1535,45 @@ COMMENT ON COLUMN purse_requests.balance_val
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!
+-- FIXME: change to materialized index by marge_pub!
+CREATE INDEX IF NOT EXISTS purse_requests_merge_pub
+ ON purse_requests (merge_pub);
+CREATE TABLE IF NOT EXISTS purse_requests_default
+ PARTITION OF purse_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_requests_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_requests_' || partition_suffix ||
'_purse_requests_serial_id_key '
+ 'UNIQUE (purse_requests_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_requests_partition('default');
+
+
+
+-- ------------------------------ purse_merges
----------------------------------------
CREATE TABLE IF NOT EXISTS purse_merges
- (purse_merge_request_serial_id BIGSERIAL -- UNIQUE
+ (purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --
UNIQUE
,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
,reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)--REFERENCES
reserves (reserve_pub) ON DELETE CASCADE
,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) --REFERENCES
purse_requests (purse_pub) ON DELETE CASCADE
,merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)
,merge_timestamp INT8 NOT NULL
,PRIMARY KEY (purse_pub)
- ); -- partition by purse_pub; plus materialized index by reserve_pub!
+ )
+ PARTITION BY HASH (purse_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
@@ -1563,42 +1586,99 @@ 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_purse_pub
+ ON purse_merges (purse_pub);
+-- FIXME: change to materialized index by reserve_pub!
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 purse_merges_default
+ PARTITION OF purse_merges
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-CREATE TABLE IF NOT EXISTS account_mergers
- (account_merge_request_serial_id BIGSERIAL -- UNIQUE
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_merges_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_merges_' || partition_suffix ||
'_purse_merge_request_serial_id_key '
+ 'UNIQUE (purse_merge_request_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_merges_partition('default');
+
+
+
+-- ------------------------------ account_merges
----------------------------------------
+
+CREATE TABLE IF NOT EXISTS account_merges
+ (account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --
UNIQUE
,reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) -- REFERENCES
reserves (reserve_pub) ON DELETE CASCADE
,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) -- REFERENCES
purse_requests (purse_pub)
- ,PRIMARY KEY (reserve_pub)
- ); -- partition by purse_pub; plus materialized index by reserve_pub!
-COMMENT ON TABLE account_mergers
+ ,PRIMARY KEY (purse_pub)
+ )
+ PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE account_merges
IS 'Merge requests where a purse- and account-owner requested merging the
purse into the account';
-COMMENT ON COLUMN account_mergers.reserve_pub
+COMMENT ON COLUMN account_merges.reserve_pub
IS 'public key of the target reserve';
-COMMENT ON COLUMN account_mergers.purse_pub
+COMMENT ON COLUMN account_merges.purse_pub
IS 'public key of the purse';
-COMMENT ON COLUMN account_mergers.reserve_sig
+COMMENT ON COLUMN account_merges.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
+CREATE INDEX IF NOT EXISTS account_merges_purse_pub
+ ON account_merges (purse_pub);
+COMMENT ON INDEX account_merges_purse_pub
IS 'needed when checking for a purse merge status';
-
+
+-- FIXME: change to materialized index by reserve_pub!
+CREATE INDEX IF NOT EXISTS account_merges_by_reserve_pub
+ ON account_merges (reserve_pub);
+
+CREATE TABLE IF NOT EXISTS account_merges_default
+ PARTITION OF account_merges
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE account_merges_' || partition_suffix || ' '
+ 'ADD CONSTRAINT account_merges_' || partition_suffix ||
'_account_merge_request_serial_id_key '
+ 'UNIQUE (account_merge_request_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_account_merges_partition('default');
+
+
+-- ------------------------------ contracts
----------------------------------------
CREATE TABLE IF NOT EXISTS contracts
- (contract_serial_id BIGSERIAL UNIQUE
+ (contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
,pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)
,e_contract BYTEA NOT NULL
,purse_expiration INT8 NOT NULL
,PRIMARY KEY (purse_pub)
- ); -- partition by purse_pub
+ )
+ PARTITION BY HASH (purse_pub);
COMMENT ON TABLE contracts
IS 'encrypted contracts associated with purses';
COMMENT ON COLUMN contracts.purse_pub
@@ -1608,6 +1688,30 @@ COMMENT ON COLUMN contracts.pub_ckey
COMMENT ON COLUMN contracts.e_contract
IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after
decryption)';
+CREATE TABLE IF NOT EXISTS contracts_default
+ PARTITION OF contracts
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE contracts_' || partition_suffix || ' '
+ 'ADD CONSTRAINT contracts_' || partition_suffix ||
'_contract_serial_id_key '
+ 'UNIQUE (contract_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_contracts_partition('default');
+
+
+-- ------------------------------ history_requests
----------------------------------------
+
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
@@ -1615,7 +1719,8 @@ CREATE TABLE IF NOT EXISTS history_requests
,history_fee_val INT8 NOT NULL
,history_fee_frac INT4 NOT NULL
,PRIMARY KEY (reserve_pub,request_timestamp)
- ); -- partition by reserve_pub
+ )
+ PARTITION BY HASH (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
@@ -1625,6 +1730,13 @@ COMMENT ON COLUMN history_requests.reserve_sig
COMMENT ON COLUMN history_requests.history_fee_val
IS 'History fee approved by the signature';
+CREATE TABLE IF NOT EXISTS history_requests_default
+ PARTITION OF history_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ close_requests
----------------------------------------
+
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
@@ -1632,7 +1744,8 @@ CREATE TABLE IF NOT EXISTS close_requests
,close_val INT8 NOT NULL
,close_frac INT4 NOT NULL
,PRIMARY KEY (reserve_pub,close_timestamp)
- ); -- partition by reserve_pub
+ )
+ PARTITION BY HASH (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
@@ -1642,17 +1755,24 @@ COMMENT ON COLUMN close_requests.reserve_sig
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 close_requests_default
+ PARTITION OF close_requests
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ purse_deposits
----------------------------------------
CREATE TABLE IF NOT EXISTS purse_deposits
- (purse_deposit_serial_id BIGSERIAL UNIQUE
+ (purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
,amount_with_fee_val INT8 NOT NULL
,amount_with_fee_frac INT4 NOT NULL
,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
- ,PRIMARY KEY (purse_pub,coin_pub)
- ); -- partition by purse_pub, plus a materialized index by coin_pub!
+ -- ,PRIMARY KEY (purse_pub,coin_pub)
+ )
+ PARTITION BY HASH (purse_pub);
COMMENT ON TABLE purse_deposits
IS 'Requests depositing coins into a purse';
COMMENT ON COLUMN purse_deposits.partner_serial_id
@@ -1666,14 +1786,43 @@ COMMENT ON COLUMN purse_deposits.amount_with_fee_val
COMMENT ON COLUMN purse_deposits.coin_sig
IS 'Signature of the coin affirming the deposit into the purse, of type
TALER_SIGNATURE_PURSE_DEPOSIT';
+-- FIXME: change to materialized index by coin_pub!
+CREATE INDEX IF NOT EXISTS purse_deposits_by_coin_pub
+ ON purse_deposits (coin_pub);
+
+CREATE TABLE IF NOT EXISTS purse_deposits_default
+ PARTITION OF purse_deposits
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE purse_deposits_' || partition_suffix || ' '
+ 'ADD CONSTRAINT purse_deposits_' || partition_suffix ||
'_purse_deposit_serial_id_key '
+ 'UNIQUE (purse_deposit_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_purse_deposits_partition('default');
+
+
+-- ------------------------------ wads_out
----------------------------------------
+
CREATE TABLE IF NOT EXISTS wads_out
- (wad_out_serial_id BIGSERIAL UNIQUE
+ (wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
,partner_serial_id INT8 NOT NULL REFERENCES partners(partner_serial_id) ON
DELETE CASCADE
,amount_val INT8 NOT NULL
,amount_frac INT4 NOT NULL
,execution_time INT8 NOT NULL
- ); -- partition by wad_id
+ )
+ PARTITION BY HASH (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
@@ -1685,9 +1834,36 @@ COMMENT ON COLUMN wads_out.amount_val
COMMENT ON COLUMN wads_out.execution_time
IS 'Time when the wire transfer was scheduled';
+CREATE INDEX IF NOT EXISTS wads_out_index_by_wad_id
+ ON wads_out (wad_id);
+
+CREATE TABLE IF NOT EXISTS wads_out_default
+ PARTITION OF wads_out
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wads_out_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wads_out_' || partition_suffix ||
'_wad_out_serial_id_key '
+ 'UNIQUE (wad_out_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wads_out_partition('default');
+
+
+-- ------------------------------ wads_out_entries
----------------------------------------
+
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
+ (wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_out_serial_id INT8 -- REFERENCES wads_out (wad_out_serial_id) ON DELETE
CASCADE
,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
@@ -1701,9 +1877,11 @@ CREATE TABLE IF NOT EXISTS wad_out_entries
,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);
+ )
+ PARTITION BY HASH (purse_pub);
+-- FIXME: convert to materialized index!
+CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_reserve_pub
+ ON wad_out_entries (reserve_pub);
COMMENT ON TABLE wad_out_entries
IS 'Purses combined into a wad';
COMMENT ON COLUMN wad_out_entries.wad_out_serial_id
@@ -1729,15 +1907,40 @@ COMMENT ON COLUMN wad_out_entries.reserve_sig
COMMENT ON COLUMN wad_out_entries.purse_sig
IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
+CREATE TABLE IF NOT EXISTS wad_out_entries_default
+ PARTITION OF wad_out_entries
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wad_out_entries_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wad_out_entries_' || partition_suffix ||
'_wad_out_entry_serial_id_key '
+ 'UNIQUE (wad_out_entry_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wad_out_entries_partition('default');
+
+
+-- ------------------------------ wads_in
----------------------------------------
+
CREATE TABLE IF NOT EXISTS wads_in
- (wad_in_serial_id BIGSERIAL UNIQUE
+ (wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
,origin_exchange_url TEXT NOT NULL
,amount_val INT8 NOT NULL
,amount_frac INT4 NOT NULL
,arrival_time INT8 NOT NULL
,UNIQUE (wad_id, origin_exchange_url)
- ); -- partition by wad_id
+ )
+ PARTITION BY HASH (wad_id);
COMMENT ON TABLE wads_in
IS 'Incoming exchange-to-exchange wad wire transfers';
COMMENT ON COLUMN wads_in.wad_id
@@ -1749,9 +1952,33 @@ COMMENT ON COLUMN wads_in.amount_val
COMMENT ON COLUMN wads_in.arrival_time
IS 'Time when the wad was received';
+CREATE TABLE IF NOT EXISTS wads_in_default
+ PARTITION OF wads_in
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wads_in_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key '
+ 'UNIQUE (wad_in_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wads_in_partition('default');
+
+
+-- ------------------------------ wads_in_entries
----------------------------------------
+
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
+ (wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+ ,wad_in_serial_id INT8 -- REFERENCES wads_in (wad_in_serial_id) ON DELETE
CASCADE
,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
@@ -1765,7 +1992,8 @@ CREATE TABLE IF NOT EXISTS wad_in_entries
,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?)
+ )
+ PARTITION BY HASH (purse_pub);
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
@@ -1790,15 +2018,36 @@ 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);
+-- FIXME: convert to materialized index!
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 wad_in_entries_default
+ PARTITION OF wad_in_entries
+ FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition(
+ IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE wad_in_entries_' || partition_suffix || ' '
+ 'ADD CONSTRAINT wad_in_entries_' || partition_suffix ||
'_wad_in_entry_serial_id_key '
+ 'UNIQUE (wad_in_entry_serial_id) '
+ );
+END
+$$;
+
+SELECT add_constraints_to_wad_in_entries_partition('default');
+
+
+-- ------------------------------ partner_accounts
----------------------------------------
+
CREATE TABLE IF NOT EXISTS partner_accounts
(payto_uri VARCHAR PRIMARY KEY
,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE
CASCADE
@@ -1817,8 +2066,9 @@ 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
-
+---------------------------------------------------------------------------
+-- Stored procedures
+---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION exchange_do_withdraw(
IN cs_nonce BYTEA,
@@ -2419,9 +2669,6 @@ THEN
END IF;
END IF;
-
-
-
-- Everything fine, return success!
out_balance_ok=TRUE;
out_noreveal_index=in_noreveal_index;
@@ -2492,7 +2739,6 @@ THEN
RETURN;
END IF;
-
INSERT INTO refunds
(deposit_serial_id
,shard
@@ -2542,7 +2788,6 @@ THEN
RETURN;
END IF;
-
IF out_gone
THEN
-- money already sent to the merchant. Tough luck.
@@ -2552,8 +2797,6 @@ THEN
RETURN;
END IF;
-
-
-- Check refund balance invariant.
SELECT
SUM(amount_with_fee_val) -- overflow here is not plausible
@@ -2629,6 +2872,8 @@ END $$;
-- IS 'Executes a refund operation, checking that the corresponding deposit
was sufficient to cover the refunded amount';
+
+
CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve(
IN in_reserve_pub BYTEA,
IN in_reserve_out_serial_id INT8,
@@ -3090,6 +3335,9 @@ BEGIN
END $$;
+-------------------------------------------------------------
+-- THE END
+-------------------------------------------------------------
-- Complete transaction
COMMIT;
diff --git a/src/exchangedb/exchangedb.conf b/src/exchangedb/exchangedb.conf
index 77748bf5..1c22301a 100644
--- a/src/exchangedb/exchangedb.conf
+++ b/src/exchangedb/exchangedb.conf
@@ -26,3 +26,7 @@ IDLE_RESERVE_EXPIRATION_TIME = 4 weeks
# After how long do we forget about reserves? Should be above
# the legal expiration timeframe of withdrawn coins.
LEGAL_RESERVE_EXPIRATION_TIME = 7 years
+
+# What is the desired delay between a transaction being ready and the
+# aggregator triggering on it?
+AGGREGATOR_SHIFT = 1 s
diff --git a/src/exchangedb/partition-0001.sql
b/src/exchangedb/partition-0001.sql
index 49f865db..ba326798 100644
--- a/src/exchangedb/partition-0001.sql
+++ b/src/exchangedb/partition-0001.sql
@@ -229,6 +229,28 @@ BEGIN
);
PERFORM add_constraints_to_deposits_partition(num_partitions::varchar);
+-- TODO: dynamically (!) creating/deleting deposits partitions:
+-- create new partitions 'as needed', drop old ones once the aggregator has
made
+-- them empty; as 'new' deposits will always have deadlines in the future,
this
+-- would basically guarantee no conflict between aggregator and exchange
service!
+-- SEE also:
https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
+-- (article is slightly wrong, as this works:)
+--CREATE TABLE tab (
+-- id bigint GENERATED ALWAYS AS IDENTITY,
+-- ts timestamp NOT NULL,
+-- data text
+-- PARTITION BY LIST ((ts::date));
+-- CREATE TABLE tab_def PARTITION OF tab DEFAULT;
+-- BEGIN
+-- CREATE TABLE tab_part2 (LIKE tab);
+-- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo');
+-- alter table tab attach partition tab_part2 for values in ('2022-03-21');
+-- commit;
+-- Naturally, to ensure this is actually 100% conflict-free, we'd
+-- need to create tables at the granularity of the wire/refund deadlines;
+-- that is right now configurable via AGGREGATOR_SHIFT option.
+
+
PERFORM create_table_partition(
'refunds'
,modulus
@@ -287,4 +309,4 @@ BEGIN
END
$$;
-COMMIT;
\ No newline at end of file
+COMMIT;
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c
b/src/exchangedb/plugin_exchangedb_postgres.c
index 3cde9773..8dc201a2 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -115,6 +115,12 @@ struct PostgresClosure
*/
struct GNUNET_TIME_Relative legal_reserve_expiration_time;
+ /**
+ * What delay should we introduce before ready transactions
+ * are actually aggregated?
+ */
+ struct GNUNET_TIME_Relative aggregator_shift;
+
/**
* Which currency should we assume all amounts to be in?
*/
@@ -5993,7 +5999,8 @@ postgres_get_ready_deposit (void *cls,
};
enum GNUNET_DB_QueryStatus qs;
- now = GNUNET_TIME_absolute_get ();
+ now = GNUNET_TIME_absolute_round_down (GNUNET_TIME_absolute_get (),
+ pg->aggregator_shift);
GNUNET_assert (start_shard_row < end_shard_row);
GNUNET_assert (end_shard_row <= INT32_MAX);
GNUNET_log (GNUNET_ERROR_TYPE_INFO,
@@ -6154,7 +6161,7 @@ postgres_iterate_matching_deposits (
uint32_t limit)
{
struct PostgresClosure *pg = cls;
- struct GNUNET_TIME_Absolute now = GNUNET_TIME_absolute_get ();
+ struct GNUNET_TIME_Absolute now = {0};
uint64_t shard = compute_shard (merchant_pub);
struct GNUNET_PQ_QueryParam params[] = {
GNUNET_PQ_query_param_auto_from_type (merchant_pub),
@@ -6173,6 +6180,8 @@ postgres_iterate_matching_deposits (
};
enum GNUNET_DB_QueryStatus qs;
+ now = GNUNET_TIME_absolute_round_down (GNUNET_TIME_absolute_get (),
+ pg->aggregator_shift);
qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
"deposits_iterate_matching",
params,
@@ -13012,6 +13021,17 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
GNUNET_free (pg);
return NULL;
}
+ if (GNUNET_OK !=
+ GNUNET_CONFIGURATION_get_value_time (cfg,
+ "exchangedb",
+ "AGGREGATOR_SHIFT",
+ &pg->aggregator_shift))
+ {
+ GNUNET_log_config_missing (GNUNET_ERROR_TYPE_WARNING,
+ "exchangedb",
+ "AGGREGATOR_SHIFT");
+ }
+
if (GNUNET_OK !=
TALER_config_get_currency (cfg,
&pg->currency))
diff --git a/src/exchangedb/test-exchange-db-postgres.conf
b/src/exchangedb/test-exchange-db-postgres.conf
index e582025b..ab70bcfc 100644
--- a/src/exchangedb/test-exchange-db-postgres.conf
+++ b/src/exchangedb/test-exchange-db-postgres.conf
@@ -28,3 +28,6 @@ IDLE_RESERVE_EXPIRATION_TIME = 4 weeks
# After how long do we forget about reserves? Should be above
# the legal expiration timeframe of withdrawn coins.
LEGAL_RESERVE_EXPIRATION_TIME = 7 years
+
+# Shift to apply before aggregating.
+AGGREGATOR_SHIFT = 1s
\ No newline at end of file
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [taler-exchange] branch master updated: add partitions to new p2p tables,
gnunet <=