On Tuesday, October 8, 2024, Yugo Nagata <nag...@sraoss.co.jp> wrote:
> On Wed, 09 Oct 2024 11:10:37 +0900 > Shinya Kato <shinya11.k...@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.
