Обсуждение: BUG #4374: pg_restore does not restore public schema comment

The following bug has been logged online:

Bug reference:      4374
Logged by:          Daniel Migowski
Email address:      dmigowski@ikoffice.de
PostgreSQL version: 8.3.3
Operating system:   Windows
Description:        pg_restore does not restore public schema comment
Details:

Hello dear developers,

Currently a schema dump (custom format, containing the public schema
comment) can be restored, but the public schema version is not restored.

I assume you check if a schema already exists and the skip the schema part.
Please don't skip setting the comment, since we use it for versioning
purposes, and restoring from a dump breaks it.

With best regards,
Daniel Migowski

"Daniel Migowski" <dmigowski@ikoffice.de> writes:
> Currently a schema dump (custom format, containing the public schema
> comment) can be restored, but the public schema version is not restored.

> I assume you check if a schema already exists and the skip the schema part.

That assumption is false, so it's not entirely clear to me exactly what
you are complaining about.  Please provide a specific test case --- what
did you do, what happened, what would you like to happen instead?

            regards, tom lane

Tom Lane wrote:
> "Daniel Migowski" <dmigowski@ikoffice.de> writes:
>> Currently a schema dump (custom format, containing the public schema
>> comment) can be restored, but the public schema version is not restored.
>
>> I assume you check if a schema already exists and the skip the schema part.
>
> That assumption is false, so it's not entirely clear to me exactly what
> you are complaining about.  Please provide a specific test case --- what
> did you do, what happened, what would you like to happen instead?

The issue actually appears to be that the comment on default schema like
`public' isn't dumped in the first place.

Setup:

    CREATE DATABASE re;
    \c re
    COMMENT ON SCHEMA public IS 'public comment';
    CREATE SCHEMA testschema;
    COMMENT ON SCHEMA testschema IS 'testschema comment';
    \q

\dn+ shows the comments as set on schema `public' and `testschema'.

The output of:
    pg_dump re
includes the statement:

    COMMENT ON SCHEMA testschema IS 'testschema comment';

but lacks any COMMENT statement for the `public' schema.

So: the user's report is incorrect in blaming pg_restore, but correct in
that comments on the public schema (and presumably other default schema)
aren't preserved by pg_dump | pg_restore. The real reason appears to be
that they're not dumped in the first place.

I haven't checked to see if a custom dump behaves differently.

--
Craig Ringer

Craig Ringer <craig@postnewspapers.com.au> writes:
> The issue actually appears to be that the comment on default schema like
> `public' isn't dumped in the first place.

Hmm ... there is a specific exclusion in _printTocEntry to suppress
dumping of either the CREATE SCHEMA command for "public" or its comment.
I don't quite recall the reasoning [ digs... ]  Oh, here it is:
http://archives.postgresql.org/pgsql-committers/2008-01/msg00212.php
You can probably find some related discussion in the archives right
before that, but the commit message is clear enough.

On the whole, since "public" is a quasi-built-in object, it doesn't seem
like a very good idea to depend on being able to alter its properties.
Why not attach your version comment to some other, entirely user-defined
object?  "CREATE SCHEMA version" perhaps ...

            regards, tom lane

Hallo Craig,

Craig Ringer schrieb:
> So: the user's report is incorrect in blaming pg_restore, but correct in
> that comments on the public schema (and presumably other default schema)
> aren't preserved by pg_dump | pg_restore. The real reason appears to be
> that they're not dumped in the first place.
>
I do a dump on Linux Postgres 8.3.1 in custom format. When I try to read
it (gzip -d > myfile;less myfile) i find a string like "COMMENT ON
SCHEMA public IS 'mycomment'". So I assumed that pg_dump actually dumps
it. But I don't know why there is a "DROP SCHEMA public" in the file.
Thats why I assumed pg_restore fails here.
> I haven't checked to see if a custom dump behaves differently.
>
> Craig Ringer
>
I didn't check the plain text format :).

Daniel Migowski

Tom Lane wrote:
> Craig Ringer <craig@postnewspapers.com.au> writes:
> > The issue actually appears to be that the comment on default schema like
> > `public' isn't dumped in the first place.
>
> Hmm ... there is a specific exclusion in _printTocEntry to suppress
> dumping of either the CREATE SCHEMA command for "public" or its comment.
> I don't quite recall the reasoning [ digs... ]  Oh, here it is:
> http://archives.postgresql.org/pgsql-committers/2008-01/msg00212.php
> You can probably find some related discussion in the archives right
> before that, but the commit message is clear enough.
>
> On the whole, since "public" is a quasi-built-in object, it doesn't seem
> like a very good idea to depend on being able to alter its properties.
> Why not attach your version comment to some other, entirely user-defined
> object?  "CREATE SCHEMA version" perhaps ...

I have added a comment in the source code about this.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +