diff options
Diffstat (limited to 'src/exchangedb')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 642 | ||||
| -rw-r--r-- | src/exchangedb/exchangedb.conf | 4 | ||||
| -rw-r--r-- | src/exchangedb/partition-0001.sql | 24 | ||||
| -rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 24 | ||||
| -rw-r--r-- | 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 @@ -116,6 +116,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? */ char *currency; @@ -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, @@ -13013,6 +13022,17 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) 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 |