I just realized that the ramifications of this change go further than just VACUUM related statements in the scripts. Assume 2 scripts
UPDATE tableA
UPDATE tableB
and
UPDATE tableA
UPDATE tableB
Before the change they could run in parallel without issues. After the change this will cause one of the queries to fail due to transaction locks. This is a conceptual change and the consequences of it are significant for everyone who came to rely upon the fact that such scripts do not run in one transaction.
While the visible effect of the change, like the one I observe with having VACUUM at the end, are easy to spot, the other adverse effects may prove to be more insidious.
IMHO it is a big conceptual change and I would expect it to come with not only a warning but also with a way to gradually transition to, allowing the legacy behavior at first by default, then on opt-in basis and eventually removing it altogether.
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
