add recoup_by_reserve as sql function

3 files changed, 76 insertions, 33 deletions

diff --git a/src/exchangedb/drop0001-exchange-part.sql b/src/exchangedb/drop0001-exchange-part.sql
index 9e5dcd11a..6ea859fb4 100644
--- a/src/exchangedb/drop0001-exchange-part.sql
+++ b/src/exchangedb/drop0001-exchange-part.sql
@@ -82,6 +82,7 @@ DROP TABLE IF EXISTS purse_actions CASCADE;
 
 DROP FUNCTION IF EXISTS exchange_do_withdraw;
 DROP FUNCTION IF EXISTS exchange_do_withdraw_limit_check;
+DROP FUNCTION IF EXISTS exchange_do_recoup_by_reserve;
 DROP FUNCTION IF EXISTS recoup_insert_trigger;
 DROP FUNCTION IF EXISTS recoup_delete_trigger;
 DROP FUNCTION IF EXISTS deposits_insert_trigger;
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index c2b3855a5..dc4f29c84 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -1915,6 +1915,72 @@ END IF;
 END $$;
 
 
+CREATE OR REPLACE FUNCTION exchange_do_recoup_by_reserve(
+  IN res_pub BYTEA
+)
+RETURNS TABLE
+(
+  denom_sig            BYTEA,
+  denominations_serial BIGINT,
+  coin_pub             BYTEA,
+  coin_sig             BYTEA,
+  coin_blind           BYTEA,
+  amount_val           BIGINT,
+  amount_frac          INTEGER,
+  recoup_timestamp     BIGINT
+)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  res_uuid BIGINT;
+  blind_ev BYTEA;
+  c_pub    BYTEA;
+BEGIN
+  SELECT reserve_uuid
+  INTO res_uuid
+  FROM reserves
+  WHERE reserves.reserve_pub = res_pub;
+
+  FOR blind_ev IN
+    SELECT h_blind_ev
+      FROM reserves_out_by_reserve
+    WHERE reserves_out_by_reserve.reserve_uuid = res_uuid
+  LOOP
+    SELECT robr.coin_pub
+      INTO c_pub
+      FROM recoup_by_reserve robr
+    WHERE robr.reserve_out_serial_id = (
+      SELECT reserves_out.reserve_out_serial_id
+        FROM reserves_out
+      WHERE reserves_out.h_blind_ev = blind_ev
+    );
+    RETURN QUERY
+      SELECT kc.denom_sig,
+             kc.denominations_serial,
+             rc.coin_pub,
+             rc.coin_sig,
+             rc.coin_blind,
+             rc.amount_val,
+             rc.amount_frac,
+             rc.recoup_timestamp
+      FROM (
+        SELECT *
+        FROM known_coins
+        WHERE known_coins.coin_pub = c_pub
+      ) kc
+      JOIN (
+        SELECT *
+        FROM recoup
+        WHERE recoup.coin_pub = c_pub
+      ) rc USING (coin_pub);
+  END LOOP;
+END;
+$$;
+
+COMMENT ON FUNCTION exchange_do_recoup_by_reserve 
+  IS 'Recoup by reserve as a function to make sure we hit only the needed partition and not all when joining as joins on distributed tables fetch ALL rows from the shards';
+
+
 CREATE OR REPLACE FUNCTION exchange_do_deposit(
   IN in_amount_with_fee_val INT8,
   IN in_amount_with_fee_frac INT4,
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 4175678a5..e6b86813f 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -2064,41 +2064,17 @@ prepare_statements (struct PostgresClosure *pg)
       "     ON (recoup_by_reserve.reserve_out_serial_id = reserves_out.reserve_out_serial_id)"
       "     WHERE reserves.reserve_pub=$1);",
       */
-      "WITH res AS MATERIALIZED ( "
-      "  SELECT * "
-      "  FROM reserves "
-      "  WHERE reserve_pub = $1 "
-      "), "
-      "coin_pub AS MATERIALIZED ( "
-      "  SELECT coin_pub "
-      "  FROM recoup_by_reserve "
-      "  JOIN (reserves_out "
-      "    JOIN ( "
-      "      SELECT * "
-      "      FROM reserves_out_by_reserve "
-      "      WHERE reserves_out_by_reserve.reserve_uuid = ( "
-      "        SELECT reserve_uuid FROM res "
-      "      ) "
-      "    ) reserves_out_by_reserve "
-      "    ON (reserves_out_by_reserve.h_blind_ev = reserves_out.h_blind_ev)) "
-      "  ON (recoup_by_reserve.reserve_out_serial_id = reserves_out.reserve_out_serial_id) "
-      ") "
-      "SELECT recoup.coin_pub "
-      "  ,recoup.coin_sig "
-      "  ,recoup.coin_blind "
-      "  ,recoup.amount_val "
-      "  ,recoup.amount_frac "
-      "  ,recoup.recoup_timestamp "
+      "SELECT robr.coin_pub "
+      "  ,robr.coin_sig "
+      "  ,robr.coin_blind "
+      "  ,robr.amount_val "
+      "  ,robr.amount_frac "
+      "  ,robr.recoup_timestamp "
       "  ,denominations.denom_pub_hash "
-      "  ,known_coins.denom_sig "
+      "  ,robr.denom_sig "
       "FROM denominations "
-      "  JOIN (known_coins "
-      "    JOIN recoup "
-      "    ON (recoup.coin_pub = known_coins.coin_pub)) "
-      "  ON (known_coins.denominations_serial = denominations.denominations_serial) "
-      "WHERE recoup.coin_pub = ( "
-      "  SELECT coin_pub FROM coin_pub "
-      "); ",
+      "  JOIN exchange_do_recoup_by_reserve($1) robr"
+      " USING (denominations_serial);",
       1),
     /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
        affecting old coins of refreshed coins */