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
