diff options
author | Christian Grothoff <christian@grothoff.org> | 2023-01-11 16:43:22 +0100 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2023-01-11 16:43:22 +0100 |
commit | af06ddc8e833d6f4bbfd49585a750227b05c056d (patch) | |
tree | 2934a0e9b5263e1cb7be0f960a6beec0ccd43964 /src/exchangedb/0002-history_requests.sql | |
parent | ce43a23ddd61fa1f1a91f8ba597bc88019d3b66c (diff) | |
download | exchange-af06ddc8e833d6f4bbfd49585a750227b05c056d.tar.xz |
-add a few missing indices/foreign key constraints
Diffstat (limited to 'src/exchangedb/0002-history_requests.sql')
-rw-r--r-- | src/exchangedb/0002-history_requests.sql | 27 |
1 files changed, 26 insertions, 1 deletions
diff --git a/src/exchangedb/0002-history_requests.sql b/src/exchangedb/0002-history_requests.sql index 5cd5c7b7b..853a435d4 100644 --- a/src/exchangedb/0002-history_requests.sql +++ b/src/exchangedb/0002-history_requests.sql @@ -26,7 +26,7 @@ DECLARE BEGIN PERFORM create_partitioned_table( 'CREATE TABLE %I ' - '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' ',request_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' @@ -64,6 +64,26 @@ BEGIN END $$; +CREATE FUNCTION constrain_table_history_requests( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + partition_name VARCHAR; +BEGIN + partition_name = concat_ws('_', 'history_requests', partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || partition_name || + ' ADD CONSTRAINT ' || partition_name || '_serial_id' + ' UNIQUE (history_request_serial_id)' + ); +END +$$; + + CREATE FUNCTION foreign_table_history_requests() RETURNS VOID LANGUAGE plpgsql @@ -94,6 +114,11 @@ INSERT INTO exchange_tables ,FALSE), ('history_requests' ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('history_requests' + ,'exchange-0002' ,'foreign' ,TRUE ,FALSE); |