conn.read_only not honored in autocommit mode

From: David Raymond <David(dot)Raymond(at)tomtom(dot)com>
To: Psycopg <psycopg(at)postgresql(dot)org>
Subject: conn.read_only not honored in autocommit mode
Date: 2023-07-18 20:24:46
Message-ID: DB7PR07MB39163E0AFB907413EE5C10F68738A@DB7PR07MB3916.eurprd07.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: psycopg

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().

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

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)

Thank you for your patience with me,

Responses

Browse psycopg by date

  From Date Subject
Next Message Daniele Varrazzo 2023-07-20 20:39:46 Re: conn.read_only not honored in autocommit mode
Previous Message Daniele Varrazzo 2023-06-03 18:28:29 Re: Seeking Insights on Roll-out Process for Multiple PyPI Packages