Re: Set AUTOCOMMIT to on in script output by pg_dump

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:
>
> > Hi hackers!
> >
> > 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.
> > This is because the script contains SQL statements that cannot be
> > executed within a transaction block.
> >
> > If you simply add set AUTOCOMMIT on to the scripts created by
> > pg_dump/pg_dumpall/pg_restore, they will work fine.
> > A patch is attached
> >
>


> I am not sure if it is good to include psql's meta-command in
> pg_dump/pg_dumpall
> results. Can we assume users will always use psql to restore the pg_dump
> results?


Agreed.  If we aren’t already outputting psql-only stuff I am a strong -1
for making this the first such case.

It would be nice to describe exactly when there is a problem as well since
very few things require being outside of a transaction.  There might be
documentation or code patches possible here to improve matters (like adding
a switch to output begin/commit in the places we’re a user might want
single-transaction behavior) 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.)

David J.