在网络应用中使用Postgres:“交易中止"错误 [英] Using Postgres in a web app: "transaction aborted" errors

问题描述

最近，出于性能原因，我将要开发的Web应用程序从MySQL迁移到PostgreSQL(我需要PostGIS提供的功能).现在经常会遇到以下错误:

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:

当前事务中止，命令被忽略，直到事务块结束

服务器应用程序使用mod_python.该错误发生在调用函数中(即为该特定客户端创建新会话的函数).这是适当的代码段(在调用sessionAppId的行上发生了例外:

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()
# .....

这是sessionAppId本身:

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]

一些说明和其他问题:

1. cursors [instance]和connections [instance]是数据库连接，并且是在该域名上提供的Web应用程序实例的游标.IE.相同的服务器为example1.com和example2.com提供服务，并根据请求使用的服务器名称，使用这些词典来调用相应的数据库.
2. 我真的需要在hello()函数中锁定表吗?
3. hello()中的大多数代码都需要在每个浏览器选项卡上维护一个单独的会话.我找不到仅与cookie有关的方法，因为打开网站的浏览器选项卡共享cookie库.有更好的方法吗?

非常感谢您.

推荐答案

该错误是由于先前的错误引起的.看这段代码:

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),)]
>>> 

如果您熟悉扭曲，请查看 twisted.enterprise.adbapi 以获取有关如何处理游标的示例.基本上，您应该始终提交或回滚游标:

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()

这篇关于在网络应用中使用Postgres:“交易中止"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！