- Fixed the psycopg2 dialect to use its

set_isolation_level() method instead of relying
upon the base "SET SESSION ISOLATION" command,
as psycopg2 resets the isolation level on each new
transaction otherwise.

4 files changed, 85 insertions, 18 deletions

diff --git a/CHANGES b/CHANGES
index 0f35f9261..b1f3af2e1 100644
--- a/CHANGES
+++ b/CHANGES
@@ -141,7 +141,14 @@ CHANGES
 
   - Fixed bug where "Can't add additional column" message
     would display the wrong name.
-        
+
+- postgresql
+  - Fixed the psycopg2 dialect to use its
+    set_isolation_level() method instead of relying
+    upon the base "SET SESSION ISOLATION" command,
+    as psycopg2 resets the isolation level on each new 
+    transaction otherwise.
+    
 - mssql
   - Fixed "default schema" query to work with
     pymssql backend.
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 5eba16372..89769b8c0 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -39,16 +39,17 @@ apply; no RETURNING clause is emitted nor is the sequence pre-executed in this
 case.
 
 To force the usage of RETURNING by default off, specify the flag
-``implicit_returning=False`` to :func:`create_engine`.
+``implicit_returning=False`` to :func:`.create_engine`.
 
 Transaction Isolation Level
 ---------------------------
 
-:func:`create_engine` accepts an ``isolation_level`` parameter which results
+:func:`.create_engine` accepts an ``isolation_level`` parameter which results
 in the command ``SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL
 <level>`` being invoked for every new connection. Valid values for this
 parameter are ``READ_COMMITTED``, ``READ_UNCOMMITTED``, ``REPEATABLE_READ``,
-and ``SERIALIZABLE``.
+and ``SERIALIZABLE``.  Note that the psycopg2 dialect does *not* use this
+technique and uses psycopg2-specific APIs (see that dialect for details).
 
 INSERT/UPDATE...RETURNING
 -------------------------
@@ -57,7 +58,7 @@ The dialect supports PG 8.2's ``INSERT..RETURNING``, ``UPDATE..RETURNING`` and
 ``DELETE..RETURNING`` syntaxes.   ``INSERT..RETURNING`` is used by default
 for single-row INSERT statements in order to fetch newly generated
 primary key identifiers.   To specify an explicit ``RETURNING`` clause,
-use the :meth:`_UpdateBase.returning` method on a per-statement basis::
+use the :meth:`._UpdateBase.returning` method on a per-statement basis::
 
     # INSERT..RETURNING
     result = table.insert().returning(table.c.col1, table.c.col2).\\
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index aa5f8d32f..04b4e1fb7 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -12,30 +12,57 @@ Note that psycopg1 is **not** supported.
 Unicode
 -------
 
-By default, the Psycopg2 driver uses the ``psycopg2.extensions.UNICODE`` extension, such that the DBAPI receives and returns all strings as Python Unicode objects directly - SQLAlchemy passes these values through without change.   Note that this setting requires that the PG client encoding be set to one which can accomodate the kind of character data being passed - typically ``utf-8``.   If the Postgresql database is configured for ``SQL_ASCII`` encoding, which is often the default for PG installations, it may be necessary for non-ascii strings to be encoded into a specific encoding before being passed to the DBAPI. If changing the database's client encoding setting is not an option, specify ``use_native_unicode=False`` as a keyword argument to ``create_engine()``, and take note of the ``encoding`` setting as well, which also defaults to ``utf-8``.   Note that disabling "native unicode" mode has a slight performance penalty, as SQLAlchemy now must translate unicode strings to/from an encoding such as utf-8, a task that is handled more efficiently within the Psycopg2 driver natively.   
+By default, the Psycopg2 driver uses the ``psycopg2.extensions.UNICODE``
+extension, such that the DBAPI receives and returns all strings as Python
+Unicode objects directly - SQLAlchemy passes these values through without
+change. Note that this setting requires that the PG client encoding be set to
+one which can accomodate the kind of character data being passed - typically
+``utf-8``. If the Postgresql database is configured for ``SQL_ASCII``
+encoding, which is often the default for PG installations, it may be necessary
+for non-ascii strings to be encoded into a specific encoding before being
+passed to the DBAPI. If changing the database's client encoding setting is not
+an option, specify ``use_native_unicode=False`` as a keyword argument to
+``create_engine()``, and take note of the ``encoding`` setting as well, which
+also defaults to ``utf-8``. Note that disabling "native unicode" mode has a
+slight performance penalty, as SQLAlchemy now must translate unicode strings
+to/from an encoding such as utf-8, a task that is handled more efficiently
+within the Psycopg2 driver natively.
 
 Connecting
 ----------
 
