BUG #4374: pg_restore does not restore public schema comment

"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. +