change sharding strategy for refund table

1 files changed, 19 insertions, 20 deletions

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index a42baa1f3..b2fb52ac2 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -686,7 +686,7 @@ CREATE TABLE IF NOT EXISTS extension_details_default
 CREATE TABLE IF NOT EXISTS deposits
   (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
   ,shard INT8 NOT NULL
-  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub) 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
@@ -754,7 +754,7 @@ SELECT add_constraints_to_deposits_partition('default');
 CREATE TABLE IF NOT EXISTS deposits_by_ready
   (wire_deadline INT8 NOT NULL
   ,shard INT8 NOT NULL
-  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE
   ,deposit_serial_id INT8
   )
   PARTITION BY RANGE (wire_deadline);
@@ -773,7 +773,7 @@ CREATE TABLE IF NOT EXISTS deposits_by_ready_default
 CREATE TABLE IF NOT EXISTS deposits_for_matching
   (refund_deadline INT8 NOT NULL
   ,shard INT8 NOT NULL
-  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE
   ,deposit_serial_id INT8
   )
   PARTITION BY RANGE (refund_deadline);
@@ -947,11 +947,9 @@ CREATE TRIGGER deposits_on_delete
 
 -- ------------------------------ refunds ----------------------------------------
 
--- FIXME-URGENT: very bad structure, should replace 'shard' by 'coin_pub'
--- as deposits is sharded by that now!
 CREATE TABLE IF NOT EXISTS refunds
   (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,shard INT8 NOT NULL -- REFERENCES deposits (shard)
+  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub) ON DELETE CASCADE
   ,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
@@ -959,7 +957,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 (shard);
+  PARTITION BY HASH (coin_pub);
 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
@@ -967,6 +965,10 @@ COMMENT ON COLUMN refunds.deposit_serial_id
 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';
 
+CREATE INDEX IF NOT EXISTS refunds_by_coin_pub_index
+  ON refunds
+  (coin_pub);
+
 CREATE TABLE IF NOT EXISTS refunds_default
   PARTITION OF refunds
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -989,9 +991,6 @@ $$;
 
 SELECT add_constraints_to_refunds_partition('default');
 
-CREATE INDEX IF NOT EXISTS refunds_by_deposit_serial_id_index
-  ON refunds
-  (shard,deposit_serial_id);
 
 
 -- ------------------------------ wire_out ----------------------------------------
@@ -1146,7 +1145,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
-  ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
+  ,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
@@ -1193,7 +1192,7 @@ SELECT add_constraints_to_recoup_partition('default');
 
 CREATE TABLE IF NOT EXISTS recoup_by_reserve
   (reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves (reserve_out_serial_id) ON DELETE CASCADE
-  ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32) 
+  ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32) REFERENCES known_coins (coin_pub)
   )
   PARTITION BY HASH (reserve_out_serial_id);
 COMMENT ON TABLE recoup_by_reserve
@@ -1251,8 +1250,8 @@ CREATE TRIGGER recoup_on_delete
 
 CREATE TABLE IF NOT EXISTS recoup_refresh
   (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,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_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)
   ,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
@@ -2709,8 +2708,8 @@ DECLARE
   deposit_frac INT8; -- amount that was originally deposited
 BEGIN
 -- Shards: SELECT deposits (coin_pub, shard, h_contract_terms, merchant_pub)
---         INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT DO NOTHING
---         SELECT refunds (by deposit_serial_id)
+--         INSERT refunds (by coin_pub, rtransaction_id) ON CONFLICT DO NOTHING
+--         SELECT refunds (by coin_pub)
 --         UPDATE known_coins (by coin_pub)
 
 SELECT
@@ -2741,7 +2740,7 @@ END IF;
 
 INSERT INTO refunds
   (deposit_serial_id
-  ,shard
+  ,coin_pub
   ,merchant_sig
   ,rtransaction_id
   ,amount_with_fee_val
@@ -2749,7 +2748,7 @@ INSERT INTO refunds
   )
   VALUES
   (dsi
-  ,in_deposit_shard
+  ,in_coin_pub
   ,in_merchant_sig
   ,in_rtransaction_id
   ,in_amount_with_fee_val
@@ -2765,7 +2764,7 @@ THEN
   -- primarily here to maximally use the existing index.
    PERFORM
    FROM refunds
-   WHERE shard=in_deposit_shard
+   WHERE coin_pub=in_coin_pub
      AND deposit_serial_id=dsi
      AND rtransaction_id=in_rtransaction_id
      AND amount_with_fee_val=in_amount_with_fee_val
@@ -2805,7 +2804,7 @@ SELECT
    tmp_val
   ,tmp_frac
   FROM refunds
-  WHERE shard=in_deposit_shard
+  WHERE coin_pub=in_coin_pub
     AND deposit_serial_id=dsi;
 IF tmp_val IS NULL
 THEN