Re: Has anyone tried out the PL/pgSQL debugger?

> I would still like to see a simple example using psql. I know you
> would not really use psql for this, but I think it would help a lot
> with getting started for folks that want to use the debugger. I did
> not spend lots of time on it, but even after reading pldbgapi.c I was
> not able to get simple session going (e.g. how to start a session and
> request the source for a procedure).
Here's a simple example using psql (I will add this to the README file
too). In this example, we are debugging a 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 'target
process'. Since we are setting a "global" breakpoint, the first backend
to trip across the target function will become the target process (you
can also set a breakpoint in a specific process if you want to be less
annoying).

---
--- pldbg_get_target_info() is simply a convenience
--- function that returns various information about
--- a potential target function/trigger. In particular,
--- given a function signature (or name in the absence
--- of any ambiguity), pldbg_get_target_info() returns
--- the OID of the function.
---

test=# SELECT * FROM pldbg_get_target_info( 'testwhere', 'f' );
target | schema | nargs | argtypes | targetname | argmodes | argnames |
targetlang | fqname | returnsset | returntype
--------+--------+-------+----------+------------+----------+----------+------------+------------------+------------+------------
26149 | 2200 | 1 | 26 | testwhere | | {x}
| 16944 | public.testwhere | f | 25
(1 row)

---
--- Create a TCP port (OS-assigned address) where the
--- target process can find us. pldbg_create_listener()
--- returns a handle that we have to give back to the
--- other pldbg_xxx() functions (the first argument in
--- the remaining function calls is the handle value
--- that we got from pldbg_create_listener()).
---

test=# SELECT * FROM pldbg_create_listener();
pldbg_create_listener
-----------------------
1
(1 row)

---
--- Now set a 'global' breakpoint on the target
--- function (whose OID is 26149). The third
--- argument, if given, specifies a line number
--- within the target function. The last argument
--- specifies an (optional) target backend process ID.
---
--- Since we are not specifying a particular backend
--- process, we will trap the first server process to
--- trip over our breakpoint.
---

test=# SELECT * from pldbg_set_global_breakpoint(1, 26149, NULL, NULL);
pldbg_set_global_breakpoint
-----------------------------
t
(1 row)

---
--- Now we have to wait for some other backend to trip
--- over our breakpoint. When that happens, the target
--- process will attach to the TCP port we created
--- earlier.
---

test=# SELECT * FROM pldbg_wait_for_target(1);
pldbg_wait_for_target
-----------------------
8433
(1 row)

*--- Now we can invoke the target function (testwhere() in this
--- example) in some other client application, perhaps a second
--- psql session.
---
--- Note that the previous call to pldbg_wait_for_target()
--- will hang at this point.
---
*
---
--- And wait for the attached target to reach a
--- breakpoint. It may seem strange to have both
--- pldbg_wait_for_target() and pldbg_wait_for_breakpoint(),
--- but we need two different functions when we are
--- doing direct-debugging.
---
--- When the target reaches a breakpoint, pldbg_wait_for_breakpoint()
--- returns the OID of the function, the line number at which the
--- the target is paused, and the name of the target function.
---
test=# SELECT * FROM pldbg_wait_for_breakpoint(1);
func | linenumber | targetname
-------+------------+------------
26149 | 5 | testwhere
(1 row)

---
--- When the target has paused, you can retrieve the
--- source code for the target function...
---
test=# SELECT * FROM pldbg_get_source(1, 26149);
pldbg_get_source
------------------------------------------------------------

declare
\x09result text;
begin
\x09select into result proname from pg_proc where oid = x;
\x09return result;
end;

(1 row)

---
--- You can also retrieve a list of all local variables
--- and their current values.
---
--- You can also retrieve the call stack or a list of
--- breakpoints. And you can change the focus of the
--- debugger to a different stack frame.
---

test=# SELECT * from pldbg_get_variables(1);
name | varclass | linenumber | isunique | isconst | isnotnull | dtype
| value
--------+----------+------------+----------+---------+-----------+-------+-------
x | A | 0 | t | t | f | 26
| 60
result | L | 2 | t | f | f | 25
| NULL
(2 rows)

---
--- To "step into", call pldbg_step_into(); notice that
--- it returns a 'breakpoint' tuple to tell you where
--- the target has paused.
---
--- You could also call pldbg_step_over(), pldbg_continue(),
--- or pldbg_set_breakpoint() here.
---

test=# SELECT * from pldbg_step_into(1);
func | linenumber | targetname
-------+------------+------------
26149 | 6 | testwhere
(1 row)

---
--- If you want to abort the target function, call
--- pldbg_abort_target(); the client application will
--- see an error message (ERROR: canceling statement
--- due to user request)
---
test=# SELECT * FROM pldbg_abort_target(1);
pldbg_abort_target
--------------------
t
(1 row)

That's a simple example showing the general flow for in-context debugging.

-- Korry