diff options
author | Marco Boss <bossm8@bfh.ch> | 2022-05-13 15:28:43 +0200 |
---|---|---|
committer | Marco Boss <bossm8@bfh.ch> | 2022-05-13 15:28:43 +0200 |
commit | 42f3f83b7d703d41c89976a90b6b745b0d350353 (patch) | |
tree | 2626194bf1c9ddb3cfb0f6006866b008a448e515 /src/exchangedb | |
parent | d6c161a72e56a15602f581ba0cb7b7a1610f2529 (diff) | |
download | exchange-42f3f83b7d703d41c89976a90b6b745b0d350353.tar.xz |
add recoup_by_reserve as sql function
Diffstat (limited to 'src/exchangedb')
-rw-r--r-- | src/exchangedb/drop0001-exchange-part.sql | 1 | ||||
-rw-r--r-- | src/exchangedb/exchange-0001-part.sql | 66 | ||||
-rw-r--r-- | src/exchangedb/plugin_exchangedb_postgres.c | 42 |
3 files changed, 76 insertions, 33 deletions
diff --git a/src/exchangedb/drop0001-exchange-part.sql b/src/exchangedb/drop0001-exchange-part.sql index 9e5dcd11a..6ea859fb4 100644 --- a/src/exchangedb/drop0001-exchange-part.sql +++ b/src/exchangedb/drop0001-exchange-part.sql @@ -82,6 +82,7 @@ DROP TABLE IF EXISTS purse_actions CASCADE; DROP FUNCTION IF EXISTS exchange_do_withdraw; DROP FUNCTION IF EXISTS exchange_do_withdraw_limit_check; +DROP FUNCTION IF EXISTS exchange_do_recoup_by_reserve; DROP FUNCTION IF EXISTS recoup_insert_trigger; DROP FUNCTION IF EXISTS recoup_delete_trigger; DROP FUNCTION IF EXISTS deposits_insert_trigger; diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql index c2b3855a5..dc4f29c84 100644 --- a/src/exchangedb/exchange-0001-part.sql +++ b/src/exchangedb/exchange-0001-part.sql @@ -1915,6 +1915,72 @@ END IF; END $$; +CREATE OR REPLACE FUNCTION exchange_do_recoup_by_reserve( + IN res_pub BYTEA +) +RETURNS TABLE +( + denom_sig BYTEA, + denominations_serial BIGINT, + coin_pub BYTEA, + coin_sig BYTEA, + coin_blind BYTEA, + amount_val BIGINT, + amount_frac INTEGER, + recoup_timestamp BIGINT +) +LANGUAGE plpgsql +AS $$ +DECLARE + res_uuid BIGINT; + blind_ev BYTEA; + c_pub BYTEA; +BEGIN + SELECT reserve_uuid + INTO res_uuid + FROM reserves + WHERE reserves.reserve_pub = res_pub; + + FOR blind_ev IN + SELECT h_blind_ev + FROM reserves_out_by_reserve + WHERE reserves_out_by_reserve.reserve_uuid = res_uuid + LOOP + SELECT robr.coin_pub + INTO c_pub + FROM recoup_by_reserve robr + WHERE robr.reserve_out_serial_id = ( + SELECT reserves_out.reserve_out_serial_id + FROM reserves_out + WHERE reserves_out.h_blind_ev = blind_ev + ); + RETURN QUERY + SELECT kc.denom_sig, + kc.denominations_serial, + rc.coin_pub, + rc.coin_sig, + rc.coin_blind, + rc.amount_val, + rc.amount_frac, + rc.recoup_timestamp + FROM ( + SELECT * + FROM known_coins + WHERE known_coins.coin_pub = c_pub + ) kc + JOIN ( + SELECT * + FROM recoup + WHERE recoup.coin_pub = c_pub + ) rc USING (coin_pub); + END LOOP; +END; +$$; + +COMMENT ON FUNCTION exchange_do_recoup_by_reserve + IS 'Recoup by reserve as a function to make sure we hit only the needed partition and not all when joining as joins on distributed tables fetch ALL rows from the shards'; + + CREATE OR REPLACE FUNCTION exchange_do_deposit( IN in_amount_with_fee_val INT8, IN in_amount_with_fee_frac INT4, diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 4175678a5..e6b86813f 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -2064,41 +2064,17 @@ prepare_statements (struct PostgresClosure *pg) " ON (recoup_by_reserve.reserve_out_serial_id = reserves_out.reserve_out_serial_id)" " WHERE reserves.reserve_pub=$1);", */ - "WITH res AS MATERIALIZED ( " - " SELECT * " - " FROM reserves " - " WHERE reserve_pub = $1 " - "), " - "coin_pub AS MATERIALIZED ( " - " SELECT coin_pub " - " FROM recoup_by_reserve " - " JOIN (reserves_out " - " JOIN ( " - " SELECT * " - " FROM reserves_out_by_reserve " - " WHERE reserves_out_by_reserve.reserve_uuid = ( " - " SELECT reserve_uuid FROM res " - " ) " - " ) reserves_out_by_reserve " - " ON (reserves_out_by_reserve.h_blind_ev = reserves_out.h_blind_ev)) " - " ON (recoup_by_reserve.reserve_out_serial_id = reserves_out.reserve_out_serial_id) " - ") " - "SELECT recoup.coin_pub " - " ,recoup.coin_sig " - " ,recoup.coin_blind " - " ,recoup.amount_val " - " ,recoup.amount_frac " - " ,recoup.recoup_timestamp " + "SELECT robr.coin_pub " + " ,robr.coin_sig " + " ,robr.coin_blind " + " ,robr.amount_val " + " ,robr.amount_frac " + " ,robr.recoup_timestamp " " ,denominations.denom_pub_hash " - " ,known_coins.denom_sig " + " ,robr.denom_sig " "FROM denominations " - " JOIN (known_coins " - " JOIN recoup " - " ON (recoup.coin_pub = known_coins.coin_pub)) " - " ON (known_coins.denominations_serial = denominations.denominations_serial) " - "WHERE recoup.coin_pub = ( " - " SELECT coin_pub FROM coin_pub " - "); ", + " JOIN exchange_do_recoup_by_reserve($1) robr" + " USING (denominations_serial);", 1), /* Used in #postgres_get_coin_transactions() to obtain recoup transactions affecting old coins of refreshed coins */ |