Re: Has anyone tried out the PL/pgSQL debugger? - Mailing list pgsql-hackers

<br /><blockquote cite="mid:7F609BA8-0F18-460D-91F5-C19973E6B4E5@pgedit.com" type="cite">I would still like to see a
simpleexample using psql. I know you would not really use psql for this, but I think it would help a lot with getting
startedfor folks that want to use the debugger. I did not spend lots of time on it, but even after reading pldbgapi.c I
wasnot able to get simple session going (e.g. how to start a session and request the source for a procedure). <br
/></blockquote>Here's a simple example using psql (I will add this to the README file too).  In this example, we are
debugginga PL/pgSQL function named 'testwhere( x int)', just because I happen to have that function in front of me. 
'testwhere(x int )' is called the 'target function' - the backend process that executes testwhere(x int) is called the
'targetprocess'.  Since we are setting a "global" breakpoint, the first backend to trip across the target function will
becomethe target process (you can also set a breakpoint in a specific process if you want to be less annoying).<br
/><br/><tt>--- <br /> --- pldbg_get_target_info() is simply a convenience<br /> --- function that returns various
informationabout <br /> --- a potential target function/trigger. In particular,<br /> --- given a function signature
(orname in the absence <br /> --- of any ambiguity), pldbg_get_target_info() returns<br /> --- the OID of the
function.<br/> ---<br /><br /> test=# SELECT * FROM pldbg_get_target_info( 'testwhere', 'f' );<br />  target | schema |
nargs| argtypes | targetname | argmodes | argnames | targetlang |      fqname      | returnsset | returntype<br />
--------+--------+-------+----------+------------+----------+----------+------------+------------------+------------+------------<br
/>  26149 |   2200 |     1 | 26       | testwhere  |          | {x}      |      16944 | public.testwhere | f         
|        25<br /> (1 row)<br /><br /> ---<br /> --- Create a TCP port (OS-assigned address) where the <br /> --- target
processcan find us.  pldbg_create_listener()<br /> --- returns a handle that we have to give back to the <br /> ---
otherpldbg_xxx() functions (the first argument in <br /> --- the remaining function calls is the handle value<br /> ---
thatwe got from pldbg_create_listener()).<br /> ---<br /></tt><br /><tt>test=# SELECT * FROM
pldbg_create_listener();<br/>  pldbg_create_listener<br /> -----------------------<br />                      1<br />
(1row)<br /><br /> --- <br /> --- Now set a 'global' breakpoint on the target <br /> --- function (whose OID is
26149). The third <br /> --- argument, if given, specifies a line number<br /> --- within the target function.  The
lastargument<br /> --- specifies an (optional) target backend process ID.<br /> ---<br /> --- Since we are not
specifyinga particular backend <br /> --- process, we will trap the first server process to<br /> --- trip over our
breakpoint.<br/> ---<br /><br /> test=# SELECT * from pldbg_set_global_breakpoint(1, 26149, NULL, NULL);<br />
 pldbg_set_global_breakpoint<br/> -----------------------------<br />  t<br /> (1 row)<br /><br /> ---<br /> --- Now we
haveto wait for some other backend to trip<br /> --- over our breakpoint. When that happens, the target<br /> ---
processwill attach to the TCP port we created <br /> --- earlier.<br /> ---<br /><br /> test=# SELECT * FROM
pldbg_wait_for_target(1);<br/>  pldbg_wait_for_target<br /> -----------------------<br />                   8433<br />
(1row)<br /><br /><b>--- Now we can invoke the target function (testwhere() in this<br /> --- example) in some other
clientapplication, perhaps a second<br /> --- psql session.<br /> ---<br /> --- Note that the previous call to
pldbg_wait_for_target()<br/> --- will hang at this point.<br /> ---<br /></b><br /> ---<br /> --- And wait for the
attachedtarget to reach a <br /> --- breakpoint.  It may seem strange to have both<br /> --- pldbg_wait_for_target()
andpldbg_wait_for_breakpoint(),<br /> --- but we need two different functions when we are<br /> --- doing
direct-debugging.<br/> ---<br /> --- When the target reaches a breakpoint, pldbg_wait_for_breakpoint()<br /> ---
returnsthe OID of the function, the line number at which the <br /> --- the target is paused, and the name of the
targetfunction.<br /> --- <br /> test=# SELECT * FROM pldbg_wait_for_breakpoint(1);<br />  func  | linenumber |
targetname<br/> -------+------------+------------<br />  26149 |          5 | testwhere<br /> (1 row)<br /><br />
---<br/> --- When the target has paused, you can retrieve the <br /> --- source code for the target function...<br />
---<br/> test=# SELECT * FROM pldbg_get_source(1, 26149);<br />                       pldbg_get_source<br />
------------------------------------------------------------<br/><br />  declare<br />  \x09result text;<br />
 begin<br/>  \x09select into result proname from pg_proc where oid = x;<br />  \x09return result;<br />  end;<br /><br
/>(1 row)<br /><br /> ---<br /> --- You can also retrieve a list of all local variables<br /> --- and their current
values.<br/> ---<br /> --- You can also retrieve the call stack or a list of <br /> --- breakpoints.  And you can
changethe focus of the <br /> --- debugger to a different stack frame.<br /> ---<br /><br /> test=# SELECT * from
pldbg_get_variables(1);<br/>   name  | varclass | linenumber | isunique | isconst | isnotnull | dtype | value<br />
--------+----------+------------+----------+---------+-----------+-------+-------<br/>  x      | A        |          0
|t        | t       | f         |    26 | 60<br />  result | L        |          2 | t        | f       | f        
|   25 | NULL<br /> (2 rows)<br /><br /> ---<br /> --- To "step into", call pldbg_step_into(); notice that<br /> --- it
returnsa 'breakpoint' tuple to tell you where <br /> --- the target has paused.<br /> ---<br /> --- You could also call
pldbg_step_over(),pldbg_continue(),<br /> --- or pldbg_set_breakpoint() here.  <br /> ---<br /><br /> test=# SELECT *
frompldbg_step_into(1);<br />  func  | linenumber | targetname<br /> -------+------------+------------<br />  26149
|         6 | testwhere<br /> (1 row)<br /><br /> ---<br /> --- If you want to abort the target function, call <br />
---pldbg_abort_target(); the client application will<br /> --- see an error message (ERROR:  canceling statement <br />
---due to user request)<br /> ---<br /> test=# SELECT * FROM pldbg_abort_target(1);<br />  pldbg_abort_target<br />
--------------------<br/>  t<br /> (1 row)<br /></tt><br /><br /> That's a simple example showing the general flow for
in-contextdebugging.<br /><br />              -- Korry<br /><br />