Using Postgres in a web app: "transaction aborted" errors

Question

Hi,

Recently I moved a web app I'm developing from MySQL to PostgreSQL for performance reasons (I need functionality PostGIS provides). Now quite often encounter the following error:

current transaction is aborted, commands ignored until end of transaction block

The server application uses mod_python. The error occurs in the hailing function (i.e. the one that creates a new session for this specific client). Here goes the appropriate piece of code (the exception occurs on the line where sessionAppId is invoked:

def hello(req):
req.content_type = "text/json"
req.headers_out.add('Cache-Control', "no-store, no-cache, must-revalidate")
req.headers_out.add('Pragma', "no-cache")
req.headers_out.add('Expires', "-1")
instance = req.hostname.split(".")[0]

cookieSecret = '....' # whatever :-)
receivedCookies = Cookie.get_cookies(req, Cookie.SignedCookie, secret = cookieSecret)
sessionList = receivedCookies.get('sessions', None)
sessionId = str(uuid.uuid4())
if sessionList:
    if type(sessionList) is not Cookie.SignedCookie:
        return "{status: 'error', errno:1, errmsg:'Permission denied.'}"
    else:
        sessionList = sessionList.value.split(",")
        for x in sessionList[:]:
            revisionCookie = receivedCookies.get('rev_' + str(sessionAppId(x, instance)), None)
            # more processing here....
# .....
cursors[instance].execute("lock revision, app, timeout IN SHARE MODE")
cursors[instance].execute("insert into app (type, active, active_revision, contents, z) values ('session', true, %s, %s, 0) returning id", (cRevision, sessionId))
sAppId = cursors[instance].fetchone()[0]
cursors[instance].execute("insert into revision (app_id, type) values (%s, 'active')", (sAppId,))
cursors[instance].execute("insert into timeout (app_id, last_seen) values (%s, now())", (sAppId,))
connections[instance].commit()
# .....

And here is sessionAppId itself:

def sessionAppId(sessionId, instance):
cursors[instance].execute("select id from app where type='session' and contents = %s", (sessionId, ))
row = cursors[instance].fetchone()
if row == None:
    return 0
else:
    return row[0]

Some clarifications and additional questions:

1. cursors[instance] and connections[instance] are the database connection and the cursor for the web app instance served on this domain name. I.e. the same server serves example1.com and example2.com, and uses these dictionaries to call the appropriate database depending on the server name the request came for.
2. Do I really need to lock the tables in the hello() function?
3. Most of the code in hello() is needed to maintain one separate session per browser tab. I couldn't find a way to do with cookies only, since browser tabs opening a website share the pool of cookies. Is there a better way to do it?

Thank you very much.

Answer 1

that error is caused because of a precedent error. look at this piece of code:

>>> import psycopg2
>>> conn = psycopg2.connect('')
>>> cur = conn.cursor()
>>> cur.execute('select current _date')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.ProgrammingError: syntax error at or near "_date"
LINE 1: select current _date
                       ^

>>> cur.execute('select current_date')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block

>>> conn.rollback()
>>> cur.execute('select current_date')
>>> cur.fetchall()
[(datetime.date(2010, 2, 5),)]
>>> 

if you are familiar with twisted, look at twisted.enterprise.adbapi for an example how to handle cursors. basically you should always commit or rollback your cursors:

try:
    cur.execute("...")
    cur.fetchall()
    cur.close()
    connection.commit()
except:
    connection.rollback()