Mercurial > hg > orthanc-databases

changeset 749:97cfbcf1a35a pg-next-699

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged default -> pg-next-699
author Alain Mazy <am@orthanc.team>
date Thu, 09 Oct 2025 15:31:21 +0200
parents b31e78cf18eb (diff) 176e5dfbad43 (current diff)
children e7a353b083fb
files PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/PrepareIndex.sql
diffstat 6 files changed, 42 insertions(+), 11 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt	Wed Oct 08 14:47:15 2025 +0200
+++ b/PostgreSQL/CMakeLists.txt	Thu Oct 09 15:31:21 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 Oct 08 14:47:15 2025 +0200
+++ b/PostgreSQL/NEWS	Thu Oct 09 15:31:21 2025 +0200
@@ -24,6 +24,9 @@
 Maintenance:
 * Now verifying the DatabasePatchLevel (revision) in another transaction than
   the one that upgrades the schema.
+* 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 Oct 08 14:47:15 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Thu Oct 09 15:31:21 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
 {
@@ -268,6 +268,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	Thu Oct 09 15:31:21 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 Oct 08 14:47:15 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Thu Oct 09 15:31:21 2025 +0200
@@ -400,6 +400,7 @@
 -- These changes will be applied at regular interval by an external thread or when someone
 -- requests the statistics
 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
+    pk BIGSERIAL PRIMARY KEY,   -- new in rev699 required for pg_repack to be able to reclaim space
     key INTEGER,
     value BIGINT);
 
@@ -458,7 +459,7 @@
 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
 RETURNS TRIGGER AS $$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(new.resourceType + 2, 1);
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -466,7 +467,7 @@
 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
 RETURNS TRIGGER AS $$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(old.resourceType + 2, -1);
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -475,8 +476,8 @@
 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
 RETURNS TRIGGER AS $body$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(0, new.compressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(1, new.uncompressedSize);
   RETURN NULL;
 END;
 $body$ LANGUAGE plpgsql;
@@ -484,8 +485,8 @@
 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
 RETURNS TRIGGER AS $body$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(0, -old.compressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(1, -old.uncompressedSize);
   RETURN NULL;
 END;
 $body$ LANGUAGE plpgsql;
@@ -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());
 
@@ -756,7 +758,7 @@
     IF TG_OP = 'INSERT' THEN
 		IF new.parentId IS NOT NULL THEN
             -- mark the parent's childCount as invalid
-			INSERT INTO InvalidChildCounts VALUES(new.parentId);
+			INSERT INTO InvalidChildCounts (id) VALUES(new.parentId);
         END IF;
 	
     ELSIF TG_OP = 'DELETE' THEN
@@ -764,7 +766,7 @@
 		IF old.parentId IS NOT NULL THEN
             BEGIN
                 -- mark the parent's childCount as invalid
-                INSERT INTO InvalidChildCounts VALUES(old.parentId);
+                INSERT INTO InvalidChildCounts (id) VALUES(old.parentId);
             EXCEPTION
                 -- when deleting the last child of a parent, the insert will fail (this is expected)
                 WHEN foreign_key_violation THEN NULL;
@@ -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	Thu Oct 09 15:31:21 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