changeset 1016:02e04288bbeb

pg-transactions
author Alain Mazy <am@osimis.io>
date Fri, 19 Jan 2024 15:53:17 +0100
parents f263cd65a754
children 3f3a1b54a839
files Sphinx/source/developers/db-versioning.rst Sphinx/source/plugins/postgresql.rst
diffstat 2 files changed, 55 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/Sphinx/source/developers/db-versioning.rst	Fri Jan 19 09:04:37 2024 +0100
+++ b/Sphinx/source/developers/db-versioning.rst	Fri Jan 19 15:53:17 2024 +0100
@@ -40,6 +40,11 @@
 *Note 2:* Starting with Orthanc 0.9.5, the option ``--upgrade`` must be provided
 on the command-line to allow Orthanc to upgrade the database schema.
 
+*Note 3:* Each DB plugin like (:ref:`PostgreSQL <postgresql>`,
+:ref:`MySQL <mysql>` or :ref:`ODBC <odbc>`) also maintains its own *revision*
+number.  The DB full version information is therefore the combination
+of the *version* and the *revision*.
+
 
 Early versions (pre-0.3.0)
 --------------------------
--- a/Sphinx/source/plugins/postgresql.rst	Fri Jan 19 09:04:37 2024 +0100
+++ b/Sphinx/source/plugins/postgresql.rst	Fri Jan 19 15:53:17 2024 +0100
@@ -132,7 +132,8 @@
       "EnableSsl" : false,               // New in release 3.0
       "MaximumConnectionRetries" : 10,   // New in release 3.0
       "ConnectionRetryInterval" : 5,     // New in release 3.0
-      "IndexConnectionsCount" : 1        // New in release 4.0
+      "IndexConnectionsCount" : 1,       // New in release 4.0
+      "TransactionMode": "SERIALIZABLE"  // New in beta version (not released yet)
     },
     "Plugins" : [
       "/home/user/orthanc-databases/BuildPostgreSQL/libOrthancPostgreSQLIndex.so",
@@ -244,6 +245,11 @@
 * ``ConnectionRetryInterval`` is only used when opening one database
   connection to PostgreSQL.
 
+* ``TransactionMode`` has been added in the ``pg-transactions`` beta version only.  2 values are
+  allowed: ``SERIALIZABLE`` (that has always been the default mode for Orthanc)
+  and ``READ COMMITTED`` that is available only from this beta version.  See
+  below.
+
 * The PostgreSQL plugin supports the :ref:`revision mechanism
   <revisions>` to protect metadata and attachments from concurrent
   modifications.
@@ -310,6 +316,49 @@
 When configuring your PostgreSQL plugin, ensure you've read the :ref:`scalability section 
 <scalability>`
 
+Transaction modes (``pg-transactions`` beta version only)
+^^^^^^^^^^^^^^^^^
+
+.. highlight:: json
+
+Starting from the current beta version, orthanc supports 2 transaction modes that 
+can be configured in the ``TransactionMode`` configuration of the ``PostgreSQL`` plugin:
+
+- ``SERIALIZABLE`` in which all write transactions are serialized which might lead
+  to performance bottlenecks when lots of threads or Orthanc instances are trying
+  to write to the same Database.
+- ``READ COMIITED`` that allows multiple threads or Orthanc instances to write at the
+  same time to the same Database.
+
+*Remark:* This feature is only available in a beta version of both Orthanc and the
+PostgreSQL plugin (``pg-transactions`` branches in the code).  This beta version is
+only available in the ``osimis/orthanc:pg-transactions-unstable`` Docker image.
+
+*Remark:* This beta version is really a beta version and **should not be used on a 
+production database**.  It shall only be used on a DB that you can delete or recover.
+The reason why it shall not be used on a production database is because this revision
+modifies the DB schema to a :ref:`version <db-versioning>`/revision ``6.2`` that might be different from the
+final schema of the future release although it will share the same version/revision.
+We can not guarantee to maintain migration scripts from this temporary schema to the final
+one.
+
+Upgrades/Downgrades (``pg-transactions`` beta version only)
+^^^^^^^^^^^^^^^^^
+
+New vesions of the PostgreSQL might modify the DB schema by adding new columns/tables/triggers.
+Upgrades from one revision to the other is always automatic.
+
+However, if, for some reasons, you would like to reinstall a previous plugin version, the
+older plugin might refuse to start because the revision is newer and unknown to it.
+
+Starting from this beta version, we are providing a downgrade script in case you want, e.g,
+to reinstall Orthanc 1.12.2 and PostgreSQL 5.1 (DB schema revision 6.1).
+
+To downgrade from the beta to the PostgreSQL 5.1, one might run this procedure::
+
+  $ wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/pg-transactions/PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql
+  $ psql -U postgres -f V6.2ToV6.1.sql
+
 
 Troubleshooting
 ---------------