Re: [GENERAL] pg_upgrade ?deficiency

Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> FWIW, I am less than convinced that it is correct for
> pg_dump[all] to emit SET default_transaction_readonly = off;

It doesn't change the database setting, just the connection which
is issuing commands to create global object -- ones that don't
reside in the database we are connected to.  As the comment in
pg_dumpall.c says, right above where I added this:

    /*
     * We used to emit \connect postgres here, but that served no purpose
     * other than to break things for installations without a postgres
     * database.  Everything we're restoring here is a global, so whichever
     * database we're connected to at the moment is fine.
     */

> The user might explicitly have set that to prevent against
> somebody restoring into the wrong database or somesuch. Why is it
> our business to override such decisions?

Hmm.  If your argument had been that the user intended that the
database be a read-only database, then I would say that your
argument held no water.  Any user can choose to make any
transaction (or all subsequent transactions on the connection)
read-write any time they want.  The problem with pg_dumpall as it
stands is that it sets the databases to that default and then tries
to load data into them, which fails.

But you have a point regarding adding what I proposed to pg_dump.
The more I think about it, the more I'm inclined to think that
pg_dumpall should insert this right after the \connect to a
database it is going to write to, rather than affecting pg_dump
output at all. That would allow you default protection against
writing pg_dump output to a database that was flagged this way.
You could get around it by connecting interactively with psql,
issuing a SET command, and bringing in dump output with \i from a
text file.  If we go this way, would we want options on pg_restore
and/or psql to issue this set when reading in a file (or piped
input)?

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