Dropped isolation level from the connection object

Don't issue a SET TRANSACTION ISOLATION LEVEL at every begin: use PG's
GUC default, eventually set by set_transaction.

Dropped the last query at connection, yay!

Method set_isolation_level() and property isolation_level refactored using
the new structures, keeping the previous semantic.

1 files changed, 113 insertions, 50 deletions

diff --git a/psycopg/connection_int.c b/psycopg/connection_int.c
index 5e2ba02..de7083f 100644
--- a/psycopg/connection_int.c
+++ b/psycopg/connection_int.c
@@ -34,6 +34,19 @@
 
 #include <string.h>
 
+/* Mapping from isolation level name to value exposed by Python.
+ * Only used for backward compatibility by the isolation_level property */
+
+const IsolationLevel conn_isolevels[] = {
+    {"",                    0}, /* autocommit */
+    {"read uncommitted",    1},
+    {"read committed",      2},
+    {"repeatable read",     3},
+    {"serializable",        4},
+    {"default",            -1},
+    { NULL }
+};
+
 
 /* Return a new "string" from a char* from the database.
  *
@@ -358,22 +371,60 @@ exit:
     return rv;
 }
 
+
 int
-conn_get_isolation_level(PGresult *pgres)
+conn_get_isolation_level(connectionObject *self)
 {
-    static const char lvl1a[] = "read uncommitted";
-    static const char lvl1b[] = "read committed";
-    int rv;
+    PGresult *pgres;
+    int rv = -1;
+    char *lname;
+    const IsolationLevel *level;
+
+    /* this may get called by async connections too: here's your result */
+    if (self->autocommit) {
+        return 0;
+    }
+
+    Py_BEGIN_ALLOW_THREADS;
+    pthread_mutex_lock(&self->lock);
+    Py_BLOCK_THREADS;
+
+    if (!psyco_green()) {
+        Py_UNBLOCK_THREADS;
+        pgres = PQexec(self->pgconn, psyco_transaction_isolation);
+        Py_BLOCK_THREADS;
+    } else {
+        pgres = psyco_exec_green(self, psyco_transaction_isolation);
+    }
+
+    if (pgres == NULL || PQresultStatus(pgres) != PGRES_TUPLES_OK) {
+        PyErr_SetString(OperationalError,
+                         "can't fetch default_transaction_isolation");
+        goto endlock;
+    }
 
-    char *isolation_level = PQgetvalue(pgres, 0, 0);
+    /* find the value for the requested isolation level */
+    lname = PQgetvalue(pgres, 0, 0);
+    level = conn_isolevels;
+    while ((++level)->name) {
+        if (0 == strcasecmp(level->name, lname)) {
+            rv = level->value;
+            break;
+        }
+    }
 
-    if ((strcmp(lvl1b, isolation_level) == 0)     /* most likely */
-        || (strcmp(lvl1a, isolation_level) == 0))
-        rv = ISOLATION_LEVEL_READ_COMMITTED;
-    else /* if it's not one of the lower ones, it's SERIALIZABLE */
-        rv = ISOLATION_LEVEL_SERIALIZABLE;
+    if (-1 == rv) {
+        char msg[256];
+        snprintf(msg, sizeof(msg), "unexpected isolation level: '%s'", lname);
+        PyErr_SetString(OperationalError, msg);
+    }
 
-    CLEARPGRES(pgres);
+endlock:
+    IFCLEARPGRES(pgres);
+
+    Py_UNBLOCK_THREADS;
+    pthread_mutex_unlock(&self->lock);
+    Py_END_ALLOW_THREADS;
 
     return rv;
 }
@@ -477,24 +528,8 @@ conn_setup(connectionObject *self, PGconn *pgconn)
         CLEARPGRES(pgres);
     }
 
