diff options
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 69 |
1 files changed, 56 insertions, 13 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index b2fb52ac2..e6902ed1c 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -772,7 +772,7 @@ CREATE TABLE IF NOT EXISTS deposits_by_ready_default CREATE TABLE IF NOT EXISTS deposits_for_matching (refund_deadline INT8 NOT NULL - ,shard INT8 NOT NULL + ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE ,deposit_serial_id INT8 ) @@ -782,7 +782,7 @@ COMMENT ON TABLE deposits_for_matching CREATE INDEX IF NOT EXISTS deposits_for_matching_main_index ON deposits_for_matching - (refund_deadline ASC, shard, coin_pub); + (refund_deadline ASC, merchant_pub, coin_pub); CREATE TABLE IF NOT EXISTS deposits_for_matching_default PARTITION OF deposits_for_matching @@ -818,12 +818,12 @@ BEGIN THEN INSERT INTO deposits_for_matching (refund_deadline - ,shard + ,merchant_pub ,coin_pub ,deposit_serial_id) VALUES (NEW.refund_deadline - ,NEW.shard + ,NEW.merchant_pub ,NEW.coin_pub ,NEW.deposit_serial_id); END IF; @@ -866,7 +866,7 @@ BEGIN THEN DELETE FROM deposits_for_matching WHERE refund_deadline = OLD.refund_deadline - AND shard = OLD.shard + AND merchant_pub = OLD.merchant_pub AND coin_pub = OLD.coin_pub AND deposit_serial_id = OLD.deposit_serial_id; END IF; @@ -887,12 +887,12 @@ BEGIN THEN INSERT INTO deposits_for_matching (refund_deadline - ,shard + ,merchant_pub ,coin_pub ,deposit_serial_id) VALUES (NEW.refund_deadline - ,NEW.shard + ,NEW.merchant_pub ,NEW.coin_pub ,NEW.deposit_serial_id); END IF; @@ -930,7 +930,7 @@ BEGIN THEN DELETE FROM deposits_for_matching WHERE refund_deadline = OLD.refund_deadline - AND shard = OLD.shard + AND merchant_pub = OLD.merchant_pub AND coin_pub = OLD.coin_pub AND deposit_serial_id = OLD.deposit_serial_id; END IF; @@ -1040,21 +1040,64 @@ $$; SELECT add_constraints_to_wire_out_partition('default'); +CREATE OR REPLACE FUNCTION wire_out_delete_trigger() + RETURNS trigger + LANGUAGE plpgsql + AS $$ +BEGIN + DELETE FROM aggregation_tracking + WHERE wtid_raw = OLD.wtid_raw; + RETURN OLD; +END $$; +COMMENT ON FUNCTION wire_out_delete_trigger() + IS 'Replicate reserve_out deletions into aggregation_tracking. This replaces an earlier use of an ON DELETE CASCADE that required a DEFERRABLE constraint and conflicted with nice partitioning.'; + +CREATE TRIGGER wire_out_on_delete + AFTER DELETE + ON wire_out + FOR EACH ROW EXECUTE FUNCTION wire_out_delete_trigger(); + + + +-- ------------------------------ aggregation_transient ---------------------------------------- + +-- Note: this table is not yet used; it is designed +-- to allow us to get rid of the 'tiny BOOL' and +-- the associated need to look at tiny +-- deposits repeatedly. +CREATE TABLE IF NOT EXISTS aggregation_transient + (amount_val INT8 NOT NULL + ,amount_frac INT4 NOT NULL + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) + ,exchange_account_section TEXT NOT NULL + ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32) + ) + PARTITION BY HASH (wire_target_h_payto); +COMMENT ON TABLE aggregation_transient + IS 'aggregations currently happening (lacking wire_out, usually because the amount is too low); this table is not replicated'; +COMMENT ON COLUMN aggregation_transient.amount_val + IS 'Sum of all of the aggregated deposits (without deposit fees)'; +COMMENT ON COLUMN aggregation_transient.wtid_raw + IS 'identifier of the wire transfer'; + +CREATE TABLE IF NOT EXISTS aggregation_transient_default + PARTITION OF aggregation_transient + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + -- ------------------------------ 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 - ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE + ,wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32) ) PARTITION BY HASH (deposit_serial_id); COMMENT ON TABLE aggregation_tracking IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)'; COMMENT ON COLUMN aggregation_tracking.wtid_raw - IS 'We first create entries in the aggregation_tracking table and then finally the wire_out entry once we know the total amount. Hence the constraint must be deferrable and we cannot use a wireout_uuid here, because we do not have it when these rows are created. Changing the logic to first INSERT a dummy row into wire_out and then UPDATEing that row in the same transaction would theoretically reduce per-deposit storage costs by 5 percent (24/~460 bytes).'; + IS 'identifier of the wire transfer'; CREATE TABLE IF NOT EXISTS aggregation_tracking_default PARTITION OF aggregation_tracking @@ -1070,7 +1113,7 @@ BEGIN EXECUTE FORMAT ( 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' - 'UNIQUE (aggregation_serial_id) ' + 'UNIQUE (aggregation_serial_id);' ); END $$; |