-URLs are of the form ``postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]``.
+URLs are of the form
+``postgresql+psycopg2://user:password@host:port/dbname[?key=value&key=value...]``.
 
-psycopg2-specific keyword arguments which are accepted by :func:`~sqlalchemy.create_engine()` are:
+psycopg2-specific keyword arguments which are accepted by
+:func:`.create_engine()` are:
 
-* *server_side_cursors* - Enable the usage of "server side cursors" for SQL statements which support
-  this feature.  What this essentially means from a psycopg2 point of view is that the cursor is 
-  created using a name, e.g. `connection.cursor('some name')`, which has the effect that result rows
-  are not immediately pre-fetched and buffered after statement execution, but are instead left 
-  on the server and only retrieved as needed.    SQLAlchemy's :class:`~sqlalchemy.engine.base.ResultProxy`
-  uses special row-buffering behavior when this feature is enabled, such that groups of 100 rows 
-  at a time are fetched over the wire to reduce conversational overhead.
-* *use_native_unicode* - Enable the usage of Psycopg2 "native unicode" mode per connection.  True  
-  by default.
+* *server_side_cursors* - Enable the usage of "server side cursors" for SQL
+  statements which support this feature. What this essentially means from a
+  psycopg2 point of view is that the cursor is created using a name, e.g.
+  `connection.cursor('some name')`, which has the effect that result rows are
+  not immediately pre-fetched and buffered after statement execution, but are
+  instead left on the server and only retrieved as needed. SQLAlchemy's
+  :class:`~sqlalchemy.engine.base.ResultProxy` uses special row-buffering
+  behavior when this feature is enabled, such that groups of 100 rows at a
+  time are fetched over the wire to reduce conversational overhead.
+* *use_native_unicode* - Enable the usage of Psycopg2 "native unicode" mode
+  per connection. True by default.
 
 Transactions
 ------------
 
 The psycopg2 dialect fully supports SAVEPOINT and two-phase commit operations.
 
+Transaction Isolation Level
+---------------------------
+
+The ``isolation_level`` parameter of :func:`.create_engine` here makes use
+psycopg2's ``set_isolation_level()`` connection method, rather than
+issuing a ``SET SESSION CHARACTERISTICS`` command.   This because psycopg2
+resets the isolation level on each new transaction, and needs to know
+at the API level what level should be used.
+
 NOTICE logging
 ---------------
 
@@ -208,7 +235,25 @@ class PGDialect_psycopg2(PGDialect):
         return psycopg
     
     def on_connect(self):
-        base_on_connect = super(PGDialect_psycopg2, self).on_connect()
+        if self.isolation_level is not None:
+            extensions = __import__('psycopg2.extensions').extensions
+            isol = {
+            'READ_COMMITTED':extensions.ISOLATION_LEVEL_READ_COMMITTED, 
+            'READ_UNCOMMITTED':extensions.ISOLATION_LEVEL_READ_UNCOMMITTED, 
+            'REPEATABLE_READ':extensions.ISOLATION_LEVEL_REPEATABLE_READ,
+            'SERIALIZABLE':extensions.ISOLATION_LEVEL_SERIALIZABLE
+            
+            }
+            def base_on_connect(conn):
+                try:
+                    conn.set_isolation_level(isol[self.isolation_level])
+                except:
+                    raise exc.InvalidRequestError(
+                                "Invalid isolation level: '%s'" % 
+                                self.isolation_level)
+        else:
+            base_on_connect = None
+            
         if self.dbapi and self.use_native_unicode:
             extensions = __import__('psycopg2.extensions').extensions
             def connect(conn):
diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py
index 86b3617ab..a605594d4 100644
--- a/test/dialect/test_postgresql.py
+++ b/test/dialect/test_postgresql.py
@@ -1325,9 +1325,23 @@ class MiscTest(TestBase, AssertsExecutionResults, AssertsCompiledSQL):
                             isolation_level='SERIALIZABLE')
         eq_(eng.execute('show transaction isolation level').scalar(),
             'serializable')
+        
+        # check that it stays
+        conn = eng.connect()
+        eq_(conn.execute('show transaction isolation level').scalar(),
+            'serializable')
+        conn.close()
+
+        conn = eng.connect()
+        eq_(conn.execute('show transaction isolation level').scalar(),
+            'serializable')
+        conn.close()
+        
         eng = create_engine(testing.db.url, isolation_level='FOO')
         if testing.db.driver == 'zxjdbc':
             exception_cls = eng.dialect.dbapi.Error
+        elif testing.db.driver == 'psycopg2':
+            exception_cls = exc.InvalidRequestError
         else:
             exception_cls = eng.dialect.dbapi.ProgrammingError
         assert_raises(exception_cls, eng.execute,