Python Psycopg2 RealDictCursor returns nondeterministic results

Question

I have a postgresql database and am querying it in python with psycopg2. If I repeat the same query multiple times under seemingly the same conditions, I sometimes get unexpected results. Here are the details:

I'm using psycopg2 and RealDictCursor:

import psycopg2
from psycopg2.extras import RealDictCursor 

self.conn = psycopg2.connect(
            host="db",
            database=os.environ["DB_NAME"],
            user=os.environ["DB_USER"],
            password=os.environ["DOCKERDBPASS"],
        )
self.cur = self.conn.cursor(cursor_factory=RealDictCursor)

My table that I want to query looks like this:

   CREATE TABLE IF NOT EXISTS ct_session(
        id_nb SERIAL PRIMARY KEY,
        bk INT NOT NULL,
        status VARCHAR(10) NOT NULL,
        created_on TIMESTAMP DEFAULT NOW(),
        closed_on TIMESTAMP NULL,
        CONSTRAINT fk_bk FOREIGN KEY(bk) REFERENCES app_user(django_id)
    );

Now this is the actual query:

query = """
SELECT
    id_nb
FROM ct_session
WHERE status = 'open'
;
"""

self.cur.execute(query)
result = self.cur.fetchone()
print("====DEBUG====", result)
if result:
    return result["id_nb"]
else:
    return None

Most of the time the printed debug line is as expected:

====DEBUG==== RealDictRow([('id_nb', 1)])

However, sometimes I get an unexpected:

====DEBUG==== RealDictRow([('status', 1)])

or

====DEBUG==== RealDictRow([(, ['id', 'user', 'status']), ('id', 1)])

This is happening in a test environment with multiple different requests per second. Is there any explanation for this behavior? Might this be related to multiple different queries being processed more or less simultaneously?

Python Psycopg2 RealDictCursor returns nondeterministic results

0 Answers