-    if (!green) {
-        Py_UNBLOCK_THREADS;
-        pgres = PQexec(pgconn, psyco_transaction_isolation);
-        Py_BLOCK_THREADS;
-    } else {
-        pgres = psyco_exec_green(self, psyco_transaction_isolation);
-    }
-
-    if (pgres == NULL || PQresultStatus(pgres) != PGRES_TUPLES_OK) {
-        PyErr_SetString(OperationalError,
-                         "can't fetch default_isolation_level");
-        IFCLEARPGRES(pgres);
-        Py_UNBLOCK_THREADS;
-        pthread_mutex_unlock(&self->lock);
-        Py_BLOCK_THREADS;
-        return -1;
-    }
-    self->isolation_level = conn_get_isolation_level(pgres);
+    /* for reset */
+    self->autocommit = 0;
 
     Py_UNBLOCK_THREADS;
     pthread_mutex_unlock(&self->lock);
@@ -779,7 +814,7 @@ _conn_poll_setup_async(connectionObject *self)
          * expected to manage the transactions himself, by sending
          * (asynchronously) BEGIN and COMMIT statements.
          */
-        self->isolation_level = ISOLATION_LEVEL_AUTOCOMMIT;
+        self->autocommit = 1;
 
         /* If the datestyle is ISO or anything else good,
          * we can skip the CONN_STATUS_DATESTYLE step. */
@@ -989,7 +1024,14 @@ conn_set(connectionObject *self, const char *param, const char *value)
 int
 conn_set_autocommit(connectionObject *self, int value)
 {
+    Py_BEGIN_ALLOW_THREADS;
+    pthread_mutex_lock(&self->lock);
+
     self->autocommit = value;
+
+    pthread_mutex_unlock(&self->lock);
+    Py_END_ALLOW_THREADS;
+
     return 0;
 }
 
@@ -998,33 +1040,54 @@ conn_set_autocommit(connectionObject *self, int value)
 int
 conn_switch_isolation_level(connectionObject *self, int level)
 {
-    PGresult *pgres = NULL;
-    char *error = NULL;
-    int res = 0;
+    int curr_level;
 
-    Py_BEGIN_ALLOW_THREADS;
-    pthread_mutex_lock(&self->lock);
+    if (-1 == (curr_level = conn_get_isolation_level(self))) {
+        return -1;
+    }
 
-    /* if the current isolation level is equal to the requested one don't switch */
-    if (self->isolation_level != level) {
+    if (curr_level == level) {
+        /* no need to change level */
+        return 0;
+    }
 
-        /* if the current isolation level is > 0 we need to abort the current
-           transaction before changing; that all folks! */
-        if (self->isolation_level != ISOLATION_LEVEL_AUTOCOMMIT) {
-            res = pq_abort_locked(self, &pgres, &error, &_save);
-        }
-        self->isolation_level = level;
+    /* Emulate the previous semantic of set_isolation_level() using the
+     * functions currently available. */
 
-        Dprintf("conn_switch_isolation_level: switched to level %d", level);
-    }
+    /* terminate the current transaction if any */
+    pq_abort(self);
 
-    pthread_mutex_unlock(&self->lock);
-    Py_END_ALLOW_THREADS;
+    if (level == 0) {
+        if (0 != conn_set(self, "default_transaction_isolation", "default")) {
+            return -1;
+        }
+        if (0 != conn_set_autocommit(self, 1)) {
+            return -1;
+        }
+    }
+    else {
+        /* find the name of the requested level */
+        const IsolationLevel *isolevel = conn_isolevels;
+        while ((++isolevel)->name) {
+            if (level == isolevel->value) {
+                break;
+            }
+        }
+        if (!isolevel->name) {
+            PyErr_SetString(OperationalError, "bad isolation level value");
+            return -1;
+        }
 
-    if (res < 0)
-        pq_complete_error(self, &pgres, &error);
+        if (0 != conn_set(self, "default_transaction_isolation", isolevel->name)) {
+            return -1;
+        }
+        if (0 != conn_set_autocommit(self, 0)) {
+            return -1;
+        }
+    }
 
-    return res;
+    Dprintf("conn_switch_isolation_level: switched to level %d", level);
+    return 0;
 }
 
 /* conn_set_client_encoding - switch client encoding on connection */