Working with the PL/pgSQL Debugger

QHB supports debugging of stored procedures written in PL/pgSQL. Debugging support is provided by the internal functions implemented in QHB, which represent the debugger interface:

• pldbg_abort_target: abort debugging session;
• pldbg_attach_to_port: attach to a debug process port;
• pldbg_continue: continue execute code after a breakpoint;
• pldbg_create_listener: get debug session ID;
• pldbg_deposit_value: overwrite the value of a given variable;
• pldbg_drop_breakpoint: destroy breakpoints;
• pldbg_get_breakpoints: get a list of breakpoints;
• pldbg_get_proxy_info: get information about a proxy process;
• pldbg_get_source: get a text representation of the debugged procedure source code;
• pldbg_get_stack: call stack check;
• pldbg_get_target_info: get information about the debugged function;
• pldbg_get_variables: get a list of variables and formal parameters of the debugged function;
• pldbg_oid_debug: set a local breakpoint at the beginning of the selected function;
• pldbg_select_frame: navigation through the stack frames of the debugged function;
• pldbg_set_breakpoint: set a local breakpoint;
• pldbg_set_global_breakpoint: sett a global breakpoint;
• pldbg_step_into: perform a debugging step with stepping into the called functions;
• pldbg_step_over: perform a debugging step with stepping through functions;
• pldbg_wait_for_breakpoint: wait for the target debug session to reach a breakpoint;
• pldbg_wait_for_target: get a specific debug session ID.

Using them, third-party client applications can implement a full-fledged debugger for stored procedures.

---

---

Using the Debugger Interface

Let's show a test debugging session based on two psql sessions:

Let's create a function to count the row number in a table whose name is passed as a parameter.

 CREATE FUNCTION get_count (tabname text) RETURNS bigint
  AS $$DECLARE
      cmd text;
      retval bigint;
  BEGIN
      cmd := 'SELECT COUNT(*) FROM '
             || quote_ident(tabname);

      EXECUTE cmd INTO retval;
      RETURN retval;
  END;
  $$ LANGUAGE plpgsql STABLE;

Let's get the debug session ID and the function OID:

   select pldbg_create_listener() as sessionid,
           'get_count'::regproc::oid as funcoid \gset

Let's set a breakpoint at the beginning of the function (3rd parameter: -1) and wait for the function to be called from any (4th parameter: NULL) session:

select * from pldbg_set_global_breakpoint(:sessionid, :funcoid, -1, NULL);

Let's put the debug (current) session into waiting mode:

select * from pldbg_wait_for_target(:sessionid);

---

---

Running Code Under the Debugger

Let's open the second session and call the target function:

select get_count('pg_class');

Execution hits the breakpoint, and now the second session goes into waiting for debugger commands mode.

The first session wakes up and goes into debug control mode.

---

---

Debugging Control

In control mode you can:

• check the call stack (pldbg_get_stack);
• navigate through functions in the stack (pldbg_select_frame);
• view the parameter values and local variables of the current function in the stack (pldbg_get_variables);
• change the values of variables (pldbg_deposit_value);
• set/remove breakpoints (pldbg_set_breakpoint/pldbg_drop_breakpoint);
• continue execution to the next breakpoint (pldbg_continue);
• perform step-by-step debugging:
  • stepping into functions (pldbg_step_into);
  • or executing the entire string (pldbg_step_over);
• abort the debugging session (pldbg_abort_target).