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(at)iki(dot)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(dot)vrhovnik(at)gmail(dot)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
>