Обсуждение: Make pg_dump suppress COMMENT ON SCHEMA public ?
There was some discussion today about restoring pg_dump output as a non-superuser: http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php In 8.3 we have eliminated one of the major roadblocks to doing that, which is that we now allow non-superuser database owners to create trusted procedural languages for themselves. There's still a minor roadblock, which is that at the moment pg_dump emits a "COMMENT ON SCHEMA public" by default, and that fails if you're not the owner of schema public, ie, the bootstrap superuser. In the past we've always written off this kind of thing as just cosmetic, but with the increasing performance advantages of doing a restore in a single transaction, I think it's important to try to eliminate "ignorable errors" in dump/restore. Especially ones as silly as this. Accordingly I propose the attached patch. It's certainly ugly, but it's not very much uglier than what was there already. Anyone who had a custom comment on schema public would lose it, but who does that? Thoughts? regards, tom lane Index: pg_backup_archiver.c =================================================================== RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.151 diff -c -r1.151 pg_backup_archiver.c *** pg_backup_archiver.c 24 Nov 2007 20:26:49 -0000 1.151 --- pg_backup_archiver.c 13 Jan 2008 01:44:00 -0000 *************** *** 2528,2538 **** /* * Avoid dumping the public schema, as it will already be created ... * unless we areusing --clean mode, in which case it's been deleted and ! * we'd better recreate it. */ ! if (!ropt->dropSchema && ! strcmp(te->desc, "SCHEMA") == 0 && strcmp(te->tag, "public") == 0) ! return; /* Select owner, schema, and tablespace as necessary */ _becomeOwner(AH, te); --- 2528,2544 ---- /* * Avoid dumping the public schema, as it will already be created ... * unless we areusing --clean mode, in which case it's been deleted and ! * we'd better recreate it. Likewise for its comment, if any. */ ! if (!ropt->dropSchema) ! { ! if (strcmp(te->desc, "SCHEMA") == 0 && ! strcmp(te->tag, "public") == 0) ! return; ! if (strcmp(te->desc, "COMMENT") == 0 && ! strcmp(te->tag, "SCHEMA public") == 0) ! return; ! } /* Select owner, schema, and tablespace as necessary */ _becomeOwner(AH, te);
Tom Lane wrote: > There was some discussion today about restoring pg_dump output as a > non-superuser: > http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php > > In 8.3 we have eliminated one of the major roadblocks to doing that, > which is that we now allow non-superuser database owners to create > trusted procedural languages for themselves. There's still a minor > roadblock, which is that at the moment pg_dump emits a "COMMENT ON > SCHEMA public" by default, and that fails if you're not the owner > of schema public, ie, the bootstrap superuser. > > In the past we've always written off this kind of thing as just > cosmetic, but with the increasing performance advantages of doing > a restore in a single transaction, I think it's important to try > to eliminate "ignorable errors" in dump/restore. Especially ones > as silly as this. > > Accordingly I propose the attached patch. It's certainly ugly, > but it's not very much uglier than what was there already. > Anyone who had a custom comment on schema public would lose it, > but who does that? > > Thoughts? Could we dump it when it's non-default only? That way the people that *have* set a custom comment on it will still get it restored, just a failure in this case. The majority of people who *haven't* set a comment will not have the problem at all. I don't really like dropping things in the dump if we can avoid it... //Magnus
On Sun, 13 Jan 2008, Magnus Hagander wrote: > Could we dump it when it's non-default only? That way the people that *have* > set a custom comment on it will still get it restored, just a failure in this > case. The majority of people who *haven't* set a comment will not have the > problem at all. > The easiest thing to do would be to drop the default comment. Then only custom comments would be dumped at all (at least for 8.3+ dbs). It's not like "Standard public schema" is particularly enlightening. Kris Jurka
Magnus Hagander <magnus@hagander.net> writes:
> Could we dump it when it's non-default only? That way the people that
> *have* set a custom comment on it will still get it restored, just a
> failure in this case. The majority of people who *haven't* set a comment
> will not have the problem at all.
The patch seems ugly enough without wiring in knowledge of what the
standard comment is :-(
We don't dump non-default comments on other system objects either,
so I don't think it's out of line to suppress the one on schema public.
Perhaps at some point we should add something to pg_description to allow
distinguishing user-supplied comments from built-in ones, and then dump
any user-supplied comment on any system object. But not today ...
regards, tom lane
Kris Jurka <books@ejurka.com> writes:
> The easiest thing to do would be to drop the default comment. Then only
> custom comments would be dumped at all (at least for 8.3+ dbs). It's not
> like "Standard public schema" is particularly enlightening.
Hmm, that's an interesting idea; although I fear that a lot of people
would find the comment being carried forward indefinitely from their
pre-8.3 databases if we don't change pg_dump's behavior.
regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Could we dump it when it's non-default only? That way the people that >> *have* set a custom comment on it will still get it restored, just a >> failure in this case. The majority of people who *haven't* set a comment >> will not have the problem at all. > > The patch seems ugly enough without wiring in knowledge of what the > standard comment is :-( I guess... > We don't dump non-default comments on other system objects either, > so I don't think it's out of line to suppress the one on schema public. The distinction then is if "public" is actually a system object. I would've considered it a non-system object, just that it happens to be created by default. Anyway. I don't see it as a big point - as long as we make sure it's documented in some way somewhere. I doubt any application would actually fail or have any kind of real problems because we miss the comment on the public schema. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> Tom Lane wrote:
>> We don't dump non-default comments on other system objects either,
>> so I don't think it's out of line to suppress the one on schema public.
> The distinction then is if "public" is actually a system object.
Yeah, it's a borderline case, which is exactly why we're having trouble
with it ...
What do you think of Kris' idea of just removing the default comment?
regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: >> Tom Lane wrote: >>> We don't dump non-default comments on other system objects either, >>> so I don't think it's out of line to suppress the one on schema public. > >> The distinction then is if "public" is actually a system object. > > Yeah, it's a borderline case, which is exactly why we're having trouble > with it ... > > What do you think of Kris' idea of just removing the default comment? That'll work for new databases, but as you say it will not work for older once since the comment will just be carried along with them. That said, removing the default comment might be a good idea anyway, because it's not like it's actually adding any value... //Magnus