Re: multi-SQL command string aborts despite IF EXISTS

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: 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 ?

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