add deposits_by_coin table to speed-up certain queries (in theory)

5 files changed, 194 insertions, 117 deletions

diff --git a/src/exchangedb/drop0001.sql b/src/exchangedb/drop0001.sql
index 2b510cbae..aabfdb5e2 100644
--- a/src/exchangedb/drop0001.sql
+++ b/src/exchangedb/drop0001.sql
@@ -29,6 +29,8 @@ SELECT _v.unregister_patch('exchange-0001');
 -- Drops for exchange-0001.sql
 DROP TRIGGER IF EXISTS reserves_out_on_insert ON reserves_out;
 DROP TRIGGER IF EXISTS reserves_out_on_delete ON reserves_out;
+DROP TRIGGER IF EXISTS deposits_on_insert ON deposits;
+DROP TRIGGER IF EXISTS deposits_on_delete ON deposits;
 DROP TABLE IF EXISTS revolving_work_shards CASCADE;
 DROP TABLE IF EXISTS extensions CASCADE;
 DROP TABLE IF EXISTS auditors CASCADE;
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 8ec025cff..ba40a91ce 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -650,7 +650,7 @@ SELECT add_constraints_to_deposits_partition('default');
 COMMENT ON TABLE deposits
   IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).';
 COMMENT ON COLUMN deposits.shard
-  IS 'Used for load sharding. Should be set based on h_payto and merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
+  IS 'Used for load sharding. Should be set based on merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
 COMMENT ON COLUMN deposits.known_coin_id
   IS 'Used for garbage collection';
 COMMENT ON COLUMN deposits.wire_target_h_payto
@@ -666,11 +666,9 @@ COMMENT ON COLUMN deposits.extension_details_serial_id
 COMMENT ON COLUMN deposits.tiny
   IS 'Set to TRUE if we decided that the amount is too small to ever trigger a wire transfer by itself (requires real aggregation)';
 
--- FIXME: check if we can ALWAYS include the shard in the WHERE clauses,
--- thereby resulting in a much better use of the index: we could do (shard,deposit_serial_id)!
 CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
   ON deposits
-  (deposit_serial_id);
+  (shard,deposit_serial_id);
 CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index
   ON deposits
   (shard ASC
@@ -694,8 +692,68 @@ COMMENT ON INDEX deposits_for_iterate_matching_index
   IS 'for deposits_iterate_matching';
 
 
+CREATE TABLE IF NOT EXISTS deposits_by_coin
+  (deposit_serial_id BIGINT
+  ,shard INT8 NOT NULL
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
+  )
+  PARTITION BY HASH (coin_pub);
+COMMENT ON TABLE deposits_by_coin
+  IS 'Enables fast lookups of deposit by coin_pub, auto-populated via TRIGGER below';
+
+CREATE TABLE IF NOT EXISTS deposits_by_coin_default
+  PARTITION OF deposits_by_coin
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+CREATE OR REPLACE FUNCTION deposits_by_coin_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO deposits_by_coin
+    (deposit_serial_id
+    ,shard
+    ,coin_pub)
+  VALUES
+    (NEW.deposit_serial_id
+    ,NEW.shard
+    ,NEW.coin_pub);
+  RETURN NEW;
+END $$;  
+COMMENT ON FUNCTION deposits_by_coin_insert_trigger()
+  IS 'Replicate deposit inserts into deposits_by_coin table.';
+
+CREATE TRIGGER deposits_on_insert
+  AFTER INSERT
+   ON deposits
+   FOR EACH ROW EXECUTE FUNCTION deposits_by_coin_insert_trigger();
+
+
+CREATE OR REPLACE FUNCTION deposits_by_coin_delete_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  DELETE FROM deposits_by_coin
+   WHERE coin_pub = OLD.coin_pub
+     AND shard = OLD.shard
+     AND deposit_serial_id = OLD.deposit_serial_id;
+  RETURN OLD;
+END $$;  
+COMMENT ON FUNCTION deposits_by_coin_delete_trigger()
+  IS 'Replicate deposits deletions into deposits_by_coin table.';
+
+CREATE TRIGGER deposit_on_delete
+  AFTER DELETE
+    ON deposits
+   FOR EACH ROW EXECUTE FUNCTION deposits_by_coin_delete_trigger();
+
+
+
 CREATE TABLE IF NOT EXISTS refunds
   (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
+  ,shard INT8 NOT NULL -- REFERENCES deposits (shard) 
   ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
   ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)
   ,rtransaction_id INT8 NOT NULL
@@ -703,7 +761,7 @@ CREATE TABLE IF NOT EXISTS refunds
   ,amount_with_fee_frac INT4 NOT NULL
   -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
   )
