Re: BUG #17810: Update from 13.09 to 13.10 breaks SQLs with VACUUM

On Mon, Feb 27, 2023 at 11:42 AM Cherio <cherio(at)gmail(dot)com> wrote:

> > I think you are working with a completely wrong mental model of what
> this change did.
>
> You are right. My mental model of the change is a speculation, but this is
> only because the specifics of the change are not clarified anywhere.
>
> From what you are saying I am deriving that transaction model is not
> affected, and the individual statements still run within their own atomic
> transactions when AutoCommit is enabled, it's just a limited set of
> commands (at the moment I am aware of VACUUM and .... ANALYZE???) can't be
> bundled with any other statements, because doing that blows up the new
> pipeline :)
>
> BTW, why ANALYZE? I just tested ANALYZE and it seems to work without the
> need to be preceded with COMMIT.
>
> Are VACUUM and ANALYZE the only commands that must be executed separately?
> Again, is there at least a brief description of the scope of what was
> affected?
> I believe I understand it better now but the picture is still made of bits
> and pieces glued with trial and error.
>

The commit that implemented this fix is here, it links to discussion:

https://github.com/postgres/postgres/commit/20432f8731404d2cef2a155144aca5ab3ae98e95

As for your usage of "conn.setAutocommit(true)" - IIUC that is irrelevant
to this entire discussion. You've chosen to bundle up multiple statements
into a single Statement.execute(string) call which obeys the rules of the
simple query protocol - multiple statements:

https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.4
(subsection 55.2.2.1)

Namely:

"When a simple Query message contains more than one SQL statement
(separated by semicolons), those statements are executed as a single
transaction, unless explicit transaction control commands are included to
force a different behavior."

Now, per the documentation for Vacuum:

https://www.postgresql.org/docs/current/sql-vacuum.html

"VACUUM cannot be executed inside a transaction block."
And so per the documentation your script has always been invalid as written.

Is there room for improved communication on the minor-release change in
behavior? Probably.

As for discussing reverting this in the back-branches given new evidence
and scenarios, that is possible and I've yet to go back and fully review
that discussion thread in light of this new information.

David J.