Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/PostgreSQLIndex.cpp @ 136:3266785d5627
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 |