change table structures again to shard better by coin_pub and not use known_coin_id so much

4 files changed, 101 insertions, 75 deletions

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index a634cbf10..d2e5f3350 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -422,7 +422,7 @@ AS $$
 BEGIN
   EXECUTE FORMAT (
     'ALTER TABLE known_coins_' || partition_suffix || ' '
-      'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key '
+      'ADD CONSTRAINT known_coins_' || partition_suffix || 'k_nown_coin_id_key '
         'UNIQUE (known_coin_id)'
   );
 END
@@ -604,7 +604,8 @@ COMMENT ON COLUMN extension_details.extension_options
 CREATE TABLE IF NOT EXISTS deposits
   (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
   ,shard INT8 NOT NULL
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE
+  ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
   ,amount_with_fee_val INT8 NOT NULL
   ,amount_with_fee_frac INT4 NOT NULL
   ,wallet_timestamp INT8 NOT NULL
@@ -620,7 +621,7 @@ CREATE TABLE IF NOT EXISTS deposits
   ,done BOOLEAN NOT NULL DEFAULT FALSE
   ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE
   ,extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE
-  ,UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)
+  ,UNIQUE (shard, coin_pub, merchant_pub, h_contract_terms)
   )
   PARTITION BY HASH (shard);
 
@@ -649,6 +650,8 @@ 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.';
+COMMENT ON COLUMN deposits.known_coin_id
+  IS 'Used for garbage collection';
 COMMENT ON COLUMN deposits.wire_target_h_payto
   IS 'Identifies the target bank account and KYC status';
 COMMENT ON COLUMN deposits.wire_salt
@@ -704,7 +707,7 @@ CREATE TABLE IF NOT EXISTS refunds
 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
-  IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id. Multiple deposits may match a refund, this only identifies one of them.';
+  IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.';
 COMMENT ON COLUMN refunds.rtransaction_id
   IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund';
 
@@ -878,7 +881,7 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
 
 CREATE TABLE IF NOT EXISTS recoup
   (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
   ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
   ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
   ,amount_val INT8 NOT NULL
@@ -886,10 +889,10 @@ CREATE TABLE IF NOT EXISTS recoup
   ,recoup_timestamp INT8 NOT NULL
   ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE
   )
-  PARTITION BY HASH (known_coin_id);
+  PARTITION BY HASH (coin_pub);
 COMMENT ON TABLE recoup
   IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.';
-COMMENT ON COLUMN recoup.known_coin_id
+COMMENT ON COLUMN recoup.coin_pub
   IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
 COMMENT ON COLUMN recoup.reserve_out_serial_id
   IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.';
@@ -925,9 +928,9 @@ CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
 CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
   ON recoup
   (reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
+CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index
   ON recoup
-  (known_coin_id);
+  (coin_pub);
 
 
 CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
@@ -984,7 +987,8 @@ CREATE TRIGGER reserves_out_on_delete
 
 CREATE TABLE IF NOT EXISTS recoup_refresh
   (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
+  ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
   ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
   ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
   ,amount_val INT8 NOT NULL
@@ -992,11 +996,13 @@ CREATE TABLE IF NOT EXISTS recoup_refresh
   ,recoup_timestamp INT8 NOT NULL
   ,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE
   )
-  PARTITION BY HASH (known_coin_id);
+  PARTITION BY HASH (coin_pub);
 COMMENT ON TABLE recoup_refresh
   IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.';
+COMMENT ON COLUMN recoup_refresh.coin_pub
+  IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
 COMMENT ON COLUMN recoup_refresh.known_coin_id
-  IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the known_coin_id, as we may keep the coin alive!';
+  IS 'FIXME: (To be) used for garbage collection (in the future)';
 COMMENT ON COLUMN recoup_refresh.rrc_serial
   IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).';
 COMMENT ON COLUMN recoup_refresh.coin_blind
