Hi Postgres gurus,
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