BUG #16698: Create extension and search path

On 03/11/2020 09:57, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16698
Logged by: Miha Vrhovnik
Email address: miha.vrhovnik@gmail.com
PostgreSQL version: 13.0
Operating system: Linux
Description:

The documentation states, that I can use search_path to define in which
schema the extension is going to be created, but this is clearly not
true..

To be precise, the documentation for CREATE EXTENSION
(https://www.postgresql.org/docs/current/sql-createextension.html) says:

schema_name

The name of the schema in which to install the extension's objects,
given that the extension allows its contents to be relocated. The
named schema must already exist. If not specified, and the
extension's control file does not specify a schema either, the
current default object creation schema is used.

If the extension specifies a schema parameter in its control file,
then that schema cannot be overridden with a SCHEMA clause. Normally,
an error will be raised if a SCHEMA clause is given and it conflicts
with the extension's schema parameter. However, if the CASCADE clause
is also given, then schema_name is ignored when it conflicts. The
given schema_name will be used for installation of any needed
extensions that do not specify schema in their control files.

Remember that the extension itself is not considered to be within any
schema: extensions have unqualified names that must be unique
database-wide. But objects belonging to the extension can be within
schemas.

The first paragraph says "schema in which to install the extension's
*objects*" (emphasis mine). And the last paragraph explains this more
explicitly.

- Heikki

I'm so sorry, but I do not understand... Even if I force the schema in
CREATE EXTENSION it still says that it already exists. (Tested on 12.4, but
it's the same with 13, as I have tested it before)
I'm missing something but the documentation doesn't state that I can only
install extension once per database. I thought that schemas provide
"complete isolation".

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1), server 12.4 (Ubuntu
12.4-1.pgdg20.04+1))
You are now connected to database "test" as user "postgres".
test=# CREATE SCHEMA foo1;
CREATE SCHEMA
test=# CREATE SCHEMA foo2;
CREATE SCHEMA
test=# CREATE EXTENSION ltree WITH SCHEMA foo1;
CREATE EXTENSION
test=# CREATE EXTENSION ltree WITH SCHEMA foo2;
ERROR: extension "ltree" already exists

Regards,
Miha

On Tue, 3 Nov 2020 at 13:18, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

Le mar. 3 nov. 2020 à 14:48, Miha Vrhovnik <miha.vrhovnik@gmail.com> a
écrit :

I'm so sorry, but I do not understand... Even if I force the schema in
CREATE EXTENSION it still says that it already exists. (Tested on 12.4, but
it's the same with 13, as I have tested it before)
I'm missing something but the documentation doesn't state that I can only
install extension once per database.

A schema isn't a database. They are different objects. You have a database,
and inside this database, some schemas, and inside a schema, some objects
(tables, functions, views, etc). Extensions are at the same level than
schemas. An extension belongs to database, not to a schema. But the
extension's objects are inside a schema. So, you can add an extension, and
its objects can belong to any schema, but you can't have the same extension
created multiple times inside the same database.

I thought that schemas provide "complete isolation".

Not sure what you mean by complete isolation, but the extension isn't
inside a schema. Its objects are.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
psql (13.0 (Ubuntu 13.0-1.pgdg20.04+1), server 12.4 (Ubuntu
12.4-1.pgdg20.04+1))
You are now connected to database "test" as user "postgres".
test=# CREATE SCHEMA foo1;
CREATE SCHEMA
test=# CREATE SCHEMA foo2;
CREATE SCHEMA
test=# CREATE EXTENSION ltree WITH SCHEMA foo1;
CREATE EXTENSION
test=# CREATE EXTENSION ltree WITH SCHEMA foo2;
ERROR: extension "ltree" already exists

Regards,
Miha

On Tue, 3 Nov 2020 at 13:18, Heikki Linnakangas <hlinnaka@iki.fi> wrote:

On 03/11/2020 09:57, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 16698
Logged by: Miha Vrhovnik
Email address: miha.vrhovnik@gmail.com
PostgreSQL version: 13.0
Operating system: Linux
Description:

The documentation states, that I can use search_path to define in which
schema the extension is going to be created, but this is clearly not
true..

To be precise, the documentation for CREATE EXTENSION
(https://www.postgresql.org/docs/current/sql-createextension.html) says:

schema_name

The name of the schema in which to install the extension's objects,
given that the extension allows its contents to be relocated. The
named schema must already exist. If not specified, and the
extension's control file does not specify a schema either, the
current default object creation schema is used.

If the extension specifies a schema parameter in its control file,
then that schema cannot be overridden with a SCHEMA clause. Normally,
an error will be raised if a SCHEMA clause is given and it conflicts
with the extension's schema parameter. However, if the CASCADE clause
is also given, then schema_name is ignored when it conflicts. The
given schema_name will be used for installation of any needed
extensions that do not specify schema in their control files.

Remember that the extension itself is not considered to be within any
schema: extensions have unqualified names that must be unique
database-wide. But objects belonging to the extension can be within
schemas.

The first paragraph says "schema in which to install the extension's
*objects*" (emphasis mine). And the last paragraph explains this more
explicitly.

- Heikki

--
Guillaume.

Miha Vrhovnik <miha.vrhovnik@gmail.com> writes:

I'm so sorry, but I do not understand... Even if I force the schema in
CREATE EXTENSION it still says that it already exists. (Tested on 12.4, but
it's the same with 13, as I have tested it before)
I'm missing something but the documentation doesn't state that I can only
install extension once per database.

Extensions are not schema-qualified objects, so no, you cannot have
more than one instance per database.

regards, tom lane

What are then extensions.. They live in some sort of limbo.. as they don't
behave like a natural extension of the database... As they behave
differently (because they "require" public shema in path if extensions are
created while in public schema ).
If they would then this would work, but it doesn't.. and it requires fully
qualified names (OR public schema in set_path) which is annoying.

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)

test=# CREATE EXTENSION ltree;
CREATE EXTENSION
test=# CREATE SCHEMA foo1;
CREATE SCHEMA
test=# CREATE SCHEMA foo2;
CREATE SCHEMA
test=# SET search_path = foo1;
SET
test=# CREATE TABLE t(l ltree);
ERROR: type "ltree" does not exist
LINE 1: CREATE TABLE t(l ltree);

BR,
Miha

On Tue, 3 Nov 2020 at 15:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Miha Vrhovnik <miha.vrhovnik@gmail.com> writes:

What are then extensions.. They live in some sort of limbo.. as they don't
behave like a natural extension of the database... As they behave
differently (because they "require" public shema in path if extensions are
created while in public schema ).

You've got the wrong mental model. Extensions are containers, which
may *contain* objects that live within schemas, but the extension itself
does not. The key reason for doing it that way is that an extension might
itself contain a schema, or more than one schema.

regards, tom lane

On Tue, Nov 3, 2020 at 10:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Miha Vrhovnik <miha.vrhovnik@gmail.com> writes:

What are then extensions.. They live in some sort of limbo.. as they

don't

behave like a natural extension of the database... As they behave
differently (because they "require" public shema in path if extensions

are

created while in public schema ).

You've got the wrong mental model. Extensions are containers, which
may *contain* objects that live within schemas, but the extension itself
does not. The key reason for doing it that way is that an extension might
itself contain a schema, or more than one schema.

Put a bit differently, extensions are global objects like roles. With
roles you grant permissions on individual databases for each role to access
it. For extensions you "create extension" within individual databases to
enable the extension in each one - potentially making its components
visible in a user-defined schema, but also the extension can use schemas in
its own non-adjustable ways.

David J.