From 783e2ae424fdd338da142e2e7472ee86b27d4035 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <grothoff@gnunet.org>
Date: Sat, 26 Mar 2022 09:00:19 +0100
Subject: add partitions to new p2p tables

---
 src/exchangedb/exchange-0001.sql | 642 +++++++++++++++++++++++++++------------
 1 file changed, 445 insertions(+), 197 deletions(-)

(limited to 'src/exchangedb/exchange-0001.sql')

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 568779f97..a42baa1f3 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -21,6 +21,8 @@ BEGIN;
 SELECT _v.register_patch('exchange-0001', NULL, NULL);
 
 
+-- ------------------------------ denominations ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS denominations
   (denominations_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
@@ -57,6 +59,8 @@ CREATE INDEX IF NOT EXISTS denominations_by_expire_legal_index
   (expire_legal);
 
 
+-- ------------------------------ denomination_revocations ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS denomination_revocations
   (denom_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,denominations_serial INT8 PRIMARY KEY REFERENCES denominations (denominations_serial) ON DELETE CASCADE
@@ -66,6 +70,8 @@ COMMENT ON TABLE denomination_revocations
   IS 'remembering which denomination keys have been revoked';
 
 
+-- ------------------------------ wire_targets ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wire_targets
   (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)
@@ -106,14 +112,13 @@ $$;
 
 SELECT add_constraints_to_wire_targets_partition('default');
 
--- FIXME partition by serial_id rather than h_payto,
--- it is used more in join conditions - crucial for sharding to select this.
--- Author: (Boss Marco)
 CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index
   ON wire_targets
   (wire_target_serial_id);
 
 
+-- ------------------------------ reserves ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS reserves
   (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
   ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
@@ -154,6 +159,7 @@ CREATE INDEX IF NOT EXISTS reserves_by_gc_date_index
 COMMENT ON INDEX reserves_by_gc_date_index
   IS 'for reserve garbage collection';
 
+-- ------------------------------ reserves_in ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS reserves_in
   (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -199,18 +205,22 @@ SELECT add_constraints_to_reserves_in_partition('default');
 CREATE INDEX IF NOT EXISTS reserves_in_by_reserve_in_serial_id_index
   ON reserves_in
   (reserve_in_serial_id);
+-- FIXME: where do we need this index? Can we do better?
 CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_section_execution_date_index
   ON reserves_in
   (exchange_account_section
   ,execution_date
   );
+-- FIXME: where do we need this index? Can we do better?
 CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_reserve_in_serial_id_index
   ON reserves_in
-  (exchange_account_section,
-  reserve_in_serial_id DESC
+  (exchange_account_section
+  ,reserve_in_serial_id DESC
   );
 
 
+-- ------------------------------ reserves_close ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS reserves_close
   (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
   ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE
@@ -256,6 +266,7 @@ $$;
 SELECT add_constraints_to_reserves_close_partition('default');
 
 
+-- ------------------------------ reserves_out ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS reserves_out
   (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -279,14 +290,13 @@ COMMENT ON COLUMN reserves_out.denominations_serial
 CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index
   ON reserves_out
   (reserve_out_serial_id);
+-- FIXME: change query to use reserves_out_by_reserve instead and materialize execution_date there as well???
 CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index
   ON reserves_out
   (reserve_uuid, execution_date);
 COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index
   IS 'for get_reserves_out and exchange_do_withdraw_limit_check';
 
-
-
 CREATE TABLE IF NOT EXISTS reserves_out_default
   PARTITION OF reserves_out
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -309,6 +319,63 @@ $$;
 SELECT add_constraints_to_reserves_out_partition('default');
 
 
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
+  (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
+  ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) 
+  )
+  PARTITION BY HASH (reserve_uuid);
+COMMENT ON TABLE reserves_out_by_reserve
+  IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.';
+
+CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
+  ON reserves_out_by_reserve
+  (reserve_uuid);
+
+CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
+  PARTITION OF reserves_out_by_reserve
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  INSERT INTO reserves_out_by_reserve
+    (reserve_uuid
+    ,h_blind_ev)
+  VALUES
+    (NEW.reserve_uuid
+    ,NEW.h_blind_ev);
+  RETURN NEW;
+END $$;  
+COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
+  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_insert
+  AFTER INSERT
+   ON reserves_out
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
+
+CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
+  RETURNS trigger
+  LANGUAGE plpgsql
+  AS $$
+BEGIN
+  DELETE FROM reserves_out_by_reserve
+   WHERE reserve_uuid = OLD.reserve_uuid;
+  RETURN OLD;
+END $$;  
+COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
+  IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
+
+CREATE TRIGGER reserves_out_on_delete
+  AFTER DELETE
+    ON reserves_out
+   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
+
+
+-- ------------------------------ auditors ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS auditors
   (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,auditor_pub BYTEA PRIMARY KEY CHECK (LENGTH(auditor_pub)=32)
@@ -329,6 +396,8 @@ COMMENT ON COLUMN auditors.last_change
   IS 'Latest time when active status changed. Used to detect replays of old messages.';
 
 
+-- ------------------------------ auditor_denom_sigs ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS auditor_denom_sigs
   (auditor_denom_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,auditor_uuid INT8 NOT NULL REFERENCES auditors (auditor_uuid) ON DELETE CASCADE
@@ -346,6 +415,8 @@ COMMENT ON COLUMN auditor_denom_sigs.auditor_sig
   IS 'Signature of the auditor, of purpose TALER_SIGNATURE_AUDITOR_EXCHANGE_KEYS.';
 
 
+-- ------------------------------ exchange_sign_keys ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS exchange_sign_keys
   (esk_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
@@ -368,6 +439,8 @@ COMMENT ON COLUMN exchange_sign_keys.expire_legal
   IS 'Time when this online signing key legally expires.';
 
 
+-- ------------------------------ signkey_revocations ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS signkey_revocations
   (signkey_revocations_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,esk_serial INT8 PRIMARY KEY REFERENCES exchange_sign_keys (esk_serial) ON DELETE CASCADE
@@ -377,6 +450,8 @@ COMMENT ON TABLE signkey_revocations
   IS 'Table storing which online signing keys have been revoked';
 
 
+-- ------------------------------ extension ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS extensions
   (extension_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,name VARCHAR NOT NULL UNIQUE
@@ -390,6 +465,8 @@ COMMENT ON COLUMN extensions.config
   IS 'Configuration of the extension as JSON-blob, maybe NULL';
 
 
+-- ------------------------------ known_coins ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS known_coins
   (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
@@ -399,7 +476,7 @@ CREATE TABLE IF NOT EXISTS known_coins
   ,remaining_val INT8 NOT NULL
   ,remaining_frac INT4 NOT NULL
   )
-  PARTITION BY HASH (coin_pub); -- FIXME: or include denominations_serial? or multi-level partitioning?
+  PARTITION BY HASH (coin_pub);
 COMMENT ON TABLE known_coins
   IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations';
 COMMENT ON COLUMN known_coins.denominations_serial
@@ -434,10 +511,8 @@ $$;
 
 SELECT add_constraints_to_known_coins_partition('default');
 
-CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
-  ON known_coins
-  (known_coin_id);
 
+-- ------------------------------ refresh_commitments ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS refresh_commitments
   (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -458,6 +533,11 @@ COMMENT ON COLUMN refresh_commitments.rc
 COMMENT ON COLUMN refresh_commitments.old_coin_pub
   IS 'Coin being melted in the refresh process.';
 
+-- Note: index spans partitions, may need to be materialized.
+CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
+  ON refresh_commitments
+  (old_coin_pub);
+
 CREATE TABLE IF NOT EXISTS refresh_commitments_default
   PARTITION OF refresh_commitments
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -479,13 +559,8 @@ $$;
 
 SELECT add_constraints_to_refresh_commitments_partition('default');
 
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_melt_serial_id_index
-  ON refresh_commitments
-  (melt_serial_id);
-CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
-  ON refresh_commitments
-  (old_coin_pub);
 
+-- ------------------------------ refresh_revealed_coins ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS refresh_revealed_coins
   (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -517,9 +592,6 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev
 COMMENT ON COLUMN refresh_revealed_coins.ev_sig
   IS 'exchange signature over the envelope';
 
-CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index
-  ON refresh_revealed_coins
-  (rrc_serial);
 CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
   ON refresh_revealed_coins
   (melt_serial_id);
@@ -551,7 +623,7 @@ $$;
 SELECT add_constraints_to_refresh_revealed_coins_partition('default');
 
 
-
+-- ------------------------------ refresh_transfer_keys ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS refresh_transfer_keys
   (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -592,25 +664,30 @@ $$;
 
 SELECT add_constraints_to_refresh_transfer_keys_partition('default');
 
-CREATE INDEX IF NOT EXISTS refresh_transfer_keys_by_rtc_serial_index
-  ON refresh_transfer_keys
-  (rtc_serial);
 
+-- ------------------------------ extension_details ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS extension_details
   (extension_details_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,extension_options VARCHAR);
+  ,extension_options VARCHAR)
+  PARTITION BY HASH (extension_details_serial_id);
 COMMENT ON TABLE extension_details
   IS 'Extensions that were provided with deposits (not yet used).';
 COMMENT ON COLUMN extension_details.extension_options
   IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the extensions supported by the exchange.';
 
+CREATE TABLE IF NOT EXISTS extension_details_default
+  PARTITION OF extension_details
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ deposits ----------------------------------------
 
 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
-  ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE --- FIXME: column needed???
+  ,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
@@ -629,34 +706,10 @@ CREATE TABLE IF NOT EXISTS deposits
   ,UNIQUE (coin_pub, merchant_pub, h_contract_terms)
   )
   PARTITION BY HASH (coin_pub);
--- FIXME:
--- TODO: dynamically (!) creating/deleting partitions:
---    create new partitions 'as needed', drop old ones once the aggregator has made
---    them empty; as 'new' deposits will always have deadlines in the future, this
---    would basically guarantee no conflict between aggregator and exchange service!
--- SEE also: https://www.cybertec-postgresql.com/en/automatic-partition-creation-in-postgresql/
--- (article is slightly wrong, as this works:)
---CREATE TABLE tab (
---  id bigint GENERATED ALWAYS AS IDENTITY,
---  ts timestamp NOT NULL,
---  data text
--- PARTITION BY LIST ((ts::date));
--- CREATE TABLE tab_def PARTITION OF tab DEFAULT;
--- BEGIN
--- CREATE TABLE tab_part2 (LIKE tab);
--- insert into tab_part2 (id,ts, data) values (5,'2022-03-21', 'foo');
--- alter table tab attach partition tab_part2 for values in ('2022-03-21');
--- commit;
--- Naturally, to ensure this is actually 100% conflict-free, we'd
--- need to create tables at the granularity of the wire/refund deadlines;
--- that is right now seconds (!). But I see no problem with changing the
--- aggregator to basically always run 1 minute behind and use minutes instead!
-
-
 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 merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
+  IS 'Used for load sharding in the materialized indices. 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
@@ -672,17 +725,10 @@ 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: we sometimes go ONLY by 'deposit_serial_id',
---        check if queries could be improved by adding shard or adding another index without shard here, or inverting the order of the index here!
-CREATE INDEX IF NOT EXISTS deposits_deposit_by_serial_id_index
-  ON deposits
-  (shard,deposit_serial_id);
-
 CREATE INDEX IF NOT EXISTS deposits_by_coin_pub_index
   ON deposits
   (coin_pub);
 
-
 CREATE TABLE IF NOT EXISTS deposits_default
   PARTITION OF deposits
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -889,7 +935,7 @@ BEGIN
        AND deposit_serial_id = OLD.deposit_serial_id;
   END IF;
   RETURN NEW;
-END $$;  
+END $$;
 COMMENT ON FUNCTION deposits_delete_trigger()
   IS 'Replicate deposit deletions into materialized indices.';
 
@@ -899,9 +945,13 @@ CREATE TRIGGER deposits_on_delete
    FOR EACH ROW EXECUTE FUNCTION deposits_delete_trigger();
 
 
+-- ------------------------------ 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) 
+  ,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
@@ -939,14 +989,12 @@ $$;
 
 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);
 
 
+-- ------------------------------ wire_out ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS wire_out
   (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
@@ -972,7 +1020,6 @@ CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index
   ON wire_out
   (wire_target_h_payto);
 
-
 CREATE TABLE IF NOT EXISTS wire_out_default
   PARTITION OF wire_out
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -995,10 +1042,13 @@ $$;
 SELECT add_constraints_to_wire_out_partition('default');
 
 
+-- ------------------------------ aggregation_tracking ----------------------------------------
 
+-- FIXME-URGENT: add colum coin_pub to select by coin_pub + deposit_serial_id for more efficient deposit lookup!?
+-- Or which direction(s) is this table used? Is the partitioning sane??
 CREATE TABLE IF NOT EXISTS aggregation_tracking
   (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE -- FIXME: change to coin_pub + deposit_serial_id for more efficient deposit -- or something else??? 
+  ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
   ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
   )
   PARTITION BY HASH (deposit_serial_id);
@@ -1028,9 +1078,6 @@ $$;
 
 SELECT add_constraints_to_aggregation_tracking_partition('default');
 
-CREATE INDEX IF NOT EXISTS aggregation_tracking_by_aggregation_serial_id_index
-  ON aggregation_tracking
-  (aggregation_serial_id);
 CREATE INDEX IF NOT EXISTS aggregation_tracking_by_wtid_raw_index
   ON aggregation_tracking
   (wtid_raw);
@@ -1038,6 +1085,8 @@ COMMENT ON INDEX aggregation_tracking_by_wtid_raw_index
   IS 'for lookup_transactions';
 
 
+-- ------------------------------ wire_fee ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wire_fee
   (wire_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,wire_method VARCHAR NOT NULL
@@ -1062,6 +1111,8 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
   (end_date);
 
 
+-- ------------------------------ global_fee ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS global_fee
   (global_fee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,start_date INT8 NOT NULL
@@ -1091,6 +1142,8 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
   (end_date);
 
 
+-- ------------------------------ recoup ----------------------------------------
+
 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)
@@ -1113,17 +1166,10 @@ COMMENT ON COLUMN recoup.coin_sig
 COMMENT ON COLUMN recoup.coin_blind
   IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.';
 
-CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
-  ON recoup
-  (recoup_uuid);
-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_coin_pub_index
   ON recoup
   (coin_pub);
 
-
 CREATE TABLE IF NOT EXISTS recoup_default
   PARTITION OF recoup
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -1145,7 +1191,6 @@ $$;
 
 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) 
@@ -1183,7 +1228,6 @@ CREATE TRIGGER recoup_on_insert
    ON recoup
    FOR EACH ROW EXECUTE FUNCTION recoup_insert_trigger();
 
-
 CREATE OR REPLACE FUNCTION recoup_delete_trigger()
   RETURNS trigger
   LANGUAGE plpgsql
@@ -1203,68 +1247,7 @@ CREATE TRIGGER recoup_on_delete
    FOR EACH ROW EXECUTE FUNCTION recoup_delete_trigger();
 
 
-
-
-
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
-  (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
-  ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) 
-  )
-  PARTITION BY HASH (reserve_uuid);
-COMMENT ON TABLE reserves_out_by_reserve
-  IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.';
-
-CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index
-  ON reserves_out_by_reserve
-  (reserve_uuid);
-
-
-CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default
-  PARTITION OF reserves_out_by_reserve
-  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_insert_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-BEGIN
-  INSERT INTO reserves_out_by_reserve
-    (reserve_uuid
-    ,h_blind_ev)
-  VALUES
-    (NEW.reserve_uuid
-    ,NEW.h_blind_ev);
-  RETURN NEW;
-END $$;  
-COMMENT ON FUNCTION reserves_out_by_reserve_insert_trigger()
-  IS 'Replicate reserve_out inserts into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_insert
-  AFTER INSERT
-   ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_insert_trigger();
-
-
-CREATE OR REPLACE FUNCTION reserves_out_by_reserve_delete_trigger()
-  RETURNS trigger
-  LANGUAGE plpgsql
-  AS $$
-BEGIN
-  DELETE FROM reserves_out_by_reserve
-   WHERE reserve_uuid = OLD.reserve_uuid;
-  RETURN OLD;
-END $$;  
-COMMENT ON FUNCTION reserves_out_by_reserve_delete_trigger()
-  IS 'Replicate reserve_out deletions into reserve_out_by_reserve table.';
-
-CREATE TRIGGER reserves_out_on_delete
-  AFTER DELETE
-    ON reserves_out
-   FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger();
-
-
-
-
+-- ------------------------------ recoup_refresh ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS recoup_refresh
   (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
@@ -1289,16 +1272,14 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial
 COMMENT ON COLUMN recoup_refresh.coin_blind
   IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.';
 
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
   ON recoup_refresh
-  (recoup_refresh_uuid);
+  (coin_pub);
+-- FIXME: any query using this index will be slow. Materialize index or change query?
+-- Also: which query uses this 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_coin_pub_index
-  ON recoup_refresh
-  (coin_pub);
-
 
 CREATE TABLE IF NOT EXISTS recoup_refresh_default
   PARTITION OF recoup_refresh
@@ -1322,6 +1303,7 @@ $$;
 SELECT add_constraints_to_recoup_refresh_partition('default');
 
 
+-- ------------------------------ prewire ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS prewire
   (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
@@ -1357,6 +1339,7 @@ CREATE TABLE IF NOT EXISTS prewire_default
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
 
 
+-- ------------------------------ wire_accounts ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS wire_accounts
   (payto_uri VARCHAR PRIMARY KEY
@@ -1378,6 +1361,8 @@ COMMENT ON COLUMN wire_accounts.last_change
 --            and is of no concern to the auditor
 
 
+-- ------------------------------ cs_nonce_locks ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS cs_nonce_locks
   (cs_nonce_lock_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,nonce BYTEA PRIMARY KEY CHECK (LENGTH(nonce)=32)
@@ -1415,6 +1400,9 @@ $$;
 
 SELECT add_constraints_to_cs_nonce_locks_partition('default');
 
+
+-- ------------------------------ work_shards ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS work_shards
   (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,last_attempt INT8 NOT NULL
@@ -1447,6 +1435,8 @@ CREATE INDEX IF NOT EXISTS work_shards_by_job_name_completed_last_attempt_index
   );
 
 
+-- ------------------------------ revolving_work_shards ----------------------------------------
+
 CREATE UNLOGGED TABLE IF NOT EXISTS revolving_work_shards
   (shard_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,last_attempt INT8 NOT NULL
@@ -1478,10 +1468,14 @@ CREATE INDEX IF NOT EXISTS revolving_work_shards_by_job_name_active_last_attempt
   ,last_attempt
   );
 
--- Tables for P2P payments
+--------------------------------------------------------------------------
+--                        Tables for P2P payments
+--------------------------------------------------------------------------
+
+-- ------------------------------ partners ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS partners
-  (partner_serial_id BIGSERIAL UNIQUE
+  (partner_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
   ,partner_master_pub BYTEA NOT NULL CHECK(LENGTH(partner_master_pub)=32)
   ,start_date INT8 NOT NULL
   ,end_date INT8 NOT NULL
@@ -1509,8 +1503,10 @@ COMMENT ON COLUMN partners.master_sig
   IS 'signature of our master public key affirming the partnership, of purpose TALER_SIGNATURE_MASTER_PARTNER_DETAILS';
 
 
+-- ------------------------------ purse_requests ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS purse_requests
-  (purse_deposit_serial_id BIGSERIAL UNIQUE
+  (purse_requests_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
   ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
   ,merge_pub BYTEA NOT NULL CHECK (LENGTH(merge_pub)=32)
   ,purse_expiration INT8 NOT NULL
@@ -1522,7 +1518,8 @@ CREATE TABLE IF NOT EXISTS purse_requests
   ,balance_frac INT4 NOT NULL DEFAULT (0)
   ,purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)
   ,PRIMARY KEY (purse_pub)
-  ); -- partition by purse_pub
+  )
+  PARTITION BY HASH (purse_pub);
 COMMENT ON TABLE purse_requests
   IS 'Requests establishing purses, associating them with a contract but without a target reserve';
 COMMENT ON COLUMN purse_requests.purse_pub
@@ -1538,19 +1535,45 @@ COMMENT ON COLUMN purse_requests.balance_val
 COMMENT ON COLUMN purse_requests.purse_sig
   IS 'Signature of the purse affirming the purse parameters, of type TALER_SIGNATURE_PURSE_REQUEST';
 
--- FIXME: create purse_by_merge materialized index table
--- for merge_pub => purse_pub mapping!
+-- FIXME: change to materialized index by marge_pub!
+CREATE INDEX IF NOT EXISTS purse_requests_merge_pub
+  ON purse_requests (merge_pub);
 
+CREATE TABLE IF NOT EXISTS purse_requests_default
+  PARTITION OF purse_requests
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_requests_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE purse_requests_' || partition_suffix || ' '
+      'ADD CONSTRAINT purse_requests_' || partition_suffix || '_purse_requests_serial_id_key '
+        'UNIQUE (purse_requests_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_purse_requests_partition('default');
+
+
+
+-- ------------------------------ purse_merges ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS purse_merges
-  (purse_merge_request_serial_id BIGSERIAL -- UNIQUE
+  (purse_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
   ,reserve_pub BYTEA NOT NULL CHECK(length(reserve_pub)=32)--REFERENCES reserves (reserve_pub) ON DELETE CASCADE
   ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) --REFERENCES purse_requests (purse_pub) ON DELETE CASCADE
   ,merge_sig BYTEA NOT NULL CHECK (LENGTH(merge_sig)=64)
   ,merge_timestamp INT8 NOT NULL
   ,PRIMARY KEY (purse_pub)
-  ); -- partition by purse_pub; plus materialized index by reserve_pub!
+  )
+  PARTITION BY HASH (purse_pub);
 COMMENT ON TABLE purse_merges
   IS 'Merge requests where a purse-owner requested merging the purse into the account';
 COMMENT ON COLUMN purse_merges.partner_serial_id
@@ -1563,42 +1586,99 @@ COMMENT ON COLUMN purse_merges.merge_sig
   IS 'signature by the purse private key affirming the merge, of type TALER_SIGNATURE_WALLET_PURSE_MERGE';
 COMMENT ON COLUMN purse_merges.merge_timestamp
   IS 'when was the merge message signed';
+
+CREATE INDEX IF NOT EXISTS purse_merges_purse_pub
+  ON purse_merges (purse_pub);
+-- FIXME: change to materialized index by reserve_pub!
 CREATE INDEX IF NOT EXISTS purse_merges_reserve_pub
   ON purse_merges (reserve_pub);
 COMMENT ON INDEX purse_merges_reserve_pub
   IS 'needed in reserve history computation';
 
+CREATE TABLE IF NOT EXISTS purse_merges_default
+  PARTITION OF purse_merges
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
 
-CREATE TABLE IF NOT EXISTS account_mergers
-  (account_merge_request_serial_id BIGSERIAL -- UNIQUE
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_merges_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE purse_merges_' || partition_suffix || ' '
+      'ADD CONSTRAINT purse_merges_' || partition_suffix || '_purse_merge_request_serial_id_key '
+        'UNIQUE (purse_merge_request_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_purse_merges_partition('default');
+
+
+
+-- ------------------------------ account_merges ----------------------------------------
+
+CREATE TABLE IF NOT EXISTS account_merges
+  (account_merge_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE
   ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
   ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32) -- REFERENCES purse_requests (purse_pub)
-  ,PRIMARY KEY (reserve_pub)
-  ); -- partition by purse_pub; plus materialized index by reserve_pub!
-COMMENT ON TABLE account_mergers
+  ,PRIMARY KEY (purse_pub)
+  )
+  PARTITION BY HASH (purse_pub);
+COMMENT ON TABLE account_merges
   IS 'Merge requests where a purse- and account-owner requested merging the purse into the account';
-COMMENT ON COLUMN account_mergers.reserve_pub
+COMMENT ON COLUMN account_merges.reserve_pub
   IS 'public key of the target reserve';
-COMMENT ON COLUMN account_mergers.purse_pub
+COMMENT ON COLUMN account_merges.purse_pub
   IS 'public key of the purse';
-COMMENT ON COLUMN account_mergers.reserve_sig
+COMMENT ON COLUMN account_merges.reserve_sig
   IS 'signature by the reserve private key affirming the merge, of type TALER_SIGNATURE_WALLET_ACCOUNT_MERGE';
 
-CREATE INDEX IF NOT EXISTS account_mergers_purse_pub
-  ON account_mergers (purse_pub);
-COMMENT ON INDEX account_mergers_purse_pub
+CREATE INDEX IF NOT EXISTS account_merges_purse_pub
+  ON account_merges (purse_pub);
+COMMENT ON INDEX account_merges_purse_pub
   IS 'needed when checking for a purse merge status';
-  
+
+-- FIXME: change to materialized index by reserve_pub!
+CREATE INDEX IF NOT EXISTS account_merges_by_reserve_pub
+  ON account_merges (reserve_pub);
+
+CREATE TABLE IF NOT EXISTS account_merges_default
+  PARTITION OF account_merges
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_account_merges_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE account_merges_' || partition_suffix || ' '
+      'ADD CONSTRAINT account_merges_' || partition_suffix || '_account_merge_request_serial_id_key '
+        'UNIQUE (account_merge_request_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_account_merges_partition('default');
+
+
+-- ------------------------------ contracts ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS contracts
-  (contract_serial_id BIGSERIAL UNIQUE
+  (contract_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
   ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
   ,pub_ckey BYTEA NOT NULL CHECK (LENGTH(pub_ckey)=32)
   ,e_contract BYTEA NOT NULL
   ,purse_expiration INT8 NOT NULL
   ,PRIMARY KEY (purse_pub)
-  ); -- partition by purse_pub
+  )
+  PARTITION BY HASH (purse_pub);
 COMMENT ON TABLE contracts
   IS 'encrypted contracts associated with purses';
 COMMENT ON COLUMN contracts.purse_pub
@@ -1608,6 +1688,30 @@ COMMENT ON COLUMN contracts.pub_ckey
 COMMENT ON COLUMN contracts.e_contract
   IS 'AES-GCM encrypted contract terms (contains gzip compressed JSON after decryption)';
 
+CREATE TABLE IF NOT EXISTS contracts_default
+  PARTITION OF contracts
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_contracts_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE contracts_' || partition_suffix || ' '
+      'ADD CONSTRAINT contracts_' || partition_suffix || '_contract_serial_id_key '
+        'UNIQUE (contract_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_contracts_partition('default');
+
+
+-- ------------------------------ history_requests ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS history_requests
   (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE
   ,request_timestamp INT8 NOT NULL
@@ -1615,7 +1719,8 @@ CREATE TABLE IF NOT EXISTS history_requests
   ,history_fee_val INT8 NOT NULL
   ,history_fee_frac INT4 NOT NULL
   ,PRIMARY KEY (reserve_pub,request_timestamp)
-  ); -- partition by reserve_pub
+  )
+  PARTITION BY HASH (reserve_pub);
 COMMENT ON TABLE history_requests
   IS 'Paid history requests issued by a client against a reserve';
 COMMENT ON COLUMN history_requests.request_timestamp
@@ -1625,6 +1730,13 @@ COMMENT ON COLUMN history_requests.reserve_sig
 COMMENT ON COLUMN history_requests.history_fee_val
   IS 'History fee approved by the signature';
 
+CREATE TABLE IF NOT EXISTS history_requests_default
+  PARTITION OF history_requests
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ close_requests ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS close_requests
   (reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32) REFERENCES reserves(reserve_pub) ON DELETE CASCADE
   ,close_timestamp INT8 NOT NULL
@@ -1632,7 +1744,8 @@ CREATE TABLE IF NOT EXISTS close_requests
   ,close_val INT8 NOT NULL
   ,close_frac INT4 NOT NULL
   ,PRIMARY KEY (reserve_pub,close_timestamp)
-  ); -- partition by reserve_pub
+  )
+  PARTITION BY HASH (reserve_pub);
 COMMENT ON TABLE close_requests
   IS 'Explicit requests by a reserve owner to close a reserve immediately';
 COMMENT ON COLUMN close_requests.close_timestamp
@@ -1642,17 +1755,24 @@ COMMENT ON COLUMN close_requests.reserve_sig
 COMMENT ON COLUMN close_requests.close_val
   IS 'Balance of the reserve at the time of closing, to be wired to the associated bank account (minus the closing fee)';
 
+CREATE TABLE IF NOT EXISTS close_requests_default
+  PARTITION OF close_requests
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+
+-- ------------------------------ purse_deposits ----------------------------------------
 
 CREATE TABLE IF NOT EXISTS purse_deposits
-  (purse_deposit_serial_id BIGSERIAL UNIQUE
+  (purse_deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
   ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
   ,purse_pub BYTEA NOT NULL CHECK (LENGTH(purse_pub)=32)
   ,coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
   ,amount_with_fee_val INT8 NOT NULL
   ,amount_with_fee_frac INT4 NOT NULL
   ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
-  ,PRIMARY KEY (purse_pub,coin_pub)
-  ); -- partition by purse_pub, plus a materialized index by coin_pub!
+  -- ,PRIMARY KEY (purse_pub,coin_pub)
+  )
+  PARTITION BY HASH (purse_pub);
 COMMENT ON TABLE purse_deposits
   IS 'Requests depositing coins into a purse';
 COMMENT ON COLUMN purse_deposits.partner_serial_id
@@ -1666,14 +1786,43 @@ COMMENT ON COLUMN purse_deposits.amount_with_fee_val
 COMMENT ON COLUMN purse_deposits.coin_sig
   IS 'Signature of the coin affirming the deposit into the purse, of type TALER_SIGNATURE_PURSE_DEPOSIT';
 
+-- FIXME: change to materialized index by coin_pub!
+CREATE INDEX IF NOT EXISTS purse_deposits_by_coin_pub
+  ON purse_deposits (coin_pub);
+
+CREATE TABLE IF NOT EXISTS purse_deposits_default
+  PARTITION OF purse_deposits
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_purse_deposits_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE purse_deposits_' || partition_suffix || ' '
+      'ADD CONSTRAINT purse_deposits_' || partition_suffix || '_purse_deposit_serial_id_key '
+        'UNIQUE (purse_deposit_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_purse_deposits_partition('default');
+
+
+-- ------------------------------ wads_out ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wads_out
-  (wad_out_serial_id BIGSERIAL UNIQUE
+  (wad_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
   ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
   ,partner_serial_id INT8 NOT NULL REFERENCES partners(partner_serial_id) ON DELETE CASCADE
   ,amount_val INT8 NOT NULL
   ,amount_frac INT4 NOT NULL
   ,execution_time INT8 NOT NULL
-  ); -- partition by wad_id
+  )
+  PARTITION BY HASH (wad_id);
 COMMENT ON TABLE wads_out
   IS 'Wire transfers made to another exchange to transfer purse funds';
 COMMENT ON COLUMN wads_out.wad_id
@@ -1685,9 +1834,36 @@ COMMENT ON COLUMN wads_out.amount_val
 COMMENT ON COLUMN wads_out.execution_time
   IS 'Time when the wire transfer was scheduled';
 
+CREATE INDEX IF NOT EXISTS wads_out_index_by_wad_id
+  ON wads_out (wad_id);
+
+CREATE TABLE IF NOT EXISTS wads_out_default
+  PARTITION OF wads_out
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_out_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE wads_out_' || partition_suffix || ' '
+      'ADD CONSTRAINT wads_out_' || partition_suffix || '_wad_out_serial_id_key '
+        'UNIQUE (wad_out_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_wads_out_partition('default');
+
+
+-- ------------------------------ wads_out_entries ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wad_out_entries
-  (wad_out_entry_serial_id BIGSERIAL UNIQUE
-  ,wad_out_serial_id INT8 REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE
+  (wad_out_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+  ,wad_out_serial_id INT8 -- REFERENCES wads_out (wad_out_serial_id) ON DELETE CASCADE
   ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
   ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
   ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
@@ -1701,9 +1877,11 @@ CREATE TABLE IF NOT EXISTS wad_out_entries
   ,deposit_fees_frac INT4 NOT NULL
   ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
   ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
-  ); -- partition by purse_pub? do we need a materialized index by reserve_pub?
-CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_wad
-  ON wad_out_entries (wad_out_serial_id);
+  )
+  PARTITION BY HASH (purse_pub);
+-- FIXME: convert to materialized index!
+CREATE INDEX IF NOT EXISTS wad_out_entries_index_by_reserve_pub
+  ON wad_out_entries (reserve_pub);
 COMMENT ON TABLE wad_out_entries
   IS 'Purses combined into a wad';
 COMMENT ON COLUMN wad_out_entries.wad_out_serial_id
@@ -1729,15 +1907,40 @@ COMMENT ON COLUMN wad_out_entries.reserve_sig
 COMMENT ON COLUMN wad_out_entries.purse_sig
   IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
 
+CREATE TABLE IF NOT EXISTS wad_out_entries_default
+  PARTITION OF wad_out_entries
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_out_entries_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE wad_out_entries_' || partition_suffix || ' '
+      'ADD CONSTRAINT wad_out_entries_' || partition_suffix || '_wad_out_entry_serial_id_key '
+        'UNIQUE (wad_out_entry_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_wad_out_entries_partition('default');
+
+
+-- ------------------------------ wads_in ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wads_in
-  (wad_in_serial_id BIGSERIAL UNIQUE
+  (wad_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
   ,wad_id BYTEA PRIMARY KEY CHECK (LENGTH(wad_id)=24)
   ,origin_exchange_url TEXT NOT NULL
   ,amount_val INT8 NOT NULL
   ,amount_frac INT4 NOT NULL
   ,arrival_time INT8 NOT NULL
   ,UNIQUE (wad_id, origin_exchange_url)
-  ); -- partition by wad_id
+  )
+  PARTITION BY HASH (wad_id);
 COMMENT ON TABLE wads_in
   IS 'Incoming exchange-to-exchange wad wire transfers';
 COMMENT ON COLUMN wads_in.wad_id
@@ -1749,9 +1952,33 @@ COMMENT ON COLUMN wads_in.amount_val
 COMMENT ON COLUMN wads_in.arrival_time
   IS 'Time when the wad was received';
 
+CREATE TABLE IF NOT EXISTS wads_in_default
+  PARTITION OF wads_in
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wads_in_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE wads_in_' || partition_suffix || ' '
+      'ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_in_serial_id_key '
+        'UNIQUE (wad_in_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_wads_in_partition('default');
+
+
+-- ------------------------------ wads_in_entries ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS wad_in_entries
-  (wad_in_entry_serial_id BIGSERIAL UNIQUE
-  ,wad_in_serial_id INT8 REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE
+  (wad_in_entry_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY --UNIQUE
+  ,wad_in_serial_id INT8 -- REFERENCES wads_in (wad_in_serial_id) ON DELETE CASCADE
   ,reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
   ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
   ,h_contract BYTEA NOT NULL CHECK(LENGTH(h_contract)=64)
@@ -1765,7 +1992,8 @@ CREATE TABLE IF NOT EXISTS wad_in_entries
   ,deposit_fees_frac INT4 NOT NULL
   ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
   ,purse_sig BYTEA NOT NULL CHECK (LENGTH(purse_sig)=64)
-  ); -- partition by purse or reserve? likely need both (so extra table?)
+  )
+  PARTITION BY HASH (purse_pub);
 COMMENT ON TABLE wad_in_entries
   IS 'list of purses aggregated in a wad according to the sending exchange';
 COMMENT ON COLUMN wad_in_entries.wad_in_serial_id
@@ -1790,15 +2018,36 @@ COMMENT ON COLUMN wad_in_entries.reserve_sig
   IS 'Signature by the receiving reserve, of purpose TALER_SIGNATURE_ACCOUNT_MERGE';
 COMMENT ON COLUMN wad_in_entries.purse_sig
   IS 'Signature by the purse of purpose TALER_SIGNATURE_PURSE_MERGE';
-CREATE INDEX IF NOT EXISTS wad_in_entries_wad_in_serial
-  ON wad_in_entries (wad_in_serial_id);
+-- FIXME: convert to materialized index!
 CREATE INDEX IF NOT EXISTS wad_in_entries_reserve_pub
   ON wad_in_entries (reserve_pub);
-COMMENT ON INDEX wad_in_entries_wad_in_serial
-  IS 'needed to lookup all transfers associated with a wad';
 COMMENT ON INDEX wad_in_entries_reserve_pub
   IS 'needed to compute reserve history';
 
+CREATE TABLE IF NOT EXISTS wad_in_entries_default
+  PARTITION OF wad_in_entries
+  FOR VALUES WITH (MODULUS 1, REMAINDER 0);
+
+CREATE OR REPLACE FUNCTION add_constraints_to_wad_in_entries_partition(
+  IN partition_suffix VARCHAR
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+BEGIN
+  EXECUTE FORMAT (
+    'ALTER TABLE wad_in_entries_' || partition_suffix || ' '
+      'ADD CONSTRAINT wad_in_entries_' || partition_suffix || '_wad_in_entry_serial_id_key '
+        'UNIQUE (wad_in_entry_serial_id) '
+  );
+END
+$$;
+
+SELECT add_constraints_to_wad_in_entries_partition('default');
+
+
+-- ------------------------------ partner_accounts ----------------------------------------
+
 CREATE TABLE IF NOT EXISTS partner_accounts
   (payto_uri VARCHAR PRIMARY KEY
   ,partner_serial_id INT8 REFERENCES partners(partner_serial_id) ON DELETE CASCADE
@@ -1817,8 +2066,9 @@ COMMENT ON COLUMN partner_accounts.last_seen
   IS 'Last time we saw this account as being active at the partner exchange. Used to select the most recent entry, and to detect when we should check again.';
 
 
--- Stored procedures
-
+---------------------------------------------------------------------------
+--                      Stored procedures
+---------------------------------------------------------------------------
 
 CREATE OR REPLACE FUNCTION exchange_do_withdraw(
   IN cs_nonce BYTEA,
@@ -2419,9 +2669,6 @@ THEN
   END IF;
 END IF;
 
-
-
-
 -- Everything fine, return success!
 out_balance_ok=TRUE;
 out_noreveal_index=in_noreveal_index;
@@ -2492,7 +2739,6 @@ THEN
   RETURN;
 END IF;
 
-
 INSERT INTO refunds
   (deposit_serial_id
   ,shard
@@ -2542,7 +2788,6 @@ THEN
   RETURN;
 END IF;
 
-
 IF out_gone
 THEN
   -- money already sent to the merchant. Tough luck.
@@ -2552,8 +2797,6 @@ THEN
   RETURN;
 END IF;
 
-
-
 -- Check refund balance invariant.
 SELECT
    SUM(amount_with_fee_val) -- overflow here is not plausible
@@ -2629,6 +2872,8 @@ END $$;
 --  IS 'Executes a refund operation, checking that the corresponding deposit was sufficient to cover the refunded amount';
 
 
+
+
 CREATE OR REPLACE FUNCTION exchange_do_recoup_to_reserve(
   IN in_reserve_pub BYTEA,
   IN in_reserve_out_serial_id INT8,
@@ -3090,6 +3335,9 @@ BEGIN
 END $$;
 
 
+-------------------------------------------------------------
+--                   THE END
+-------------------------------------------------------------
 
 -- Complete transaction
 COMMIT;
-- 
cgit v1.2.3