Psycopg psycopg

Comments and changes to this ticket

• 

  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 November 13th, 2010 @ 03:48 AM

  • Tag set to “patch”
• 

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

  Cheers.

• You flagged this item as spam.
  

  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.

  WDYT?

• 

  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 http://initd.org/psycopg/docs/connection.html#connection.set_session 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 ;)