@@ -1029,9 +1035,9 @@ CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
 CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
   ON recoup_refresh
   (rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
   ON recoup_refresh
-  (known_coin_id);
+  (coin_pub);
 
 
 CREATE TABLE IF NOT EXISTS prewire
@@ -1488,7 +1494,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 + known_coin_id, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING;
+--         INSERT deposits (by shard + coin_pub, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING;
 --         UPDATE known_coins (by coin_pub)
 
 IF NOT NULL in_extension_details
@@ -1523,6 +1529,7 @@ END IF;
 
 INSERT INTO deposits
   (shard
+  ,coin_pub
   ,known_coin_id
   ,amount_with_fee_val
   ,amount_with_fee_frac
@@ -1540,6 +1547,7 @@ INSERT INTO deposits
   )
   VALUES
   (in_shard
+  ,in_coin_pub
   ,in_known_coin_id
   ,in_amount_with_fee_val
   ,in_amount_with_fee_frac
@@ -1570,7 +1578,7 @@ THEN
    FROM deposits
    WHERE
      shard=in_shard AND
-     known_coin_id=in_known_coin_id 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;
@@ -1832,7 +1840,7 @@ DECLARE
 DECLARE
   deposit_frac INT8; -- amount that was originally deposited
 BEGIN
--- Shards: SELECT deposits (by shard, known_coin_id,h_contract_terms, merchant_pub)
+-- Shards: SELECT deposits (by shard, coin_pub, h_contract_terms, merchant_pub)
 --         INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT DO NOTHING
 --         SELECT refunds (by deposit_serial_id)
 --         UPDATE known_coins (by coin_pub)
@@ -1849,7 +1857,7 @@ INTO
   ,out_gone
 FROM deposits
 WHERE shard=in_deposit_shard
-  AND known_coin_id=in_known_coin_id
+  AND coin_pub=in_coin_pub
   AND h_contract_terms=in_h_contract_terms
   AND merchant_pub=in_merchant_pub;
 
@@ -2019,10 +2027,10 @@ DECLARE
   tmp_frac INT8; -- amount recouped
 BEGIN
 -- Shards: SELECT known_coins (by coin_pub)
---         SELECT recoup (by known_coin_id)
+--         SELECT recoup      (by coin_pub)
 --         UPDATE known_coins (by coin_pub)
 --         UPDATE reserves (by reserve_pub)
---         INSERT recoup (by known_coin_id)
+--         INSERT recoup      (by coin_pub)
 
 out_internal_failure=FALSE;
 
@@ -2052,7 +2060,7 @@ THEN
   INTO
     out_recoup_timestamp
     FROM recoup
-    WHERE known_coin_id=in_known_coin_id;
+    WHERE coin_pub=in_coin_pub;
 
   out_recoup_ok=FOUND;
   RETURN;
@@ -2097,7 +2105,7 @@ END IF;
 
 
 INSERT INTO recoup
-  (known_coin_id
+  (coin_pub
   ,coin_sig
   ,coin_blind
   ,amount_val
@@ -2106,7 +2114,7 @@ INSERT INTO recoup
   ,reserve_out_serial_id
   )
 VALUES
-  (in_known_coin_id
+  (in_coin_pub
   ,in_coin_sig
   ,in_coin_blind
   ,tmp_val
@@ -2148,9 +2156,9 @@ DECLARE
 BEGIN
 
 -- Shards: UPDATE known_coins (by coin_pub)
---         SELECT recoup_refresh (by known_coin_id)
+--         SELECT recoup_refresh (by coin_pub)
 --         UPDATE known_coins (by coin_pub)
---         INSERT recoup_refresh (by known_coin_id)
+--         INSERT recoup_refresh (by coin_pub)
 
 
 out_internal_failure=FALSE;
@@ -2181,7 +2189,7 @@ THEN
     INTO
       out_recoup_timestamp
     FROM recoup_refresh
-    WHERE known_coin_id=in_known_coin_id;
+    WHERE coin_pub=in_coin_pub;
   out_recoup_ok=FOUND;
   RETURN;
 END IF;
@@ -2222,7 +2230,8 @@ END IF;
 
 
 INSERT INTO recoup_refresh
-  (known_coin_id
+  (coin_pub
+  ,known_coin_id
   ,coin_sig
   ,coin_blind
   ,amount_val
@@ -2231,7 +2240,8 @@ INSERT INTO recoup_refresh
   ,rrc_serial
   )
 VALUES
-  (in_known_coin_id
+  (in_coin_pub
+  ,in_known_coin_id
   ,in_coin_sig
   ,in_coin_blind
   ,tmp_val
@@ -2292,7 +2302,7 @@ SELECT
 
 DELETE FROM recoup
   WHERE reserve_out_serial_id < reserve_out_min;
-
+-- FIXME: recoup_refresh lacks GC!
 
 SELECT
      reserve_uuid
@@ -2305,7 +2315,8 @@ SELECT
 DELETE FROM reserves_out
   WHERE reserve_uuid < reserve_uuid_min;
 
-
+-- FIXME: this query will be horribly slow;
+-- need to find another way to formulate it...
 DELETE FROM denominations
   WHERE expire_legal < in_now
     AND denominations_serial NOT IN
@@ -2314,14 +2325,14 @@ DELETE FROM denominations
     AND denominations_serial NOT IN
       (SELECT DISTINCT denominations_serial
          FROM known_coins
-        WHERE known_coin_id IN
-          (SELECT DISTINCT known_coin_id
+        WHERE coin_pub IN
+          (SELECT DISTINCT coin_pub
              FROM recoup))
     AND denominations_serial NOT IN
       (SELECT DISTINCT denominations_serial
          FROM known_coins
-        WHERE known_coin_id IN
-          (SELECT DISTINCT known_coin_id
+        WHERE coin_pub IN
+          (SELECT DISTINCT coin_pub
              FROM recoup_refresh));
 
 SELECT
diff --git a/src/exchangedb/irbt_callbacks.c b/src/exchangedb/irbt_callbacks.c
index 50b69a729..e0220a493 100644
--- a/src/exchangedb/irbt_callbacks.c
+++ b/src/exchangedb/irbt_callbacks.c
@@ -511,6 +511,8 @@ irbt_cb_table_deposits (struct PostgresClosure *pg,
     GNUNET_PQ_query_param_uint64 (&td->serial),
     GNUNET_PQ_query_param_uint64 (&td->details.deposits.shard),
     GNUNET_PQ_query_param_uint64 (&td->details.deposits.known_coin_id),
+    GNUNET_PQ_query_param_auto_from_type (
+      &td->details.deposits.coin_pub),
     TALER_PQ_query_param_amount (&td->details.deposits.amount_with_fee),
     GNUNET_PQ_query_param_timestamp (&td->details.deposits.wallet_timestamp),
     GNUNET_PQ_query_param_timestamp (
@@ -706,7 +708,8 @@ irbt_cb_table_recoup (struct PostgresClosure *pg,
     GNUNET_PQ_query_param_auto_from_type (&td->details.recoup.coin_blind),
     TALER_PQ_query_param_amount (&td->details.recoup.amount),
     GNUNET_PQ_query_param_timestamp (&td->details.recoup.timestamp),
-    GNUNET_PQ_query_param_uint64 (&td->details.recoup.known_coin_id),
+    GNUNET_PQ_query_param_auto_from_type (
+      &td->details.recoup.coin_pub),
     GNUNET_PQ_query_param_uint64 (&td->details.recoup.reserve_out_serial_id),
     GNUNET_PQ_query_param_end
   };
@@ -735,6 +738,8 @@ irbt_cb_table_recoup_refresh (struct PostgresClosure *pg,
     TALER_PQ_query_param_amount (&td->details.recoup_refresh.amount),
     GNUNET_PQ_query_param_timestamp (&td->details.recoup_refresh.timestamp),
     GNUNET_PQ_query_param_uint64 (&td->details.recoup_refresh.known_coin_id),
+    GNUNET_PQ_query_param_auto_from_type (
+      &td->details.recoup.coin_pub),
     GNUNET_PQ_query_param_uint64 (&td->details.recoup_refresh.rrc_serial),
     GNUNET_PQ_query_param_end
   };
diff --git a/src/exchangedb/lrbt_callbacks.c b/src/exchangedb/lrbt_callbacks.c
index 9e9f37780..a5b30c765 100644
--- a/src/exchangedb/lrbt_callbacks.c
+++ b/src/exchangedb/lrbt_callbacks.c
@@ -920,6 +920,9 @@ lrbt_cb_table_deposits (void *cls,
       GNUNET_PQ_result_spec_uint64 (
         "known_coin_id",
         &td.details.deposits.known_coin_id),
+      GNUNET_PQ_result_spec_auto_from_type (
+        "coin_pub",
+        &td.details.deposits.coin_pub),
       TALER_PQ_RESULT_SPEC_AMOUNT (
         "amount_with_fee",
         &td.details.deposits.amount_with_fee),
@@ -1306,8 +1309,9 @@ lrbt_cb_table_recoup (void *cls,
                                    &td.details.recoup.amount),
       GNUNET_PQ_result_spec_timestamp ("recoup_timestamp",
                                        &td.details.recoup.timestamp),
-      GNUNET_PQ_result_spec_uint64 ("known_coin_id",
-                                    &td.details.recoup.known_coin_id),
+      GNUNET_PQ_result_spec_auto_from_type (
+        "coin_pub",
+        &td.details.recoup.coin_pub),
       GNUNET_PQ_result_spec_uint64 ("reserve_out_serial_id",
                                     &td.details.recoup.reserve_out_serial_id),
       GNUNET_PQ_result_spec_end
@@ -1363,6 +1367,9 @@ lrbt_cb_table_recoup_refresh (void *cls,
                                        &td.details.recoup_refresh.timestamp),
       GNUNET_PQ_result_spec_uint64 ("known_coin_id",
                                     &td.details.recoup_refresh.known_coin_id),
+      GNUNET_PQ_result_spec_auto_from_type (
+        "coin_pub",
+        &td.details.recoup_refresh.coin_pub),
       GNUNET_PQ_result_spec_uint64 ("rrc_serial",
                                     &td.details.recoup_refresh.rrc_serial),
       GNUNET_PQ_result_spec_end
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 0346c505b..4338aef3a 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -980,11 +980,8 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_with_fee_val "
       ",amount_with_fee_frac "
       ") SELECT deposit_serial_id, $3, $5, $6, $7"
-      "    FROM deposits"
-      "   WHERE known_coin_id="
-      "     (SELECT known_coin_id "
-      "        FROM known_coins"
-      "       WHERE coin_pub=$1)"
+      "    FROM deposits" // FIXME: also select by shard!
+      "   WHERE coin_pub=$1"
       "     AND h_contract_terms=$4"
       "     AND merchant_pub=$2",
       7),
@@ -1002,8 +999,8 @@ prepare_statements (struct PostgresClosure *pg)
       ",denom.fee_refund_frac "
       ",refund_serial_id"
       " FROM refunds"
-      " JOIN deposits USING (deposit_serial_id)"
-      " JOIN known_coins USING (known_coin_id)"
+      " JOIN deposits USING (deposit_serial_id)"  // FIXME: use shard, too!
+      " JOIN known_coins USING (coin_pub)"
       " JOIN denominations denom USING (denominations_serial)"
       " WHERE coin_pub=$1;",
       1),
@@ -1014,8 +1011,8 @@ prepare_statements (struct PostgresClosure *pg)
       " 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)"
+      " 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;",
@@ -1034,8 +1031,8 @@ prepare_statements (struct PostgresClosure *pg)
       ",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 deposits USING (deposit_serial_id)" // FIXME: use shard!
+      "   JOIN known_coins kc USING (coin_pub)"
       "   JOIN denominations denom ON (kc.denominations_serial = denom.denominations_serial)"
       " WHERE refund_serial_id>=$1"
       " ORDER BY refund_serial_id ASC;",
@@ -1053,6 +1050,7 @@ prepare_statements (struct PostgresClosure *pg)
       "insert_deposit",
       "INSERT INTO deposits "
       "(known_coin_id"
+      ",coin_pub"
       ",amount_with_fee_val"
       ",amount_with_fee_frac"
       ",wallet_timestamp"
@@ -1065,10 +1063,10 @@ prepare_statements (struct PostgresClosure *pg)
       ",coin_sig"
       ",exchange_timestamp"
       ",shard"
-      ") SELECT known_coin_id, $2, $3, $4, $5, $6, "
+      ") SELECT known_coin_id, $1, $2, $3, $4, $5, $6, "
       " $7, $8, $9, $10, $11, $12, $13"
       "    FROM known_coins"
-      "   WHERE coin_pub=$1"
+      "   WHERE coin_pub=$1" // FIXME: maybe we know known_coin_id already in caller?
       " ON CONFLICT DO NOTHING;",
       13),
     /* Fetch an existing deposit request, used to ensure idempotency
@@ -1087,8 +1085,8 @@ prepare_statements (struct PostgresClosure *pg)
       ",h_contract_terms"
       ",wire_salt"
       ",payto_uri AS receiver_wire_account"
-      " FROM deposits"
-      " JOIN known_coins USING (known_coin_id)"
+      " FROM deposits" // FIXME: also select on shard!?
+      " JOIN known_coins USING (coin_pub)"
       " JOIN denominations USING (denominations_serial)"
       " JOIN wire_targets USING (wire_target_h_payto)"
       " WHERE ((coin_pub=$1)"
@@ -1117,10 +1115,10 @@ prepare_statements (struct PostgresClosure *pg)
       ",deposit_serial_id"
       " FROM deposits"
       "    JOIN wire_targets USING (wire_target_h_payto)"
-      "    JOIN known_coins kc USING (known_coin_id)"
+      "    JOIN known_coins kc USING (coin_pub)"
       "    JOIN denominations denom USING (denominations_serial)"
       " WHERE ("
-      "  (deposit_serial_id>=$1)"
+      "  (deposit_serial_id>=$1)" // FIXME: also select by shard!?
       " )"
       " ORDER BY deposit_serial_id ASC;",
       1),
@@ -1140,9 +1138,9 @@ prepare_statements (struct PostgresClosure *pg)
       ",wire_deadline"
       " FROM deposits"
       "    JOIN wire_targets USING (wire_target_h_payto)"
-      "    JOIN known_coins USING (known_coin_id)"
+      "    JOIN known_coins USING (coin_pub)"
       "    JOIN denominations denom USING (denominations_serial)"
-      " WHERE ((coin_pub=$1)"
+      " WHERE ((coin_pub=$1)" // FIXME: also select by shard!
       "    AND (merchant_pub=$3)"
       "    AND (h_contract_terms=$2)"
       " );",
@@ -1165,7 +1163,7 @@ prepare_statements (struct PostgresClosure *pg)
       "  JOIN wire_targets "
       "    USING (wire_target_h_payto)"
       "  JOIN known_coins kc"
-      "    USING (known_coin_id)"
+      "    USING (coin_pub)"
       "  JOIN denominations denom"
       "    USING (denominations_serial)"
       " WHERE "
@@ -1193,7 +1191,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",h_contract_terms"
       ",kc.coin_pub"
       " FROM deposits"
-      "    JOIN known_coins kc USING (known_coin_id)"
+      "    JOIN known_coins kc USING (coin_pub)"
       "    JOIN denominations denom USING (denominations_serial)"
       " WHERE"
       "      merchant_pub=$1"
@@ -1224,6 +1222,7 @@ 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"
@@ -1247,7 +1246,7 @@ prepare_statements (struct PostgresClosure *pg)
       "    JOIN wire_targets"
       "      USING (wire_target_h_payto)"
       "    JOIN known_coins kc"
-      "      USING (known_coin_id)"
+      "      USING (coin_pub)"
       "    JOIN denominations denoms"
       "      USING (denominations_serial)"
       " WHERE coin_pub=$1;",
@@ -1296,7 +1295,7 @@ prepare_statements (struct PostgresClosure *pg)
       "    JOIN wire_targets"
       "      USING (wire_target_h_payto)"
       "    JOIN known_coins kc"
-      "      USING (known_coin_id)"
+      "      USING (coin_pub)"
       "    JOIN denominations denom"
       "      USING (denominations_serial)"
       "    JOIN wire_out"
@@ -1321,7 +1320,7 @@ prepare_statements (struct PostgresClosure *pg)
       "    JOIN aggregation_tracking"
       "      USING (deposit_serial_id)"
       "    JOIN known_coins"
-      "      USING (known_coin_id)"
+      "      USING (coin_pub)"
       "    JOIN denominations denom"
       "      USING (denominations_serial)"
       "    JOIN wire_out"
@@ -1497,7 +1496,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",done"
       " FROM deposits d"
       "   JOIN known_coins"
-      "     USING (known_coin_id)"
+      "     USING (coin_pub)"
       "   JOIN wire_targets"
       "     USING (wire_target_h_payto)"
       " WHERE wire_deadline >= $1"
@@ -1505,7 +1504,7 @@ prepare_statements (struct PostgresClosure *pg)
       " AND NOT (EXISTS (SELECT 1"
       "            FROM refunds"
       "            JOIN deposits dx USING (deposit_serial_id)"
-      "            WHERE (dx.known_coin_id = d.known_coin_id))"
+      "            WHERE (dx.coin_pub = d.coin_pub))"
       "       OR EXISTS (SELECT 1"
       "            FROM aggregation_tracking"
       "            WHERE (aggregation_tracking.deposit_serial_id = d.deposit_serial_id)))"
@@ -1564,7 +1563,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_frac"
       " FROM recoup"
       "    JOIN known_coins coins"
-      "      USING (known_coin_id)"
+      "      USING (coin_pub)"
       "    JOIN reserves_out ro"
       "      USING (reserve_out_serial_id)"
       "    JOIN reserves"
@@ -1601,7 +1600,7 @@ prepare_statements (struct PostgresClosure *pg)
       "    INNER JOIN known_coins old_coins"
       "      ON (rfc.old_coin_pub = old_coins.coin_pub)"
       "    INNER JOIN known_coins new_coins"
-      "      ON (new_coins.known_coin_id = recoup_refresh.known_coin_id)"
+      "      ON (new_coins.coin_pub = recoup_refresh.coin_pub)"
       "    INNER JOIN denominations new_denoms"
       "      ON (new_coins.denominations_serial = new_denoms.denominations_serial)"
       "    INNER JOIN denominations old_denoms"
@@ -1649,7 +1648,7 @@ prepare_statements (struct PostgresClosure *pg)
       " JOIN known_coins coins"
       "   ON (coins.denominations_serial = denoms.denominations_serial)"
       " JOIN recoup rc"
-      "   ON (rc.known_coin_id = coins.known_coin_id)"
+      "   ON (rc.coin_pub = coins.coin_pub)"
       " JOIN reserves_out ro"
       "   ON (ro.reserve_out_serial_id = rc.reserve_out_serial_id)"
       " JOIN reserves_out_by_reserve ror"
@@ -1674,7 +1673,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",recoup_refresh_uuid"
       " FROM recoup_refresh"
       " JOIN known_coins coins"
-      "   USING (known_coin_id)"
+      "   USING (coin_pub)"
       " JOIN denominations denoms"
       "   USING (denominations_serial)"
       " WHERE rrc_serial IN"
@@ -1739,7 +1738,7 @@ prepare_statements (struct PostgresClosure *pg)
       " JOIN reserves"
       "   USING (reserve_uuid)"
       " JOIN known_coins coins"
-      "   USING (known_coin_id)"
+      "   USING (coin_pub)"
       " JOIN denominations denoms"
       "   ON (denoms.denominations_serial = coins.denominations_serial)"
       " WHERE coins.coin_pub=$1;",
@@ -1766,7 +1765,7 @@ prepare_statements (struct PostgresClosure *pg)
       "    JOIN known_coins old_coins"
       "      ON (rfc.old_coin_pub = old_coins.coin_pub)"
       "    JOIN known_coins coins"
-      "      ON (recoup_refresh.known_coin_id = coins.known_coin_id)"
+      "      ON (recoup_refresh.coin_pub = coins.coin_pub)"
       "    JOIN denominations denoms"
       "      ON (denoms.denominations_serial = coins.denominations_serial)"
       " WHERE coins.coin_pub=$1;",
@@ -2439,6 +2438,7 @@ prepare_statements (struct PostgresClosure *pg)
       "SELECT"
       " deposit_serial_id AS serial"
       ",shard"
+      ",coin_pub"
       ",known_coin_id"
       ",amount_with_fee_val"
       ",amount_with_fee_frac"
@@ -2546,7 +2546,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_val"
       ",amount_frac"
       ",recoup_timestamp"
-      ",known_coin_id"
+      ",coin_pub"
       ",reserve_out_serial_id"
       " FROM recoup"
       " WHERE recoup_uuid > $1"
@@ -2561,6 +2561,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_val"
       ",amount_frac"
       ",recoup_timestamp"
+      ",coin_pub"
       ",known_coin_id"
       ",rrc_serial"
       " FROM recoup_refresh"
@@ -2770,6 +2771,7 @@ prepare_statements (struct PostgresClosure *pg)
       "(deposit_serial_id"
       ",shard"
       ",known_coin_id"
+      ",coin_pub"
       ",amount_with_fee_val"
       ",amount_with_fee_frac"
       ",wallet_timestamp"
@@ -2787,8 +2789,8 @@ prepare_statements (struct PostgresClosure *pg)
       ",extension_details_serial_id"
       ") VALUES "
       "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,"
-      " $11, $12, $13, $14, $15, $16, $17, $18);",
-      18),
+      " $11, $12, $13, $14, $15, $16, $17, $18, $19);",
+      19),
     GNUNET_PQ_make_prepare (
       "insert_into_table_refunds",
       "INSERT INTO refunds"
@@ -2858,7 +2860,7 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_val"
       ",amount_frac"
       ",recoup_timestamp"
-      ",known_coin_id"
+      ",coin_pub"
       ",reserve_out_serial_id"
       ") VALUES "
       "($1, $2, $3, $4, $5, $6, $7, $8);",
@@ -2873,10 +2875,11 @@ prepare_statements (struct PostgresClosure *pg)
       ",amount_frac"
       ",recoup_timestamp"
       ",known_coin_id"
+      ",coin_pub"
       ",rrc_serial"
       ") VALUES "
-      "($1, $2, $3, $4, $5, $6, $7, $8);",
-      8),
+      "($1, $2, $3, $4, $5, $6, $7, $8, $9);",
+      9),
     GNUNET_PQ_make_prepare (
       "insert_into_table_extensions",
       "INSERT INTO extensions"