One more detail. Adding COMMIT; just before VACUUM makes it work again.
It looks like the AutoCommit flag is being ignored after the latest point release.
SQL statements that precede VACUUM include simple INSERT/UPDATE statements, CTEs and some include anonymous Pg/PlSql blocks. None of them start explicit transactions.
Also I was wrong in my previous post saying it still works with server 14.7. The new point release introduces the same regression compared to 14.6.
I do not have version 15 server to test the behavior.
The following bug has been logged on the website:
Bug reference: 17810
Logged by: Yuri Cherio
Email address: cherio@gmail.com
PostgreSQL version: 13.10
Operating system: Ubuntu Linux 20.04.5 LTS
Description:
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:
Statement statement = connection.createStatement();
connection.setAutoCommit(true);
statement.execute(sql);
...
statement.getMoreResults();
...
This worked well for years until the server (not the client program) was
upgraded to 13.10. After the upgrade all compound SQL statements with VACUUM
at the end fail with this error:
09:48:56.585 SEVERE Script processing failed:
org.postgresql.util.PSQLException: ERROR: VACUUM cannot be executed within a
pipeline
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at
org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
JDBC driver version is 42.3.3 (i tried the latest 42.5.4 and it doesn't make
a difference).
The same SQL scripts are still working with the latest postgresql 14.7