Celery tasks with psycopg: ProgrammingError the last operation didn’t produce a result

I’m working on aproject in which I have

A PostgreSQL 16.2 database
A Python 3.12 backend using psycopg 3.2.1 and psycopg_pool 3.2.2.
Celery for handling asynchronous tasks.

The celery tasks uses the database pool through the following code:


import os
from psycopg_pool import ConnectionPool
from contextlib import contextmanager

PG_USERNAME = os.getenv('PG_USERNAME')
if not PG_USERNAME:
    raise ValueError(f"Invalid postgres username")

PG_PASSWORD = os.getenv('PG_PASSWORD')
if not PG_PASSWORD:
    raise ValueError(f"Invalid postgres pass")

PG_HOST = os.getenv('PG_HOST')
if not PG_HOST:
    raise ValueError(f"Invalid postgres host")

PG_PORT = os.getenv('PG_PORT')
if not PG_PORT:
    raise ValueError(f"Invalid postgres port")

# Options used to prevent closed connections
# conn_options = f"-c statement_timeout=1800000 -c tcp_keepalives_idle=30 -c tcp_keepalives_interval=30"
conninfo = f'host={PG_HOST} port={PG_PORT} dbname=postgres user={PG_USERNAME} password={PG_PASSWORD}'
connection_pool = ConnectionPool(
    min_size=4,
    max_size=100,
    conninfo=conninfo,
    check=ConnectionPool.check_connection,
    #options=conn_options,
)


@contextmanager
def get_db_conn():
    conn = connection_pool.getconn()
    try:
        yield conn
    finally:
        connection_pool.putconn(conn)

And an example celery task would be

@app.task(bind=True)
def example_task(self, id):
    with get_db_conn() as conn:
        try:
            with conn.cursor(row_factory=dict_row) as cursor:
                test = None
                cursor.execute('SELECT * FROM test WHERE id = %s', (id,))
                try:
                    test = cursor.fetchone()
                except psycopg.errors.ProgrammingError:
                    logger.warning(f'Test log msg')
                    conn.rollback()
                    return
                
                cursor.execute("UPDATE test SET status = 'running' WHERE id = %s", (id,))
                conn.commit()
                
                # Some processing...
                
               # Fetch another resource needed
               cursor.execute('SELECT * FROM test WHERE id = %s', (test['resource_id'],))
               cursor.fetchone()

                # Update the entry with the result
                cursor.execute("""
                    UPDATE test
                    SET status = 'done', properties = %s
                    WHERE id = %s
                """, (Jsonb(properties),  id))
                conn.commit()
        except Exception as e:
            logger.exception(f'Error: {e}')
            conn.rollback()
            with conn.cursor(row_factory=dict_row) as cursor:
                # Update status to error with exception information
                cursor.execute("""
                    UPDATE test
                    SET status = 'error', error = %s
                    WHERE id = %s
                """, (Jsonb({'error': str(e), 'stacktrace': traceback.format_exc()}), webpage_id))
                conn.commit()

The code works most of the times, but sometimes, when multiple tasks of the same type are being launched, I’m getting some errors of type psycopg.ProgrammingError: the last operation didn't produce a result on the second fetchone() call.

Meanwhile, on the database I can see the following warning
WARNING: there is already a transaction in progress

I suspect there might be some problems with the way I’m working with connections, but I cannot find were.

As far as I know, once get_db_conn() is called that connection is not available for other tasks, so in theory there cannot be multiple tasks using the same connection, and therefore there should be no transaction already in progress when performing the second fetchone() call.

The resource exists, as every other task can access it, so that’s not the problem.

If both the main target row of test as well as the additional one selected based on its test.resource_id foreign key aren’t shareable, lock them. Otherwise, concurrent workers are likely bumping into each other, taking on the processing of the same row and altering its fields and the fields of the one its associated with through resource_id, at unpredictable points between subsequent steps of this operation.

Regular explicit locks get automatically released on commit/rollback so to keep your conn.commit() after updating target’s status field, you can use session-level advisory locks to let them last multiple transactions instead:

