Re: BUG #5184: default tablespace owner is not dumped

The following bug has been logged online:

Bug reference: 5184
Logged by: Robert Haas
Email address: robertmhaas(at)gmail(dot)com
PostgreSQL version: CVS HEAD
Operating system: Linux
Description: default tablespace owner is not dumped
Details:

The following command does not change the output of "pg_dumpall":

alter tablespace pg_default owner to bob;

But this one does:

alter tablespace foo owner to bob;

The problem is that we only emit CREATE TABLESPACE commands for non-system
tablespaces. That is correct, but it seems like we might need to emit ALTER
TABLESPACE commands instead for system tablespaces.

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> The following command does not change the output of "pg_dumpall":
> alter tablespace pg_default owner to bob;

I don't think this is a bug. It's one specific aspect of a general
principle that system objects don't get dumped. If they did, using
pg_dump to upgrade across major versions would be somewhere between
impossible and your worst nightmare.

It might be nice if manual changes to system objects got dumped,
but that's really an AI-complete problem --- which properties of
the objects represent manual changes, and how can we know whether
trying to apply those changes to a new system version will work?

regards, tom lane

On Fri, Nov 13, 2009 at 1:13 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
>> The following command does not change the output of "pg_dumpall":
>> alter tablespace pg_default owner to bob;
>
> I don't think this is a bug.  It's one specific aspect of a general
> principle that system objects don't get dumped.  If they did, using
> pg_dump to upgrade across major versions would be somewhere between
> impossible and your worst nightmare.
>
> It might be nice if manual changes to system objects got dumped,
> but that's really an AI-complete problem --- which properties of
> the objects represent manual changes, and how can we know whether
> trying to apply those changes to a new system version will work?

Well, in this particular case, the existence of the default tablespace
seems like something that should not be dumped, but all properties
other than name and location - currently, owner and acl - seem like
they should be dumped. If we don't, then we have the odd situation
that dumping and restoring a database on THE SAME version of
PostgreSQL doesn't produce an equivalent database - you may have
permissions errors where you didn't before, or visca-versa.

I think it would be over the top to suggest that pg_dump has to cope
with modifications that can only occur through manual updates to the
system catalogs, but it seems like anything that can be done using DDL
statements should be handled. It seems a little wonky to admit ALTER
statements against internal Pg names, I agree... I suppose we could
try to define some alternate syntax.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I think it would be over the top to suggest that pg_dump has to cope
> with modifications that can only occur through manual updates to the
> system catalogs, but it seems like anything that can be done using DDL
> statements should be handled.

Like, say, DELETE FROM pg_proc?

Basically, if you use superuser powers to fool with the definition of
any system object, it's on your own head whether the result works at all
and whether or how to preserve that change into a new version of
Postgres. I do not think it's part of pg_dump's charter to try to
handle that. In a significant fraction of cases, preserving the change
would be exactly the wrong thing, but there is no way for pg_dump to
know the difference.

regards, tom lane

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I think it would be over the top to suggest that pg_dump has to cope
>> with modifications that can only occur through manual updates to the
>> system catalogs, but it seems like anything that can be done using DDL
>> statements should be handled.
>
> Like, say, DELETE FROM pg_proc?

That's not DDL...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Tom Lane wrote:
>
> It might be nice if manual changes to system objects got dumped,
> but that's really an AI-complete problem --- which properties of
> the objects represent manual changes, and how can we know whether
> trying to apply those changes to a new system version will work?

A diff against template0 or template1 could help, I guess.

Bug #3684 is related, though in this case the schema "public" may be
considered a preexisting *user* object.

On Fri, Nov 13, 2009 at 10:39 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> Tom Lane wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> I think it would be over the top to suggest that pg_dump has to cope
>>> with modifications that can only occur through manual updates to the
>>> system catalogs, but it seems like anything that can be done using DDL
>>> statements should be handled.
>>
>> Like, say, DELETE FROM pg_proc?
>
> That's not DDL...

Exactly. There's nothing we can do about random changes to system
catalogs, but it seems pretty strange to allow someone to issue an
ALTER TABLESPACE command but then ignore it in pg_dump.

...Robert