From 37f294b6add3ff4780b14ad54331e98c47b8453a Mon Sep 17 00:00:00 2001 From: Joseph Date: Fri, 6 Jan 2023 11:05:05 -0500 Subject: modifications to batch1 --- .../exchange_do_batch_reserves_in_insert.sql | 50 +++++++++++++--------- 1 file changed, 30 insertions(+), 20 deletions(-) (limited to 'src/exchangedb/exchange_do_batch_reserves_in_insert.sql') diff --git a/src/exchangedb/exchange_do_batch_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql index 4e0383f6c..d1cc35d3e 100644 --- a/src/exchangedb/exchange_do_batch_reserves_in_insert.sql +++ b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql @@ -32,7 +32,10 @@ CREATE OR REPLACE FUNCTION exchange_do_batch_reserves_in_insert( OUT ruuid INT8) LANGUAGE plpgsql AS $$ - +DECLARE + curs refcursor; +DECLARE + i RECORD; BEGIN ruuid= 0; out_reserve_found = TRUE; @@ -46,28 +49,35 @@ transaction_duplicate= TRUE; ,in_payto_uri) ON CONFLICT DO NOTHING; - 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) - ON CONFLICT DO NOTHING - RETURNING reserves.reserve_uuid INTO ruuid; + OPEN curs 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) + ON CONFLICT DO NOTHING + RETURNING reserve_uuid, reserve_pub) + SELECT * FROM reserve_changes; + FETCH FROM curs INTO i; IF FOUND THEN -- We made a change, so the reserve did not previously exist. - out_reserve_found = FALSE; - ELSE - -- We made no change, which means the reserve existed. - out_reserve_found = TRUE; + IF in_reserve_pub = i.reserve_pub + THEN + out_reserve_found = FALSE; + ruuid = i.reserve_uuid; + END IF; END IF; + CLOSE curs; + PERFORM pg_notify(in_notify, NULL); INSERT INTO reserves_in (reserve_pub @@ -92,7 +102,7 @@ transaction_duplicate= TRUE; transaction_duplicate = FALSE; ELSE -- Unhappy... --- RAISE EXCEPTION 'Reserve did not exist, but INSERT into reserves_in gave conflict'; + RAISE EXCEPTION 'Reserve did not exist, but INSERT into reserves_in gave conflict'; transaction_duplicate = TRUE; -- ROLLBACK; END IF; -- cgit v1.2.3 From 28c3ae47de7428b4f7019db9158390f8d14ca93d Mon Sep 17 00:00:00 2001 From: Joseph Date: Fri, 6 Jan 2023 11:15:32 -0500 Subject: try batch1 with cursor --- .../exchange_do_batch_reserves_in_insert.sql | 20 +++++++++++++------- 1 file changed, 13 insertions(+), 7 deletions(-) (limited to 'src/exchangedb/exchange_do_batch_reserves_in_insert.sql') diff --git a/src/exchangedb/exchange_do_batch_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql index d1cc35d3e..c95f75c3c 100644 --- a/src/exchangedb/exchange_do_batch_reserves_in_insert.sql +++ b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql @@ -36,6 +36,8 @@ DECLARE curs refcursor; DECLARE i RECORD; +DECLARE + curs_trans refcursor; BEGIN ruuid= 0; out_reserve_found = TRUE; @@ -79,6 +81,8 @@ transaction_duplicate= TRUE; CLOSE curs; PERFORM pg_notify(in_notify, NULL); + OPEN curs_trans FOR + WITH reserve_transaction AS( INSERT INTO reserves_in (reserve_pub ,wire_reference @@ -95,16 +99,18 @@ transaction_duplicate= TRUE; ,in_exchange_account_name ,in_wire_source_h_payto ,in_expiration_date) - ON CONFLICT DO NOTHING; + ON CONFLICT DO NOTHING + RETURNING reserve_pub) + SELECT * FROM reserve_transaction; + FETCH FROM curs_trans INTO i; IF FOUND THEN + IF i.reserve_pub = in_reserve_pub + THEN -- HAPPY PATH THERE IS NO DUPLICATE TRANS - transaction_duplicate = FALSE; - ELSE - -- Unhappy... - RAISE EXCEPTION 'Reserve did not exist, but INSERT into reserves_in gave conflict'; - transaction_duplicate = TRUE; --- ROLLBACK; + transaction_duplicate = FALSE; + END IF; END IF; + CLOSE curs_trans; RETURN; END $$; -- cgit v1.2.3