pg_dumpall SET default_transaction_read_only = off (was Re: == PostgreSQL Weekly News - January 28 2018 ==)

Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> writes:
> On Mon, Jan 29, 2018 at 03:57:48AM +0100, David Fetter wrote:
>> Tom Lane pushed:
>> ... This leaves us with no solution for the
>> default_transaction_read_only issue that commit 4bd371f6f intended to work
>> around, other than "you gotta remove such settings before dumping/upgrading".
>> However, in view of the fact that parallel restore broke that hack years ago
>> and no one has noticed, it's fair to question how many people care.

> I do (I am OP from the original report). The reason no one's
> noticed is that so far Debian's pg_upgradecluster doesn't by
> default seem to use more than one job for restore (it does
> use pg_dump/pg_restore over pg_upgrade by default).

> However, reading _this_ commit message ...

>> ... Commit
>> 4bd371f6f's hack to emit "SET default_transaction_read_only = off" is gone: we
>> now dodge that problem by the expedient of not issuing ALTER DATABASE SET
>> commands until after reconnecting to the target database.

> ... am I right in assuming my use case (dumping/restoring
> databases with default_transaction_read_only=True) should
> still work without removing that setting beforehand ?

No; you're reading the commits in reverse order. The hack I thought we
could use to preserve that behavior didn't work.

AFAICS, the only way we could maintain something like the old behavior
here is to promote it to a full-fledged feature, something like a
pg_dump/pg_restore option "--ignore-read-only" to make those programs
issue "SET default_transaction_read_only = off" at the right times,
which in turn would need to be invoked by a similar pg_dumpall switch.

(You could maybe argue for having pg_dumpall invoke that by default,
but if we're going this far we might as well go the extra mile and
make it switchable all the way down. One of the ways in which this
was a hack, IMO, was that there wasn't a switch required to enable it.
The argument that this behavior is always appropriate for pg_dumpall
but never for pg_dump seems pretty thin to me.)

While that doesn't seem like it would be terribly hard to do, I don't
personally have the interest to go do it.

regards, tom lane