Thread: pg_dump fail to not dump public schema orders

Hello,

I noticed pg_dump failed to not dump creation or comment commands for public
schema when we explicitly ask it to dump public schema.

Shorter example: pg_dump -n public dump will give:

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

CREATE SCHEMA public;


ALTER SCHEMA public OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'standard public schema';


Obviously, it trigger errors when we try to restore it as public schema already
exists.


Git bisect blame this commit (since pg11):

commit 5955d934194c3888f30318209ade71b53d29777f (refs/bisect/bad)
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date:   Thu Jan 25 13:54:42 2018 -0500
    Improve pg_dump's handling of "special" built-in objects.

I first tried to add an only_dump_public_schema test. I am not used to how
pg_dump tests works but I do not think it is the best approach due to how many
test I had to disable for only_dump_public_schema.

Then I tried to change selectDumpableNamespace in order to apply the same
treatment to public schema when we explicitly ask pg_dump to dump public schema.

Unfortunately this broke other tests, all related to how we handle COLLATION.
For example:

#   Failed test 'only_dump_test_schema: should not dump ALTER COLLATION test0
OWNER TO'

#   Failed test 'only_dump_test_schema: should not dump COMMENT ON COLLATION test0'

#   Failed test 'only_dump_test_schema: should not dump CREATE COLLATION test0
FROM "C"'

#   Failed test 'only_dump_test_schema: should not dump REVOKE CREATE ON SCHEMA
public FROM public'


Regards,

Hello,

I noticed pg_dump failed to not dump creation or comment commands for public
schema when we explicitly ask it to dump public schema.

Shorter example: pg_dump -n public dump will give:

[Create schema public....]

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, May 29, 2020, Adrien Nayrat <adrien.nayrat@anayrat.info> wrote:
>> I noticed pg_dump failed to not dump creation or comment commands for
>> public schema when we explicitly ask it to dump public schema.

> As far as I can tell this is working as intended/documented.  The public
> schema doesn’t and doesn’t and shouldn’t get special treatment relative to
> any other user schema here.

Note this is something we intentionally changed a little while ago
(v11, looks like), along with a larger refactoring of pg_dump vs.
pg_dumpall.  But yeah, public is not treated differently from other
schemas anymore.

            regards, tom lane

On 5/29/20 3:56 PM, David G. Johnston wrote:
> On Friday, May 29, 2020, Adrien Nayrat <adrien.nayrat@anayrat.info
> <mailto:adrien.nayrat@anayrat.info>> wrote:
> 
>     Hello,
> 
>     I noticed pg_dump failed to not dump creation or comment commands for public
>     schema when we explicitly ask it to dump public schema.
> 
>     Shorter example: pg_dump -n public dump will give:
> 
>  
> 
>     [Create schema public....]
> 
>  
> As far as I can tell this is working as intended/documented.  The public schema
> doesn’t and doesn’t and shouldn’t get special treatment relative to any other
> user schema here.
> 

I am not sure. See this comment from selectDumpableNamespace:

/*
 * The public schema is a strange beast that sits in a sort of
 * no-mans-land between being a system object and a user object.  We
 * don't want to dump creation or comment commands for it, because
 * that complicates matters for non-superuser use of pg_dump.  But we
 * should dump any ACL changes that have occurred for it, and of
 * course we should dump contained objects.
 */


FYI this behavior appeared with pg11. With pg10 you won't have "CREATE SCHEMA
public" orders.

Regards,

On 5/29/20 7:40 PM, David G. Johnston wrote:
>  
> 
>     FYI this behavior appeared with pg11. With pg10 you won't have "CREATE SCHEMA
>     public" orders.
> 
> 
> That matches what Tom said.
> It is indeed a behavior change and the commit that caused it to change didn't
> change the documentation - so either the current behavior is a bug or the old
> documentation is wrong for failing to describe the old behavior sufficiently.

Yes, if it is expected it should me mentioned in release notes. As is, it is a
regression.

FYI, our continuous integration hit this issue: First we restore the schema and
then we apply migration step. We do this for every schema and this change broke
the initial restoration. It is weird that the restoration can fail on a clear
database.

> 
> I stand by my comment that the current behavior and documentation agree - it
> doesn't call out any special behavior for the public schema being specified in
> "-n" and none is observed (now).
> 
> I'm tending to believe that the code benefits that resulted in this change are
> sufficient to keep new behavior as-is and not go back and introduce special
> public schema handling code to get it back to the way things were.  The public
> schema has issues and at this point the only reason it should exist and be
> populated in a database is for learning or quick debugging.  Its not worth
> breaking stuff to make that point more bluntly but if the natural evolution of
> the code results in people either adapting or abandoning the public schema I
> find that to be an acceptable price for progress.


Excuse me, but there is no mention that public schema exists for learning or
quick debugging?
https://www.postgresql.org/docs/11/ddl-schemas.html

I am pretty sure most users use public schema and even postgres default database.

Regards,