attempt to make GRID5K_MARCO_OPT superfluous

3 files changed, 83 insertions, 22 deletions

diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql
index 2b2b18e6a..7c138d9f5 100644
--- a/src/exchangedb/drop0001.sql
+++ b/src/exchangedb/drop0001.sql
@@ -27,6 +27,8 @@ BEGIN;
 SELECT _v.unregister_patch('exchange-0001');
 
 -- Drops for exchange-0001.sql
+DROP TRIGGER IF EXISTS reserves_out_on_insert ON reserves_out_default;
+DROP TRIGGER IF EXISTS reserves_out_on_delete ON reserves_out_default;
 DROP TABLE IF EXISTS revolving_work_shards CASCADE;
 DROP TABLE IF EXISTS extensions CASCADE;
 DROP TABLE IF EXISTS auditors CASCADE;
@@ -63,6 +65,7 @@ DROP FUNCTION IF EXISTS add_constraints_to_known_coins_partition;
 DROP TABLE IF EXISTS reserves_close CASCADE;
 DROP FUNCTION IF EXISTS add_constraints_to_reserves_close_partition;
 DROP TABLE IF EXISTS reserves_out CASCADE;
+DROP TABLE IF EXISTS reserves_out_by_reserve CASCADE;
 DROP FUNCTION IF EXISTS add_constraints_to_reserves_out_partition;
 DROP TABLE IF EXISTS reserves_in CASCADE;
 DROP FUNCTION IF EXISTS add_constraints_to_reserves_in_partition;
@@ -76,6 +79,9 @@ DROP FUNCTION IF EXISTS exchange_do_withdraw(bigint,int,bytea,bytea,bytea,bytea,
 
 DROP FUNCTION IF EXISTS exchange_do_withdraw_limit_check(bytea,bigint,bigint,int) ;
 
+DROP FUNCTION IF EXISTS reserves_out_by_reserve_insert_trigger();
+DROP FUNCTION IF EXISTS reserves_out_by_reserve_delete_trigger();
+
 DROP FUNCTION IF EXISTS exchange_do_deposit;
 
 DROP FUNCTION IF EXISTS exchange_do_melt;
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 58a6b7288..77a9ba2a8 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -119,7 +119,7 @@ CREATE TABLE IF NOT EXISTS reserves
   ,current_balance_val INT8 NOT NULL
   ,current_balance_frac INT4 NOT NULL
   ,expiration_date INT8 NOT NULL
-  ,gc_date INT8 NOT NULL
+  ,gc_date INT8 NOT NULL    
   )
   PARTITION BY HASH (reserve_pub);
 COMMENT ON TABLE reserves
@@ -933,6 +933,58 @@ CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
   (known_coin_id);
 
 
+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 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_default 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_default table.';
+
+CREATE TRIGGER reserves_out_on_delete
+  AFTER DELETE
+    ON reserves_out
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+
+
 CREATE TABLE IF NOT EXISTS recoup_refresh
   (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index b5fca072c..34d3b9f9c 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -713,21 +713,23 @@ prepare_statements (struct PostgresClosure *pg)
     GNUNET_PQ_make_prepare (
       "get_reserves_out",
       "SELECT"
-      " h_blind_ev"
+      " ro.h_blind_ev"
       ",denom.denom_pub_hash"
-      ",denom_sig"
-      ",reserve_sig"
-      ",execution_date"
-      ",amount_with_fee_val"
-      ",amount_with_fee_frac"
+      ",ro.denom_sig"
+      ",ro.reserve_sig"
+      ",ro.execution_date"
+      ",ro.amount_with_fee_val"
+      ",ro.amount_with_fee_frac"
       ",denom.fee_withdraw_val"
       ",denom.fee_withdraw_frac"
-      " FROM reserves"
-      "    JOIN reserves_out"
-      "      USING (reserve_uuid)"
-      "    JOIN denominations denom"
-      "      USING (denominations_serial)"
-      " WHERE reserve_pub=$1;",
+      " FROM denominations denom"
+      " JOIN reserves_out ro"
+      "   ON (ro.denominations_serial = denom.denominations_serial)"
+      " JOIN reserves_out_by_reserve ror"
+      "   ON (ro.h_blind_ev = ror.h_blind_ev)"
+      " JOIN reserves res"
+      "   ON (res.reserve_uuid = ror.reserve_uuid)"
+      " WHERE res.reserve_pub=$1;",
       1),
     /* Used in #postgres_select_withdrawals_above_serial_id() */
     GNUNET_PQ_make_prepare (
@@ -1643,15 +1645,16 @@ prepare_statements (struct PostgresClosure *pg)
       "  coins.denom_sig"
       " FROM denominations denoms"
       " JOIN known_coins coins"
-      "  ON (coins.denominations_serial = denoms.denominations_serial)"
-      " JOIN recoup"
-      "  USING (known_coin_id)"
-      " JOIN ("
-      "  reserves_out"
-      "   JOIN reserves"
-      "    USING (reserve_uuid)"
-      "  ) USING (reserve_out_serial_id)"
-      " WHERE reserve_pub=$1;",
+      "   ON (coins.denominations_serial = denoms.denominations_serial)"
+      " JOIN recoup rc"
+      "   ON (rc.known_coin_id = coins.known_coin_id)"
+      " JOIN reserves_out ro"
+      "   ON (ro.reserve_out_serial_id = rc.reserve_out_serial_id)"
+      " JOIN reserves_out_by_reserve ror"
+      "   ON (ror.h_blind_ev = ro.h_blind_ev)"
+      " JOIN reserves res"
+      "   ON (res.reserve_uuid = ror.reserve_uuid)"
+      " WHERE res.reserve_pub=$1;",
       1),
     /* Used in #postgres_get_coin_transactions() to obtain recoup transactions
        affecting old coins of refreshed coins */