From d0a69da8954fd72f361795c2e007bad3fe5accd1 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <grothoff@gnunet.org>
Date: Sun, 27 Mar 2022 10:32:28 +0200
Subject: towards removing tiny bit

---
 src/exchangedb/exchange-0001.sql | 69 ++++++++++++++++++++++++++++++++--------
 1 file changed, 56 insertions(+), 13 deletions(-)

(limited to 'src/exchangedb/exchange-0001.sql')

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
 $$;
-- 
cgit v1.2.3

