Обсуждение: BUG #17526: PG_DUMP is not outputting "CREATE SCHEMA" statements when an extension is assigned to a schema

The following bug has been logged on the website:

Bug reference:      17526
Logged by:          chris
Email address:      developer@moregatebiotech.com
PostgreSQL version: 14.4
Operating system:   Linux
Description:

PG_DUMP is not outputting "CREATE SCHEMA" statements when an extension is
assigned to a schema.

I've also verfied this same behaviour on version 13.7 running under Windows
and also using the adminpack extension created in pg_catalog instead of
hstore in public.

A shell script to demonstrate the issue:

createdb test1
createdb test2
psql test1 -c "create schema test"
psql test2 -c "create schema test"
psql -d test2 -c "create extension hstore with schema public"
psql -d test2 -c "alter extension hstore add schema test"
pg_dump --no-comments test1
pg_dump --no-comments test2

Following are the results of the script, noting that for the case of the
test1 db, the "CREATE SCHEMA test;" statement is being correctly output by
pg_dump but in the case of the test2 db, which has the hstore extension
added to it, the create schema statement is absent:


user@debian-testing:~$ createdb test1
user@debian-testing:~$ createdb test2
user@debian-testing:~$ psql test1 -c "create schema test"
CREATE SCHEMA
user@debian-testing:~$ psql test2 -c "create schema test"
CREATE SCHEMA
user@debian-testing:~$ psql -d test2 -c "create extension hstore with schema
public"
CREATE EXTENSION
user@debian-testing:~$ psql -d test2 -c "alter extension hstore add schema
test"
ALTER EXTENSION
user@debian-testing:~$ pg_dump --no-comments test1
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.4 (Debian 14.4-1)
-- Dumped by pg_dump version 14.4 (Debian 14.4-1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: test; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA test;


ALTER SCHEMA test OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

user@debian-testing:~$ pg_dump --no-comments test2
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.4 (Debian 14.4-1)
-- Dumped by pg_dump version 14.4 (Debian 14.4-1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: hstore; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;


--
-- PostgreSQL database dump complete
--

The following bug has been logged on the website:

Bug reference:      17526
Logged by:          chris
Email address:      developer@moregatebiotech.com
PostgreSQL version: 14.4
Operating system:   Linux
Description:       

PG_DUMP is not outputting "CREATE SCHEMA" statements when an extension is
assigned to a schema.

"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 what
> it means to be a member of the extension.  Most users are not going to
> execute the “alter extension add” command on there database as it doesn’t
> 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.  “Given
> control of the schema” or “Schema becomes an implementation detail of the
> extension” 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