Re: Set AUTOCOMMIT to on in script output by pg_dump

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Tuesday, October 8, 2024, Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:
>> On Wed, 09 Oct 2024 11:10:37 +0900
>> Shinya Kato <Shinya11(dot)Kato(at)oss(dot)nttdata(dot)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".

If AUTOCOMMIT were a mainstream feature then maybe it'd be worth
doing something about this, but IMO it's a deprecated backwater,
so I'm not very excited about it.

If we do want to do something about it, the patch needs more thought
about where to put the additional output. As an example, it looks
like it breaks the expectation that pg_dump-to-text should generate
output identical to pg_dump-to-archive followed by pg_restore-to-text.

> ... 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.

regards, tom lane