multi-SQL command string aborts despite IF EXISTS

From: Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: multi-SQL command string aborts despite IF EXISTS
Date: 2019-01-28 11:23:26
Message-ID: 20190128112326.GB2141@hermes.hilbert.loc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durgamahesh Manne 2019-01-28 12:20:37 Regarding query execution for long time
Previous Message Achilleas Mantzios 2019-01-28 10:29:25 Re: logical replication problem