Re: BUG #16698: Create extension and search path

Le mar. 3 nov. 2020 à 14:48, Miha Vrhovnik <miha(dot)vrhovnik(at)gmail(dot)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(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
>>
>

--
Guillaume.