@app.task(bind=True)
def example_task(self, id):
    with get_db_conn() as conn:
        try:
            with conn.cursor(row_factory=dict_row) as cursor:
                test = None
                cursor.execute("""SELECT *, pg_advisory_lock_shared(resource_id)
                                  FROM test 
                                  WHERE id = %s
                                    AND pg_try_advisory_lock(id)
                               """, (id,))
                try:
                    test = cursor.fetchone()
                    #if it fails here, someone else is already processing this `id`
                    #if it waits, someone else was altering the row behind `resource_id`
                    #in the 2nd case, it's best to wait for them to finish
                except psycopg.errors.ProgrammingError:
                    logger.warning(f'Test log msg')
                    conn.rollback()
                    return
                cursor.execute("""UPDATE test 
                                  SET status = 'running' 
                                  WHERE id = %s
                               """, (id,))
                conn.commit()
                # Some processing...
                # Fetch another resource needed
                cursor.execute("""SELECT *
                                  FROM test 
                                  WHERE id = %s
                                  /*AND probably more conditions here*/
                               """, (test['resource_id'],))
                cursor.fetchone()
                # Update the entry with the result
                cursor.execute("""UPDATE test
                                  SET status = 'done'
                                    , properties = %s
                                  WHERE id = %s
                                  RETURNING pg_advisory_unlock(id)
                                          , pg_advisory_unlock(resource_id)
                               """, (Jsonb(properties),  id))
                conn.commit()
        except Exception as e:
            logger.exception(f'Error: {e}')
            conn.rollback()
            with conn.cursor(row_factory=dict_row) as cursor:
                # Update status to error with exception information
                cursor.execute("""UPDATE test
                                  SET status = 'error', error = %s
                                  WHERE id = %s
                                  RETURNING pg_advisory_unlock(id)
                                          , pg_advisory_unlock(resource_id)
                               """, (Jsonb({'error': str(e), 'stacktrace': traceback.format_exc()}), webpage_id))
                conn.commit()

The problem might also be in the part of the code that you did not share, where you pick and assign the id you pass to example_task(self, id) from outside. If that’s more or less how workers find their next task:

select id 
from test 
where status='ready'
order by priority
       , created_at
limit 1;

Then there’s nothing stopping two workers from picking the same one if the second one grabs it before the first one has the chance to conn.commit() its status change.
You could acquire the lock right there and make all following calls skip to the nearest row that’s still free:

select id 
from test 
where status='ready'
order by priority
       , created_at
for update skip locked--this
limit 1;

But to hold on to a lock like that you’d have to only conn.commit() once you’re done with the whole operation, without running commits between its sub-steps – otherwise you’d lose the lock along the way.

To guard the rest of the operation beyond the nearest .commit(), use that lock to secure the query against immediate collisions but also add an advisory lock that survives multiple transactions.
Advisory locks don’t offer a skip locked but it can be emulated with a recurisve cte (walks the id‘s and stops at the first one that doesn’t return false on locking attempt). Or, you can just look up which id‘s are already advisory-locked according to pg_locks.objid and exclude those

select id, pg_try_advisory_lock(id)
from test 
where status='ready' 
and id not in(select objid 
              from pg_locks
              where locktype='advisory')
order by priority
       , created_at
for update skip locked
limit 1;

You could also get rid of that entirely and look up free id‘s straight from the worker:

@app.task(bind=True)
def example_task(self, id):
    with get_db_conn() as conn:
        try:
            with conn.cursor(row_factory=dict_row) as cursor:
                test = None
                cursor.execute("""WITH find_free_id_and_lock_it AS
                                  (UPDATE test
                                   SET status='running' 
                                   WHERE id=(SELECT id
                                             FROM test
                                             WHERE status='ready'
                                             ORDER BY priority
                                                    , created_at
                                             FOR UPDATE SKIP LOCKED
                                             LIMIT 1)
                                   RETURNING *)
                                  ,lock_resource AS
                                  (SELECT *, pg_advisory_lock_shared(id) 
                                   FROM test
                                   WHERE id=(SELECT resource_id 
                                             FROM find_free_id_and_lock_it)
                                   FOR SHARE/*waits if necessary*/)
                                   SELECT target.*
                                        , resource.*--replace with alias list
                                   FROM find_free_id_and_lock_it AS target
                                   JOIN lock_resource AS resource
                                     ON target.resource_id=resource.id;
                               """, (id,))
                try:
                    test = cursor.fetchone()
                except psycopg.errors.ProgrammingError:
                    logger.warning(f'Test log msg')
                    conn.rollback()
                    return
                conn.commit()
                # Some processing...
                cursor.execute("""UPDATE test
                                  SET status = 'done'
                                    , properties = %s
                                  WHERE id = %s
                                  RETURNING pg_advisory_unlock(resource_id)
                               """, (Jsonb(properties),  id))
                conn.commit()
        except Exception as e:
            logger.exception(f'Error: {e}')
            conn.rollback()
            with conn.cursor(row_factory=dict_row) as cursor:
                # Update status to error with exception information
                cursor.execute("""UPDATE test
                                  SET status = 'error', error = %s
                                  WHERE id = %s
                                  RETURNING pg_advisory_unlock(resource_id)
                               """, (Jsonb({'error': str(e), 'stacktrace': traceback.format_exc()}), webpage_id))
                conn.commit()

Both target and resource lookups, adequate locks as well as the status update are all applied within a single query and transaction. Depending on what you do in # Some processing... and how long that takes, it might be preferable to acquire the shared lock on resource later, just in time, like it was done originally.

Filed under: Kiến thức lập trình - @ 22:16

Thẻ: pythonpostgresqlcelerypsycopg3

Thiết kế website giá rẻ

Danh mục

Celery tasks with psycopg: ProgrammingError the last operation didn’t produce a result