From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: "developer@moregatebiotech.com" <developer@moregatebiotech.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17526: PG_DUMP is not outputting "CREATE SCHEMA" statements
 when an extension is assigned to a schema
In-reply-to: 
 <CAKFQuwZPcWJ8FTQx_uqCoBuQGxyn5ML37iTe5igid1p++UqU7Q@mail.gmail.com>
References: <17526-2e7a7cf9eb165a82@postgresql.org>
 <CAKFQuwZPcWJ8FTQx_uqCoBuQGxyn5ML37iTe5igid1p++UqU7Q@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 22 Jun 2022 06:53:56 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3344793.1655906512.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 22 Jun 2022 10:01:52 -0400
Message-ID: <3344794.1655906512@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3344794.1655906512%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Not a bug.  Extension members are not dumped because the system expects
> that a subsequent create extension command will create them.  That is wh=
at
> it means to be a member of the extension.  Most users are not going to
> execute the =E2=80=9Calter extension add=E2=80=9D command on there datab=
ase as it doesn=E2=80=99t
> really provide end-user useful functionality.

Yeah.  Correct use of this option would be to put the CREATE SCHEMA
command in an extension upgrade script.  (Notionally, you could also
write "alter extension ... add schema ..." in that script, but the
extension script execution mechanism takes care of that for you.)

> Extension assigned to a schema is not the correct model here.  =E2=80=9C=
Given
> control of the schema=E2=80=9D or =E2=80=9CSchema becomes an implementat=
ion detail of the
> extension=E2=80=9D are.

If you want to put the extension's objects into a user-selected schema,
you just create the schema and do "create extension ... with schema ...".
It is not correct to then do "alter extension ... add schema", because
in this approach the schema does not "belong to" the extension.

			regards, tom lane