Re: RDS postgresql 17 function monitoring

On 10/21/26 1:47 AM, Brajendra Pratap Singh wrote:

Reply to list also, in others word use Reply All

Ccing list

> Hi Adrian,
>
> Thanks for your response, Below are the asked details.
>
> Postgresql version : 17
> Procedural language: pl/pgsql
>
> We can't share the code due to security constraints but the flow is like
> below.
>
> 1) We have 1st RDS postgresql instance where the actual data present
> from multiple source databases hosts in different schemas based on
> customer wise.
>
> 2) 2nd RDS postgresql instance from where the function gets executed and
> connects to first RDS postgresql instance (both are on 17 version) via
> FDW to fetch data customer wise created temporary table and perform
> merge or can say multiple other transformation with the data fetched
> from 1st host and available data already present in 2nd host where we
> have table partitions based on customer and accordingly perform insert/
> update operations on target tables.
>
> 3) But the problem is as we have multiple customers and that function
> ran customerwise at a time and then again 2nd means in loop so unable to
> find like where it sticks in that function execution, where multiple
> other functions also called out as I said for other transformation and
> leggings.
>
> 4) Now the main moto is to find the function step where it's currently
> running and taking time earlier or in future run to either log these
> things or track down.

A very basic example of one possible method:

CREATE OR REPLACE FUNCTION public.notice_test()
RETURNS void
LANGUAGE plpgsql
AS $function$

DECLARE
rand_interval integer;
BEGIN

FOR i in 1..10 LOOP
RAISE NOTICE 'Loop counter is %', i;
RAISE NOTICE 'Time is %', clock_timestamp();
SELECT INTO rand_interval random(1, 10);
RAISE NOTICE 'Random interval is %', rand_interval;
PERFORM pg_sleep(rand_interval);
END LOOP;

END;

$function$
;

test=# select notice_test();
NOTICE: Loop counter is 1
NOTICE: Time is 2026-10-21 08:06:21.784199-07
NOTICE: Random interval is 4
NOTICE: Loop counter is 2
NOTICE: Time is 2026-10-21 08:06:25.788436-07
NOTICE: Random interval is 4
NOTICE: Loop counter is 3
NOTICE: Time is 2026-10-21 08:06:29.793281-07
NOTICE: Random interval is 5
NOTICE: Loop counter is 4
NOTICE: Time is 2026-10-21 08:06:34.798375-07
NOTICE: Random interval is 1
NOTICE: Loop counter is 5
NOTICE: Time is 2026-10-21 08:06:35.79943-07
NOTICE: Random interval is 7
NOTICE: Loop counter is 6
NOTICE: Time is 2026-10-21 08:06:42.807301-07
NOTICE: Random interval is 1
NOTICE: Loop counter is 7
NOTICE: Time is 2026-10-21 08:06:43.808328-07
NOTICE: Random interval is 3
NOTICE: Loop counter is 8
NOTICE: Time is 2026-10-21 08:06:46.811336-07
NOTICE: Random interval is 4
NOTICE: Loop counter is 9
NOTICE: Time is 2026-10-21 08:06:50.815217-07
NOTICE: Random interval is 2
NOTICE: Loop counter is 10
NOTICE: Time is 2026-10-21 08:06:52.817121-07
NOTICE: Random interval is 7
notice_test

>
> Thanks,
> BPratap
>
>
>
>
> On Mon, 19 Oct 2026, 10:26 pm Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
>
>
> On 10/16/26 10:39 AM, Brajendra Pratap Singh wrote:
> > Hi All,
> >
> > We need to monitor a function which is running since long to
> understand
> > at which step it is currently running and taking long time to
> debug the
> > function.
>
>
> Postgres version
>
> Procedural language used?
>
> The function source or if that is something you do not want public,
> then
> a description of what the code does.
>
> Define how long a time it is taking.
>
> >
> > Please suggest and let me know if any further information is
> required.
> >
> > Thanks,
> > BPratap
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com