RDS postgresql 17 function monitoring

Поиск
Список
Период
Сортировка
Искать

RDS postgresql 17 function monitoring

От:
Brajendra Pratap Singh <singh.bpratap766@gmail.com>
Дата:
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.

Please suggest and let me know if any further information is required.

Thanks,
BPratap

Re: RDS postgresql 17 function monitoring

От:
Ron Johnson <ronljohnsonjr@gmail.com>
Дата:
On Mon, Oct 19, 2026 at 12:17 PM Brajendra Pratap Singh <singh.bpratap766@gmail.com> 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.

Please suggest and let me know if any further information is required

I had to spin up a full-sized test database, and add lots of RAISE NOTICE statements showing the timestamp and what data it was working on at that moment.  The sad part was that it ran fast when run once, but was slow when called more than five times.  "set plan_cache_mode = force_custom_plan;" solved my problem.

Of course, your problem is certainly different.  Decomposing it, running EXPLAIN, looking for sequential scans (including in FK references) is the way.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Re: RDS postgresql 17 function monitoring

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:


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



Re: RDS postgresql 17 function monitoring

От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
On Fri, 2026-10-16 at 23:09 +0530, Brajendra Pratap Singh wrote:
> 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.

I usually use auto_explain for that, with auto_explain.log_nested_statements set
to "on" and a sufficiently low auto_explain.log_min_duration.

Yours,
Laurenz Albe


Re: RDS postgresql 17 function monitoring

От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
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,  > 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.klaver@aklaver.com


FAQ