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 (current diff) 3e4d8da97292 (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, 170 insertions(+), 13 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/ISqlLookupFormatter.cpp Wed Aug 19 10:09:23 2026 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.cpp Fri Sep 18 14:41:29 2026 +0200 @@ -807,6 +807,11 @@ assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); + const bool isOrthancIdentifiersDefined = (!request.orthanc_id_patient().empty() || + !request.orthanc_id_study().empty() || + !request.orthanc_id_series().empty() || + !request.orthanc_id_instance().empty()); + std::string orderingSql; std::string orderingJoins; @@ -878,7 +883,14 @@ } else { - orderingSql = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + if (isOrthancIdentifiersDefined && DetectLevel(request) == queryLevel) + { // this is a single resource, no need for ordering (ordering may prevents a lot of optimizations from the query planner) + orderingSql = "0 AS rowNumber"; + } + else + { + orderingSql = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + } } sql = ("SELECT " + @@ -890,11 +902,6 @@ std::string joins, comparisons; - const bool isOrthancIdentifiersDefined = (!request.orthanc_id_patient().empty() || - !request.orthanc_id_study().empty() || - !request.orthanc_id_series().empty() || - !request.orthanc_id_instance().empty()); - // handle parent constraints if (isOrthancIdentifiersDefined && Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel)) {
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Wed Aug 19 10:09:23 2026 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Fri Sep 18 14:41:29 2026 +0200 @@ -189,7 +189,7 @@ PQclear(result); LOG(ERROR) << "PostgreSQL error: " << message; - ThrowException(false); + ThrowException(true); } }
--- a/PostgreSQL/CMakeLists.txt Wed Aug 19 10:09:23 2026 +0200 +++ b/PostgreSQL/CMakeLists.txt Fri Sep 18 14:41:29 2026 +0200 @@ -96,7 +96,8 @@ 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_REV6_TO_REV10 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev6ToRev10.sql + POSTGRESQL_UPGRADE_REV6_TO_REV10 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev6ToRev10.sql + POSTGRESQL_UPGRADE_REV10_TO_REV11 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev10ToRev11.sql )
--- a/PostgreSQL/NEWS Wed Aug 19 10:09:23 2026 +0200 +++ b/PostgreSQL/NEWS Fri Sep 18 14:41:29 2026 +0200 @@ -1,3 +1,14 @@ +Pending changes in the mainline +=============================== + +Changes: +* Added an index on AttachedFiles uuids to speed-up the SDK calls + 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. + + Release 10.3 (2026-08-19) =========================
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Aug 19 10:09:23 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 10 +#define CURRENT_DB_REVISION 11 namespace OrthancDatabases { @@ -283,6 +283,19 @@ currentRevision = 10; } + if (currentRevision == 10) + { + LOG(WARNING) << "Upgrading DB schema from revision 10 to revision 11"; + + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV10_TO_REV11); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + hasAppliedAnUpgrade = true; + currentRevision = 11; + } + 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/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 Wed Aug 19 10:09:23 2026 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Fri Sep 18 14:41:29 2026 +0200 @@ -260,9 +260,11 @@ -- 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 FOUND THEN + -- keep track of the deleted resources for C++ code + INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId); + END IF; + -- 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 @@ -852,12 +854,16 @@ 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 11 +CREATE INDEX IF NOT EXISTS AttachedFilesUuid ON AttachedFiles (uuid); + +---------------------------------- -- 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, 10); -- 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
--- /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