From b0c106124c99a45282d94499f87fb1768914dba3 Mon Sep 17 00:00:00 2001
From: Joseph <Joseph.xu@efrei.net>
Date: Mon, 19 Dec 2022 10:02:43 -0500
Subject: new batch test and standard deviation

---
 .../exchange_do_batch2_reserves_in_insert.sql      | 155 +++++++++------------
 1 file changed, 64 insertions(+), 91 deletions(-)

(limited to 'src/exchangedb/exchange_do_batch2_reserves_in_insert.sql')

diff --git a/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql
index 8d3942a6..6a0cc6d7 100644
--- a/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql
+++ b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql
@@ -26,6 +26,7 @@ CREATE OR REPLACE FUNCTION exchange_do_batch2_reserves_insert(
   IN in_payto_uri VARCHAR,
   IN in_reserve_expiration INT8,
   IN in_notify text,
+  IN in2_notify text,
   IN in2_reserve_pub BYTEA,
   IN in2_wire_ref INT8,
   IN in2_credit_val INT8,
@@ -44,22 +45,29 @@ CREATE OR REPLACE FUNCTION exchange_do_batch2_reserves_insert(
 LANGUAGE plpgsql
 AS $$
 DECLARE
-  curs_reserve_exist CURSOR
-  FOR SELECT reserve_pub
-  FROM reserves
-  WHERE ruuid = reserves.reserve_uuid OR ruuid2 = reserves.reserve_uuid;
+  curs_reserve_exist REFCURSOR;
 
+--  FOR SELECT reserve_pub
+--  FROM reserves
+--  WHERE in_reserve_pub = reserves.reserve_pub
+--  OR in2_reserve_pub = reserves.reserve_pub;
+DECLARE
   curs_transaction_exist CURSOR
   FOR SELECT reserve_pub
   FROM reserves_in
   WHERE in_reserve_pub = reserves_in.reserve_pub
-  OR reserves_in.reserve_pub = in2_reserve_pub;
-
+  OR in2_reserve_pub = reserves_in.reserve_pub;
+DECLARE
   i RECORD;
 
 BEGIN
-
   --SIMPLE INSERT ON CONFLICT DO NOTHING
+  transaction_duplicate=FALSE;
+  transaction_duplicate2=FALSE;
+  out_reserve_found = TRUE;
+  out_reserve_found2 = TRUE;
+  ruuid=0;
+  ruuid2=0;
   INSERT INTO wire_targets
     (wire_target_h_payto
     ,payto_uri)
@@ -70,93 +78,65 @@ BEGIN
     ,in2_payto_uri)
   ON CONFLICT DO NOTHING;
 
-  FOR k IN curs_reserve_exist
-  LOOP
-    IF in_reserve_pub = k.reserve_pub
+  OPEN curs_reserve_exist FOR
+  WITH reserve_changes AS (
+    INSERT INTO reserves
+      (reserve_pub
+      ,current_balance_val
+      ,current_balance_frac
+      ,expiration_date
+      ,gc_date)
+      VALUES
+      (in_reserve_pub
+      ,in_credit_val
+      ,in_credit_frac
+      ,in_expiration_date
+      ,in_gc_date),
+      (in2_reserve_pub
+      ,in2_credit_val
+      ,in2_credit_frac
+      ,in_expiration_date
+      ,in_gc_date)
+     ON CONFLICT DO NOTHING
+     RETURNING reserve_uuid,reserve_pub)
+    SELECT * FROM reserve_changes;
+
+  FETCH FROM curs_reserve_exist INTO i;
+  IF FOUND
+  THEN
+    IF in_reserve_pub = i.reserve_pub
     THEN
-         out_reserve_found = TRUE;
+       out_reserve_found = FALSE;
+       ruuid = i.reserve_uuid;
     END IF;
-
-    IF in2_reserve_pub = k.reserve_pub
+    IF in2_reserve_pub = i.reserve_pub
     THEN
-         out_reserve_found2 = TRUE;
+        out_reserve_found2 = FALSE;
+        ruuid2 = i.reserve_uuid;
     END IF;
-
-    IF out_reserve_found AND out_reserve_found2
+    FETCH FROM curs_reserve_exist INTO i;
+    IF FOUND
     THEN
-        EXIT;
-    END IF;
-  END LOOP;
-
-
-  IF out_reserve_found IS NULL
-  THEN
-        out_reserve_found=FALSE;
-  END IF;
-  IF out_reserve_found2 IS NULL
-  THEN
+      IF in_reserve_pub = i.reserve_pub
+      THEN
+        out_reserve_found = FALSE;
+        ruuid = i.reserve_uuid;
+      END IF;
+      IF in2_reserve_pub = i.reserve_pub
+      THEN
         out_reserve_found2 = FALSE;
+        ruuid2 = i.reserve_uuid;
+     END IF;
+    END IF;
   END IF;
+  CLOSE curs_reserve_exist;
   IF out_reserve_found AND out_reserve_found2
   THEN
-     transaction_duplicate = FALSE;
-     transaction_duplicate2 = FALSE;
-     RETURN;
+      RETURN;
   END IF;
 
-  /*LOOP TO STORE UUID*/
-  FOR i IN
-  WITH input_rows
-  (reserve_pub
-    ,current_balance_val
-    ,current_balance_frac
-    ,expiration_date
-    ,gc_date)
-  AS
-  (
-   VALUES
-    (in_reserve_pub
-    ,in_credit_val
-    ,in_credit_frac
-    ,in_expiration_date
-    ,in_gc_date),
-    (in2_reserve_pub
-    ,in2_credit_val
-    ,in2_credit_frac
-    ,in_expiration_date
-    ,in_gc_date)
-   ), ins AS (
-   INSERT INTO reserves
-   (reserve_pub
-    ,current_balance_val
-    ,current_balance_frac
-    ,expiration_date
-    ,gc_date)
-    SELECT * FROM input_rows
-    ON CONFLICT DO NOTHING
-    RETURNING reserve_uuid)
-   SELECT
-	*
-   FROM
-	(
-		SELECT
-			reserve_uuid,
-			ROW_NUMBER () OVER (ORDER BY reserve_uuid)
-		FROM
-			ins
-	) x
-  LOOP
-    IF i.ROW_NUMBER = 1
-    THEN
-      ruuid = i.reserve_uuid;
-    ELSE
-      ruuid2 = i.reserve_uuid;
-    END IF;
-  END LOOP;
-
-
-
   PERFORM pg_notify(in_notify, NULL);
+  PERFORM pg_notify(in2_notify, NULL);
 
   INSERT INTO reserves_in
     (reserve_pub
@@ -207,15 +187,8 @@ BEGIN
     END LOOP;
   END IF;
 
-  IF transaction_duplicate IS NULL
-  THEN
-        transaction_duplicate=FALSE;
-  END IF;
-  IF transaction_duplicate2 IS NULL
-  THEN
-        transaction_duplicate2 = FALSE;
-  END IF;
-
+  CLOSE curs_reserve_exist;
+  CLOSE curs_transaction_exist;
   RETURN;
 END $$;
 
-- 
cgit v1.2.3

