Mercurial > hg > orthanc-databases
changeset 849:6177e8e6be50
merged pg-next-1099 -> default
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Fri, 18 Sep 2026 14:41:29 +0200 |
| parents | 2ef3d2ce4905 (diff) 3e4d8da97292 (current diff) |
| children | d8d0b7b0cc75 8e0c16a006c8 |
| files | PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/Downgrades/Rev1099ToRev10.sql PostgreSQL/Plugins/SQL/Downgrades/Rev11ToRev10.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev1099.sql PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev11.sql |
| diffstat | 8 files changed, 128 insertions(+), 133 deletions(-) [+] |
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt Thu Sep 17 16:36:20 2026 +0200 +++ b/PostgreSQL/CMakeLists.txt Fri Sep 18 14:41:29 2026 +0200 @@ -97,7 +97,7 @@ 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_REV6_TO_REV10 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev6ToRev10.sql - POSTGRESQL_UPGRADE_REV10_TO_REV1099 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev10ToRev1099.sql + POSTGRESQL_UPGRADE_REV10_TO_REV11 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev10ToRev11.sql )
--- a/PostgreSQL/NEWS Thu Sep 17 16:36:20 2026 +0200 +++ b/PostgreSQL/NEWS Fri Sep 18 14:41:29 2026 +0200 @@ -3,15 +3,10 @@ Changes: * Added an index on AttachedFiles uuids to speed-up the SDK calls - SetAttachmentCustomData and GetAttachmentCustomData + SetAttachmentCustomData and GetAttachmentCustomData that are used by, + notably, the advanced-storage plugin. * Small update of the "DeleteResource" function to avoid a warning when multiple clients are trying to delete the same resource at the same time. - Warning: - test_concurrent_uploads_same_study (Concurrency.test_concurrency.TestConcurrency.test_concurrent_uploads_same_study) ... 2025-12-05 08:04:24.133 UTC [73] ERROR: null value in column "resourcetype" of relation "deletedresources" violates not-null constraint - 2025-12-05 08:04:24.133 UTC [73] DETAIL: Failing row contains (null, null). - 2025-12-05 08:04:24.133 UTC [73] CONTEXT: SQL statement "INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId)" - PL/pgSQL function deleteresource(bigint) line 85 at SQL statement - 2025-12-05 08:04:24.133 UTC [73] STATEMENT: SELECT * FROM DeleteResource($1) Release 10.3 (2026-08-19)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Thu Sep 17 16:36:20 2026 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Sep 18 14:41:29 2026 +0200 @@ -49,7 +49,7 @@ static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4; } -#define CURRENT_DB_REVISION 1099 +#define CURRENT_DB_REVISION 11 namespace OrthancDatabases { @@ -285,15 +285,15 @@ if (currentRevision == 10) { - LOG(WARNING) << "Upgrading DB schema from revision 10 to revision 1099"; + LOG(WARNING) << "Upgrading DB schema from revision 10 to revision 11"; std::string query; Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV10_TO_REV1099); + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV10_TO_REV11); t.GetDatabaseTransaction().ExecuteMultiLines(query); hasAppliedAnUpgrade = true; - currentRevision = 1099; + currentRevision = 11; } if (hasAppliedAnUpgrade)
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev1099ToRev10.sql Thu Sep 17 16:36:20 2026 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,114 +0,0 @@ --- restore the old DeleteResource function - -CREATE OR REPLACE FUNCTION DeleteResource( - IN id BIGINT, - OUT remaining_ancestor_resource_type INTEGER, - OUT remaining_anncestor_public_id TEXT) AS $body$ -DECLARE - deleted_resource_row RECORD; - deleted_parent_row RECORD; - deleted_grand_parent_row RECORD; - deleted_grand_grand_parent_row RECORD; - locked_parent_row RECORD; - locked_resource_row RECORD; -BEGIN - SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping - -- note: temporary tables are created at connection level -> they are likely to exist. - -- These tables are used by the triggers - CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( - resourceType INTEGER NOT NULL, - publicId VARCHAR(64) NOT NULL - ); - RESET client_min_messages; - -- clear the temporary table in case it has been created earlier in the connection - DELETE FROM DeletedResources; - -- create/clear the DeletedFiles temporary table - PERFORM CreateDeletedFilesTemporaryTable(); - -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that - -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize - -- that they are deleting the last instance and the parent resources would not be deleted. - -- Locking only the immediate parent is sufficient to prevent from this. - SELECT * INTO locked_parent_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; - -- Before deleting the resource itself, we lock it to retrieve the resourceType and to make sure not 2 connections try to - -- delete it at the same time - SELECT * INTO locked_resource_row FROM resources WHERE internalid = id FOR UPDATE; - -- before delete the resource itself, we must delete its grand-grand-children, the grand-children and its children no to violate - -- the parentId referencing an existing primary key constrain. This is actually implementing the ON DELETE CASCADE that was on the parentId in previous revisions. - - -- If this resource has grand-grand-children, delete them - if locked_resource_row.resourceType < 1 THEN - WITH grand_grand_children_to_delete AS (SELECT grandGrandChildLevel.internalId, grandGrandChildLevel.resourceType, grandGrandChildLevel.publicId - FROM Resources childLevel - INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId - INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId - WHERE childLevel.parentId = id), - - deleted_grand_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_grand_children_to_delete) - RETURNING resourceType, publicId) - INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_grand_children_rows; - END IF; - -- If this resource has grand-children, delete them - if locked_resource_row.resourceType < 2 THEN - WITH grand_children_to_delete AS (SELECT grandChildLevel.internalId, grandChildLevel.resourceType, grandChildLevel.publicId - FROM Resources childLevel - INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId - WHERE childLevel.parentId = id), - - deleted_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_children_to_delete) - RETURNING resourceType, publicId) - INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_children_rows; - END IF; - -- If this resource has children, delete them - if locked_resource_row.resourceType < 3 THEN - WITH deleted_children AS (DELETE FROM Resources - WHERE parentId = id - RETURNING resourceType, publicId) - INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_children; - END IF; - -- delete the resource itself - DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_resource_row; - - -- keep track of the deleted resources for C++ code - INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId); - - -- If this resource still has siblings, keep track of the remaining parent - -- (a parent that must not be deleted but whose LastUpdate must be updated) - SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id - FROM Resources - WHERE internalId = deleted_resource_row.parentId - AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId); - IF deleted_resource_row.resourceType > 0 THEN - -- If this resource is the latest child, delete the parent - DELETE FROM Resources WHERE internalId = deleted_resource_row.parentId - AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId) - RETURNING * INTO deleted_parent_row; - IF FOUND THEN - INSERT INTO DeletedResources VALUES (deleted_parent_row.resourceType, deleted_parent_row.publicId); - IF deleted_parent_row.resourceType > 0 THEN - -- If this resource is the latest child, delete the parent - DELETE FROM Resources WHERE internalId = deleted_parent_row.parentId - AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_parent_row.parentId) - RETURNING * INTO deleted_grand_parent_row; - IF FOUND THEN - INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); - IF deleted_grand_parent_row.resourceType > 0 THEN - -- If this resource is the latest child, delete the parent - DELETE FROM Resources WHERE internalId = deleted_grand_parent_row.parentId - AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_grand_parent_row.parentId) - RETURNING * INTO deleted_grand_parent_row; - IF FOUND THEN - INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); - END IF; - END IF; - END IF; - END IF; - END IF; - END IF; -END; - -DROP INDEX IF EXISTS AttachedFilesUuid; - --- 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, 10); -- GlobalProperty_DatabasePatchLevel
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev11ToRev10.sql Fri Sep 18 14:41:29 2026 +0200 @@ -0,0 +1,114 @@ +-- restore the old DeleteResource function + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remaining_ancestor_resource_type INTEGER, + OUT remaining_anncestor_public_id TEXT) AS $body$ +DECLARE + deleted_resource_row RECORD; + deleted_parent_row RECORD; + deleted_grand_parent_row RECORD; + deleted_grand_grand_parent_row RECORD; + locked_parent_row RECORD; + locked_resource_row RECORD; +BEGIN + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + -- note: temporary tables are created at connection level -> they are likely to exist. + -- These tables are used by the triggers + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + RESET client_min_messages; + -- clear the temporary table in case it has been created earlier in the connection + DELETE FROM DeletedResources; + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that + -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize + -- that they are deleting the last instance and the parent resources would not be deleted. + -- Locking only the immediate parent is sufficient to prevent from this. + SELECT * INTO locked_parent_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; + -- Before deleting the resource itself, we lock it to retrieve the resourceType and to make sure not 2 connections try to + -- delete it at the same time + SELECT * INTO locked_resource_row FROM resources WHERE internalid = id FOR UPDATE; + -- before delete the resource itself, we must delete its grand-grand-children, the grand-children and its children no to violate + -- the parentId referencing an existing primary key constrain. This is actually implementing the ON DELETE CASCADE that was on the parentId in previous revisions. + + -- If this resource has grand-grand-children, delete them + if locked_resource_row.resourceType < 1 THEN + WITH grand_grand_children_to_delete AS (SELECT grandGrandChildLevel.internalId, grandGrandChildLevel.resourceType, grandGrandChildLevel.publicId + FROM Resources childLevel + INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId + INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId + WHERE childLevel.parentId = id), + + deleted_grand_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_grand_children_to_delete) + RETURNING resourceType, publicId) + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_grand_children_rows; + END IF; + -- If this resource has grand-children, delete them + if locked_resource_row.resourceType < 2 THEN + WITH grand_children_to_delete AS (SELECT grandChildLevel.internalId, grandChildLevel.resourceType, grandChildLevel.publicId + FROM Resources childLevel + INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId + WHERE childLevel.parentId = id), + + deleted_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_children_to_delete) + RETURNING resourceType, publicId) + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_children_rows; + END IF; + -- If this resource has children, delete them + if locked_resource_row.resourceType < 3 THEN + WITH deleted_children AS (DELETE FROM Resources + WHERE parentId = id + RETURNING resourceType, publicId) + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_children; + END IF; + -- delete the resource itself + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_resource_row; + + -- keep track of the deleted resources for C++ code + INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId); + + -- If this resource still has siblings, keep track of the remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id + FROM Resources + WHERE internalId = deleted_resource_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId); + IF deleted_resource_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_resource_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId) + RETURNING * INTO deleted_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_parent_row.resourceType, deleted_parent_row.publicId); + IF deleted_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + IF deleted_grand_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_grand_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_grand_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + END IF; + END IF; + END IF; + END IF; + END IF; + END IF; +END; + +DROP INDEX IF EXISTS AttachedFilesUuid; + +-- 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, 10); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Thu Sep 17 16:36:20 2026 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Fri Sep 18 14:41:29 2026 +0200 @@ -854,7 +854,7 @@ CREATE INDEX IF NOT EXISTS InvalidChildCountsId ON InvalidChildCounts (id); -- see https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6 --- new in rev 1099 +-- new in rev 11 CREATE INDEX IF NOT EXISTS AttachedFilesUuid ON AttachedFiles (uuid); @@ -863,7 +863,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, 1099); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (4, 11); -- 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
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev1099.sql Thu Sep 17 16:36:20 2026 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,5 +0,0 @@ --- Update from Rev 10 to Rev 1099 - --- the DeleteResource function is updated in PrepareIndex.sql --- the AttachedFilesUuid index is created in PrepareIndex.sql -SELECT 1; \ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev11.sql Fri Sep 18 14:41:29 2026 +0200 @@ -0,0 +1,5 @@ +-- Update from Rev 10 to Rev 11 + +-- the DeleteResource function is updated in PrepareIndex.sql +-- the AttachedFilesUuid index is created in PrepareIndex.sql +SELECT 1; \ No newline at end of file