Re: pg_dumpall Sets Roll default_tablespace Before Creating Tablespaces

On Oct20, 2011, at 00:09 , Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Wed, Oct 19, 2011 at 5:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I'm beginning to think that the correct solution to these problems is to
>>> greatly restrict what you can set in ALTER ROLE/DATABASE SET.  Or at
>>> least to document that if you use it, you get to keep both pieces after
>>> you break pg_dump.
>
>> This is another instance of the general principle that we need to
>> create all the objects first, and then set their properties.  I
>> believe you came up with one counterexample where we needed to set the
>> GUC first in order to be able to create the object, but ISTM most of
>> them are going the other way.
>
> Well, a "general principle" for which we already know one counterexample
> isn't general enough for me.  The problem that I'm having here is that
> it's not clear that there is any general solution, short of pg_dumpall
> having variable-by-variable knowledge of which GUCs to set when, and
> maybe even that wouldn't be good enough.

This whole issue reminds me of the situation we had before pg_dump
had the smarts to traverse the object dependency graph and emit schema
objects in the correct order. (pg_dump gained that ability somewhere
around 7.3 or 7.4 if memory serves correctly)

So here's a wild idea. Could we somehow make use of the dependency
machinery to solve this once and for all? Maybe we could record the
dependency between per role and/or database GUC settings and the
referenced objects.

Or we could add a flag "FORCE" to ALTER ROLE/DATABASE SET for pg_dump's
benefit which would skip all validity checks on the value (except it being
of the correct type, maybe).

Taking this even further, why do we bother with non-immutable (i.e.,
depending on the database's contents) checks during ALTER ROLE/DATABASET SET
at all? If we don't record a dependency on referenced schema objects,
nothing prevents that object from being dropped *after* the ALTER ROLE/DATABASE
SET occurred...

If we're trying to protect against typos in settings such as default_tablespace,
a WARNING ought to be sufficient.

best regards,
Florian Pflug