Re: BUG #16698: Create extension and search path

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:

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