How to make sure connection autocommit is off? (for cursors)

[CapnSpellcheck]

This is more of a looking-for-information issue.

PostgreSQL has the concept of autocommit mode: any update SQL is committed immediately after issued. This appears to be the default for clients, because when we issue an update with node-postrgres, without closing connection, the data is committed and change is reflected.

The postgresql documentation has the following statement about cursors:

The Connection must not be in autocommit mode. The backend closes cursors at the end of transactions, so in autocommit mode the backend will have closed the cursor before anything can be fetched from it. Source: https://jdbc.postgresql.org/documentation/head/query.html -- yes the doc page is in fact from JDBC, but the note about autocommit seems to apply to the server.

Note also that psql has the command \set AUTOCOMMIT off, but there is no similar functionality in SQL. So there must be a binary-protocol for it. So how can we properly use the pg-cursor object? Have you tested that Cursor.read only reads the passed row count?

[max-l]

I can confirm that autocommit is on by default, contraty to what the documentation says

[boromisp]

Autocommit is no longer a server-side setting. As far as I understand it, setting autocommit off basically means starting a transaction explicitly before running queries, and committing or (rolling back) when appropriate.