Mercurial > hg > orthanc-databases

changeset 740:2382e6fab2b6 pg-next-699

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added a new pk column in 2 tables to enable pg_repack
author Alain Mazy <am@orthanc.team>
date Mon, 22 Sep 2025 11:02:20 +0200
parents 696f2dd60f4d
children 9097dd8235b8
files PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/Downgrades/Rev699ToRev6.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev699.sql
diffstat 6 files changed, 39 insertions(+), 3 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt	Wed Sep 17 14:41:20 2025 +0200
+++ b/PostgreSQL/CMakeLists.txt	Mon Sep 22 11:02:20 2025 +0200
@@ -95,7 +95,8 @@
   POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql
   POSTGRESQL_UPGRADE_REV3_TO_REV4    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev3ToRev4.sql
   POSTGRESQL_UPGRADE_REV4_TO_REV5    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev4ToRev5.sql
-  POSTGRESQL_UPGRADE_REV5_TO_REV6  ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev5ToRev6.sql
+  POSTGRESQL_UPGRADE_REV5_TO_REV6    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev5ToRev6.sql
+  POSTGRESQL_UPGRADE_REV6_TO_REV699  ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev6ToRev699.sql
   )
 
 
--- a/PostgreSQL/NEWS	Wed Sep 17 14:41:20 2025 +0200
+++ b/PostgreSQL/NEWS	Mon Sep 22 11:02:20 2025 +0200
@@ -1,3 +1,11 @@
+Pending changes in the mainline
+===============================
+
+* Added a new primary key column in the InvalidChildCounts and GlobalIntegersChanges
+  tables.  This new column is required for pg_repack to be able to reclaim space on
+  these tables.
+
+
 Release 9.0 (2025-08-13)
 ========================
 
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Sep 17 14:41:20 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Sep 22 11:02:20 2025 +0200
@@ -49,7 +49,7 @@
   static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
-#define CURRENT_DB_REVISION 6
+#define CURRENT_DB_REVISION 699
 
 namespace OrthancDatabases
 {
@@ -261,6 +261,19 @@
             currentRevision = 6;
           }
 
+          if (currentRevision == 6)
+          {
+            LOG(WARNING) << "Upgrading DB schema from revision 6 to revision 699";
+
+            std::string query;
+
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV6_TO_REV699);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+            hasAppliedAnUpgrade = true;
+            currentRevision = 699;
+          }
+
           if (hasAppliedAnUpgrade)
           {
             LOG(WARNING) << "Upgrading DB schema by applying PrepareIndex.sql";
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev699ToRev6.sql	Mon Sep 22 11:02:20 2025 +0200
@@ -0,0 +1,8 @@
+ALTER TABLE InvalidChildCounts DROP COLUMN pk;
+ALTER TABLE GlobalIntegersChanges DROP COLUMN pk;
+----------
+
+-- set the global properties that actually documents the DB version, revision and some of the capabilities
+-- modify only the ones that have changed
+DELETE FROM GlobalProperties WHERE property IN (4);
+INSERT INTO GlobalProperties VALUES (4, 6); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Sep 17 14:41:20 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Sep 22 11:02:20 2025 +0200
@@ -85,6 +85,7 @@
         );
 
 CREATE TABLE IF NOT EXISTS GlobalIntegers(
+       pk BIGSERIAL PRIMARY KEY,   -- new in rev699 required for pg_repack to be able to reclaim space
        key INTEGER PRIMARY KEY,
        value BIGINT);
 -- GlobalIntegers keys:
@@ -704,6 +705,7 @@
 -- At regular interval, the DB housekeeping thread updates the childCount column of
 -- resources with an entry in this table.
 CREATE TABLE IF NOT EXISTS InvalidChildCounts(
+    pk BIGSERIAL PRIMARY KEY,   -- new in rev699 required for pg_repack to be able to reclaim space
     id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
     updatedAt TIMESTAMP DEFAULT NOW());
 
@@ -854,7 +856,7 @@
 -- set the global properties that actually documents the DB version, revision and some of the capabilities
 DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14);
 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
-INSERT INTO GlobalProperties VALUES (4, 6); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (4, 699); -- GlobalProperty_DatabasePatchLevel
 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
 INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance  -- this is actually the 3rd version of HasCreateInstance
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev699.sql	Mon Sep 22 11:02:20 2025 +0200
@@ -0,0 +1,4 @@
+-- Adding a PK to these 2 table to allow pg_repack to process these tables, enabling reclaiming disk space and defragmenting the tables.
+
+ALTER TABLE InvalidChildCounts ADD COLUMN pk BIGSERIAL PRIMARY KEY;
+ALTER TABLE GlobalIntegersChanges ADD COLUMN pk BIGSERIAL PRIMARY KEY;
\ No newline at end of file