From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: developer@moregatebiotech.com
Subject: BUG #17526: PG_DUMP is not outputting "CREATE SCHEMA" statements when an extension is assigned to a schema
Date: Wed, 22 Jun 2022 01:59:27 +0000
Message-ID: <17526-2e7a7cf9eb165a82@postgresql.org> (raw)
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
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, developer@moregatebiotech.com
Subject: Re: BUG #17526: PG_DUMP is not outputting "CREATE SCHEMA" statements when an extension is assigned to a schema
In-Reply-To: <17526-2e7a7cf9eb165a82@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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