Re: BUG #16698: Create extension and search path - Mailing list pgsql-bugs

From Guillaume Lelarge
Subject Re: BUG #16698: Create extension and search path
Date
Msg-id CAECtzeVu1mWq20JVkmb6ONiouQg784A8MKkW9yWXLULgaA=zYQ@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16698: Create extension and search path  (Miha Vrhovnik <miha.vrhovnik@gmail.com>)
List pgsql-bugs
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.

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: pg should ignore u+200b zero width space
Next
From: Tom Lane
Date:
Subject: Re: BUG #16698: Create extension and search path