How to force PostgreSQL function run sequentially

user_6875341 0
Viewed 1393

I have a PostgreSQL function A. Many clients will call A:

- client X1 send query 1 "SELECT A();" then
- client X2 send  query 2 "SELECT A();" then
- client X3 send query 3  "SELECT A();" then
...

How to force function A to run sequentially?

Mean that force: query 1 run --> finish or timeout --> query 2 run --> finish or timeout --> query run --> finish or timeout ... (not allow query 1 and query 2 run simultaneously)

parallel-processing postgresql
2 Answers

Use advisory locks.

The first command in the function body should be (1234 is an exemplary integer constant):

perform pg_advisory_xact_lock(1234);

When two concurrent sessions call the function, one of them will wait until the function in the second one completes. This is a transaction-level advisory lock, automatically released when a transaction terminates.

Alternatively, you can use a session-level advisory lock, which can (should) be manually released:

create function example()
returns void language plpgsql as $$
begin
    perform pg_advisory_lock(1234);
    --
    -- function's commands
    --
    perform pg_advisory_unlock(1234);
end $$;

Any advisory lock obtained in a session is automatically released at the end of the session (if it hasn't been released earlier).

user_19957380

I think the full answer is use pg_advisory_xact_lock + idle_in_transaction_session_timeout in function A.

Query 2 will wait until query 1 is completed (lock auto released) or timeout (session auto killed and lock also auto released).

user_68753410
Related