Обсуждение: multi-SQL command string aborts despite IF EXISTS

Поиск
Список
Период
Сортировка

multi-SQL command string aborts despite IF EXISTS

От
Karsten Hilbert
Дата:
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
DicomIdentifiersIndexValues2ON 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
DicomIdentifiersIndexValues2ON 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

Re: multi-SQL command string aborts despite IF EXISTS

От
Laurenz Albe
Дата:
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
DicomIdentifiersIndexValues2ON 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
DicomIdentifiersIndexValues2ON 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

Re: multi-SQL command string aborts despite IF EXISTS

От
Karsten Hilbert
Дата:
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