Concurrent updates with psycopg2-CodePudding

On a server, I have an API which is called by one or multiple clients. The server can receive, almost at the same time, multiple update instructions for the same PostgreSQL attribute. These requests do not arrive at the exact same time, and I would just like to execute them one after another. Because of the (Flask) API, I cannot control that a previous call is over, so the new update can be called before the previous one is completed. I would except psycopg2/postgresql to be able to queue the updates and proceed them quietly one after another (maybe not even in order), but it does not seem to work that way.

Below is a simplified version of the function called on the server when an update is required. The attribute to be updated is a JSONB object, if that matters. Hence there is 'path' to determine which part of the JSONB object to update. So the API call looks like this:

def pg_update(data, path):
    conn = psycopg2.connect(...) # always the same database.
    cur = conn.cursor()

    # JSONB update for a single selected row and column.
    # 'column' and 'select_row' are determined with the parameters 'data' and 'path'.
    command = (
        f"""UPDATE MY_TABLE SET """
        f"""{column} = jsonb_merge({column}, %s) """
        f"""WHERE {select_row};"""
    )

    cur.execute(command, [Json(data)])
    conn.commit()

When called twice in a row, this call leads to the error (at "cur.execute"):

psycopg2.errors.InternalError_: tuple concurrently updated

Since the server keeps running all the time, I can also define conn outside the API call "pg_update" so that all calls are processed with the same psycopg2 connection. But then, when two updates are requested for the same attribute, I get instead:

psycopg2.ProgrammingError: execute cannot be used while an asynchronous query is underway

I forced async_ = False in psycopg2.connect, just in case. No change.

To summarize, how can I allow for multiple updates of the same attributes, one after another, even when these updates are requested almost at the same time from independent, uncontrollable client requests? Maybe there is a need for a locking mechanism? A queuing mechanism?

CodePudding user response：

You can use SELECT FOR UPDATE mechanism. Here are some simple examples, might be helpful.