Postgres pg_background asynchronous queries

Question

I am trying to perform DELETE and INSERT statements in a trigger function, like

BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM…;
    INSERT INTO…;
    RETURN OLD;
  ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
    DELETE FROM…;
    INSERT INTO…;
    RETURN NEW;
  END IF;
  RETURN NULL; -- result is ignored since this is an AFTER trigger
END;

but as trigger execution is synchronous, the performance of each statement will be bad, so I need to have the statements in the trigger function to be performed asynchronously. I had found some approaches like dblink and pg_background. In dblink, PostgreSQL creates a new connection, which also does not suit for my use case, and it also takes long, so I dropped it.

I tried pg_background to achieve asynchronous execution like

DECLARE
    result text;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        SELECT * FROM pg_background_result(pg_background_launch(sql_command)) AS (result TEXT) INTO  result;
        SELECT * FROM pg_background_result(pg_background_launch(sql_command)) as (result TEXT) INTO result;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
        SELECT * FROM pg_background_result(pg_background_launch(sql_command)) as (result TEXT) INTO     result;
        SELECT * FROM pg_background_result(pg_background_launch(sql_command)) as (result TEXT) INTO result;
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;

Here we are also facing performance issues. PostgreSQL consumes more time than with direct synchronous statements.

Is this approach correct for my use case? How can I achieve it using some other approach?

I had tried with LISTEN and NOTIFY using pg_notify(), but I can’t listen and perform additional statements inside Postgres itself, so I have written a Java application to listen for this notification and perform the statements asynchronously. That is working fine, but I need to reduce the external dependencies here.

Pranas Zeromskas · Accepted Answer · 2025-04-28 16:28:36Z

0

This is a late reply but if it is still relevant: Running

PERFORM pg_background_launch(sql_command)

does indeed make it run async.

However, function

SELECT * FROM pg_background_result()

you are using is awaiting for the result of the async task. That makes you trigger syncronous. If you do not need the result of the async task, you can just use pg_background_launch query. However if you will later need the results of the query, i could suggest creating a table for the results and altering sql_command so that it would insert it's result into that table.

answered Apr 28 at 16:28

Pranas Zeromskas

16

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres pg_background asynchronous queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related