Search Postgresql Archives

multi-SQL command string aborts despite IF EXISTS

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux