multi-SQL command string aborts despite IF EXISTS
Hello all,
the Orthanc DICOM server tries to create a trigram index using this code:
db->Execute(
"CREATE EXTENSION IF NOT EXISTS pg_trgm; "
"CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");
which results in this sequence of events inside PG11:
2019-01-28 08:52:50 GMT ORT: exec_execute_message, postgres.c:2011
2019-01-28 08:52:50 GMT LOG: 00000: Anweisung: CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_2019-01-28 08:52:50 GMT ORT: exec_simple_query, postgres.c:975
2019-01-28 08:52:50 GMT FEHLER: 42501: keine Berechtigung, um Erweiterung �pg_trgm� zu erzeugen
2019-01-28 08:52:50 GMT TIPP: Nur Superuser k�nnen diese Erweiterung anlegen.
2019-01-28 08:52:50 GMT ORT: execute_extension_script, extension.c:809
2019-01-28 08:52:50 GMT ANWEISUNG: CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
2019-01-28 08:52:50 GMT LOG: 00000: Anweisung: ABORT
Apparently, the two SQL commands are being sent as one
command string.
It is quite reasonable that the CREATE EXTENSION part fails
because the connected user, indeed, does not have sufficient
permissions, as it should be. However, the pg_trgm extension
is pre-installed by the database superuser such that index
creation should succeed.
Now, I would have thought that the "IF NOT EXISTS" part of
the CREATE EXTENSION would have allowed the subsequent CREATE
INDEX to succeed.
I am wrong ?
Will aborts inside a multi-SQL string prevent from being
executed any SQL commands later in that same string ?
(Mind you, the code above does not abort the *transaction*
but does not execute the second SQL command.)
Many thanks for insights,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
Karsten Hilbert wrote:
the Orthanc DICOM server tries to create a trigram index using this code:
db->Execute(
"CREATE EXTENSION IF NOT EXISTS pg_trgm; "
"CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");which results in this sequence of events inside PG11:
2019-01-28 08:52:50 GMT ORT: exec_execute_message, postgres.c:2011
2019-01-28 08:52:50 GMT LOG: 00000: Anweisung: CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_2019-01-28 08:52:50 GMT ORT: exec_simple_query, postgres.c:975
2019-01-28 08:52:50 GMT FEHLER: 42501: keine Berechtigung, um Erweiterung »pg_trgm« zu erzeugen
2019-01-28 08:52:50 GMT TIPP: Nur Superuser können diese Erweiterung anlegen.
2019-01-28 08:52:50 GMT ORT: execute_extension_script, extension.c:809
2019-01-28 08:52:50 GMT ANWEISUNG: CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
2019-01-28 08:52:50 GMT LOG: 00000: Anweisung: ABORTApparently, the two SQL commands are being sent as one
command string.It is quite reasonable that the CREATE EXTENSION part fails
because the connected user, indeed, does not have sufficient
permissions, as it should be. However, the pg_trgm extension
is pre-installed by the database superuser such that index
creation should succeed.Now, I would have thought that the "IF NOT EXISTS" part of
the CREATE EXTENSION would have allowed the subsequent CREATE
INDEX to succeed.I am wrong ?
No, you are right.
The "pg_trgm" extension does *not* exist in the database, and that is your problem.
Perhaps you preinstalled the extension in the wrong database (postgres?).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Mon, Jan 28, 2019 at 03:17:47PM +0100, Laurenz Albe wrote:
Now, I would have thought that the "IF NOT EXISTS" part of
the CREATE EXTENSION would have allowed the subsequent CREATE
INDEX to succeed.I am wrong ?
No, you are right.
The "pg_trgm" extension does *not* exist in the database, and that is your problem.
Wow, I didn't realize the difference between pg_extension and
pg_available_extensions ...
Perhaps you preinstalled the extension in the wrong database (postgres?).
It wasn't, indeed, *installed* (despite being available ;)
Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B