diff options
-rw-r--r-- | src/exchangedb/Makefile.am | 27 | ||||
-rw-r--r-- | src/exchangedb/exchange_do_batch2_reserves_in_insert.sql | 221 | ||||
-rw-r--r-- | src/exchangedb/exchange_do_batch_reserves_in_insert.sql (renamed from src/exchangedb/exchange_do_batch_reserves_in.sql) | 75 | ||||
-rw-r--r-- | src/exchangedb/exchange_do_batch_reserves_update.sql | 75 | ||||
-rw-r--r-- | src/exchangedb/pg_batch2_reserves_in_insert.c | 29 | ||||
-rw-r--r-- | src/exchangedb/pg_batch_reserves_in_insert.c | 4 | ||||
-rw-r--r-- | src/exchangedb/procedures.sql.in | 5 | ||||
-rw-r--r-- | src/exchangedb/test_exchangedb_batch_reserves_in_insert.c | 12 | ||||
-rw-r--r-- | src/exchangedb/test_exchangedb_by_j.c | 13 |
9 files changed, 366 insertions, 95 deletions
diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index a6eb6747c..56fe2ff5e 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -282,13 +282,15 @@ check_PROGRAMS = \ bench-db-postgres\ perf-exchangedb-reserves-in-insert-postgres\ test-exchangedb-by-j-postgres\ - test-exchangedb-batch-reserves-in-insert-postgres + test-exchangedb-batch-reserves-in-insert-postgres\ + test-exchangedb-populate-table-postgres AM_TESTS_ENVIRONMENT=export TALER_PREFIX=$${TALER_PREFIX:-@libdir@};export PATH=$${TALER_PREFIX:-@prefix@}/bin:$$PATH; TESTS = \ test-exchangedb-postgres\ test-exchangedb-by-j-postgres\ perf-exchangedb-reserves-in-insert-postgres\ - test-exchangedb-batch-reserves-in-insert-postgres + test-exchangedb-batch-reserves-in-insert-postgres\ + test-exchangedb-populate-table-postgres test_exchangedb_postgres_SOURCES = \ @@ -361,6 +363,27 @@ bench_db_postgres_LDADD = \ -lgnunetutil \ $(XLIB) +test_exchangedb_populate_table_postgres_SOURCES = \ + test_exchangedb_populate_table.c +test_exchangedb_populate_table_postgres_LDADD = \ + libtalerexchangedb.la \ + $(top_builddir)/src/json/libtalerjson.la \ + $(top_builddir)/src/util/libtalerutil.la \ + $(top_builddir)/src/pq/libtalerpq.la \ + -ljansson \ + -lgnunetjson \ + -lgnunetutil \ + $(XLIB) + +bench_db_postgres_SOURCES = \ + bench_db.c +bench_db_postgres_LDADD = \ + libtalerexchangedb.la \ + $(top_builddir)/src/util/libtalerutil.la \ + $(top_builddir)/src/pq/libtalerpq.la \ + -lgnunetpq \ + -lgnunetutil \ + $(XLIB) EXTRA_test_exchangedb_postgres_DEPENDENCIES = \ libtaler_plugin_exchangedb_postgres.la diff --git a/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql new file mode 100644 index 000000000..8d3942a6d --- /dev/null +++ b/src/exchangedb/exchange_do_batch2_reserves_in_insert.sql @@ -0,0 +1,221 @@ +-- +-- 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 <http://www.gnu.org/licenses/> +-- +CREATE OR REPLACE FUNCTION exchange_do_batch2_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_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, + OUT out_reserve_found BOOLEAN, + OUT out_reserve_found2 BOOLEAN, + OUT transaction_duplicate BOOLEAN, + OUT transaction_duplicate2 BOOLEAN, + OUT ruuid INT8, + OUT ruuid2 INT8) +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_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; + + i RECORD; + +BEGIN + + --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) + ON CONFLICT DO NOTHING; + + FOR k IN curs_reserve_exist + LOOP + IF in_reserve_pub = k.reserve_pub + THEN + out_reserve_found = TRUE; + END IF; + + IF in2_reserve_pub = k.reserve_pub + THEN + out_reserve_found2 = TRUE; + END IF; + + IF out_reserve_found AND out_reserve_found2 + 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 + out_reserve_found2 = FALSE; + END IF; + IF out_reserve_found AND out_reserve_found2 + THEN + transaction_duplicate = FALSE; + transaction_duplicate2 = FALSE; + 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); + + 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) + ON CONFLICT DO NOTHING; + IF FOUND + THEN + transaction_duplicate = FALSE; /*HAPPY PATH THERE IS NO DUPLICATE TRANS AND NEW RESERVE*/ + transaction_duplicate2 = FALSE; + RETURN; + ELSE + FOR l IN curs_transaction_exist + LOOP + IF in_reserve_pub = l.reserve_pub + THEN + transaction_duplicate = TRUE; + END IF; + + IF in2_reserve_pub = l.reserve_pub + THEN + transaction_duplicate2 = TRUE; + END IF; + + IF transaction_duplicate AND transaction_duplicate2 + THEN + RETURN; + END IF; + 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; + + RETURN; +END $$; + diff --git a/src/exchangedb/exchange_do_batch_reserves_in.sql b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql index faad2ca84..ef4c84aa7 100644 --- a/src/exchangedb/exchange_do_batch_reserves_in.sql +++ b/src/exchangedb/exchange_do_batch_reserves_in_insert.sql @@ -13,7 +13,8 @@ -- You should have received a copy of the GNU General Public License along with -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION batch_reserves_in( + +CREATE OR REPLACE FUNCTION exchange_do_batch_reserves_in_insert( IN in_reserve_pub BYTEA, IN in_expiration_date INT8, IN in_gc_date INT8, @@ -25,16 +26,22 @@ CREATE OR REPLACE FUNCTION batch_reserves_in( IN in_wire_source_h_payto BYTEA, ---h_payto IN in_payto_uri VARCHAR, IN in_reserve_expiration INT8, + IN in_notify text, OUT out_reserve_found BOOLEAN, OUT transaction_duplicate BOOLEAN, OUT ruuid INT8) LANGUAGE plpgsql AS $$ -DECLARE - my_amount_val INT8; -DECLARE - my_amount_frac INT4; + BEGIN + --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) + ON CONFLICT DO NOTHING; INSERT INTO reserves (reserve_pub @@ -50,6 +57,7 @@ BEGIN ,in_gc_date) ON CONFLICT DO NOTHING RETURNING reserve_uuid INTO ruuid; + PERFORM pg_notify(in_notify, NULL); IF FOUND THEN @@ -57,18 +65,10 @@ BEGIN out_reserve_found = FALSE; ELSE -- We made no change, which means the reserve existed. - out_reserve_found = TRUE; + out_reserve_found = TRUE; /*RESERVE EXISTED BUT WE DO NOT KNOW ANY INFORMATIONS ABOUT TRANSACTION, RETURN*/ + RETURN; END IF; - --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) - ON CONFLICT DO NOTHING; - INSERT INTO reserves_in (reserve_pub ,wire_reference @@ -84,47 +84,14 @@ BEGIN ,in_credit_frac ,in_exchange_account_name ,in_wire_source_h_payto - ,in_expiration_date); - - --IF THE INSERTION WAS A SUCCESS IT MEANS NO DUPLICATED TRANSACTION + ,in_expiration_date) + ON CONFLICT DO NOTHING; IF FOUND THEN - transaction_duplicate = FALSE; - IF out_reserve_found - THEN - UPDATE reserves - SET - current_balance_frac = current_balance_frac+in_credit_frac - - CASE - WHEN current_balance_frac + in_credit_frac >= 100000000 - THEN 100000000 - ELSE 1 - END - ,current_balance_val = current_balance_val+in_credit_val - + CASE - WHEN current_balance_frac + in_credit_frac >= 100000000 - THEN 1 - ELSE 0 - END - ,expiration_date=GREATEST(expiration_date,in_expiration_date) - ,gc_date=GREATEST(gc_date,in_expiration_date) - WHERE reserves.reserve_pub=in_reserve_pub; - out_reserve_found = TRUE; - RETURN; - ELSE - out_reserve_found=FALSE; - RETURN; - END IF; - out_reserve_found = TRUE; + transaction_duplicate = FALSE; /*HAPPY PATH THERE IS NO DUPLICATE TRANS AND NEW RESERVE*/ + RETURN; ELSE - transaction_duplicate = TRUE; - IF out_reserve_found - THEN - out_reserve_found = TRUE; - RETURN; - ELSE - out_reserve_found = FALSE; - RETURN; - END IF; + transaction_duplicate = TRUE; /*HAPPY PATH IF THERE IS A DUPLICATE TRANS WE JUST NEED TO ROLLBACK COMPLAIN*/ + RETURN; END IF; END $$; diff --git a/src/exchangedb/exchange_do_batch_reserves_update.sql b/src/exchangedb/exchange_do_batch_reserves_update.sql new file mode 100644 index 000000000..f6b972c67 --- /dev/null +++ b/src/exchangedb/exchange_do_batch_reserves_update.sql @@ -0,0 +1,75 @@ +-- +-- 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 <http://www.gnu.org/licenses/> +-- + +CREATE OR REPLACE PROCEDURE exchange_do_batch_reserves_update( + IN in_reserve_pub BYTEA, + IN in_expiration_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_reserve_found BOOLEAN, + IN in_wire_source_h_payto BYTEA, + IN in_notify text) ---h_payto +LANGUAGE plpgsql +AS $$ +BEGIN + + 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); + +--IF THE INSERTION WAS A SUCCESS IT MEANS NO DUPLICATED TRANSACTION + IF FOUND + THEN +-- transaction_duplicate = FALSE; + IF in_reserve_found + THEN + UPDATE reserves + SET + current_balance_frac = current_balance_frac+in_credit_frac + - CASE + WHEN current_balance_frac + in_credit_frac >= 100000000 + THEN 100000000 + ELSE 1 + END + ,current_balance_val = current_balance_val+in_credit_val + + CASE + WHEN current_balance_frac + in_credit_frac >= 100000000 + THEN 1 + ELSE 0 + END + ,expiration_date=GREATEST(expiration_date,in_expiration_date) + ,gc_date=GREATEST(gc_date,in_expiration_date) + WHERE reserves.reserve_pub=in_reserve_pub; + END IF; + PERFORM pg_notify(in_notify, NULL); + END IF; + +END $$; diff --git a/src/exchangedb/pg_batch2_reserves_in_insert.c b/src/exchangedb/pg_batch2_reserves_in_insert.c index 77120254c..553dd3160 100644 --- a/src/exchangedb/pg_batch2_reserves_in_insert.c +++ b/src/exchangedb/pg_batch2_reserves_in_insert.c @@ -1,3 +1,4 @@ + /* This file is part of TALER Copyright (C) 2022 Taler Systems SA @@ -93,7 +94,7 @@ TEH_PG_batch2_reserves_in_insert (void *cls, ",transaction_duplicate2" ",ruuid AS reserve_uuid" ",ruuid2 AS reserve_uuid2" - " FROM batch2_reserves_insert" + " FROM exchange_do_batch2_reserves_insert" " ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21);"); expiry = GNUNET_TIME_absolute_to_timestamp ( GNUNET_TIME_absolute_add (reserves->execution_time.abs_time, @@ -199,18 +200,18 @@ TEH_PG_batch2_reserves_in_insert (void *cls, results[i] = (transaction_duplicate) ? GNUNET_DB_STATUS_SUCCESS_NO_RESULTS : GNUNET_DB_STATUS_SUCCESS_ONE_RESULT; - conflicts[i] = conflicted; - conflicts2[i] = conflicted2; - // fprintf(stdout, "%d", conflicts[i]); - // fprintf(stdout, "%d", conflicts2[i]); - if ((! conflicts[i] && transaction_duplicate) || (! conflicts2[i] && - transaction_duplicate2)) - { - GNUNET_break (0); - TEH_PG_rollback (pg); - return GNUNET_DB_STATUS_HARD_ERROR; - } - need_update |= conflicted |= conflicted2; + conflicts[i] = conflicted; + conflicts2[i] = conflicted2; + // fprintf(stdout, "%d",conflicts[i]); + // fprintf(stdout, "%d", conflicts2[i]); + if ((!conflicts[i] && transaction_duplicate) ||(!conflicts2[i] && transaction_duplicate2)) + { + GNUNET_break (0); + TEH_PG_rollback (pg); + return GNUNET_DB_STATUS_HARD_ERROR; + } + need_update |= conflicted; + need_update2 |= conflicted2; } // commit { @@ -237,7 +238,7 @@ TEH_PG_batch2_reserves_in_insert (void *cls, enum GNUNET_DB_QueryStatus qs2; PREPARE (pg, "reserves_in_add_transaction", - "SELECT batch_reserves_update" + "CALL exchange_do_batch_reserves_update" " ($1,$2,$3,$4,$5,$6,$7,$8,$9);"); for (unsigned int i = 0; i<reserves_length; i++) { diff --git a/src/exchangedb/pg_batch_reserves_in_insert.c b/src/exchangedb/pg_batch_reserves_in_insert.c index 9f00fa1d2..00c6b922e 100644 --- a/src/exchangedb/pg_batch_reserves_in_insert.c +++ b/src/exchangedb/pg_batch_reserves_in_insert.c @@ -86,7 +86,7 @@ TEH_PG_batch_reserves_in_insert ( "out_reserve_found AS conflicted" ",transaction_duplicate" ",ruuid AS reserve_uuid" - " FROM batch_reserves_insert" + " FROM exchange_do_batch_reserves_in_insert" " ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12);"); expiry = GNUNET_TIME_absolute_to_timestamp ( GNUNET_TIME_absolute_add (reserves->execution_time.abs_time, @@ -203,7 +203,7 @@ TEH_PG_batch_reserves_in_insert ( enum GNUNET_DB_QueryStatus qs2; PREPARE (pg, "reserves_in_add_transaction", - "SELECT batch_reserves_update" + "SELECT exchange_do_batch_reserves_update" " ($1,$2,$3,$4,$5,$6,$7,$8,$9);"); for (unsigned int i = 0; i<reserves_length; i++) { diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in index b412b66d7..dc918101f 100644 --- a/src/exchangedb/procedures.sql.in +++ b/src/exchangedb/procedures.sql.in @@ -35,7 +35,8 @@ SET search_path TO exchange; #include "exchange_do_reserve_open_deposit.sql" #include "exchange_do_reserve_open.sql" #include "exchange_do_insert_or_update_policy_details.sql" -#include "exchange_do_batch_reserves_in.sql" - +#include "exchange_do_batch_reserves_in_insert.sql" +#include "exchange_do_batch_reserves_update.sql" +#include "exchange_do_batch2_reserves_in_insert.sql" COMMIT; diff --git a/src/exchangedb/test_exchangedb_batch_reserves_in_insert.c b/src/exchangedb/test_exchangedb_batch_reserves_in_insert.c index 460778b88..b9b5a874d 100644 --- a/src/exchangedb/test_exchangedb_batch_reserves_in_insert.c +++ b/src/exchangedb/test_exchangedb_batch_reserves_in_insert.c @@ -83,15 +83,9 @@ run (void *cls) } (void) plugin->drop_tables (plugin->cls); if (GNUNET_OK != - plugin->create_tables (plugin->cls)) - { - GNUNET_break (0); - result = 77; - goto cleanup; - } - if (GNUNET_OK != - plugin->setup_partitions (plugin->cls, - num_partitions)) + plugin->create_tables (plugin->cls, + true, + num_partitions)) { GNUNET_break (0); result = 77; diff --git a/src/exchangedb/test_exchangedb_by_j.c b/src/exchangedb/test_exchangedb_by_j.c index 834373b55..3b9b0eea4 100644 --- a/src/exchangedb/test_exchangedb_by_j.c +++ b/src/exchangedb/test_exchangedb_by_j.c @@ -92,17 +92,6 @@ run (void *cls) goto cleanup; } - for (unsigned int i = 0; i< 7; i++) - - if (GNUNET_OK != - plugin->setup_partitions (plugin->cls, - num_partitions)) - { - GNUNET_break (0); - result = 77; - goto cleanup; - } - for (unsigned int i = 0; i< 8; i++) { @@ -134,7 +123,7 @@ run (void *cls) reserves[k].wire_reference = k; } FAILIF (batch_size != - plugin->batch_reserves_in_insert (plugin->cls, + plugin->batch2_reserves_in_insert (plugin->cls, reserves, batch_size, results)); |