I have this weird behaviour when having multiple queries on same cursor:
previous request is SELECT * FROM template WHERE id=10, result is 1 row, fetched with .fetchone()
then:
print(cnx.cur.query)
# output OK: b"SELECT * FROM template WHERE id=10"
print(cnx.cur.statusmessage)
# output OK: SELECT 1
cnx.cur.execute("SELECT * FROM msg WHERE id=%s ORDER BY ts DESC LIMIT 1", [345])
print(cnx.cur.query)
# output OK: b"SELECT * FROM msg WHERE id=345 ORDER BY ts DESC LIMIT 1"
print(cnx.cur.statusmessage, cnx.cur.rowcount)
# output OK: 1 1
row = cnx.cur.fetchone()
print(row)
# output KO: prints result from previous query, ie table template
The psycopg2 pg connection always stays open for weeks, using same cursors (one RealDictCursor, one DictCursor depending my query).
But it's first time for years that I have such collisions, any insight?
edited:
creating a new cursor (DictCursor) -and same cnx- before .execute() and closing it after .fetchone() for each request does not change much, still having from time to time last request result (row from template instead of from msg)
I upgraded from psycopg2 2.7.1 to 2.8.4
Then solved this SSL error by adding lazy-apps = True in uwsgi config.
So far cursors don't collide anymore
For short, uwsgi was forking, sharing same pg cnx, whereas psycopg2 connection are not thread safe at this level. Seems psycopg2 now throws an exception (the SSL error) related to that.