... okay, so the reason why --set-db-properties isn't as general-purpose
a switch as you might think finally penetrated my thick skull :-(
The problem is that, as the patch is currently constituted, that will
result in emitting a lot of "ALTER DATABASE foo" type commands. If
somebody tries to load the pg_dump output into a database not named foo,
mayhem ensues. This is exactly the same problem people have already
noted with respect to COMMENT ON DATABASE, but now we're propagating it to
other operations that have (much) higher downsides for getting it wrong.
What we need, therefore, is ALTER DATABASE CURRENT_DATABASE, which
I see is part of the pending patch for fixing the COMMENT ON DATABASE
problem. That one is stuck in Waiting on Author, but I'm inclined to
go see if I can push it across the finish line, and then come back
to this one.
Even with that, there's a small problem: the backend cannot reasonably
support ALTER DATABASE CURRENT_DATABASE SET TABLESPACE, except perhaps
in the no-op case where the specified tablespace is already the right
one. So this puts a serious hole in my thoughts about allowing the
tablespace to be adjusted during --set-db-properties without --create.
What I think we should do for the time being is to have pg_dump treat
database tablespace as a property it can't adjust after creation, just
as it can't adjust locale or encoding. That's a loss of functionality
for pg_dumpall/pg_upgrade compared to where we are today, in that if
you've set up the template1 or postgres DBs with nondefault tablespace
then that won't propagate to the new cluster. But the same can already
be said about their locale and encoding, and I find it hard to believe
that many people are trying to give those two DBs tablespace settings
different from the cluster default, anyway.
The only way around that that I can see is to give pg_dump some
additional switch along the lines of --i-promise-i-wont-restore-
this-into-a-database-with-a-different-name, and then it could emit
the same sort of
\connect template1
alter database postgres set tablespace ...;
\connect postgres
dance that pg_dumpall is using today. But that sort of ugliness
is exactly what we said we didn't want in this patch.
regards, tom lane
