Mercurial > hg > orthanc-databases

comparison NOTES @ 437:d979f25e60cf pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Re-organized DB creation/upgrade into standalone files
author Alain Mazy <am@osimis.io>
date Mon, 18 Dec 2023 18:50:01 +0100
parents f16faa1fdc46
children 2a48f8fcec6e
comparison
equal deleted inserted replaced
436:f16faa1fdc46 437:d979f25e60cf
191 osimis/orthanc:23.11.0: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.966 s 191 osimis/orthanc:23.11.0: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.966 s
192 osimis/orthanc:current: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 5.092 s 192 osimis/orthanc:current: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 5.092 s
193 193
194 194
195 TODO: 195 TODO:
196 - check RETURNS SET OF ! 196 - reenable PatientRecyclingOrder
197 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ 197 - have a separate "thread" to UpdateStatistics ?
198 SELECT * FROM foo WHERE fooid = $1;
199 $$ LANGUAGE SQL;
200 - have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows
201 ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", -> make unique + handle on conflicts
202 countIdentifierTags, identifierTags);
203
204 ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", -> make unique + handle on conflicts
205 countMainDicomTags, mainDicomTags);
206
207 ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); handle on conflicts
208
209 CREATE OR REPLACE FUNCTION InsertOrReplaceMetadata(VARIADIC params SETOF metadata)
210 RETURNS VOID AS $$
211 BEGIN
212
213 FOR i IN 1..array_length(my_params, 1) LOOP
214 my_sum := my_sum + my_params[i].my_column;
215 END LOOP;
216 RETURN my_sum;
217 END;
218 $$ LANGUAGE plpgsql;
219
220 #include <postgresql/libpq-fe.h>
221
222 int main() {
223 PGconn *conn = PQconnectdb("dbname=mydb user=myuser password=mypassword");
224 if (PQstatus(conn) != CONNECTION_OK) {
225 std::cerr << "Connection to database failed: " << PQerrorMessage(conn) << std::endl;
226 PQfinish(conn);
227 return 1;
228 }
229
230 const char *function_name = "my_function";
231 int num_params = 3;
232 int params[] = {1, 2, 3};
233
234 char *query = PQescapeLiteral(conn, function_name, strlen(function_name));
235 for (int i = 0; i < num_params; i++) {
236 char *param = PQescapeLiteral(conn, (const char *)&params[i], sizeof(int));
237 query = (char *)realloc(query, strlen(query) + strlen(param) + 1);
238 strcat(query, ",");
239 strcat(query, param);
240 PQfreemem(param);
241 }
242 strcat(query, ")");
243
244 PGresult *res = PQexec(conn, query);
245 if (PQresultStatus(res) != PGRES_TUPLES_OK) {
246 std::cerr << "Function call failed: " << PQerrorMessage(conn) << std::endl;
247 PQfreemem(query);
248 PQclear(res);
249 PQfinish(conn);
250 return 1;
251 }
252
253 int result = atoi(PQgetvalue(res, 0, 0));
254 std::cout << "Result: " << result << std::endl;
255
256 PQfreemem(query);
257 PQclear(res);
258 PQfinish(conn);
259
260
261 - test events generation StableSeries .... 198 - test events generation StableSeries ....
262 - run tests with docker localy + in CI
263 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 199 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820
264 - PatientAddedFunc contains an IF
265 - validate upgrade DB from previous Orthanc and from scratch 200 - validate upgrade DB from previous Orthanc and from scratch
201 - check minimal version of PG (9.5 - 9.6 ? for create index if not exists)
202 - implement a downgrade script ?
266 - test with older version of PG 203 - test with older version of PG
267 - In Docker images, re-enable MySQL & ODBC plugins + tests 204 - In Docker images, re-enable MySQL & ODBC plugins + tests
268 205
269 DONE: 206 DONE:
270 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) 207 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP)
208 - PatientAddedFunc contains an IF (check if other IF/THEN/ELSE pattern remains)