CODEWITHSUNDEEP
javapostgresqlasynchronoustriggerspostgresql-10
sanjiv
sanjiv

Reputation: 11

Postgres pg_background asynchronous queries

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.

Upvotes: 1

Views: 133

Answers (0)

Related Questions