Mercurial > hg > orthanc-databases

changeset 594:14ba6a6f633f find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
ChildrenIndex2 creation now compatible with PG 9
author Alain Mazy <am@orthanc.team>
date Fri, 15 Nov 2024 11:11:05 +0100 (9 months ago)
parents 0ecf0f9558a6
children 272eeb046a88
files PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql
diffstat 5 files changed, 43 insertions(+), 10 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt	Fri Nov 08 16:24:24 2024 +0100
+++ b/PostgreSQL/CMakeLists.txt	Fri Nov 15 11:11:05 2024 +0100
@@ -92,6 +92,7 @@
   POSTGRESQL_PREPARE_INDEX           ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql
   POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql
   POSTGRESQL_UPGRADE_REV1_TO_REV2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql
+  POSTGRESQL_UPGRADE_REV2_1_REPLACE_CHILDREN_INDEX    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql
   )
 
 
--- a/PostgreSQL/NEWS	Fri Nov 08 16:24:24 2024 +0100
+++ b/PostgreSQL/NEWS	Fri Nov 15 11:11:05 2024 +0100
@@ -1,4 +1,4 @@
-Pending changes in the mainline
+Pending changes in the mainline (future 7.0)
 ===============================
 
 DB schema revision: 2
@@ -7,14 +7,16 @@
 Minimum Orthanc runtime: 1.12.3
 Optimal Orthanc runtime: 1.12.5+
 
+Minimal Postgresql Server version: 9
+Optimal Postgresql Server version: 11+
+
 * WARNING: An Index is being replaced to improve performance.  The creation
   of the new index can take some time (we have observed 3 minutes on a
   DB with 70M instances).  Orthanc will not be available during the
   creation of this index.  If needed, you can create it manually before installing
   the new plugin by executing these SQL commands:
 
-    CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId);  -- new in rev 3
-    DROP INDEX IF EXISTS ChildrenIndex;  -- till rev 2; replaced by ChildrenIndex in rev 3 but no need to uninstall ChildrenIndex2 it when downgrading
+    https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql
 
 
 * Fix updates from plugin version 3.3 to latest version
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Nov 08 16:24:24 2024 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Nov 15 11:11:05 2024 +0100
@@ -228,13 +228,13 @@
 
           if (!t.GetDatabaseTransaction().DoesIndexExist("ChildrenIndex2"))
           {
-            // apply all idempotent changes that are in the PrepareIndex.  In this case, we are just interested by
-            // ChildrenIndex2 that does not need to be uninstalled in case of downgrade.
-            ApplyPrepareIndex(t, manager);
+            LOG(WARNING) << "Installing ChildrenIndex2";
+
+            std::string query;
 
-            // delete old index
-            DatabaseManager::StandaloneStatement statement(manager, "DROP INDEX IF EXISTS ChildrenIndex");
-            statement.Execute();
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_1_REPLACE_CHILDREN_INDEX);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
           }
 
           // If you add new tests here, update the test in the "ReadOnly" code below
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Fri Nov 08 16:24:24 2024 +0100
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Fri Nov 15 11:11:05 2024 +0100
@@ -106,7 +106,22 @@
         PRIMARY KEY(server, property)
         );
 
-CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId);  -- new in rev 3
+-- new ChildrenIndex2 introduced in v 7.0 (replacing previous ChildrenIndex)
+DO $$
+DECLARE
+    pg_version text;
+BEGIN
+    SELECT version() INTO pg_version;
+
+    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
+        -- PostgreSQL 11 or later
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
+    ELSE
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
+    END IF;
+END $$;
+
+
 CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
 CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
 CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql	Fri Nov 15 11:11:05 2024 +0100
@@ -0,0 +1,15 @@
+DO $$
+DECLARE
+    pg_version text;
+BEGIN
+    SELECT version() INTO pg_version;
+
+    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
+        -- PostgreSQL 11 or later
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
+    ELSE
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
+    END IF;
+END $$;
+
+DROP INDEX IF EXISTS ChildrenIndex;  -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading
\ No newline at end of file