Re: [GENERAL] pg_upgrade ?deficiency - Mailing list pgsql-hackers

On Wed, Nov 27, 2013 at 03:36:12PM -0800, Kevin Grittner wrote:
> Of the people who posted on this thread supporting that, I think
> Tom said it best:
> 
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> > I'm inclined to agree with Kevin that this behavior is wrong and
> > should be fixed (and back-patched), so far as pg_dumpall is concerned.
> > pg_dumpall's charter is to be able to recreate a database cluster's
> > contents in a virgin installation, but it's failing to honor that
> > contract if the cluster has any ALTER DATABASE SET default_read_only
> > settings.  Similarly, I think it's reasonable to try to make pg_upgrade
> > cope with the case.
> >
> > I also agree with *not* changing pg_dump, since it is not the charter
> > of pg_dump to recreate a whole cluster, and the objection about possibly
> > restoring into a database that was meant to be protected by this setting
> > seems to have some force.
> 
> For example, I have seen dumps accidentally restored to the
> postgres database on multiple occasions.  You might, for example,
> flag the postgres database with this, and thereby block such
> accidents.  The patch as it stands would allow pg_dumpall to
> replicate such a cluster, flag and all.  Without the patch you get
> many errors.
> 
> It is also much easier to work around with pg_dump output.  You
> could get a psql connection to a database, set this off for the
> connection, and use \i to read the pg_dump output file.  Or you
> could concatenate a SET statement in front of the pg_dump output
> when piping it in.  There is no correspondingly easy solution for
> pg_dumpall.

Well, I can understand that, but part of the argument was that
default_transaction_read_only is not part of the database, but rather
just the transaction default:

> Karsten wrote:
> Maybe I am splitting hairs but setting transactions to readonly
> per default does not mean the database *as such* is to be readonly.
> It literally applies to the *default* state of transactions (as
> opposed to the ONLY state of transactions). No more, no less.

I ask again!

> What is the logic that has us setting statement_timeout in
> pg_dump but default_transaction_read_only in pg_dumpall?

Why can't I get an answer to that question?  Is it that
statement_timeout is less likely to lead to a restore failure?  Are all
the other settings output from pg_dump safe?  Is only
default_transaction_read_only a problem?  Whatever the answer is, the
patch should explain why we are singling out
default_transaction_read_only for pg_dumpall use and everything else is
in pg_dump.

Why does it feel like I am going around in circles here?  I feel I like
am reliving the materialized view record comparison thread all over
again.  :-(

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +