MIME-Version: 1.0
References: 
 <DB7PR07MB39163E0AFB907413EE5C10F68738A@DB7PR07MB3916.eurprd07.prod.outlook.com>
In-Reply-To: 
 <DB7PR07MB39163E0AFB907413EE5C10F68738A@DB7PR07MB3916.eurprd07.prod.outlook.com>
From: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Date: Thu, 20 Jul 2023 21:39:46 +0100
Message-ID: 
 <CA+mi_8ZJ6rXvLJSQ96YK+wR9Wdt9i4BLfcoSmQ4F2eA3Etcz-A@mail.gmail.com>
Subject: Re: conn.read_only not honored in autocommit mode
To: David Raymond <David.Raymond@tomtom.com>
Cc: Psycopg <psycopg@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bmi_8ZJ6rXvLJSQ96YK%2BwR9Wdt9i4BLfcoSmQ4F2eA3Etcz-A%40mail.gmail.com>
Precedence: bulk

Hello!

On Tue, 18 Jul 2023 at 21:24, David Raymond <David.Raymond@tomtom.com> wrote:
>
> It appears that the .read_only attribute of a connection is not honored when the connection is in autocommit mode.
>
> There's no mention of this on the Connection classes page for read_only, and in the section linked to in Transactions management you have to read it 3 times to realize it's only used with Connection.transaction().

Indeed. The docs at
<https://www.psycopg.org/psycopg3/docs/basic/transactions.html#transaction-characteristics>
say:

    [transaction parameters] affect the transactions started
implicitly by non-autocommit transactions
    and the ones started explicitly by Connection.transaction() for
both autocommit and non-autocommit
    transactions.

So yes, it's not explicit that *they don't affect implicit autocommit
transactions*... because there is no transaction to affect. We can
improve the docs by making it more explicit.


> Is it the intent to not be used for regular autocommit connections, or is it an oversight?
>
> I used conn.set_session(readonly = True, deferrable = False, autocommit = True) in psycopg2, and that seemed to work at preventing accidental changes.
> I've just been moving to psycopg recently, and with no set_session I replaced it with putting autocommit = True in the connection call and following it with .read_only = True and .deferrable = False

This is indeed a difference of behaviour wrt psycopg2 and it should be
documented in <https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html>.

The change comes from the fact that emulating parameters such as
`read_only` in autocommit means using a radically different approach
compared to non-autocommit mode: in non-autocommit mode there is
syntax such as BEGIN READ ONLY that can be used, whereas in autocommit
we call SET default_transaction_read_only in the session. This means
that the session now has relevant state, connection pooling software
may get in the way, and what happens if the user starts switching
between autocommit and non-autocommit? Should we send SET
default_transaction_read_only TO DEFAULT? I honestly don't remember
what psycopg2 does. The problems with the use of SET
default_transaction_XXX are documented in
<https://www.psycopg.org/docs/connection.html#connection.set_session>
(see "changed in version 2.7"). For psycopg 3 we decided to avoid the
problems/inconsistencies by limiting transaction attributes to...
transactions, so only to the implicit (non-autocommit) and explicit
(transaction() block) ones.

> Since I vastly prefer autocommit mode, is my best bet then to replace
> conn.read_only = True
> with either
> conn.execute("set session characteristics as transaction read only, not deferrable;")
> or
> conn.execute("set default_transaction_read_only to true;")
> conn.execute("set default_transaction_deferrable to false;")
> ?
>
> (Honestly in my mind I thought this is what changing .read_only, .deferrable, or .isolation_level did in the background already)

That's correct: the approach should be the the client executing a SET
default_transaction_read_only TO true (or SET SESSION CHARACTERISTICS
if you prefer: they are equivalent as per
<https://www.postgresql.org/docs/current/sql-set-transaction.html>).

Note that you can also create a by-default-read-only connection by
setting the parameter at connection time, using the 'options'
connection parameter, for example:

    >>> conn = psycopg.connect("", options="-c
default_transaction_read_only=true", autocommit=True)

    >>> conn.execute("create table foo ()")
    Traceback (most recent call last)
    ...
    ReadOnlySqlTransaction: cannot execute CREATE TABLE in a read-only
transaction

    >>>conn.execute("select 1").fetchall()  # connection is
autocommit, so it's not failed
    [(1,)]


> Thank you for your patience with me,

Thank you for reporting these documentation shortcomings! Will fix them.

Cheers

-- Daniele