Mercurial > hg > orthanc-databases
changeset 595:272eeb046a88 find-refactoring
Introduced a new ChildCount table in PG to improve retrieval of NumberOfRelatedStudyInstances and other similar tags that could consume up to 95% of a request time + added a DB-Housekeeper thread to populate the new table
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Tue, 26 Nov 2024 17:59:14 +0100 |
| parents | 14ba6a6f633f |
| children | 28c9b3e5b3ad |
| files | Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h MySQL/Plugins/MySQLIndex.h Odbc/Plugins/OdbcIndex.h PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/PostgreSQLIndex.h PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql |
| diffstat | 13 files changed, 427 insertions(+), 107 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Fri Nov 15 11:11:05 2024 +0100 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Tue Nov 26 17:59:14 2024 +0100 @@ -1436,6 +1436,9 @@ if (isBackendInUse_) { + IndexConnectionsPool::Accessor accessor(*pool); + accessor.GetBackend().Shutdown(); + isBackendInUse_ = false; } else
--- a/Framework/Plugins/IndexBackend.cpp Fri Nov 15 11:11:05 2024 +0100 +++ b/Framework/Plugins/IndexBackend.cpp Tue Nov 26 17:59:14 2024 +0100 @@ -3587,20 +3587,46 @@ } else if (childrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers { - sql += "UNION ALL SELECT " - " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " - " Lookup.internalId AS c1_internalId, " - " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " - " " + formatter.FormatNull("TEXT") + " AS c3_string1, " - " " + formatter.FormatNull("TEXT") + " AS c4_string2, " - " " + formatter.FormatNull("TEXT") + " AS c5_string3, " - " " + formatter.FormatNull("INT") + " AS c6_int1, " - " " + formatter.FormatNull("INT") + " AS c7_int2, " - " " + formatter.FormatNull("INT") + " AS c8_int3, " - " COUNT(childLevel.internalId) AS c9_big_int1, " - " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " - "FROM Lookup " - " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId GROUP BY Lookup.internalId "; + if (HasChildCountTable()) + { + // we get the count value either from the childCount table if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (ChildCount.childCount)," + " (SELECT COUNT(childLevel.internalId)" + " FROM Resources AS childLevel" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "LEFT JOIN ChildCount ON Lookup.internalId = ChildCount.parentId "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(childLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId GROUP BY Lookup.internalId "; + } } if (childrenSpec->retrieve_metadata_size() > 0) @@ -3659,21 +3685,50 @@ } else if (grandchildrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers { - sql += "UNION ALL SELECT " - " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " - " Lookup.internalId AS c1_internalId, " - " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " - " " + formatter.FormatNull("TEXT") + " AS c3_string1, " - " " + formatter.FormatNull("TEXT") + " AS c4_string2, " - " " + formatter.FormatNull("TEXT") + " AS c5_string3, " - " " + formatter.FormatNull("INT") + " AS c6_int1, " - " " + formatter.FormatNull("INT") + " AS c7_int2, " - " " + formatter.FormatNull("INT") + " AS c8_int3, " - " COUNT(grandChildLevel.internalId) AS c9_big_int1, " - " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " - "FROM Lookup " - " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " - " INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId GROUP BY Lookup.internalId "; + if (HasChildCountTable()) + { + // we get the count value either from the childCount table if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (SELECT SUM(ChildCount.childCount)" + " FROM ChildCount" + " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + " WHERE ChildCount.parentId = childLevel.internalId)," + " (SELECT COUNT(grandChildLevel.internalId)" + " FROM Resources AS childLevel" + " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(grandChildLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + " INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId GROUP BY Lookup.internalId "; + } } if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0) @@ -3742,22 +3797,53 @@ } else if (grandgrandchildrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers { - sql += "UNION ALL SELECT " - " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " - " Lookup.internalId AS c1_internalId, " - " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " - " " + formatter.FormatNull("TEXT") + " AS c3_string1, " - " " + formatter.FormatNull("TEXT") + " AS c4_string2, " - " " + formatter.FormatNull("TEXT") + " AS c5_string3, " - " " + formatter.FormatNull("INT") + " AS c6_int1, " - " " + formatter.FormatNull("INT") + " AS c7_int2, " - " " + formatter.FormatNull("INT") + " AS c8_int3, " - " COUNT(grandChildLevel.internalId) AS c9_big_int1, " - " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " - "FROM Lookup " - "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " - "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId " - "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId GROUP BY Lookup.internalId "; + if (HasChildCountTable()) + { + // we get the count value either from the childCount table if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (SELECT SUM(ChildCount.childCount)" + " FROM ChildCount" + " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + " INNER JOIN Resources AS grandChildLevel ON grandChildLevel.parentId = childLevel.internalId" + " WHERE ChildCount.parentId = grandChildLevel.internalId)," + " (SELECT COUNT(grandGrandChildLevel.internalId)" + " FROM Resources AS childLevel" + " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + " INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(grandChildLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId " + "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId GROUP BY Lookup.internalId "; + } } } }
--- a/Framework/Plugins/IndexBackend.h Fri Nov 15 11:11:05 2024 +0100 +++ b/Framework/Plugins/IndexBackend.h Tue Nov 26 17:59:14 2024 +0100 @@ -54,6 +54,8 @@ virtual void ClearRemainingAncestor(DatabaseManager& manager); + virtual bool HasChildCountTable() const = 0; + void SignalDeletedFiles(IDatabaseBackendOutput& output, DatabaseManager& manager); @@ -458,6 +460,10 @@ #endif + virtual void Shutdown() + {}; + + /** * "maxDatabaseRetries" is to handle * "OrthancPluginErrorCode_DatabaseCannotSerialize" if there is a
--- a/MySQL/Plugins/MySQLIndex.h Fri Nov 15 11:11:05 2024 +0100 +++ b/MySQL/Plugins/MySQLIndex.h Tue Nov 26 17:59:14 2024 +0100 @@ -34,6 +34,12 @@ MySQLParameters parameters_; bool clearAll_; + protected: + virtual bool HasChildCountTable() const + { + return false; + } + public: MySQLIndex(OrthancPluginContext* context, const MySQLParameters& parameters,
--- a/Odbc/Plugins/OdbcIndex.h Fri Nov 15 11:11:05 2024 +0100 +++ b/Odbc/Plugins/OdbcIndex.h Tue Nov 26 17:59:14 2024 +0100 @@ -34,6 +34,12 @@ unsigned int connectionRetryInterval_; std::string connectionString_; + protected: + virtual bool HasChildCountTable() const + { + return false; + } + public: OdbcIndex(OrthancPluginContext* context, const std::string& connectionString,
--- a/PostgreSQL/CMakeLists.txt Fri Nov 15 11:11:05 2024 +0100 +++ b/PostgreSQL/CMakeLists.txt Tue Nov 26 17:59:14 2024 +0100 @@ -92,7 +92,7 @@ POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql POSTGRESQL_UPGRADE_REV1_TO_REV2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql - POSTGRESQL_UPGRADE_REV2_1_REPLACE_CHILDREN_INDEX ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql + POSTGRESQL_UPGRADE_REV2_TO_REV3 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql )
--- a/PostgreSQL/NEWS Fri Nov 15 11:11:05 2024 +0100 +++ b/PostgreSQL/NEWS Tue Nov 26 17:59:14 2024 +0100 @@ -1,7 +1,7 @@ Pending changes in the mainline (future 7.0) =============================== -DB schema revision: 2 +DB schema revision: 3 Minimum plugin SDK (for build): 1.12.3 Optimal plugin SDK (for build): 1.12.5+ Minimum Orthanc runtime: 1.12.3 @@ -18,6 +18,9 @@ https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_1_ReplaceChildrenIndex.sql +* WARNING: A new table is being created when upgrading to this version. A new thread + is populating this new table and might consume DB bandwitdh and CPU. + * Fix updates from plugin version 3.3 to latest version * Added support for ExtendedChanges:
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Nov 15 11:11:05 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Tue Nov 26 17:59:14 2024 +0100 @@ -32,6 +32,7 @@ #include <Compatibility.h> // For std::unique_ptr<> #include <Toolbox.h> +#include <SystemToolbox.h> #include <Logging.h> #include <OrthancException.h> @@ -54,7 +55,8 @@ bool readOnly) : IndexBackend(context, readOnly), parameters_(parameters), - clearAll_(false) + clearAll_(false), + housekeeperShouldStop_(false) { } @@ -108,7 +110,7 @@ if (!IsReadOnly()) { - // lock the full DB while checking if it needs to be create/ugraded + // lock the full DB while checking if it needs to be created/ugraded PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); if (clearAll_) @@ -143,22 +145,30 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } - bool needToRunUpgradeFromUnknownToV1 = false; - bool needToRunUpgradeV1toV2 = false; + bool applyUpgradeFromUnknownToV1 = false; + bool applyUpgradeV1toV2 = false; + bool applyUpgradeV2toV3 = false; + bool applyPrepareIndex = false; int revision; if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) { LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1"; revision = 1; - needToRunUpgradeFromUnknownToV1 = true; - needToRunUpgradeV1toV2 = true; + applyUpgradeFromUnknownToV1 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } else if (revision == 1) { LOG(WARNING) << "DatabasePatchLevel is 1"; - needToRunUpgradeFromUnknownToV1 = true; - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; + } + else if (revision == 2) + { + LOG(WARNING) << "DatabasePatchLevel is 2"; + applyUpgradeV2toV3 = true; } int hasTrigram = 0; @@ -169,31 +179,24 @@ // We've observed 9 minutes on DB with 100000 studies LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database " << "to speed up wildcard searches. This may take several minutes"; - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } int property = 0; - // these extensions are not installed anymore from v6.0 of the plugin (but the plugin is fast to compute the size and count the resources) - // if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - // Orthanc::GlobalProperty_HasFastCountResources) || - // property != 1) - // { - // needToRunUpgradeV1toV2 = true; - // } - // if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - // Orthanc::GlobalProperty_GetTotalSizeIsFast) || - // property != 1) - // { - // needToRunUpgradeV1toV2 = true; - // } if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex) || property != 1) { - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } - if (needToRunUpgradeFromUnknownToV1) + // If you add new tests here, update the test in the "ReadOnly" code below + + applyPrepareIndex = applyUpgradeV2toV3; + + if (applyUpgradeFromUnknownToV1) { LOG(WARNING) << "Upgrading DB schema from unknown to revision 1"; std::string query; @@ -203,7 +206,7 @@ t.GetDatabaseTransaction().ExecuteMultiLines(query); } - if (needToRunUpgradeV1toV2) + if (applyUpgradeV1toV2) { LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2"; @@ -212,32 +215,25 @@ Orthanc::EmbeddedResources::GetFileResource (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2); t.GetDatabaseTransaction().ExecuteMultiLines(query); - - // apply all idempotent changes that are in the PrepareIndexV2 - ApplyPrepareIndex(t, manager); } - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasComputeStatisticsReadOnly) || - property != 1) + if (applyUpgradeV2toV3) { - // apply all idempotent changes that are in the PrepareIndex. In this case, we are just interested by - // ComputeStatisticsReadOnly() that does not need to be uninstalled in case of downgrade. - ApplyPrepareIndex(t, manager); - } - - if (!t.GetDatabaseTransaction().DoesIndexExist("ChildrenIndex2")) - { - LOG(WARNING) << "Installing ChildrenIndex2"; + LOG(WARNING) << "Upgrading DB schema from revision 2 to revision 3"; std::string query; Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_1_REPLACE_CHILDREN_INDEX); + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_TO_REV3); t.GetDatabaseTransaction().ExecuteMultiLines(query); } - // If you add new tests here, update the test in the "ReadOnly" code below + if (applyPrepareIndex) + { + // apply all idempotent changes that are in the PrepareIndex.sql + ApplyPrepareIndex(t, manager); + } + } t.Commit(); @@ -250,12 +246,19 @@ DatabaseManager::Transaction t(manager, TransactionType_ReadOnly); // test if the latest "extension" has been installed - if (!t.GetDatabaseTransaction().DoesIndexExist("ChildrenIndex2")) - { + int revision; + if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel) + || revision != 2) + { LOG(ERROR) << "READ-ONLY SYSTEM: the DB does not have the correct schema to run with this version of the plugin"; throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } } + + if (!IsReadOnly()) + { + StartDbHousekeeper(manager); + } } @@ -745,6 +748,60 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } + void PostgreSQLIndex::StartDbHousekeeper(DatabaseManager& manager) + { + housekeeperShouldStop_ = false; + dbHousekeeper_.reset(new boost::thread(WorkerHousekeeper, housekeeperShouldStop_, &manager)); + } + + void PostgreSQLIndex::Shutdown() + { + // TODO: stop thread + housekeeperShouldStop_ = true; + if (dbHousekeeper_.get() != NULL && dbHousekeeper_->joinable()) + { + dbHousekeeper_->join(); + dbHousekeeper_.reset(); + } + } + + void PostgreSQLIndex::WorkerHousekeeper(bool& housekeeperShouldStop, + DatabaseManager* manager) + { + OrthancPluginSetCurrentThreadName(OrthancPlugins::GetGlobalContext(), "PG DB-HK"); + LOG(WARNING) << "Starting the DB Housekeeper thread"; + + bool hasComputedAllMissingChildCount = false; + while (!housekeeperShouldStop && !hasComputedAllMissingChildCount) + { + if (!hasComputedAllMissingChildCount) + { + LOG(INFO) << "Computing missing ChildCount"; + + DatabaseManager::Transaction t(*manager, TransactionType_ReadWrite); + + try + { + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE, *manager, + "SELECT ComputeMissingChildCount(50)"); + + statement.Execute(); + + int64_t updatedCount = statement.ReadInteger64(0); + hasComputedAllMissingChildCount = updatedCount == 0; + + t.Commit(); + } + catch (...) + { + LOG(ERROR) << "Unexpected error"; + } + } + Orthanc::SystemToolbox::USleep(10000); + } + + LOG(INFO) << "Stopping the DB Housekeeper thread"; + } // #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) // bool PostgreSQLIndex::HasFindSupport() const
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Fri Nov 15 11:11:05 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Tue Nov 26 17:59:14 2024 +0100 @@ -25,6 +25,8 @@ #include "../../Framework/Plugins/IndexBackend.h" #include "../../Framework/PostgreSQL/PostgreSQLParameters.h" +#include <boost/thread.hpp> + namespace OrthancDatabases { @@ -33,6 +35,8 @@ private: PostgreSQLParameters parameters_; bool clearAll_; + std::unique_ptr<boost::thread> dbHousekeeper_; + bool housekeeperShouldStop_; protected: virtual void ClearDeletedFiles(DatabaseManager& manager) ORTHANC_OVERRIDE; @@ -41,8 +45,17 @@ virtual void ClearRemainingAncestor(DatabaseManager& manager) ORTHANC_OVERRIDE; + virtual bool HasChildCountTable() const ORTHANC_OVERRIDE + { + return true; + } + void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager); + void StartDbHousekeeper(DatabaseManager& manager); + + static void WorkerHousekeeper(bool& housekeeperShouldStop, DatabaseManager* manager); + public: PostgreSQLIndex(OrthancPluginContext* context, const PostgreSQLParameters& parameters, @@ -136,6 +149,8 @@ int64_t& compressedSize, int64_t& uncompressedSize) ORTHANC_OVERRIDE; + virtual void Shutdown() ORTHANC_OVERRIDE; + // #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) // virtual bool HasFindSupport() const ORTHANC_OVERRIDE; // #endif
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql Tue Nov 26 17:59:14 2024 +0100 @@ -0,0 +1,15 @@ +-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1). + -- It actually deletes the ChildCount table and triggers + -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than + -- ChildrenIndex and is not incompatible with previous revisions. + +DROP TRIGGER IF EXISTS DecrementChildCount ON Resources; +DROP TRIGGER IF EXISTS IncrementChildCount ON Resources; +DROP TABLE ChildCount; +DROP FUNCTION UpdateChildCount; + + +-- 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, 11); +INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Fri Nov 15 11:11:05 2024 +0100 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Tue Nov 26 17:59:14 2024 +0100 @@ -106,7 +106,6 @@ PRIMARY KEY(server, property) ); --- new ChildrenIndex2 introduced in v 7.0 (replacing previous ChildrenIndex) DO $$ DECLARE pg_version text; @@ -115,6 +114,8 @@ IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN -- PostgreSQL 11 or later + + -- new ChildrenIndex2 introduced in Rev3 (replacing previous ChildrenIndex) EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; ELSE EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; @@ -129,6 +130,7 @@ CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex3 ON DicomIdentifiers(tagGroup, tagElement, value); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value); CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId); @@ -585,11 +587,116 @@ $body$ LANGUAGE plpgsql; +-- new in Rev3 + +CREATE TABLE IF NOT EXISTS ChildCount ( + parentId BIGINT PRIMARY KEY REFERENCES Resources(internalId) ON DELETE CASCADE, + childCount INTEGER NOT NULL DEFAULT 0, + CONSTRAINT UniqueParentID UNIQUE (parentId) +); + + +-- Computes the ChildCount for a number of resources for which it has not been computed yet. +-- This is actually used only after an update to Rev3. A thread will call this function +-- at regular interval to update all missing values and stop once all values have been processed. +CREATE OR REPLACE FUNCTION ComputeMissingChildCount( + IN batch_size BIGINT, + OUT updated_rows_count BIGINT +) RETURNS BIGINT AS $body$ + +BEGIN + + INSERT INTO ChildCount (parentID, childCount) + SELECT r.internalId AS parentId, COUNT(childLevel.internalId) AS childCount + FROM Resources AS r + LEFT JOIN Resources AS childLevel ON r.internalId = childLevel.parentId + WHERE r.internalId IN ( + SELECT internalId FROM Resources AS r + WHERE resourceType < 3 AND NOT EXISTS(SELECT 1 FROM ChildCount WHERE ChildCount.parentId = r.internalId) + LIMIT batch_size) + GROUP BY r.internalId; + + -- Get the number of rows affected + GET DIAGNOSTICS updated_rows_count = ROW_COUNT; + +END; +$body$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION UpdateChildCount() +RETURNS TRIGGER AS $body$ +DECLARE + parent_id BIGINT; +BEGIN + IF TG_OP = 'INSERT' THEN + IF NEW.parentId IS NOT NULL THEN + -- try to increment the childcount from the parent + UPDATE ChildCount + SET childCount = childCount + 1 + WHERE parentId = NEW.parentId + RETURNING parentId INTO parent_id; + + -- this should only happen for old studies whose childCount has not yet been computed + IF NOT FOUND THEN + INSERT INTO childcount (parentId, childCount) + SELECT parentID, COUNT(*) + FROM Resources + WHERE parentId = parent_id + GROUP BY parentId; + END IF; + END IF; + + -- this is a future parent, start counting the children + IF NEW.resourcetype < 3 THEN + insert into ChildCount (parentId, childCount) + values (new.internalId, 0); + END IF; + + ELSIF TG_OP = 'DELETE' THEN + + IF NEW.parentId IS NOT NULL THEN + + -- Decrement the child count for the parent + UPDATE ChildCount + SET childCount = childCount - 1 + WHERE parentId = OLD.parentId + RETURNING parentId INTO parent_id; + + -- this should only happen for old studies whose childCount has not yet been computed + IF NOT FOUND THEN + INSERT INTO childcount (parentId, childCount) + SELECT parentID, COUNT(*) + FROM Resources + WHERE parentId = parent_id + GROUP BY parentId; + END IF; + END IF; + + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +-- Trigger for INSERT +CREATE TRIGGER IncrementChildCount +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE FUNCTION UpdateChildCount(); + +-- Trigger for DELETE +CREATE TRIGGER DecrementChildCount +AFTER DELETE ON Resources +FOR EACH ROW +WHEN (OLD.parentId IS NOT NULL) +EXECUTE FUNCTION UpdateChildCount(); + + -- 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, 2); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (4, 3); -- 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/Partial/Rev2_1_ReplaceChildrenIndex.sql Fri Nov 15 11:11:05 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,15 +0,0 @@ -DO $$ -DECLARE - pg_version text; -BEGIN - SELECT version() INTO pg_version; - - IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN - -- PostgreSQL 11 or later - EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; - ELSE - EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; - END IF; -END $$; - -DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading \ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql Tue Nov 26 17:59:14 2024 +0100 @@ -0,0 +1,31 @@ +-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2) +-- It actually contains only the changes that: + -- can not be executed with an idempotent statement in SQL + -- or would polute the PrepareIndex.sql + -- do facilite an up-time upgrade +-- This file is executed only if the current schema is in revision 2 and it is executed +-- before PrepareIndex.sql that is idempotent. + + +-- create a new ChildrenIndex2 that is replacing ChildrenIndex. +-- We create it in this partial update so it can be created while the system is up ! +DO $$ +DECLARE + pg_version text; +BEGIN + SELECT version() INTO pg_version; + + IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN + -- PostgreSQL 11 or later + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; + ELSE + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; + END IF; +END $$; + +DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading + + +-- other changes performed in PrepareIndex.sql: + -- add ChildCount tables and triggers +