Re: [GENERAL] pg_upgrade ?deficiency

Sending to hackers for comment; seems setting
default_transaction_read_only to true via ALTER database/user or
postgresql.conf can cause pg_dump, pg_dumpall, and pg_upgrade failures.
We are considering the right solution:

---------------------------------------------------------------------------

On Fri, Nov 22, 2013 at 01:32:30PM -0500, Bruce Momjian wrote:
> 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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +