This project is archived and is in readonly mode.

#12 ✓invalid
Jean-Baptiste Quenot

[PATCH] Add isolation level for BEGIN READ ONLY

Reported by Jean-Baptiste Quenot | November 12th, 2010 @ 10:56 AM

Comments and changes to this ticket

  • Daniele Varrazzo

    Daniele Varrazzo November 13th, 2010 @ 03:48 AM

    • State changed from “new” to “open”

    I like the idea and would like to apply the patch. It needs documentation and testing though: would you be able to provide them?

    Thank you very much.

    On a side note, I see it uses a syntax only introduced in PG 8.0. I would hear from fog if he's ok in dropping support for PG 7.4 (he should): in this case we may optimize the other BEGIN variants as well. Otherwise the statement should be changed in a BEGIN; SET TRANSACTION READ ONLY;

  • Daniele Varrazzo
  • Daniele Varrazzo

    Daniele Varrazzo November 18th, 2010 @ 01:14 AM

    • State changed from “open” to “invalid”

    After further analysis I think, if the "read only" feature is to be introduced, set_isolation_level is not the correct place. The command issued to the database is not a set transaction isolation level: read only is an orthogonal concept so you can have read only + read committed and read only + serializable.

    If there has to be a read only transaction I think it should be a separate connection method, with a separate member in the connection state. It may also be implemented as a connection subclass.

  • Daniele Varrazzo

    Daniele Varrazzo December 18th, 2010 @ 07:45 PM

    Hi JB,

    I am thinking about a more general overhauling of the connection/transaction settings: we'll talk about that as soon as we have a ML again (it should happen soon). I'm gathering a few use cases and yours will be kept into account for sure.

    BTW, while playing a little bit with session and transaction settings, I've noticed that you can execute set session default_transaction_read_only to true; in a connection: further transactions on the same connection should be in read-only mode.

        In [1]: import psycopg2
        In [2]: cnn = psycopg2.connect("dbname=test")
        In [3]: cur = cnn.cursor()
        In [4]: cur.execute("set session default_transaction_read_only to true;")
        In [5]: cnn.commit() # because the above implicitly started a tx
        In [6]: cur.execute("create table foo(bar text)") # this makes another tx
        InternalError                             Traceback (most recent call last)
        /home/piro/dev/psycopg2/<ipython console> in <module>()
        InternalError: transaction is read-only

    I think this is good enough, as the query must be executed once per connection, not once per transaction, so you can run it e.g. in a function returning a new connection, not at every BEGIN. We'll discuss about other options anyway.


  • Jean-Baptiste Quenot

    Jean-Baptiste Quenot September 1st, 2011 @ 03:36 PM

    Daniele, using "set default" for readonly is a good idea. But if I make the parallel with transaction modes, I can't use "set default_transaction_isolation to SERIALIZABLE" because psycopg2 will override this default setting. I won't suggest to remove the set_isolation_level() function, that would be too much work, but we could add a new set_readonly() function, to be consistent.


  • Daniele Varrazzo

    Daniele Varrazzo September 1st, 2011 @ 04:00 PM

    Hi JBQ,

    Psycopg 2.4.2 has the new set_session() method that allows you to have read only transactions. Internally it uses set default_*. See for the docs.

    set_isolation_level() is not going to be removed, as there's too much code using it, but internally has been reimplemented using the new method, e.g. we don't pass some "BEGIN SERIALIZABLE" at the transaction start, but we change the session's default_transaction_isolation. With this refactoring, not only now we have read only sessions, but we issue less queries at connection and transaction begin time.

    Hope you like the change ;)

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="">the new tracker</a>.
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Shared Ticket Bins

People watching this ticket


Referenced by