From af06ddc8e833d6f4bbfd49585a750227b05c056d Mon Sep 17 00:00:00 2001
From: Christian Grothoff <christian@grothoff.org>
Date: Wed, 11 Jan 2023 16:43:22 +0100
Subject: -add a few missing indices/foreign key constraints

---
 src/exchangedb/0002-history_requests.sql | 27 ++++++++++++++++++++++++++-
 1 file changed, 26 insertions(+), 1 deletion(-)

(limited to 'src/exchangedb/0002-history_requests.sql')

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);
-- 
cgit v1.2.3