Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 136:3266785d5627

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cleaning up PostgreSQL locks with constants
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 09 May 2019 09:52:11 +0200
parents cc3dc759c989
children 4cd7e45b671e
comparison
equal deleted inserted replaced
135:e26690365c25 136:3266785d5627
22 #include "PostgreSQLIndex.h" 22 #include "PostgreSQLIndex.h"
23 23
24 #include "../../Framework/Plugins/GlobalProperties.h" 24 #include "../../Framework/Plugins/GlobalProperties.h"
25 #include "../../Framework/PostgreSQL/PostgreSQLDatabase.h" 25 #include "../../Framework/PostgreSQL/PostgreSQLDatabase.h"
26 #include "../../Framework/PostgreSQL/PostgreSQLTransaction.h" 26 #include "../../Framework/PostgreSQL/PostgreSQLTransaction.h"
27 #include "PostgreSQLDefinitions.h"
27 28
28 #include <EmbeddedResources.h> // Auto-generated file 29 #include <EmbeddedResources.h> // Auto-generated file
29 30
30 #include <Core/Logging.h> 31 #include <Core/Logging.h>
31 #include <Core/OrthancException.h> 32 #include <Core/OrthancException.h>
69 70
70 db->Open(); 71 db->Open();
71 72
72 if (parameters_.HasLock()) 73 if (parameters_.HasLock())
73 { 74 {
74 db->AdvisoryLock(42 /* some arbitrary constant */); 75 db->AdvisoryLock(POSTGRESQL_LOCK_INDEX);
75 } 76 }
76 77
77 /** 78 {
78 * Try and acquire a transient advisory lock to protect the setup 79 PostgreSQLDatabase::TransientAdvisoryLock lock(*db, POSTGRESQL_LOCK_DATABASE_SETUP);
79 * of the database, because concurrent statements like "CREATE 80
80 * TABLE" are not protected by transactions. 81 if (clearAll_)
81 * https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/h3PRApJFBAAJ
82 **/
83 PostgreSQLDatabase::TransientAdvisoryLock lock(*db, 44 /* some arbitrary constant */);
84
85 if (clearAll_)
86 {
87 db->ClearAll();
88 }
89
90 {
91 PostgreSQLTransaction t(*db);
92
93 if (!db->DoesTableExist("Resources"))
94 { 82 {
95 std::string query; 83 db->ClearAll();
96
97 Orthanc::EmbeddedResources::GetFileResource
98 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX);
99 db->Execute(query);
100
101 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion);
102 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, 1);
103 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasTrigramIndex, 0);
104 } 84 }
85
86 {
87 PostgreSQLTransaction t(*db);
88
89 if (!db->DoesTableExist("Resources"))
90 {
91 std::string query;
92
93 Orthanc::EmbeddedResources::GetFileResource
94 (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX);
95 db->Execute(query);
96
97 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion);
98 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, 1);
99 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasTrigramIndex, 0);
100 }
105 101
106 if (!db->DoesTableExist("Resources")) 102 if (!db->DoesTableExist("Resources"))
107 { 103 {
108 LOG(ERROR) << "Corrupted PostgreSQL database"; 104 LOG(ERROR) << "Corrupted PostgreSQL database";
109 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); 105 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
110 } 106 }
111 107
112 int version = 0; 108 int version = 0;
113 if (!LookupGlobalIntegerProperty(version, *db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion) || 109 if (!LookupGlobalIntegerProperty(version, *db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||
114 version != 6) 110 version != 6)
115 { 111 {
116 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version; 112 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version;
117 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 113 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
118 } 114 }
119 115
120 int revision; 116 int revision;
121 if (!LookupGlobalIntegerProperty(revision, *db, t, Orthanc::GlobalProperty_DatabasePatchLevel)) 117 if (!LookupGlobalIntegerProperty(revision, *db, t, Orthanc::GlobalProperty_DatabasePatchLevel))
122 { 118 {
123 revision = 1; 119 revision = 1;
124 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 120 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
125 } 121 }
126 122
127 if (revision != 1) 123 if (revision != 1)
128 { 124 {
129 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; 125 LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision;
130 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 126 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
131 } 127 }
132 128
133 t.Commit();
134 }
135
136 {
137 PostgreSQLTransaction t(*db);
138
139 int hasTrigram = 0;
140 if (!LookupGlobalIntegerProperty(hasTrigram, *db, t,
141 Orthanc::GlobalProperty_HasTrigramIndex) ||
142 hasTrigram != 1)
143 {
144 /**
145 * Apply fix for performance issue (speed up wildcard search
146 * by using GIN trigrams). This implements the patch suggested
147 * in issue #47, BUT we also keep the original
148 * "DicomIdentifiersIndexValues", as it leads to better
149 * performance for "strict" searches (i.e. searches involving
150 * no wildcard).
151 * https://www.postgresql.org/docs/current/static/pgtrgm.html
152 * https://bitbucket.org/sjodogne/orthanc/issues/47/index-improvements-for-pg-plugin
153 **/
154 try
155 {
156 // We've observed 9 minutes on DB with 100000 studies
157 LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database "
158 << "to speed up wildcard searches. This may take several minutes";
159
160 db->Execute(
161 "CREATE EXTENSION IF NOT EXISTS pg_trgm; "
162 "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");
163
164 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasTrigramIndex, 1);
165 LOG(WARNING) << "Trigram index has been created";
166
167 t.Commit();
168 }
169 catch (Orthanc::OrthancException&)
170 {
171 LOG(WARNING) << "Performance warning: Your PostgreSQL server does "
172 << "not support trigram matching";
173 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the "
174 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib";
175 }
176 }
177 else
178 {
179 t.Commit(); 129 t.Commit();
180 } 130 }
181 } 131
182
183 {
184 PostgreSQLTransaction t(*db);
185
186 int property = 0;
187 if (!LookupGlobalIntegerProperty(property, *db, t,
188 Orthanc::GlobalProperty_HasCreateInstance) ||
189 property != 2)
190 { 132 {
191 LOG(INFO) << "Installing the CreateInstance extension"; 133 PostgreSQLTransaction t(*db);
192 134
193 if (property == 1) 135 int hasTrigram = 0;
194 { 136 if (!LookupGlobalIntegerProperty(hasTrigram, *db, t,
195 // Drop older, experimental versions of this extension 137 Orthanc::GlobalProperty_HasTrigramIndex) ||
196 db->Execute("DROP FUNCTION CreateInstance(" 138 hasTrigram != 1)
197 "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)"); 139 {
198 } 140 /**
141 * Apply fix for performance issue (speed up wildcard search
142 * by using GIN trigrams). This implements the patch suggested
143 * in issue #47, BUT we also keep the original
144 * "DicomIdentifiersIndexValues", as it leads to better
145 * performance for "strict" searches (i.e. searches involving
146 * no wildcard).
147 * https://www.postgresql.org/docs/current/static/pgtrgm.html
148 * https://bitbucket.org/sjodogne/orthanc/issues/47/index-improvements-for-pg-plugin
149 **/
150 try
151 {
152 // We've observed 9 minutes on DB with 100000 studies
153 LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database "
154 << "to speed up wildcard searches. This may take several minutes";
155
156 db->Execute(
157 "CREATE EXTENSION IF NOT EXISTS pg_trgm; "
158 "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");
159
160 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasTrigramIndex, 1);
161 LOG(WARNING) << "Trigram index has been created";
162
163 t.Commit();
164 }
165 catch (Orthanc::OrthancException&)
166 {
167 LOG(WARNING) << "Performance warning: Your PostgreSQL server does "
168 << "not support trigram matching";
169 LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the "
170 << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib";
171 }
172 }
173 else
174 {
175 t.Commit();
176 }
177 }
178
179 {
180 PostgreSQLTransaction t(*db);
181
182 int property = 0;
183 if (!LookupGlobalIntegerProperty(property, *db, t,
184 Orthanc::GlobalProperty_HasCreateInstance) ||
185 property != 2)
186 {
187 LOG(INFO) << "Installing the CreateInstance extension";
188
189 if (property == 1)
190 {
191 // Drop older, experimental versions of this extension
192 db->Execute("DROP FUNCTION CreateInstance("
193 "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)");
194 }
199 195
200 std::string query; 196 std::string query;
201 Orthanc::EmbeddedResources::GetFileResource 197 Orthanc::EmbeddedResources::GetFileResource
202 (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE); 198 (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE);
203 db->Execute(query); 199 db->Execute(query);
204 200
205 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasCreateInstance, 2); 201 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasCreateInstance, 2);
202 }
203
204
205 if (!LookupGlobalIntegerProperty(property, *db, t,
206 Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
207 property != 1)
208 {
209 LOG(INFO) << "Installing the FastTotalSize extension";
210
211 std::string query;
212 Orthanc::EmbeddedResources::GetFileResource
213 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE);
214 db->Execute(query);
215
216 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1);
217 }
218
219
220 // Installing this extension requires the "GlobalIntegers" table
221 // created by the "FastTotalSize" extension
222 property = 0;
223 if (!LookupGlobalIntegerProperty(property, *db, t,
224 Orthanc::GlobalProperty_HasFastCountResources) ||
225 property != 1)
226 {
227 LOG(INFO) << "Installing the FastCountResources extension";
228
229 std::string query;
230 Orthanc::EmbeddedResources::GetFileResource
231 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
232 db->Execute(query);
233
234 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasFastCountResources, 1);
235 }
236
237
238 // Installing this extension requires the "GlobalIntegers" table
239 // created by the "GetLastChangeIndex" extension
240 property = 0;
241 if (!LookupGlobalIntegerProperty(property, *db, t,
242 Orthanc::GlobalProperty_GetLastChangeIndex) ||
243 property != 1)
244 {
245 LOG(INFO) << "Installing the GetLastChangeIndex extension";
246
247 std::string query;
248 Orthanc::EmbeddedResources::GetFileResource
249 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX);
250 db->Execute(query);
251
252 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetLastChangeIndex, 1);
253 }
254
255 t.Commit();
206 } 256 }
207
208
209 if (!LookupGlobalIntegerProperty(property, *db, t,
210 Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
211 property != 1)
212 {
213 LOG(INFO) << "Installing the FastTotalSize extension";
214
215 std::string query;
216 Orthanc::EmbeddedResources::GetFileResource
217 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE);
218 db->Execute(query);
219
220 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1);
221 }
222
223
224 // Installing this extension requires the "GlobalIntegers" table
225 // created by the "FastTotalSize" extension
226 property = 0;
227 if (!LookupGlobalIntegerProperty(property, *db, t,
228 Orthanc::GlobalProperty_HasFastCountResources) ||
229 property != 1)
230 {
231 LOG(INFO) << "Installing the FastCountResources extension";
232
233 std::string query;
234 Orthanc::EmbeddedResources::GetFileResource
235 (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
236 db->Execute(query);
237
238 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasFastCountResources, 1);
239 }
240
241
242 // Installing this extension requires the "GlobalIntegers" table
243 // created by the "GetLastChangeIndex" extension
244 property = 0;
245 if (!LookupGlobalIntegerProperty(property, *db, t,
246 Orthanc::GlobalProperty_GetLastChangeIndex) ||
247 property != 1)
248 {
249 LOG(INFO) << "Installing the GetLastChangeIndex extension";
250
251 std::string query;
252 Orthanc::EmbeddedResources::GetFileResource
253 (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX);
254 db->Execute(query);
255
256 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_GetLastChangeIndex, 1);
257 }
258
259 t.Commit();
260 } 257 }
261 258
262 return db.release(); 259 return db.release();
263 } 260 }
264 261