Andres Freund <andres@2ndquadrant.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