Mercurial > hg > orthanc-databases
changeset 844:e76a0b1763dc pg-next-1099
Added an index on AttachedFiles uuids to speed-up Set/GetAttachmentCustomData
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Mon, 14 Sep 2026 15:02:52 +0200 |
| parents | 68b52a369685 |
| children | 441a73af4626 |
| files | PostgreSQL/NEWS PostgreSQL/Plugins/SQL/Downgrades/Rev1099ToRev10.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev1099.sql |
| diffstat | 4 files changed, 16 insertions(+), 8 deletions(-) [+] |
line wrap: on
line diff
--- a/PostgreSQL/NEWS Mon Sep 14 14:54:00 2026 +0200 +++ b/PostgreSQL/NEWS Mon Sep 14 15:02:52 2026 +0200 @@ -2,15 +2,16 @@ =============================== Changes: +* Added an index on AttachedFiles uuids to speed-up the SDK calls + SetAttachmentCustomData and GetAttachmentCustomData * Small update of the "DeleteResource" function to avoid a warning when multiple clients are trying to delete the same resource at the same time. - TODO: handle migration. - 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) + 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/SQL/Downgrades/Rev1099ToRev10.sql Mon Sep 14 14:54:00 2026 +0200 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev1099ToRev10.sql Mon Sep 14 15:02:52 2026 +0200 @@ -106,6 +106,8 @@ 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);
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Sep 14 14:54:00 2026 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Sep 14 15:02:52 2026 +0200 @@ -854,7 +854,11 @@ 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 +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);
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev1099.sql Mon Sep 14 14:54:00 2026 +0200 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev10ToRev1099.sql Mon Sep 14 15:02:52 2026 +0200 @@ -1,3 +1,4 @@ -- Update from Rev 10 to Rev 1099 + -- the DeleteResource function is updated in PrepareIndex.sql - +-- the AttachedFilesUuid index is created in PrepareIndex.sql