Re: conn.read_only not honored in autocommit mode
От | Daniele Varrazzo |
---|---|
Тема | Re: conn.read_only not honored in autocommit mode |
Дата | |
Msg-id | CA+mi_8ZJ6rXvLJSQ96YK+wR9Wdt9i4BLfcoSmQ4F2eA3Etcz-A@mail.gmail.com обсуждение исходный текст |
Ответ на | conn.read_only not honored in autocommit mode (David Raymond <David.Raymond@tomtom.com>) |
Список | psycopg |
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 managementyou 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 atpreventing accidental changes. > I've just been moving to psycopg recently, and with no set_session I replaced it with putting autocommit = True in theconnection 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 backgroundalready) 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
В списке psycopg по дате отправления: