Thank you for the clarification.
I completely understand the need to make things right. At the same time it baffles me that a breaking change would be introduced somewhere in a point release. The release notes don't seem to even mention this or give a warning to watch out for behavior change:
https://www.postgresql.org/docs/13/release-13-10.html
While adding explicit COMMIT seems to be a working solution (at least for the moment) it would be great to understand the nature of the change, the specifics of what was allowed before and what the behaviour is like after the change, the cases being affected, etc. I (and anyone who stumbles upon this after the upgrade) would appreciate it if you could point me in the right direction of where to look.
My claim about 14.7 was invalid indeed. The same change occurred when upgrading from 14.6.
PG Bug reporting form <noreply@postgresql.org> writes:
> My system has numerous SQL scripts (each script contains multiple SQL
> statements). Many of them look like
> UPDATE ...
> INSERT ...
> ...
> VACUUM ...
> These scripts are being run from a Java program, with AutoCommit=true, via
> JDBC as:
Sorry, you'll have to fix your scripts to do that separately.
(It might be sufficient to add COMMIT before the VACUUM.)
It was a bug that we didn't enforce this requirement all along.
> The same SQL scripts are still working with the latest postgresql 14.7
I'm quite skeptical of that claim; I see the restriction being
enforced the same way in all supported branches.
regards, tom lane