pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16698: Create extension and search path
8+ messages / 6 participants
[nested] [flat]

* BUG #16698: Create extension and search path
@ 2020-11-03 07:57  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 8+ messages in thread

From: PG Bug reporting form @ 2020-11-03 07:57 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: miha.vrhovnik@gmail.com

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..

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# CREATE SCHEMA foo1;
CREATE SCHEMA
postgres=# SET search_path = 'foo1';
SET
postgres=# CREATE EXTENSION ltree;
CREATE EXTENSION
postgres=# CREATE SCHEMA foo2;
CREATE SCHEMA
postgres=# SET search_path = 'foo2';
SET
postgres=# CREATE EXTENSION ltree;
ERROR:  extension "ltree" already exists
postgres=# SHOW search_path;
 search_path 
-------------
 foo2
(1 row)

It's the same with example from the documentation.(This continues in the
same session, so search_path is set to foo2 at first create extension)

postgres=# CREATE EXTENSION hstore;
CREATE EXTENSION
postgres=# SET search_path = 'foo1';
SET
postgres=# CREATE EXTENSION hstore;
ERROR:  extension "hstore" already exists

It's a shame that this doesn't work as documented as creating migrations or
tests where each test is run in different schema is more difficult.

BR,
Miha



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 12:18  Heikki Linnakangas <hlinnaka@iki.fi>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 8+ messages in thread

From: Heikki Linnakangas @ 2020-11-03 12:18 UTC (permalink / raw)
  To: miha.vrhovnik@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 13:14  Miha Vrhovnik <miha.vrhovnik@gmail.com>
  parent: Heikki Linnakangas <hlinnaka@iki.fi>
  0 siblings, 2 replies; 8+ messages in thread

From: Miha Vrhovnik @ 2020-11-03 13:14 UTC (permalink / raw)
  To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 14:25  Guillaume Lelarge <guillaume@lelarge.info>
  parent: Miha Vrhovnik <miha.vrhovnik@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Guillaume Lelarge @ 2020-11-03 14:25 UTC (permalink / raw)
  To: Miha Vrhovnik <miha.vrhovnik@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; PG Bug reporting form <noreply@postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 14:36  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Miha Vrhovnik <miha.vrhovnik@gmail.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2020-11-03 14:36 UTC (permalink / raw)
  To: Miha Vrhovnik <miha.vrhovnik@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 17:10  Miha Vrhovnik <miha.vrhovnik@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Miha Vrhovnik @ 2020-11-03 17:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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:
> > 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
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 17:50  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Miha Vrhovnik <miha.vrhovnik@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2020-11-03 17:50 UTC (permalink / raw)
  To: Miha Vrhovnik <miha.vrhovnik@gmail.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: BUG #16698: Create extension and search path
@ 2020-11-03 20:46  David G. Johnston <david.g.johnston@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: David G. Johnston @ 2020-11-03 20:46 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Miha Vrhovnik <miha.vrhovnik@gmail.com>; Heikki Linnakangas <hlinnaka@iki.fi>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; PG Bug reporting form <noreply@postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2020-11-03 20:46 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-03 07:57 BUG #16698: Create extension and search path PG Bug reporting form <noreply@postgresql.org>
2020-11-03 12:18 ` Heikki Linnakangas <hlinnaka@iki.fi>
2020-11-03 13:14   ` Miha Vrhovnik <miha.vrhovnik@gmail.com>
2020-11-03 14:25     ` Guillaume Lelarge <guillaume@lelarge.info>
2020-11-03 14:36     ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-03 17:10       ` Miha Vrhovnik <miha.vrhovnik@gmail.com>
2020-11-03 17:50         ` Tom Lane <tgl@sss.pgh.pa.us>
2020-11-03 20:46           ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox