Re: pg_upgrade ?deficiency

Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Nov 20, 2013 at 02:36:08PM +0100, Karsten Hilbert wrote:
>>> Karsten Hilbert wrote:
>>>> Let me try to rephrase:
>>>>
>>>> Fact: pg_upgrade can NOT properly upgrade clusters which
>>>>       contain databases that are set to
>>>>       "default_transaction_read_only on"
>>>> Question: Is this intended ?
>>>
>>> I am pretty sure that this is an oversight and hence a bug.
>>
>> oversight, yes ... I thought as much and was therefore a bit
>> cautious of calling it a bug, chose to name it "?deficiency" ;-)
>
> Well, pg_upgrade can't handle every possible configuration.  How
> do we even restore into such a database?  You marked the database
> as read-only, and pg_upgrade is going to honor that and not
> modify it.

That interpretation makes no sense to me.  I know of users who have
databases where 90% of their transactions don't modify data, so
they set the *default* for transactions to read only, and override
that for transactions that are read write.  The default is not, and
never has been, a restriction on what is allowed -- it is a default
that is quite easy to override.  If we have tools that don't handle
that correctly, I consider that a bug.

> I believe a pg_dumpall restore might fail in the same way.

Then it should also be fixed.

> You need to change the default on the old cluster before
> upgrading.  It is overly cumbersome to set the
> default_transaction_read_only for every database connection

Why is this any different from other settings we cover at the front
of pg_dump output?:

| SET statement_timeout = 0;
| SET lock_timeout = 0;
| SET client_encoding = 'UTF8';
| SET standard_conforming_strings = on;
| SET check_function_bodies = false;
| SET client_min_messages = warning;

> and there are many other settings that might also cause failures.

You mean, like the above?

> What you might be able to do is to set PGOPTIONS to "-c
> default_transaction_read_only=false" and run pg_upgrade.  If more
> people report this problem, I could document this work-around.

This is most likely to bite those using serializable transactions
for data integrity, because declaring transactions read only makes
a huge difference in performance in those cases.  That is where I
have seen people set the default for read only to on; they want to
explicitly set it off only when needed.

I would be happy to supply a patch to treat
default_transaction_read_only the same as statement_timeout or
standard_conforming_strings in pg_dump and related utilities.
Since it causes backup/restore failure on perfectly valid databases
I even think this is a bug which merits back-patching.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company