Re: Set AUTOCOMMIT to on in script output by pg_dump

On Tuesday, October 8, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Tuesday, October 8, 2024, Yugo Nagata <nagata@sraoss.co.jp> wrote:
> >> On Wed, 09 Oct 2024 11:10:37 +0900
> >> Shinya Kato <Shinya11.Kato@oss.nttdata.com> wrote:
> >>> When SQL scripts created with pg_dump/pg_dumpall/pg_restore are
> executed
> >>> in psql with AUTOCOMMIT turned off, they will not succeed in many
> cases.
>
> > Agreed.  If we aren’t already outputting psql-only stuff I am a strong -1
> > for making this the first such case.
>
> I really doubt that this is the only way in which you can break a
> pg_dump script by executing it in a non-default psql environment.
> We'd likely be better advised to spend some documentation effort
> recommending that pg_dump scripts be executed under "psql --no-psqlrc".


+1

Reinforcing that our output script basically assumes a default execution
environment seems worth mentioning even if it seems self-evident once it’s
said.



>
>
> ... but this approach breaks well-established
> > encapsulation and overrides user expectations in a bad way (since
> > autocommit=on is the default they choose to turn it off so turning it
> back
> > on silently - not even documented - is bad.)
>
> That particular angle doesn't bother me so much, because pg_dump
> scripts already feel free to change search_path as well as a bunch
> of other server parameters.
>

I wasn’t referring to the idea these should be restorable on non-PostgreSQL
systems though, only that if someone wanted to just open a connection in
their rust driver and send this text through that session it will (mostly?)
work.

pg_dumpall, though, is fundamentally tied to psql if databases are dumped,
if the resultant script has to be platform independently executable.  I’m
open to a patch addressing this more narrowly but I’m still thinking that
we should be telling the user to use defaults instead of enforcing
ourselves.

David J.