Re: How to trace the postgres?

[Wen Yi]

That's works,
Thanks very much!


Yours,
Wen Yi






 




------------------ Original ------------------
From:                                                                           
                                             "Junwang Zhao"                     
                                                               
<zhjw...@gmail.com&gt;;
Date:&nbsp;Mon, Jun 12, 2023 09:18 PM
To:&nbsp;"Wen Yi"<896634...@qq.com&gt;;

Subject:&nbsp;Re: How to trace the postgres?



On Mon, Jun 12, 2023 at 6:38?6?2PM Wen Yi <896634...@qq.com&gt; wrote:
&gt;
&gt; Hi team,
&gt; Now I am start to analyse the process of the postgres, using the gdb.
&gt; As you can see, at the beginnning of my work, I use the ps to find the 
postgres's process.
&gt;
&gt; [postgres@fedora postgresql]$ ps -ef | grep postgres
&gt; postgres&nbsp;&nbsp; 67468&nbsp;&nbsp;&nbsp; 1599&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:01 
/home/postgres/pgsql/lib/bin/postgres -D /home/postgres/pgsql/data
&gt; postgres&nbsp;&nbsp; 67471&nbsp;&nbsp; 67468&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: checkpointer
&gt; postgres&nbsp;&nbsp; 67472&nbsp;&nbsp; 67468&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: background writer
&gt; postgres&nbsp;&nbsp; 67474&nbsp;&nbsp; 67468&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: walwriter
&gt; postgres&nbsp;&nbsp; 67475&nbsp;&nbsp; 67468&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: autovacuum 
launcher
&gt; postgres&nbsp;&nbsp; 67476&nbsp;&nbsp; 67468&nbsp; 0 08:11 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: logical 
replication launcher
&gt; root&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90486&nbsp;&nbsp; 90459&nbsp; 0 
14:32 pts/0&nbsp;&nbsp;&nbsp; 00:00:00 su postgres
&gt; postgres&nbsp;&nbsp; 90494&nbsp;&nbsp; 90486&nbsp; 0 14:32 
pts/0&nbsp;&nbsp;&nbsp; 00:00:00 bash
&gt; postgres&nbsp;&nbsp; 90526&nbsp;&nbsp; 90494&nbsp; 0 14:32 
pts/0&nbsp;&nbsp;&nbsp; 00:00:00 psql
&gt; postgres&nbsp;&nbsp; 90527&nbsp;&nbsp; 67468&nbsp; 0 14:32 
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00:00:00 postgres: postgres 
postgres [local] idle
&gt; root&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 90680&nbsp;&nbsp; 90652&nbsp; 0 
14:33 pts/2&nbsp;&nbsp;&nbsp; 00:00:00 su postgres
&gt; postgres&nbsp;&nbsp; 90683&nbsp;&nbsp; 90680&nbsp; 0 14:33 
pts/2&nbsp;&nbsp;&nbsp; 00:00:00 bash
&gt; postgres&nbsp;&nbsp; 90766&nbsp;&nbsp; 90683&nbsp; 0 14:33 
pts/2&nbsp;&nbsp;&nbsp; 00:00:00 ps -ef
&gt; postgres&nbsp;&nbsp; 90767&nbsp;&nbsp; 90683&nbsp; 0 14:33 
pts/2&nbsp;&nbsp;&nbsp; 00:00:00 grep --color=auto postgres
&gt;
&gt; After that, I use the gdp to debug one of the postgres's process
&gt;
&gt; [postgres@fedora postgresql]$ gdb -p 67468
&gt;
67468 is the postmaster process, you should gdb the 90527, which shows
idle, that means it's waiting for your next psql command.

&gt; WaitEventSetWait (set=0x25221d8, timeout=60000, 
occurred_events=0x7ffe2ea9df70, nevents=64, wait_event_info=0) at latch.c:1478
&gt; 1478&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1480&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1481&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1484&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1487&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1490&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1492&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1493&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1494&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt; 1495&nbsp;&nbsp;&nbsp; in latch.c
&gt; (gdb) next
&gt;
&gt; With the gdb's work, I use the psql to send the query to the postgres.
&gt;
&gt; One thing confuses me is, I really don't know how to trace the postgres's 
process, I try to trace the background writer, the walwriter but I still don't 
know what is them work logical.
&gt;
&gt; I think they seem to work in a loop(I read the related code, that's true), 
but what I want to verify is to identify all the functions and objects involved 
in the entire process of PGSQL (from syntax parsing to rewriting, from 
rewriting to optimization, and from optimization to execution).
&gt;
&gt; Can someone give me some advice?
&gt; Thanks in advance!
&gt;
&gt; Yours,
&gt; Wen Yi



-- 
Regards
Junwang Zhao