add p2p tables to sharding logic

1 files changed, 22 insertions, 300 deletions

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');