-  PARTITION BY HASH (deposit_serial_id);
+  PARTITION BY HASH (shard);
 COMMENT ON TABLE refunds
   IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.';
 COMMENT ON COLUMN refunds.deposit_serial_id
@@ -736,6 +794,10 @@ SELECT add_constraints_to_refunds_partition('default');
 CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
   ON refunds
   (refund_serial_id);
+CREATE INDEX IF NOT EXISTS refunds_by_deposit_serial_id_index
+  ON refunds
+  (shard,deposit_serial_id);
+
 
 
 CREATE TABLE IF NOT EXISTS wire_out
@@ -959,7 +1021,7 @@ BEGIN
   RETURN NEW;
 END $$;  
 COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
-  IS 'Replicate reserve_out inserts into reserve_out_by_reserve_default table.';
+  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
 
 CREATE TRIGGER reserves_out_on_insert
   AFTER INSERT
@@ -977,7 +1039,7 @@ BEGIN
   RETURN OLD;
 END $$;  
 COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
-  IS 'Replicate reserve_out deletions into reserve_out_by_reserve_default table.';
+  IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
 
 CREATE TRIGGER reserves_out_on_delete
   AFTER DELETE
@@ -1494,7 +1556,7 @@ DECLARE
 BEGIN
 -- Shards: INSERT extension_details (by extension_details_serial_id)
 --         INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
