From 52c1eed3aae99b2a41bad34e46eaa55fc5895b70 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc" <kop@karlpinc.com>
Date: Thu, 14 Sep 2023 09:54:11 -0500
Subject: [PATCH] Set the default transaction isolation to serializable

---
 doc/src/architecture.m4 | 17 +++++++++++++++++
 doc/src/epilog.inc.m4   |  3 +++
 make_files/make_db.mk   | 14 +++++++++++++-
 3 files changed, 33 insertions(+), 1 deletion(-)

diff --git a/doc/src/architecture.m4 b/doc/src/architecture.m4
index bc8f749..c22f043 100644
--- a/doc/src/architecture.m4
+++ b/doc/src/architecture.m4
@@ -23,6 +23,12 @@ sdb_generated_rst()dnl
 System Architecture
 ===================
 
+Primary importance is placed on data integrity.  The system is optimized
+for data integrity rather than maximal performance.\ [#f1]_
+
+The expectation is that the database will be read more often than written
+and is configured with that in mind.\ [#f2]_
+
 .. toctree::
    :maxdepth: 3
 
@@ -31,3 +37,14 @@ System Architecture
    architecture/timestamps.rst
    architecture/users.rst
    architecture/schemas.rst
+
+
+.. rubric:: Footnotes
+
+.. [#f1] Among other choices of configuration, SokweDB ensures that
+         concurrent database updates by different users will not lead
+         to data inconsistency by setting the `transaction isolation`_
+         to ``serializable``.
+
+.. [#f2] In particular, many indexes exist.  This speeds query results
+         but slows database writes.
diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4
index 15186b3..94a2ce8 100644
--- a/doc/src/epilog.inc.m4
+++ b/doc/src/epilog.inc.m4
@@ -33,6 +33,9 @@ sdb_generated_rst()dnl
 .. _date and time types:
    https://www.postgresql.org/docs/current/datatype-datetime.html
 
+.. _transaction isolation:
+   https://www.postgresql.org/docs/current/transaction-iso.html
+
 .. _UUID: https://en.wikipedia.org/wiki/Universally_unique_identifier
 
 .. _technical debt: https://en.wiktionary.org/wiki/technical_debt
diff --git a/make_files/make_db.mk b/make_files/make_db.mk
index 8f33d79..a4ca1fb 100644
--- a/make_files/make_db.mk
+++ b/make_files/make_db.mk
@@ -526,7 +526,9 @@ CREATE_DB := db/run_createdb.sh
 SET_SEARCH_PATH := ALTER DATABASE $(TARGET_DB) \
                      SET search_path = sokwedb, lookup, upload, "$$user";
 SET_DATESTYLE := ALTER DATABASE $(TARGET_DB) \
-                     SET DATESTYLE =  'ISO,MDY';
+                     SET DATESTYLE = 'ISO,MDY';
+SET_ISOLATION_LEVEL := ALTER DATABASE $(TARGET_DB) \
+                           SET transaction_isolation = serializable;
 CREATE_SUPERUSER := CREATE ROLE $(TARGET_ROLE) LOGIN SUPERUSER \
                     PASSWORD '$(NEW_PASSWORD)';
 CREATE_ADMINUSER := CREATE ROLE $(TARGET_ROLE) LOGIN CREATEDB CREATEROLE \
@@ -584,6 +586,7 @@ init-database: $(GENERATED_SHELL) $(PSQL_DEPENDS)
                 exit 1 ; }
 	$(CREATE_DB)
 	( $(PSQL_SETUP) \
+	  printf "$(SET_ISOLATION_LEVEL)\n" ; \
 	  printf '$(SET_SEARCH_PATH)\n' ; \
 	  printf "$(SET_DATESTYLE)\n" ; \
 	  db/set_timezone.sh ; \
@@ -662,6 +665,15 @@ set-datestyle: $(PSQL_DEPENDS)
 	  ) \
 	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
 
+##   set-isolation      Set the default transaction_isolation_level in the
+##                      database named by the TARGET_DB variable.
+.PHONY: set-isolation
+set-isolation: $(PSQL_DEPENDS)
+	( $(PSQL_SETUP) \
+	  printf "$(SET_ISOLATION_LEVEL)\n" ; \
+	  ) \
+	  | psql $(PSQL_ARGS) $(PSQL_SINGLE_TRANS) --set=ON_ERROR_STOP=y
+
 ##   set-timezone       Set the default TimeZone in the database named
 ##                      by the TARGET_DB variable.
 .PHONY: set-timezone
-- 
2.34.1