Re: pg_upgrade ?deficiency - Mailing list pgsql-general

On Thu, Nov 21, 2013 at 06:22:50AM -0800, Kevin Grittner wrote:
> > 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.

OK, this is good information to hear.

> > I believe a pg_dumpall restore might fail in the same way.
>
> Then it should also be fixed.

Yes, that is easy to do.

> > 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.

Not sure about backpatching.  default_transaction_read_only has been
around since 7.4.  Setting it to true would cause pg_dump to fail unless
you changed the database setting, and pg_dumpall would fail completely
as there is no way to turn off the database setting.

The problem is that I don't remember any report of this failing in
pg_dump, pg_dumpall, or pg_upgrade, so saying it is a major issue is
hard to accept.

However, looking forward, I think we should add it to pg_dump (and hence
pg_dumpall), and we should fix pg_upgrade so it is more reliable.  I am
thinking we should either document in pg_upgrade the use of PGOPTIONS to
avoid this issue, or have pg_upgrade append to PGOPTIONS in its
environment to use some of pg_dump's resets, and that will be passed to
libpq connections, psql, and all the utilities pg_upgrade calls.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +