From 02ecf68a3d9edde9ef48650f64b7332af845beee Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sat, 9 Jan 2021 13:18:01 +0100 Subject: more optimizations of tables with foreign keys --- src/exchangedb/plugin_exchangedb_postgres.c | 85 +++++++++++++++++------------ 1 file changed, 51 insertions(+), 34 deletions(-) (limited to 'src/exchangedb/plugin_exchangedb_postgres.c') diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 6d8e72732..d8dbd2241 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -781,16 +781,22 @@ postgres_get_session (void *cls) /* Store information about the desired denominations for a refresh operation, used in #postgres_insert_refresh_reveal() */ GNUNET_PQ_make_prepare ("insert_refresh_revealed_coin", + "WITH rcx AS" + " (SELECT melt_serial_id" + " FROM refresh_commitments" + " WHERE rc=$1)" "INSERT INTO refresh_revealed_coins " - "(rc " + "(melt_serial_id " ",freshcoin_index " ",link_sig " ",denominations_serial " ",coin_ev" ",h_coin_ev" ",ev_sig" - ") SELECT $1, $2, $3, denominations_serial, $5, $6, $7 " + ") SELECT rcx.melt_serial_id, $2, $3, " + " denominations_serial, $5, $6, $7 " " FROM denominations" + " CROSS JOIN rcx" " WHERE denom_pub_hash=$4;", 7), /* Obtain information about the coins created in a refresh @@ -805,6 +811,8 @@ postgres_get_session (void *cls) " FROM refresh_revealed_coins" " JOIN denominations denom " " USING (denominations_serial)" + " JOIN refresh_commitments" + " USING (melt_serial_id)" " WHERE rc=$1" " ORDER BY freshcoin_index ASC;", 1), @@ -813,11 +821,12 @@ postgres_get_session (void *cls) keys we learned */ GNUNET_PQ_make_prepare ("insert_refresh_transfer_keys", "INSERT INTO refresh_transfer_keys " - "(rc" + "(melt_serial_id" ",transfer_pub" ",transfer_privs" - ") VALUES " - "($1, $2, $3);", + ") SELECT melt_serial_id, $2, $3" + " FROM refresh_commitments" + " WHERE rc=$1", 3), /* Used in #postgres_get_refresh_reveal() to retrieve transfer keys from /refresh/reveal */ @@ -826,23 +835,24 @@ postgres_get_session (void *cls) " transfer_pub" ",transfer_privs" " FROM refresh_transfer_keys" + " JOIN refresh_commitments" + " USING (melt_serial_id)" " WHERE rc=$1;", 1), - - /* Used in #postgres_insert_refund() to store refund information */ GNUNET_PQ_make_prepare ("insert_refund", "INSERT INTO refunds " - "(known_coin_id " - ",merchant_pub " + "(deposit_serial_id " ",merchant_sig " - ",h_contract_terms " ",rtransaction_id " ",amount_with_fee_val " ",amount_with_fee_frac " - ") SELECT known_coin_id, $2, $3, $4, $5, $6, $7" - " FROM known_coins" - " WHERE coin_pub=$1", + ") SELECT deposit_serial_id, $3, $5, $6, $7" + " FROM deposits" + " JOIN known_coins USING (known_coin_id)" + " WHERE coin_pub=$1" + " AND h_contract_terms=$4" + " AND merchant_pub=$2", 7), /* Query the 'refunds' by coin public key */ GNUNET_PQ_make_prepare ("get_refunds_by_coin", @@ -851,12 +861,13 @@ postgres_get_session (void *cls) ",merchant_sig" ",h_contract_terms" ",rtransaction_id" - ",amount_with_fee_val" - ",amount_with_fee_frac" + ",refunds.amount_with_fee_val" + ",refunds.amount_with_fee_frac" ",denom.fee_refund_val " ",denom.fee_refund_frac " ",refund_serial_id" " FROM refunds" + " JOIN deposits USING (deposit_serial_id)" " JOIN known_coins USING (known_coin_id)" " JOIN denominations denom USING (denominations_serial)" " WHERE coin_pub=$1;", @@ -864,9 +875,10 @@ postgres_get_session (void *cls) /* Query the 'refunds' by coin public key, merchant_pub and contract hash */ GNUNET_PQ_make_prepare ("get_refunds_by_coin_and_contract", "SELECT" - " amount_with_fee_val" - ",amount_with_fee_frac" + " refunds.amount_with_fee_val" + ",refunds.amount_with_fee_frac" " FROM refunds" + " JOIN deposits USING (deposit_serial_id)" " JOIN known_coins USING (known_coin_id)" " WHERE coin_pub=$1" " AND merchant_pub=$2" @@ -881,10 +893,11 @@ postgres_get_session (void *cls) ",rtransaction_id" ",denom.denom_pub" ",kc.coin_pub" - ",amount_with_fee_val" - ",amount_with_fee_frac" + ",refunds.amount_with_fee_val" + ",refunds.amount_with_fee_frac" ",refund_serial_id" " FROM refunds" + " JOIN deposits USING (deposit_serial_id)" " JOIN known_coins kc USING (known_coin_id)" " JOIN denominations denom ON (kc.denominations_serial = denom.denominations_serial)" " WHERE refund_serial_id>=$1" @@ -1086,9 +1099,9 @@ postgres_get_session (void *cls) ",rrc.link_sig" " FROM refresh_commitments" " JOIN refresh_revealed_coins rrc" - " USING (rc)" + " USING (melt_serial_id)" " JOIN refresh_transfer_keys tp" - " USING (rc)" + " USING (melt_serial_id)" " JOIN denominations denoms" " ON (rrc.denominations_serial = denoms.denominations_serial)" " WHERE old_known_coin_id=" @@ -1239,16 +1252,17 @@ postgres_get_session (void *cls) ",wire_deadline" ",tiny" ",done" - " FROM deposits" + " FROM deposits d" " JOIN known_coins USING (known_coin_id)" " WHERE wire_deadline >= $1" " AND wire_deadline < $2" " AND NOT (EXISTS (SELECT 1" " FROM refunds" - " WHERE (refunds.known_coin_id = deposits.known_coin_id))" + " JOIN deposits dx USING (deposit_serial_id)" + " WHERE (dx.known_coin_id = d.known_coin_id))" " OR EXISTS (SELECT 1" " FROM aggregation_tracking" - " WHERE (aggregation_tracking.deposit_serial_id = deposits.deposit_serial_id)))" + " WHERE (aggregation_tracking.deposit_serial_id = d.deposit_serial_id)))" " ORDER BY wire_deadline ASC", 2), /* Used in #postgres_select_wire_out_above_serial_id() */ @@ -1364,10 +1378,10 @@ postgres_get_session (void *cls) " FROM recoup_refresh" " INNER JOIN refresh_revealed_coins rrc" " USING (rrc_serial)" - " INNER JOIN refresh_commitments rc" - " ON (rrc.rc = rc.rc)" + " INNER JOIN refresh_commitments rfc" + " ON (rrc.melt_serial_id = rfc.melt_serial_id)" " INNER JOIN known_coins old_coins" - " ON (rc.old_known_coin_id = old_coins.known_coin_id)" + " ON (rfc.old_known_coin_id = old_coins.known_coin_id)" " INNER JOIN known_coins new_coins" " ON (new_coins.known_coin_id = recoup_refresh.known_coin_id)" " INNER JOIN denominations new_denoms" @@ -1442,7 +1456,7 @@ postgres_get_session (void *cls) " (SELECT rrc.rrc_serial" " FROM refresh_commitments" " JOIN refresh_revealed_coins rrc" - " USING (rc)" + " USING (melt_serial_id)" " WHERE old_known_coin_id=" " (SELECT known_coin_id" " FROM known_coins" @@ -1518,10 +1532,10 @@ postgres_get_session (void *cls) " FROM recoup_refresh" " JOIN refresh_revealed_coins rrc" " USING (rrc_serial)" - " JOIN refresh_commitments rc" - " ON (rrc.rc = rc.rc)" + " JOIN refresh_commitments rfc" + " ON (rrc.melt_serial_id = rfc.melt_serial_id)" " JOIN known_coins old_coins" - " ON (rc.old_known_coin_id = old_coins.known_coin_id)" + " ON (rfc.old_known_coin_id = old_coins.known_coin_id)" " JOIN known_coins coins" " ON (recoup_refresh.known_coin_id = coins.known_coin_id)" " JOIN denominations denoms" @@ -1543,7 +1557,7 @@ postgres_get_session (void *cls) "SELECT" " okc.coin_pub AS old_coin_pub" " FROM refresh_revealed_coins rrc" - " JOIN refresh_commitments rcom USING (rc)" + " JOIN refresh_commitments rcom USING (melt_serial_id)" " JOIN known_coins okc ON (rcom.old_known_coin_id = okc.known_coin_id)" " WHERE h_coin_ev=$1" " LIMIT 1;", @@ -2041,6 +2055,7 @@ postgres_get_session (void *cls) ",rrc_serial" ",denominations_serial" " FROM refresh_revealed_coins" + " JOIN refresh_commitments USING (melt_serial_id)" " ORDER BY rrc_serial ASC;", 0), GNUNET_PQ_make_prepare ( @@ -2051,6 +2066,7 @@ postgres_get_session (void *cls) ",transfer_pub" ",transfer_privs" " FROM refresh_transfer_keys" + " JOIN refresh_commitments USING (melt_serial_id)" " ORDER BY rtc_serial ASC;", 0), GNUNET_PQ_make_prepare ("select_above_serial_by_table_deposits", @@ -2080,10 +2096,11 @@ postgres_get_session (void *cls) ",merchant_sig" ",h_contract_terms" ",rtransaction_id" - ",amount_with_fee_val" - ",amount_with_fee_frac" + ",refunds.amount_with_fee_val" + ",refunds.amount_with_fee_frac" ",known_coin_id" " FROM refunds" + " JOIN deposits USING (deposit_serial_id)" " ORDER BY refund_serial_id ASC;", 0), GNUNET_PQ_make_prepare ("select_above_serial_by_table_wire_out", -- cgit v1.2.3