add p2p tables to sharding logic

1 files changed, 752 insertions, 0 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
 $$;