use reserve history instead of materialized index

2 files changed, 18 insertions, 92 deletions

diff --git a/src/exchangedb/0002-reserves_out.sql b/src/exchangedb/0002-reserves_out.sql
index 1e4038957..f0965d222 100644
--- a/src/exchangedb/0002-reserves_out.sql
+++ b/src/exchangedb/0002-reserves_out.sql
@@ -74,7 +74,6 @@ BEGIN
     ' ADD CONSTRAINT ' || table_name || '_reserve_out_serial_id_key'
     ' UNIQUE (reserve_out_serial_id)'
   );
-  -- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well???
   EXECUTE FORMAT (
     'CREATE INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index '
     'ON ' || table_name || ' '
@@ -82,7 +81,7 @@ BEGIN
   );
   EXECUTE FORMAT (
     'COMMENT ON INDEX ' || table_name || '_by_reserve_uuid_and_execution_date_index '
-    'IS ' || quote_literal('for get_reserves_out and exchange_do_withdraw_limit_check') || ';'
+    'IS ' || quote_literal('for do_gc, do_recoup_by_reserve, select_kyc_relevant_withdraw_events and a few others') || ';'
   );
 END
 $$;
@@ -108,61 +107,11 @@ END
 $$;
 
 
-CREATE FUNCTION create_table_reserves_out_by_reserve(
-  IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
-  table_name TEXT DEFAULT 'reserves_out_by_reserve';
-BEGIN
-  PERFORM create_partitioned_table(
-  'CREATE TABLE %I'
-    '(reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
-    ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64)'
-    ') %s '
-    ,table_name
-    ,'PARTITION BY HASH (reserve_uuid)'
-    ,partition_suffix
-  );
-  PERFORM comment_partitioned_table (
-     '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.'
-    ,table_name
-    ,partition_suffix
-  );
-END $$;
-
-
-CREATE FUNCTION constrain_table_reserves_out_by_reserve(
-  IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
-  table_name TEXT DEFAULT 'reserves_out_by_reserve';
-BEGIN
-  table_name = concat_ws('_', table_name, partition_suffix);
-  EXECUTE FORMAT (
-    'CREATE INDEX ' || table_name || '_main_index '
-    'ON ' || table_name || ' '
-    '(reserve_uuid);'
-  );
-END $$;
-
-
-CREATE FUNCTION reserves_out_by_reserve_insert_trigger()
+CREATE FUNCTION reserves_out_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);
   INSERT INTO reserve_history
     (reserve_pub
     ,table_name
@@ -176,21 +125,8 @@ BEGIN
   WHERE res.reserve_uuid = NEW.reserve_uuid;
   RETURN NEW;
 END $$;
-COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
-  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
-
-
-CREATE 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.';
+COMMENT ON FUNCTION reserves_out_insert_trigger()
+  IS 'Replicate reserve_out inserts into reserve_history table.';
 
 
 CREATE FUNCTION master_table_reserves_out()
@@ -201,14 +137,11 @@ BEGIN
   CREATE TRIGGER reserves_out_on_insert
   AFTER INSERT
    ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
-  CREATE TRIGGER reserves_out_on_delete
-  AFTER DELETE
-    ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_insert_trigger();
 END $$;
 COMMENT ON FUNCTION master_table_reserves_out()
-  IS 'Setup triggers to replicate reserve_out into reserve_out_by_reserve.';
+  IS 'Setup triggers to replicate reserve_out into reserve_history.';
+
 
 
 INSERT INTO exchange_tables
@@ -233,16 +166,6 @@ INSERT INTO exchange_tables
     ,'foreign'
     ,TRUE
     ,FALSE),
-    ('reserves_out_by_reserve'
-    ,'exchange-0002'
-    ,'create'
-    ,TRUE
-    ,FALSE),
-    ('reserves_out_by_reserve'
-    ,'exchange-0002'
-    ,'constrain'
-    ,TRUE
-    ,FALSE),
     ('reserves_out'
     ,'exchange-0002'
     ,'master'
diff --git a/src/exchangedb/exchange_do_recoup_by_reserve.sql b/src/exchangedb/exchange_do_recoup_by_reserve.sql
index 016826615..80f953c4a 100644
--- a/src/exchangedb/exchange_do_recoup_by_reserve.sql
+++ b/src/exchangedb/exchange_do_recoup_by_reserve.sql
@@ -36,22 +36,25 @@ DECLARE
   c_pub    BYTEA;
 BEGIN
   SELECT reserve_uuid
-  INTO res_uuid
-  FROM exchange.reserves
-  WHERE reserves.reserve_pub = res_pub;
+   INTO res_uuid
+   FROM reserves
+   WHERE reserve_pub = res_pub;
 
   FOR blind_ev IN
     SELECT h_blind_ev
-      FROM exchange.reserves_out_by_reserve
-    WHERE reserves_out_by_reserve.reserve_uuid = res_uuid
+      FROM reserves_out ro
+      JOIN reserve_history rh
+        ON (rh.serial_id = ro.reserve_out_serial_id)
+    WHERE rh.reserve_pub = res_pub
+      AND rh.table_name='reserves_out'
   LOOP
     SELECT robr.coin_pub
       INTO c_pub
       FROM exchange.recoup_by_reserve robr
     WHERE robr.reserve_out_serial_id = (
-      SELECT reserves_out.reserve_out_serial_id
-        FROM exchange.reserves_out
-      WHERE reserves_out.h_blind_ev = blind_ev
+      SELECT reserve_out_serial_id
+        FROM reserves_out
+      WHERE h_blind_ev = blind_ev
     );
     RETURN QUERY
       SELECT kc.denom_sig,