Re: [GENERAL] pg_upgrade ?deficiency

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(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +