add p2p tables to sharding logic

4 files changed, 860 insertions, 300 deletions

diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
index 23b50d25d..01e68dfb5 100644
--- a/src/exchangedb/common-0001.sql
+++ b/src/exchangedb/common-0001.sql
@@ -1136,7 +1136,560 @@ BEGIN
 END
 $$;
 
+--------------------------------------------------------------------------
+--                        Tables for P2P payments
+--------------------------------------------------------------------------
+
+------------------------------- purse_requests ----------------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_purse_requests(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'purse_requests';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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'
+      ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)'
+      ',age_limit INT4 NOT NULL'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ',balance_val INT8 NOT NULL DEFAULT (0)'
+      ',balance_frac INT4 NOT NULL DEFAULT (0)'
+      ',purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)'
+      ',PRIMARY KEY (purse_pub)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by marge_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_merge_pub '
+    'ON ' || table_name || ' '
+    '(merge_pub);'
+  );
+
+END
+$$;
+
+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
+$$;
+
+---------------------------- purse_merges -----------------------------
+
+CREATE OR REPLACE FUNCTION create_table_purse_merges(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'purse_merges';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by reserve_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub '
+    'ON ' || table_name || ' '
+    '(reserve_pub);'
+  );
+  EXECUTE FORMAT (
+    'COMMENT ON INDEX ' || table_name || '_reserve_pub '
+    'IS ' || quote_literal('needed in reserve history computation') || ';'
+  );
+
+END
+$$;
+
+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
+$$;
+
+------------------------- account_merges ----------------------------
+
+CREATE OR REPLACE FUNCTION create_table_account_merges(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'account_merges';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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 (purse_pub)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by reserve_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub '
+    'ON ' || table_name || ' '
+    '(reserve_pub);'
+  );
+
+END
+$$;
+
+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
+$$;
+
+------------------------- contracts -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_contracts(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'contracts';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+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
+$$;
+
+--------------------------- history_requests --------------------------
+
+
+CREATE OR REPLACE FUNCTION create_table_history_requests(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'history_requests';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE
+      ',request_timestamp INT8 NOT NULL'
+      ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
+      ',history_fee_val INT8 NOT NULL'
+      ',history_fee_frac INT4 NOT NULL'
+      ',PRIMARY KEY (reserve_pub,request_timestamp)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (reserve_pub)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+--------------------------- close_requests ---------------------------
+
+CREATE OR REPLACE FUNCTION create_table_close_requests(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'close_requests';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE
+      ',close_timestamp INT8 NOT NULL'
+      ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
+      ',close_val INT8 NOT NULL'
+      ',close_frac INT4 NOT NULL'
+      ',PRIMARY KEY (reserve_pub,close_timestamp)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (reserve_pub)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+------------------------------- purse_deposits -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_purse_deposits(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'purse_deposits';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by coin_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_coin_pub '
+    'ON ' || table_name || ' '
+    '(coin_pub);'
+  );
+
+END
+$$;
+
+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
+$$;
+
+---------------------------- wads_out -------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wads_out(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'wads_out';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (wad_id)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+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
+$$;
+
+--------------------------- wad_out_entries --------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wad_out_entries(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'wad_out_entries';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+      ',purse_expiration INT8 NOT NULL'
+      ',merge_timestamp INT8 NOT NULL'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ',wad_fee_val INT8 NOT NULL'
+      ',wad_fee_frac INT4 NOT NULL'
+      ',deposit_fees_val INT8 NOT NULL'
+      ',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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by reserve_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_reserve_pub '
+    'ON ' || table_name || ' '
+    '(reserve_pub);'
+  );
+
+END
+$$;
+
+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
+$$;
+
+-------------------------- wads_in --------------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wads_in(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'wads_in';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (wad_id)'
+    ,shard_suffix
+  );
+
+END
+$$;
+
+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) '
+      ',ADD CONSTRAINT wads_in_' || partition_suffix || '_wad_is_origin_exchange_url_key '
+        'UNIQUE (wad_id, origin_exchange_url) '
+  );
+END
+$$;
+
+
+------------------------- wads_in_entries --------------------------
+
+CREATE OR REPLACE FUNCTION create_table_wad_in_entries(
+  IN shard_suffix VARCHAR DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR DEFAULT 'wad_in_entries';
+BEGIN
+
+  PERFORM create_partitioned_table(
+    'CREATE TABLE IF NOT EXISTS %I '
+      '(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)'
+      ',purse_expiration INT8 NOT NULL'
+      ',merge_timestamp INT8 NOT NULL'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ',wad_fee_val INT8 NOT NULL'
+      ',wad_fee_frac INT4 NOT NULL'
+      ',deposit_fees_val INT8 NOT NULL'
+      ',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)'
+    ') %s ;'
+    ,table_name
+    ,'PARTITION BY HASH (purse_pub)'
+    ,shard_suffix
+  );
+
+  table_name = concat_ws('_', table_name, shard_suffix);
+
+  -- FIXME: change to materialized index by reserve_pub!
+  EXECUTE FORMAT (
+    'CREATE INDEX IF NOT EXISTS ' || table_name || '_reserve_pub '
+    'ON ' || table_name || ' '
+    '(reserve_pub);'
+  );
+  EXECUTE FORMAT (
+    'COMMENT ON INDEX ' || table_name || '_reserve_pub '
+    'IS ' || quote_literal('needed in reserve history computation') || ';'
+  );
+
+END
+$$;
+
+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
+$$;
+
+-------------------------------------------------------------------
 ------------------------- Partitions ------------------------------
+-------------------------------------------------------------------
 
 CREATE OR REPLACE FUNCTION create_hash_partition(
     source_table_name VARCHAR
@@ -1250,6 +1803,35 @@ BEGIN
   ALTER TABLE IF EXISTS cs_nonce_locks
     DETACH partition cs_nonce_locks_default;
 
+  ALTER TABLE IF EXISTS purse_requests
+    DETACH partition purse_requests_default;
+
+  ALTER TABLE IF EXISTS purse_merges
+    DETACH partition purse_merges_default;
+  
+  ALTER TABLE IF EXISTS account_merges
+    DETACH partition account_merges_default;
+
+  ALTER TABLE IF EXISTS contracts
+    DETACH partition contracts_default;
+
+  ALTER TABLE IF EXISTS history_requests
+    DETACH partition history_requests_default;
+  
+  ALTER TABLE IF EXISTS close_requests
+    DETACH partition close_requests_default;
+  
+  ALTER TABLE IF EXISTS purse_deposits
+    DETACH partition purse_deposits_default;
+
+  ALTER TABLE IF EXISTS wad_out_entries
+    DETACH partition wad_out_entries_default;
+
+  ALTER TABLE IF EXISTS wads_in
+    DETACH partition wads_in_default;
+
+  ALTER TABLE IF EXISTS wad_in_entries
+    DETACH partition wad_in_entries_default;
 END
 $$;
 
@@ -1289,6 +1871,17 @@ BEGIN
   DROP TABLE IF EXISTS prewire_default;
   DROP TABLE IF EXISTS cs_nonce_locks_default;
 
+  DROP TABLE IF EXISTS purse_requests_default;
+  DROP TABLE IF EXISTS purse_merges_default;
+  DROP TABLE IF EXISTS account_merges_default;
+  DROP TABLE IF EXISTS contracts_default;
+  DROP TABLE IF EXISTS history_requests_default;
+  DROP TABLE IF EXISTS close_requests_default;
+  DROP TABLE IF EXISTS purse_deposits_default;
+  DROP TABLE IF EXISTS wad_out_entries_default;
+  DROP TABLE IF EXISTS wads_in_default;
+  DROP TABLE IF EXISTS wad_in_entries_default;
+
 END
 $$;
 
@@ -1481,6 +2074,76 @@ BEGIN
     );
     PERFORM add_constraints_to_cs_nonce_locks_partition(num_partitions::varchar);
 
+    ---------------- P2P ----------------------
+
+    PERFORM create_hash_partition(
+      'purse_requests'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_purse_requests_partition(num_partitions::varchar);
+
+    PERFORM create_hash_partition(
+      'purse_merges'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_purse_merges_partition(num_partitions::varchar);
+
+    PERFORM create_hash_partition(
+      'account_merges'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_account_merges_partition(num_partitions::varchar);
+    
+    PERFORM create_hash_partition(
+      'contracts'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_contracts_partition(num_partitions::varchar);
+
+    PERFORM create_hash_partition(
+      'history_requests'
+      ,modulus
+      ,num_partitions
+    );
+
+    PERFORM create_hash_partition(
+      'close_requests'
+      ,modulus
+      ,num_partitions
+    );
+
+    PERFORM create_hash_partition(
+      'purse_deposits'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_purse_deposits_partition(num_partitions::varchar);
+
+    PERFORM create_hash_partition(
+      'wad_out_entries'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_wad_out_entries_partition(num_partitions::varchar);
+
+    PERFORM create_hash_partition(
+      'wads_in'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_wads_in_partition(num_partitions::varchar);
+    
+    PERFORM create_hash_partition(
+      'wad_in_entries'
+      ,modulus
+      ,num_partitions
+    );
+    PERFORM add_constraints_to_wad_in_entries_partition(num_partitions::varchar);
+
     num_partitions=num_partitions-1;
     EXIT WHEN num_partitions=0;
 
@@ -1627,6 +2290,22 @@ BEGIN
     DROP CONSTRAINT IF EXISTS cs_nonce_locks_pkey CASCADE
   ;
 
+  ALTER TABLE IF EXISTS purse_requests
+    DROP CONSTRAINT IF EXISTS purse_requests_reserve_pub_request_timestamp_pkey
+  ;
+
+  ALTER TABLE IF EXISTS close_requests
+    DROP CONSTRAINT IF EXISTS close_requests_reserve_pub_close_timestamp_pkey
+  ;
+
+  ALTER TABLE IF EXISTS purse_deposits
+    DROP CONSTRAINT IF EXISTS purse_deposits_purse_pub_coin_pub_pkey
+  ;
+
+  ALTER TABLE IF EXISTS wads_in
+    DROP CONSTRAINT IF EXISTS wads_in_wad_id_origin_exchange_url_key CASCADE
+  ;
+
 END
 $$;
   
@@ -1832,6 +2511,79 @@ BEGIN
     ,local_user
   );
 
+  ------------------- P2P --------------------
+
+  PERFORM create_foreign_hash_partition(
+    'purse_requests'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'purse_merges'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'account_merges'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'contracts'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'history_requests'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'close_requests'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'purse_deposits'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'wad_out_entries'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'wads_in'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+  PERFORM create_foreign_hash_partition(
+    'wad_in_entries'
+    ,total_num_shards
+    ,shard_suffix
+    ,current_shard_num
+    ,local_user
+  );
+
 END
 $$;
 
diff --git a/src/exchangedb/drop-common.sql b/src/exchangedb/drop-common.sql
index 5c4bddfdb..b14a00c82 100644
--- a/src/exchangedb/drop-common.sql
+++ b/src/exchangedb/drop-common.sql
@@ -61,6 +61,25 @@ DROP FUNCTION IF EXISTS create_table_reserves;
 DROP FUNCTION IF EXISTS create_table_cs_nonce_locks;
 DROP FUNCTION IF EXISTS add_constraints_to_cs_nonce_locks_partition;
 
+DROP FUNCTION IF EXISTS create_table_purse_requests;
+DROP FUNCTION IF EXISTS add_constraints_to_purse_requests_partition;
+DROP FUNCTION IF EXISTS create_table_purse_merges;
+DROP FUNCTION IF EXISTS add_constraints_to_purse_merges_partition;
+DROP FUNCTION IF EXISTS create_table_account_merges;
+DROP FUNCTION IF EXISTS add_constraints_to_account_merges_partition;
+DROP FUNCTION IF EXISTS create_table_contracts;
+DROP FUNCTION IF EXISTS add_constraints_to_contracts_partition;
+DROP FUNCTION IF EXISTS create_table_history_requests;
+DROP FUNCTION IF EXISTS create_table_close_requests;
+DROP FUNCTION IF EXISTS create_table_purse_deposits;
+DROP FUNCTION IF EXISTS add_constraints_to_purse_deposits_partition;
+DROP FUNCTION IF EXISTS create_table_wad_out_entries;
+DROP FUNCTION IF EXISTS add_constraints_to_wad_out_entries_partition;
+DROP FUNCTION IF EXISTS create_table_wads_in;
+DROP FUNCTION IF EXISTS add_constraints_to_wads_in_partition;
+DROP FUNCTION IF EXISTS create_table_wad_in_entries;
+DROP FUNCTION IF EXISTS add_constraints_to_wad_in_entries_partition;
+
 DROP FUNCTION IF EXISTS create_partitioned_table;
 DROP FUNCTION IF EXISTS create_hash_partition;
 DROP FUNCTION IF EXISTS create_range_partition;
diff --git a/src/exchangedb/exchange-0001-part.sql b/src/exchangedb/exchange-0001-part.sql
index a478a0d16..b35348235 100644
--- a/src/exchangedb/exchange-0001-part.sql
+++ b/src/exchangedb/exchange-0001-part.sql
@@ -991,21 +991,8 @@ COMMENT ON COLUMN partners.master_sig
 
 -- ------------------------------ purse_requests ----------------------------------------
 
-CREATE TABLE IF NOT EXISTS purse_requests
-  (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
-  ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
-  ,age_limit INT4 NOT NULL
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  ,balance_val INT8 NOT NULL DEFAULT (0)
-  ,balance_frac INT4 NOT NULL DEFAULT (0)
-  ,purse_sig BYTEA NOT NULL CHECK(LENGTH(purse_sig)=64)
-  ,PRIMARY KEY (purse_pub)
-  )
-  PARTITION BY HASH (purse_pub);
+SELECT create_table_purse_requests();
+
 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
@@ -1021,45 +1008,18 @@ 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: 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 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 HASH (purse_pub);
+SELECT create_table_purse_merges();
+
 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
@@ -1073,47 +1033,17 @@ COMMENT ON COLUMN purse_merges.merge_sig
 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 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 (purse_pub)
-  )
-  PARTITION BY HASH (purse_pub);
+SELECT create_table_account_merges();
+
 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_merges.reserve_pub
@@ -1123,48 +1053,17 @@ COMMENT ON COLUMN account_merges.purse_pub
 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_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 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 HASH (purse_pub);
+SELECT create_table_contracts();
+
 COMMENT ON TABLE contracts
   IS 'encrypted contracts associated with purses';
 COMMENT ON COLUMN contracts.purse_pub
@@ -1178,35 +1077,13 @@ 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
-  ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
-  ,history_fee_val INT8 NOT NULL
-  ,history_fee_frac INT4 NOT NULL
-  ,PRIMARY KEY (reserve_pub,request_timestamp)
-  )
-  PARTITION BY HASH (reserve_pub);
+SELECT create_table_history_requests();
+
 COMMENT ON TABLE history_requests
   IS 'Paid history requests issued by a client against a reserve';
 COMMENT ON COLUMN history_requests.request_timestamp
@@ -1220,18 +1097,10 @@ 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
-  ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
-  ,close_val INT8 NOT NULL
-  ,close_frac INT4 NOT NULL
-  ,PRIMARY KEY (reserve_pub,close_timestamp)
-  )
-  PARTITION BY HASH (reserve_pub);
+SELECT create_table_close_requests();
+
 COMMENT ON TABLE close_requests
   IS 'Explicit requests by a reserve owner to close a reserve immediately';
 COMMENT ON COLUMN close_requests.close_timestamp
@@ -1248,17 +1117,8 @@ CREATE TABLE IF NOT EXISTS close_requests_default
 
 -- ------------------------------ purse_deposits ----------------------------------------
 
-CREATE TABLE IF NOT EXISTS purse_deposits
-  (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 HASH (purse_pub);
+SELECT create_table_purse_deposits();
+
 COMMENT ON TABLE purse_deposits
   IS 'Requests depositing coins into a purse';
 COMMENT ON COLUMN purse_deposits.partner_serial_id
@@ -1272,43 +1132,17 @@ 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 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 HASH (wad_id);
+SELECT create_table_wads_out();
+
 COMMENT ON TABLE wads_out
   IS 'Wire transfers made to another exchange to transfer purse funds';
 COMMENT ON COLUMN wads_out.wad_id
@@ -1320,54 +1154,17 @@ 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 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)
-  ,purse_expiration INT8 NOT NULL
-  ,merge_timestamp INT8 NOT NULL
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  ,wad_fee_val INT8 NOT NULL
-  ,wad_fee_frac INT4 NOT NULL
-  ,deposit_fees_val INT8 NOT NULL
-  ,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 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);
+SELECT create_table_wad_out_entries();
+
 COMMENT ON TABLE wad_out_entries
   IS 'Purses combined into a wad';
 COMMENT ON COLUMN wad_out_entries.wad_out_serial_id
@@ -1397,36 +1194,12 @@ 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 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 HASH (wad_id);
+SELECT create_table_wads_in();
+
 COMMENT ON TABLE wads_in
   IS 'Incoming exchange-to-exchange wad wire transfers';
 COMMENT ON COLUMN wads_in.wad_id
@@ -1442,44 +1215,13 @@ 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 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)
-  ,purse_expiration INT8 NOT NULL
-  ,merge_timestamp INT8 NOT NULL
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  ,wad_fee_val INT8 NOT NULL
-  ,wad_fee_frac INT4 NOT NULL
-  ,deposit_fees_val INT8 NOT NULL
-  ,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 HASH (purse_pub);
+SELECT create_table_wad_in_entries();
+
 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
@@ -1504,31 +1246,11 @@ 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';
--- 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_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');
 
 
diff --git a/src/exchangedb/shard-0001-part.sql b/src/exchangedb/shard-0001-part.sql
index d971b0c7a..aa912c058 100644
--- a/src/exchangedb/shard-0001-part.sql
+++ b/src/exchangedb/shard-0001-part.sql
@@ -89,6 +89,33 @@ BEGIN
   PERFORM create_table_cs_nonce_locks(shard_suffix);
   PERFORM add_constraints_to_cs_nonce_locks_partition(shard_suffix);
 
+  PERFORM create_table_purse_requests(shard_suffix);
+  PERFORM add_constraints_to_purse_requests_partition(shard_suffix);
+
+  PERFORM create_table_purse_merges(shard_suffix);
+  PERFORM add_constraints_to_purse_merges_partition(shard_suffix);
+
+  PERFORM create_table_account_merges(shard_suffix);
+  PERFORM add_constraints_to_account_merges_partition(shard_suffix);
+
+  PERFORM create_table_contracts(shard_suffix);
+  PERFORM add_constraints_to_contracts_partition(shard_suffix);
+
+  PERFORM create_table_history_requests(shard_suffix);
+
+  PERFORM create_table_close_requests(shard_suffix);
+
+  PERFORM create_table_purse_deposits(shard_suffix);
+  PERFORM add_constraints_to_purse_deposits_partition(shard_suffix);
+
+  PERFORM create_table_wad_out_entries(shard_suffix);
+  PERFORM add_constraints_to_wad_out_entries_partition(shard_suffix);
+
+  PERFORM create_table_wads_in(shard_suffix);
+  PERFORM add_constraints_to_wads_in_partition(shard_suffix);
+
+  PERFORM create_table_wad_in_entries(shard_suffix);
+  PERFORM add_constraints_to_wad_in_entries_partition(shard_suffix);
 END
 $$;
 
@@ -193,6 +220,46 @@ BEGIN
     'DROP TABLE IF EXISTS %I CASCADE'
     ,'cs_nonce_locks_' || shard_suffix
   );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'purse_requests_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'purse_merges_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'account_merges_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'contracts_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'history_requests_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'close_requests_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'purse_deposits_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'wad_out_entries_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'wads_in_' || shard_suffix
+  );
+  EXECUTE FORMAT(
+    'DROP TABLE IF EXISTS %I CASCADE'
+    ,'wad_in_entries_' || shard_suffix
+  );
 END
 $$;