---         INSERT deposits (by shard + coin_pub, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING;
+--         INSERT deposits (by shard + merchant_pub + h_payto), ON CONFLICT DO NOTHING;
 --         UPDATE known_coins (by coin_pub)
 
 IF NOT NULL in_extension_details
@@ -1569,19 +1631,18 @@ THEN
   -- Idempotency check: see if an identical record exists.
   -- Note that by checking 'coin_sig', we implicitly check
   -- identity over everything that the signature covers.
-  -- We do select over merchant_pub and h_contract_terms
+  -- We do select over merchant_pub and wire_target_h_payto
   -- primarily here to maximally use the existing index.
   SELECT
      exchange_timestamp
    INTO
      out_exchange_timestamp
    FROM deposits
-   WHERE
-     shard=in_shard AND
-     coin_pub=in_coin_pub AND
-     merchant_pub=in_merchant_pub AND
-     h_contract_terms=in_h_contract_terms AND
-     coin_sig=in_coin_sig;
+   WHERE shard=in_shard
+     AND merchant_pub=in_merchant_pub
+     AND wire_target_h_payto=in_h_payto
+     AND coin_pub=in_coin_pub
+     AND coin_sig=in_coin_sig;
 
   IF NOT FOUND
   THEN
@@ -1846,20 +1907,21 @@ BEGIN
 --         UPDATE known_coins (by coin_pub)
 
 SELECT
-   deposit_serial_id
-  ,amount_with_fee_val
-  ,amount_with_fee_frac
-  ,done
+   dep.deposit_serial_id
+  ,dep.amount_with_fee_val
+  ,dep.amount_with_fee_frac
+  ,dep.done
 INTO
    dsi
   ,deposit_val
   ,deposit_frac
   ,out_gone
-FROM deposits
-WHERE shard=in_deposit_shard
-  AND coin_pub=in_coin_pub
-  AND h_contract_terms=in_h_contract_terms
-  AND merchant_pub=in_merchant_pub;
+FROM deposits_by_coin dbc
+  JOIN deposits dep USING (shard,deposit_serial_id)
+ WHERE dbc.coin_pub=in_coin_pub
+  AND dep.shard=in_deposit_shard
+  AND dep.merchant_pub=in_merchant_pub
+  AND dep.h_contract_terms=in_h_contract_terms;
 
 IF NOT FOUND
 THEN
@@ -1874,6 +1936,7 @@ END IF;
 
 INSERT INTO refunds
   (deposit_serial_id
+  ,shard
   ,merchant_sig
   ,rtransaction_id
   ,amount_with_fee_val
@@ -1881,6 +1944,7 @@ INSERT INTO refunds
   )
   VALUES
   (dsi
+  ,in_deposit_shard
   ,in_merchant_sig
   ,in_rtransaction_id
   ,in_amount_with_fee_val
@@ -1896,11 +1960,11 @@ THEN
   -- primarily here to maximally use the existing index.
    PERFORM
    FROM refunds
-   WHERE
-     deposit_serial_id=dsi AND
-     rtransaction_id=in_rtransaction_id AND
-     amount_with_fee_val=in_amount_with_fee_val AND
-     amount_with_fee_frac=in_amount_with_fee_frac;
+   WHERE shard=in_deposit_shard
+     AND deposit_serial_id=dsi
+     AND rtransaction_id=in_rtransaction_id
+     AND amount_with_fee_val=in_amount_with_fee_val
+     AND amount_with_fee_frac=in_amount_with_fee_frac;
 
   IF NOT FOUND
   THEN
@@ -1939,8 +2003,8 @@ SELECT
    tmp_val
   ,tmp_frac
   FROM refunds
-  WHERE
-    deposit_serial_id=dsi;
+  WHERE shard=in_deposit_shard
+    AND deposit_serial_id=dsi;
 IF tmp_val IS NULL
 THEN
   RAISE NOTICE 'failed to sum up existing refunds';
diff --git a/src/exchangedb/irbt_callbacks.c b/src/exchangedb/irbt_callbacks.c
index e0220a493..fd6e49b1d 100644
--- a/src/exchangedb/irbt_callbacks.c
+++ b/src/exchangedb/irbt_callbacks.c
@@ -554,6 +554,7 @@ irbt_cb_table_refunds (struct PostgresClosure *pg,
 {
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_uint64 (&td->serial),
+    GNUNET_PQ_query_param_uint64 (&td->details.refunds.shard),
     GNUNET_PQ_query_param_auto_from_type (&td->details.refunds.merchant_sig),
     GNUNET_PQ_query_param_uint64 (&td->details.refunds.rtransaction_id),
     TALER_PQ_query_param_amount (&td->details.refunds.amount_with_fee),
diff --git a/src/exchangedb/lrbt_callbacks.c b/src/exchangedb/lrbt_callbacks.c
index a5b30c765..011b6a3e9 100644
--- a/src/exchangedb/lrbt_callbacks.c
+++ b/src/exchangedb/lrbt_callbacks.c
@@ -1010,6 +1010,9 @@ lrbt_cb_table_refunds (void *cls,
       GNUNET_PQ_result_spec_uint64 (
         "serial",
         &td.serial),
+      GNUNET_PQ_result_spec_uint64 (
+        "shard",
+        &td.details.refunds.shard),
       GNUNET_PQ_result_spec_auto_from_type (
         "merchant_sig",
         &td.details.refunds.merchant_sig),
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 2fd8b21c5..6912e55e0 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -979,43 +979,45 @@ prepare_statements (struct PostgresClosure *pg)
       ",rtransaction_id "
       ",amount_with_fee_val "
       ",amount_with_fee_frac "
-      ") SELECT deposit_serial_id, $3, $5, $6, $7"
-      "    FROM deposits" // FIXME: also select by shard!
-      "   WHERE coin_pub=$1"
-      "     AND h_contract_terms=$4"
-      "     AND merchant_pub=$2",
+      ") SELECT dbc.deposit_serial_id, $3, $5, $6, $7"
+      "    FROM deposits_by_coin dbc"
+      "    JOIN deposits dep USING (shard,deposit_serial_id)"
+      "   WHERE dbc.coin_pub=$1"
+      "     AND dep.h_contract_terms=$4"
+      "     AND dep.merchant_pub=$2",
       7),
     /* Query the 'refunds' by coin public key */
     GNUNET_PQ_make_prepare (
       "get_refunds_by_coin",
       "SELECT"
-      " merchant_pub"
-      ",merchant_sig"
-      ",h_contract_terms"
-      ",rtransaction_id"
-      ",refunds.amount_with_fee_val"
-      ",refunds.amount_with_fee_frac"
+      " dep.merchant_pub"
+      ",ref.merchant_sig"
+      ",dep.h_contract_terms"
+      ",ref.rtransaction_id"
+      ",ref.amount_with_fee_val"
+      ",ref.amount_with_fee_frac"
       ",denom.fee_refund_val "
       ",denom.fee_refund_frac "
-      ",refund_serial_id"
-      " FROM refunds"
-      " JOIN deposits USING (deposit_serial_id)"  // FIXME: use shard, too!
-      " JOIN known_coins USING (coin_pub)"
+      ",ref.refund_serial_id"
+      " FROM deposits_by_coin dbc"
+      " JOIN refunds ref USING (deposit_serial_id)"
+      " JOIN deposits dep ON (dbc.shard = dep.shard AND dbc.deposit_serial_id = dep.deposit_serial_id)"
+      " JOIN known_coins kc ON (dbc.coin_pub = kc.coin_pub)"
       " JOIN denominations denom USING (denominations_serial)"
-      " WHERE coin_pub=$1;",
+      " WHERE dbc.coin_pub=$1;",
       1),
     /* Query the 'refunds' by coin public key, merchant_pub and contract hash */
     GNUNET_PQ_make_prepare (
       "get_refunds_by_coin_and_contract",
       "SELECT"
-      " refunds.amount_with_fee_val"
-      ",refunds.amount_with_fee_frac"
-      " FROM refunds"
-      " JOIN deposits USING (deposit_serial_id)" // FIXME: use shard!
-      " JOIN known_coins USING (coin_pub)"
-      " WHERE coin_pub=$1"
-      "   AND merchant_pub=$2"
-      "   AND h_contract_terms=$3;",
+      " ref.amount_with_fee_val"
+      ",ref.amount_with_fee_frac"
+      " FROM deposits_by_coin dbc"
+      " JOIN refunds ref USING (shard,deposit_serial_id)"
+      " JOIN deposits dep ON (dbc.shard = dep.shard AND dbc.deposit_serial_id = dep.deposit_serial_id)"
+      " WHERE dbc.coin_pub=$1"
+      "   AND dep.merchant_pub=$2"
+      "   AND dep.h_contract_terms=$3;",
       3),
     /* Fetch refunds with rowid '\geq' the given parameter */
     GNUNET_PQ_make_prepare (
@@ -1031,7 +1033,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",refunds.amount_with_fee_frac"
       ",refund_serial_id"
       " FROM refunds"
-      "   JOIN deposits USING (deposit_serial_id)" // FIXME: use shard!
+      "   JOIN deposits USING (shard, deposit_serial_id)"
       "   JOIN known_coins kc USING (coin_pub)"
       "   JOIN denominations denom ON (kc.denominations_serial = denom.denominations_serial)"
       " WHERE refund_serial_id>=$1"
@@ -1074,24 +1076,25 @@ prepare_statements (struct PostgresClosure *pg)
     GNUNET_PQ_make_prepare (
       "get_deposit",
       "SELECT"
-      " amount_with_fee_val"
-      ",amount_with_fee_frac"
+      " dep.amount_with_fee_val"
+      ",dep.amount_with_fee_frac"
       ",denominations.fee_deposit_val"
       ",denominations.fee_deposit_frac"
-      ",wallet_timestamp"
-      ",exchange_timestamp"
-      ",refund_deadline"
-      ",wire_deadline"
-      ",h_contract_terms"
-      ",wire_salt"
-      ",payto_uri AS receiver_wire_account"
-      " FROM deposits" // FIXME: also select on shard!?
-      " JOIN known_coins USING (coin_pub)"
+      ",dep.wallet_timestamp"
+      ",dep.exchange_timestamp"
+      ",dep.refund_deadline"
+      ",dep.wire_deadline"
+      ",dep.h_contract_terms"
+      ",dep.wire_salt"
+      ",wt.payto_uri AS receiver_wire_account"
+      " FROM deposits_by_coin dbc"
+      " JOIN deposits dep USING (shard,deposit_serial_id)"
+      " JOIN known_coins kc ON (kc.coin_pub = dbc.coin_pub)"
       " JOIN denominations USING (denominations_serial)"
-      " JOIN wire_targets USING (wire_target_h_payto)"
-      " WHERE ((coin_pub=$1)"
-      "    AND (merchant_pub=$3)"
-      "    AND (h_contract_terms=$2));",
+      " JOIN wire_targets wt USING (wire_target_h_payto)"
+      " WHERE dbc.coin_pub=$1"
+      "   AND dep.merchant_pub=$3"
+      "   AND dep.h_contract_terms=$2;",
       3),
     /* Fetch deposits with rowid '\geq' the given parameter */
     GNUNET_PQ_make_prepare (
@@ -1127,23 +1130,23 @@ prepare_statements (struct PostgresClosure *pg)
     GNUNET_PQ_make_prepare (
       "get_deposit_without_wtid",
       "SELECT"
-      " kyc_ok"
-      ",wire_target_serial_id AS payment_target_uuid"
-      ",wire_salt"
-      ",payto_uri"
-      ",amount_with_fee_val"
-      ",amount_with_fee_frac"
+      " wt.kyc_ok"
+      ",wt.wire_target_serial_id AS payment_target_uuid"
+      ",dep.wire_salt"
+      ",wt.payto_uri"
+      ",dep.amount_with_fee_val"
+      ",dep.amount_with_fee_frac"
       ",denom.fee_deposit_val"
       ",denom.fee_deposit_frac"
-      ",wire_deadline"
-      " FROM deposits"
-      "    JOIN wire_targets USING (wire_target_h_payto)"
-      "    JOIN known_coins USING (coin_pub)"
+      ",dep.wire_deadline"
+      " FROM deposits_by_coin dbc"
+      "    JOIN deposits dep USING (shard,deposit_serial_id)"
+      "    JOIN wire_targets wt USING (wire_target_h_payto)"
+      "    JOIN known_coins kc ON (kc.coin_pub = dbc.coin_pub)"
       "    JOIN denominations denom USING (denominations_serial)"
-      " WHERE ((coin_pub=$1)" // FIXME: also select by shard!
-      "    AND (merchant_pub=$3)"
-      "    AND (h_contract_terms=$2)"
-      " );",
+      " WHERE dbc.coin_pub=$1"
+      "   AND dep.merchant_pub=$3"
+      "   AND dep.h_contract_terms=$2;",
       3),
     /* Used in #postgres_get_ready_deposit() */
     GNUNET_PQ_make_prepare (
@@ -1222,34 +1225,35 @@ prepare_statements (struct PostgresClosure *pg)
       2),
     /* Used in #postgres_get_coin_transactions() to obtain information
        about how a coin has been spend with /deposit requests. */
-    // FIXME: this one is horribly inefficient right now!
     GNUNET_PQ_make_prepare (
       "get_deposit_with_coin_pub",
       "SELECT"
-      " amount_with_fee_val"
-      ",amount_with_fee_frac"
+      " dep.amount_with_fee_val"
+      ",dep.amount_with_fee_frac"
       ",denoms.fee_deposit_val"
       ",denoms.fee_deposit_frac"
       ",denoms.denom_pub_hash"
       ",kc.age_commitment_hash"
-      ",wallet_timestamp"
-      ",refund_deadline"
-      ",wire_deadline"
-      ",merchant_pub"
-      ",h_contract_terms"
-      ",wire_salt"
-      ",payto_uri"
-      ",coin_sig"
-      ",deposit_serial_id"
-      ",done"
-      " FROM deposits"
-      "    JOIN wire_targets"
+      ",dep.wallet_timestamp"
+      ",dep.refund_deadline"
+      ",dep.wire_deadline"
+      ",dep.merchant_pub"
+      ",dep.h_contract_terms"
+      ",dep.wire_salt"
+      ",wt.payto_uri"
+      ",dep.coin_sig"
+      ",dep.deposit_serial_id"
+      ",dep.done"
+      " FROM deposits_by_coin dbc"
+      "    JOIN deposits dep"
+      "      USING (shard,deposit_serial_id)"
+      "    JOIN wire_targets wt"
       "      USING (wire_target_h_payto)"
       "    JOIN known_coins kc"
-      "      USING (coin_pub)"
+      "      ON (kc.coin_pub = dbc.coin_pub)"
       "    JOIN denominations denoms"
       "      USING (denominations_serial)"
-      " WHERE coin_pub=$1;",
+      " WHERE dbc.coin_pub=$1;",
       1),
 
     /* Used in #postgres_get_link_data(). */
@@ -1303,32 +1307,33 @@ prepare_statements (struct PostgresClosure *pg)
       " WHERE wtid_raw=$1;",
       1),
     /* Used in #postgres_lookup_transfer_by_deposit */
-    // FIXME: select by shard?
     GNUNET_PQ_make_prepare (
       "lookup_deposit_wtid",
       "SELECT"
       " aggregation_tracking.wtid_raw"
       ",wire_out.execution_date"
-      ",amount_with_fee_val"
-      ",amount_with_fee_frac"
-      ",wire_salt"
-      ",payto_uri"
+      ",dep.amount_with_fee_val"
+      ",dep.amount_with_fee_frac"
+      ",dep.wire_salt"
+      ",wt.payto_uri"
       ",denom.fee_deposit_val"
       ",denom.fee_deposit_frac"
-      " FROM deposits"
-      "    JOIN wire_targets"
+      " FROM deposits_by_coin dbc"
+      "    JOIN deposits dep"
+      "      USING (shard,deposit_serial_id)"
+      "    JOIN wire_targets wt"
       "      USING (wire_target_h_payto)"
       "    JOIN aggregation_tracking"
       "      USING (deposit_serial_id)"
-      "    JOIN known_coins"
-      "      USING (coin_pub)"
+      "    JOIN known_coins kc"
+      "      ON (kc.coin_pub = dbc.coin_pub)"
       "    JOIN denominations denom"
       "      USING (denominations_serial)"
       "    JOIN wire_out"
       "      USING (wtid_raw)"
-      " WHERE coin_pub=$1"
-      "  AND merchant_pub=$3"
-      "  AND h_contract_terms=$2",
+      " WHERE dbc.coin_pub=$1"
+      "   AND dep.merchant_pub=$3"
+      "   AND dep.h_contract_terms=$2",
       3),
     /* Used in #postgres_insert_aggregation_tracking */
     GNUNET_PQ_make_prepare (
@@ -2468,6 +2473,7 @@ prepare_statements (struct PostgresClosure *pg)
       "select_above_serial_by_table_refunds",
       "SELECT"
       " refund_serial_id AS serial"
+      ",shard"
       ",merchant_sig"
       ",rtransaction_id"
       ",amount_with_fee_val"
@@ -2799,15 +2805,16 @@ prepare_statements (struct PostgresClosure *pg)
     GNUNET_PQ_make_prepare (
       "insert_into_table_refunds",
       "INSERT INTO refunds"
-      "(refund_serial_id"
+      "(shard"
+      ",refund_serial_id"
       ",merchant_sig"
       ",rtransaction_id"
       ",amount_with_fee_val"
       ",amount_with_fee_frac"
       ",deposit_serial_id"
       ") VALUES "
-      "($1, $2, $3, $4, $5, $6);",
-      6),
+      "($1, $2, $3, $4, $5, $6, $7);",
+      7),
     GNUNET_PQ_make_prepare (
       "insert_into_table_aggregation_tracking",
       "INSERT INTO aggregation_tracking"