Обсуждение: Reg - pg_background async triggers

              I try to perform DELETE and INSERT queries in the Trigger function,

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 it is synchronous the performance of the each queries will be high so I need to make the Queries in trigger function to be performed asynchronously, I had found some approaches like dblink and pg_background, In db_link it creates a new connection which is also not suit for my case, it also comsumes time so I dropped it ☹. 

I tried pg_background to achieve async queries 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 also we are facing performance issue it consumes more time than a direct sync Queries, So is this approach is correct for my case and how to achieve it by any other approach. I had tried with LISTEN NOTIFY as pg_notify() but I can’t listen and perform additional queries inside postgres itself so I have wrote a java application to listen for this notification and perform the queries asynchronously it is working fine😊 but I need to reduce external dependency here so please look up this issue any suggestions most welcome..

#postgresql