From b90d25ecfcaad0bd421998360dff2c4629b5705b Mon Sep 17 00:00:00 2001 From: Joseph Date: Wed, 21 Dec 2022 05:29:06 -0500 Subject: Rollback in sqlcode --- src/benchmark/bank-benchmark-rsa.conf | 2 +- .../exchange_do_batch8_reserves_in_insert.sql | 444 +++++++++++++++++++++ .../exchange_do_batch_reserves_update.sql | 31 +- src/exchangedb/pg_batch2_reserves_in_insert.c | 17 +- src/exchangedb/test_exchangedb_by_j.c | 2 +- 5 files changed, 480 insertions(+), 16 deletions(-) create mode 100644 src/exchangedb/exchange_do_batch8_reserves_in_insert.sql diff --git a/src/benchmark/bank-benchmark-rsa.conf b/src/benchmark/bank-benchmark-rsa.conf index f2f4dee50..41934b086 100644 --- a/src/benchmark/bank-benchmark-rsa.conf +++ b/src/benchmark/bank-benchmark-rsa.conf @@ -37,7 +37,7 @@ MASTER_PRIV_FILE = ${TALER_DATA_HOME}/exchange/offline-keys/master.priv BASE_URL = "http://localhost:8083/" [exchangedb-postgres] -config = "postgres:///talercheck" +config = "postgres://exchange:taler@192.168.42.42/exchange" [benchmark-remote-exchange] host = localhost diff --git a/src/exchangedb/exchange_do_batch8_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch8_reserves_in_insert.sql new file mode 100644 index 000000000..ab11e3806 --- /dev/null +++ b/src/exchangedb/exchange_do_batch8_reserves_in_insert.sql @@ -0,0 +1,444 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see +-- +CREATE OR REPLACE FUNCTION exchange_do_batch8_reserves_insert( + IN in_reserve_pub BYTEA, + IN in_expiration_date INT8, + IN in_gc_date INT8, + IN in_wire_ref INT8, + IN in_credit_val INT8, + IN in_credit_frac INT4, + IN in_exchange_account_name VARCHAR, + IN in_exectution_date INT8, + IN in_wire_source_h_payto BYTEA, ---h_payto + IN in_payto_uri VARCHAR, + IN in_reserve_expiration INT8, + IN in_notify text, + IN in2_notify text, + IN in3_notify text, + IN in4_notify text, + IN in5_notify text, + IN in6_notify text, + IN in7_notify text, + IN in8_notify text, + IN in2_reserve_pub BYTEA, + IN in2_wire_ref INT8, + IN in2_credit_val INT8, + IN in2_credit_frac INT4, + IN in2_exchange_account_name VARCHAR, + IN in2_exectution_date INT8, + IN in2_wire_source_h_payto BYTEA, ---h_payto + IN in2_payto_uri VARCHAR, + IN in2_reserve_expiration INT8, + IN in3_reserve_pub BYTEA, + IN in3_wire_ref INT8, + IN in3_credit_val INT8, + IN in3_credit_frac INT4, + IN in3_exchange_account_name VARCHAR, + IN in3_exectution_date INT8, + IN in3_wire_source_h_payto BYTEA, ---h_payto + IN in3_payto_uri VARCHAR, + IN in3_reserve_expiration INT8, + IN in4_reserve_pub BYTEA, + IN in4_wire_ref INT8, + IN in4_credit_val INT8, + IN in4_credit_frac INT4, + IN in4_exchange_account_name VARCHAR, + IN in4_exectution_date INT8, + IN in4_wire_source_h_payto BYTEA, ---h_payto + IN in4_payto_uri VARCHAR, + IN in4_reserve_expiration INT8, + IN in5_reserve_pub BYTEA, + IN in5_wire_ref INT8, + IN in5_credit_val INT8, + IN in5_credit_frac INT4, + IN in5_exchange_account_name VARCHAR, + IN in5_exectution_date INT8, + IN in5_wire_source_h_payto BYTEA, ---h_payto + IN in5_payto_uri VARCHAR, + IN in5_reserve_expiration INT8, + IN in6_reserve_pub BYTEA, + IN in6_wire_ref INT8, + IN in6_credit_val INT8, + IN in6_credit_frac INT4, + IN in6_exchange_account_name VARCHAR, + IN in6_exectution_date INT8, + IN in6_wire_source_h_payto BYTEA, ---h_payto + IN in6_payto_uri VARCHAR, + IN in6_reserve_expiration INT8, + IN in7_reserve_pub BYTEA, + IN in7_wire_ref INT8, + IN in7_credit_val INT8, + IN in7_credit_frac INT4, + IN in7_exchange_account_name VARCHAR, + IN in7_exectution_date INT8, + IN in7_wire_source_h_payto BYTEA, ---h_payto + IN in7_payto_uri VARCHAR, + IN in7_reserve_expiration INT8, + IN in8_reserve_pub BYTEA, + IN in8_wire_ref INT8, + IN in8_credit_val INT8, + IN in8_credit_frac INT4, + IN in8_exchange_account_name VARCHAR, + IN in8_exectution_date INT8, + IN in8_wire_source_h_payto BYTEA, ---h_payto + IN in8_payto_uri VARCHAR, + IN in8_reserve_expiration INT8, + OUT out_reserve_found BOOLEAN, + OUT out_reserve_found2 BOOLEAN, + OUT out_reserve_found3 BOOLEAN, + OUT out_reserve_found4 BOOLEAN, + OUT out_reserve_found5 BOOLEAN, + OUT out_reserve_found6 BOOLEAN, + OUT out_reserve_found7 BOOLEAN, + OUT out_reserve_found8 BOOLEAN, + OUT transaction_duplicate BOOLEAN, + OUT transaction_duplicate2 BOOLEAN, + OUT transaction_duplicate3 BOOLEAN, + OUT transaction_duplicate4 BOOLEAN, + OUT transaction_duplicate5 BOOLEAN, + OUT transaction_duplicate6 BOOLEAN, + OUT transaction_duplicate7 BOOLEAN, + OUT transaction_duplicate8 BOOLEAN, + OUT ruuid INT8, + OUT ruuid2 INT8, + OUT ruuid3 INT8, + OUT ruuid4 INT8, + OUT ruuid5 INT8, + OUT ruuid6 INT8, + OUT ruuid7 INT8, + OUT ruuid8 INT8) +LANGUAGE plpgsql +AS $$ +DECLARE + curs_reserve_existed refcursor; +DECLARE + k INT8; +DECLARE + curs_transaction_existed refcursor; + +DECLARE + i RECORD; +DECLARE + r RECORD; + +BEGIN +--INITIALIZATION + transaction_duplicate=FALSE; + transaction_duplicate2=FALSE; + transaction_duplicate3=FALSE; + transaction_duplicate4=FALSE; + transaction_duplicate5=FALSE; + transaction_duplicate6=FALSE; + transaction_duplicate7=FALSE; + transaction_duplicate8=FALSE; + out_reserve_found = TRUE; + out_reserve_found2 = TRUE; + out_reserve_found3 = TRUE; + out_reserve_found4 = TRUE; + out_reserve_found5 = TRUE; + out_reserve_found6 = TRUE; + out_reserve_found7 = TRUE; + out_reserve_found8 = TRUE; + ruuid=0; + ruuid2=0; + ruuid3=0; + ruuid4=0; + ruuid5=0; + ruuid6=0; + ruuid7=0; + ruuid8=0; + k=0; + + --SIMPLE INSERT ON CONFLICT DO NOTHING + INSERT INTO wire_targets + (wire_target_h_payto + ,payto_uri) + VALUES + (in_wire_source_h_payto + ,in_payto_uri), + (in2_wire_source_h_payto + ,in2_payto_uri), + (in3_wire_source_h_payto + ,in3_payto_uri), + (in4_wire_source_h_payto + ,in4_payto_uri), + (in5_wire_source_h_payto + ,in5_payto_uri), + (in6_wire_source_h_payto + ,in6_payto_uri), + (in7_wire_source_h_payto + ,in7_payto_uri), + (in8_wire_source_h_payto + ,in8_payto_uri) + ON CONFLICT DO NOTHING; + + OPEN curs_reserve_existed 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), + (in3_reserve_pub + ,in3_credit_val + ,in3_credit_frac + ,in_expiration_date + ,in_gc_date), + (in4_reserve_pub + ,in4_credit_val + ,in4_credit_frac + ,in_expiration_date + ,in_gc_date), + (in5_reserve_pub + ,in5_credit_val + ,in5_credit_frac + ,in_expiration_date + ,in_gc_date), + (in6_reserve_pub + ,in6_credit_val + ,in6_credit_frac + ,in_expiration_date + ,in_gc_date), + (in7_reserve_pub + ,in7_credit_val + ,in7_credit_frac + ,in_expiration_date + ,in_gc_date), + (in8_reserve_pub + ,in8_credit_val + ,in8_credit_frac + ,in_expiration_date + ,in_gc_date) + ON CONFLICT DO NOTHING + RETURNING reserve_uuid,reserve_pub) + SELECT * FROM reserve_changes; + + + WHILE k < 8 LOOP + + FETCH FROM curs_reserve_existed INTO i; + IF FOUND + THEN + IF in_reserve_pub = i.reserve_pub + THEN + out_reserve_found = FALSE; + ruuid = i.reserve_uuid; + k = k+1; + END IF; + IF in2_reserve_pub = i.reserve_pub + THEN + out_reserve_found2 = FALSE; + ruuid2 = i.reserve_uuid; + k=k+1; + END IF; + IF in3_reserve_pub = i.reserve_pub + THEN + out_reserve_found3 = FALSE; + ruuid3 = i.reserve_uuid; + k=k+1; + END IF; + IF in4_reserve_pub = i.reserve_pub + THEN + out_reserve_found4 = FALSE; + ruuid4 = i.reserve_uuid; + k=k+1; + END IF; + IF in5_reserve_pub = i.reserve_pub + THEN + out_reserve_found5 = FALSE; + ruuid5 = i.reserve_uuid; + k=k+1; + END IF; + IF in6_reserve_pub = i.reserve_pub + THEN + out_reserve_found6 = FALSE; + ruuid6 = i.reserve_uuid; + k=k+1; + END IF; + IF in7_reserve_pub = i.reserve_pub + THEN + out_reserve_found7 = FALSE; + ruuid7 = i.reserve_uuid; + k=k+1; + END IF; + IF in8_reserve_pub = i.reserve_pub + THEN + out_reserve_found8 = FALSE; + ruuid8 = i.reserve_uuid; + k=k+1; + END IF; + END IF; + + END LOOP; + + CLOSE curs_reserve_existed; + IF out_reserve_found + AND out_reserve_found2 + AND out_reserve_found3 + AND out_reserve_found4 + AND out_reserve_found5 + AND out_reserve_found6 + AND out_reserve_found7 + AND out_reserve_found8 + THEN + RETURN; + END IF; + + PERFORM pg_notify(in_notify, NULL); + PERFORM pg_notify(in2_notify, NULL); + PERFORM pg_notify(in3_notify, NULL); + PERFORM pg_notify(in4_notify, NULL); + PERFORM pg_notify(in5_notify, NULL); + PERFORM pg_notify(in6_notify, NULL); + PERFORM pg_notify(in7_notify, NULL); + PERFORM pg_notify(in8_notify, NULL); + k=0; + OPEN curs_transaction_existed FOR + WITH reserve_in_changes AS ( + INSERT INTO reserves_in + (reserve_pub + ,wire_reference + ,credit_val + ,credit_frac + ,exchange_account_section + ,wire_source_h_payto + ,execution_date) + VALUES + (in_reserve_pub + ,in_wire_ref + ,in_credit_val + ,in_credit_frac + ,in_exchange_account_name + ,in_wire_source_h_payto + ,in_expiration_date), + (in2_reserve_pub + ,in2_wire_ref + ,in2_credit_val + ,in2_credit_frac + ,in2_exchange_account_name + ,in2_wire_source_h_payto + ,in_expiration_date), + (in3_reserve_pub + ,in3_wire_ref + ,in3_credit_val + ,in3_credit_frac + ,in3_exchange_account_name + ,in3_wire_source_h_payto + ,in_expiration_date), + (in4_reserve_pub + ,in4_wire_ref + ,in4_credit_val + ,in4_credit_frac + ,in4_exchange_account_name + ,in4_wire_source_h_payto + ,in_expiration_date), + (in5_reserve_pub + ,in5_wire_ref + ,in5_credit_val + ,in5_credit_frac + ,in5_exchange_account_name + ,in5_wire_source_h_payto + ,in_expiration_date), + (in6_reserve_pub + ,in6_wire_ref + ,in6_credit_val + ,in6_credit_frac + ,in6_exchange_account_name + ,in6_wire_source_h_payto + ,in_expiration_date), + (in7_reserve_pub + ,in7_wire_ref + ,in7_credit_val + ,in7_credit_frac + ,in7_exchange_account_name + ,in7_wire_source_h_payto + ,in_expiration_date), + (in8_reserve_pub + ,in8_wire_ref + ,in8_credit_val + ,in8_credit_frac + ,in8_exchange_account_name + ,in8_wire_source_h_payto + ,in_expiration_date) + ON CONFLICT DO NOTHING + RETURNING reserve_pub) + SELECT * FROM reserve_in_changes; + + + WHILE k < 8 LOOP + FETCH FROM curs_transaction_existed INTO r; + IF FOUND + THEN + IF in_reserve_pub = r.reserve_pub + THEN + transaction_duplicate = TRUE; + k=k+1; + END IF; + IF in2_reserve_pub = r.reserve_pub + THEN + transaction_duplicate2 = TRUE; + k=k+1; + END IF; + IF in3_reserve_pub = r.reserve_pub + THEN + transaction_duplicate3 = TRUE; + k=k+1; + END IF; + IF in4_reserve_pub = r.reserve_pub + THEN + transaction_duplicate4 = TRUE; + k=k+1; + END IF; + IF in5_reserve_pub = r.reserve_pub + THEN + transaction_duplicate5 = TRUE; + k=k+1; + END IF; + IF in6_reserve_pub = r.reserve_pub + THEN + transaction_duplicate6 = TRUE; + k=k+1; + END IF; + IF in7_reserve_pub = r.reserve_pub + THEN + transaction_duplicate7 = TRUE; + k=k+1; + END IF; + IF in8_reserve_pub = r.reserve_pub + THEN + transaction_duplicate8 = TRUE; + k=k+1; + END IF; + + END IF; + + END LOOP; + + CLOSE curs_transaction_existed; + RETURN; +END $$; diff --git a/src/exchangedb/exchange_do_batch_reserves_update.sql b/src/exchangedb/exchange_do_batch_reserves_update.sql index a6b7122d5..af5d358fb 100644 --- a/src/exchangedb/exchange_do_batch_reserves_update.sql +++ b/src/exchangedb/exchange_do_batch_reserves_update.sql @@ -26,8 +26,13 @@ CREATE OR REPLACE PROCEDURE exchange_do_batch_reserves_update( IN in_notify text) LANGUAGE plpgsql AS $$ +DECLARE + i RECORD; +DECLARE + curs refcursor; BEGIN - + OPEN curs FOR + WITH reserves_update AS ( INSERT INTO reserves_in (reserve_pub ,wire_reference @@ -43,12 +48,14 @@ BEGIN ,in_credit_frac ,in_exchange_account_name ,in_wire_source_h_payto - ,in_expiration_date); + ,in_expiration_date) + ON CONFLICT DO NOTHING + RETURNING reserve_pub, credit_val, credit_frac) + SELECT * FROM reserves_in; ---IF THE INSERTION WAS A SUCCESS IT MEANS NO DUPLICATED TRANSACTION - IF FOUND + FETCH FROM curs INTO i; + IF FOUND --IF THE INSERTION WAS A SUCCESS IT MEANS NO DUPLICATED TRANSACTION THEN - IF in_reserve_found THEN UPDATE reserves @@ -67,9 +74,21 @@ BEGIN END ,expiration_date=GREATEST(expiration_date,in_expiration_date) ,gc_date=GREATEST(gc_date,in_expiration_date) - WHERE reserves.reserve_pub=in_reserve_pub; + WHERE reserve_pub=in_reserve_pub; + END IF; + PERFORM pg_notify(in_notify, NULL); + ELSE + CLOSE curs; + IF ! out_reserve_found + THEN + ROLLBACK; END IF; PERFORM pg_notify(in_notify, NULL); + +/* UPDATE reserves_in + SET credit_frac = credit_frac - in_credit_frac + AND credit_val = credit_val + in_credit_val + WHERE reserve_pub = in_reserve_pub;*/ END IF; END $$; diff --git a/src/exchangedb/pg_batch2_reserves_in_insert.c b/src/exchangedb/pg_batch2_reserves_in_insert.c index f80f7ea07..268f20d46 100644 --- a/src/exchangedb/pg_batch2_reserves_in_insert.c +++ b/src/exchangedb/pg_batch2_reserves_in_insert.c @@ -820,21 +820,22 @@ TEH_PG_batch2_reserves_in_insert (void *cls, enum GNUNET_DB_QueryStatus qs3; PREPARE (pg, - "reserves_in_add_transaction", + "reserves_update", "CALL exchange_do_batch_reserves_update" " ($1,$2,$3,$4,$5,$6,$7,$8,$9);"); for (unsigned int i = 0; ireserve_pub), + GNUNET_PQ_query_param_auto_from_type (reserves[i].reserve_pub), GNUNET_PQ_query_param_timestamp (&expiry), - GNUNET_PQ_query_param_uint64 (&reserve->wire_reference), - TALER_PQ_query_param_amount (reserve->balance), - GNUNET_PQ_query_param_string (reserve->exchange_account_name), + GNUNET_PQ_query_param_uint64 (&reserves[i].wire_reference), + TALER_PQ_query_param_amount (reserves[i].balance), + GNUNET_PQ_query_param_string (reserves[i].exchange_account_name), GNUNET_PQ_query_param_bool (conflicts[i]), GNUNET_PQ_query_param_auto_from_type (&h_payto), GNUNET_PQ_query_param_string (notify_s[i]), @@ -842,12 +843,12 @@ TEH_PG_batch2_reserves_in_insert (void *cls, }; qs3 = GNUNET_PQ_eval_prepared_non_select (pg->conn, - "reserves_in_add_transaction", + "reserves_update", params); if (qs3<0) { GNUNET_log (GNUNET_ERROR_TYPE_WARNING, - "Failed to update reserves (%d)\n", + "Failed to update (%d)\n", qs3); return qs3; } diff --git a/src/exchangedb/test_exchangedb_by_j.c b/src/exchangedb/test_exchangedb_by_j.c index c114f59ce..9769d964b 100644 --- a/src/exchangedb/test_exchangedb_by_j.c +++ b/src/exchangedb/test_exchangedb_by_j.c @@ -107,7 +107,7 @@ run (void *cls) const char *sndr = "payto://x-taler-bank/localhost:8080/1"; struct TALER_Amount value; unsigned int batch_size = batches[i]; - unsigned int iterations = 1024*10; + unsigned int iterations = 16;//1024*10; struct TALER_ReservePublicKeyP reserve_pubs[iterations]; struct GNUNET_TIME_Absolute now; struct GNUNET_TIME_Timestamp ts; -- cgit v1.2.3