Debugger v14

The debugger gives developers and DBAs the ability to test and debug server-side programs using a graphical, dynamic environment. The types of programs that you can debug are SPL stored procedures, functions, triggers, and packages as well as PL/pgSQL functions and triggers.

The debugger is integrated with pgAdmin 4 and EDB Postgres Enterprise Manager. If you installed EDB Postgres Advanced Server on a Windows host, pgAdmin 4 is automatically installed. The pgAdmin 4 icon is in the Windows Start menu.

If your EDB Postgres Advanced Server host is on a CentOS or Linux system, you can use yum to install pgAdmin4. Open a command line, assume superuser privileges, and enter:

yum install edb-pgadmin4*

On Linux, you must also install the edb-asxx-server-pldebugger RPM package, where xx is the EDB Postgres Advanced Server version number. Information about pgAdmin 4 is available at https://www.pgadmin.org/.

The RPM installation adds the pgAdmin4 icon to your Applications menu.

You can use the debugger in two basic ways to test programs:

• Standalone debugging. Use the debugger to start the program to test. Supply any input parameter values required by the program. You can immediately observe and step through the code of the program. Standalone debugging is the typical method used for new programs and for initial problem investigation.

• In-context debugging. In-context debugging is useful if it's difficult to reproduce a problem using standalone debugging due to complex interaction with the calling application. Using this approach, the program to test is started by an application other than the debugger. You set a global breakpoint on the program to test. The application that makes the first call to the program encounters the global breakpoint. Then the application suspends execution. At that point, the debugger takes control of the called program. You can then observe and step through the code of the called program as it runs in the context of the calling application.

  After you have completely stepped through the code of the called program in the debugger, the suspended application resumes executing.

The debugging tools and operations are the same whether using standalone or in-context debugging. The difference is in how to invoke the program being debugged.

These instructions use the standalone debugging method. To start the debugger for in-context debugging, see Setting global breakpoint for in-context debugging.

Configuring the debugger

Before using the debugger, edit the postgresql.conf file (located in the data subdirectory of your EDB Postgres Advanced Server home directory). Add $libdir/plugin_debugger to the libraries listed in the shared_preload_libraries configuration parameter:

shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/plugin_debugger'

• On Linux, the postgresql.conf file is located in: /var/lib/edb/asxx/data
• On Windows, the postgresql.conf file is located in: C:\Program Files\edb\asxx\data

Where xx is the version of EDB Postgres Advanced Server.

After modifying the shared_preload_libraries parameter, restart the database server.

Starting the debugger

Use pgAdmin 4 to access the debugger for standalone debugging. To open the debugger:

1. Select the name of the stored procedure or function you want to debug in the pgAdmin 4 Browser panel. Or, to debug a package, select the specific procedure or function under the package node of the package you want to debug.

2. Select Object > Debugging > Debug.

You can't debug triggers using standalone debugging. You must use in-context debugging. See Setting global breakpoint for in-context debugging for information.

The Debugger window

You can use the Debugger window to pass parameter values when you are standalone debugging a program that expects parameters. When you start the debugger, the Debugger window opens to display any IN or IN OUT parameters the program expects. If the program declares no IN or IN OUT parameters, the Debugger window doesn't open.

Use the fields on the Debugger window to provide a value for each parameter:

• The Name field contains the formal parameter name.
• The Type field contains the parameter data type.
• Select the Null? check box to indicate that the parameter is a NULL value.
• Select the Expression? check box if the Value field contains an expression.
• The Value field contains the parameter value that's passed to the program.
• Select the Use Default? check box to indicate for the program to use the value in the Default Value field.
• The Default Value field contains the default value of the parameter.

If you're debugging a procedure or function that's a member of a package that has an initialization section, select the Debug Package Initializer check box to step into the package initialization section, This setting allows you to debug the initialization section code before debugging the procedure or function. If you don't select the check box, the debugger executes the package initialization section without allowing you to see or step through the individual lines of code as they execute.

After entering the desired parameter values, select Debug to start the debugging process.

After you complete a full debugging cycle by stepping through the program code, the Debugger window reopens. You can enter new parameter values and repeat the debugging cycle or end the debugging session.

Main debugger window

The main debugger window contains two panels:

• The top Program Body panel displays the program source code.
• The bottom Tabs panel provides a set of tabs for different information.

Use the tool bar icons located at the top panel to access debugging functions.

The Program Body panel

The Program Body panel displays the source code of the program that's being debugged. The figure shows that the debugger is about to execute the SELECT statement. The blue indicator in the program body highlights the next statement to execute.

The Tabs panel

You can use the bottom Tabs panel to view or modify parameter values or local variables or to view messages generated by RAISE INFO and function results.

The following is the information displayed by the tabs in the panel:

• The Parameters tab displays the current parameter values.
• The Local variables tab displays the value of any variables declared in the program.
• The Messages tab displays any results returned by the program as it executes.
• The Results tab displays any program results, such as the value from the RETURN statement of a function.
• The Stack tab displays the call stack.

The Stack tab

The Stack tab displays a list of programs that are currently on the call stack, that is, programs that were invoked but that haven't yet completed. When a program is called, the name of the program is added to the top of the list displayed in the Stack tab. When the program ends, its name is removed from the list.

The Stack tab also displays information about program calls. The information includes:

• The location of the call in the program
• The call arguments
• The name of the program being called

Reviewing the call stack can help you trace the course of execution through a series of nested programs. The figure shows that emp_query_caller is about to call a subprogram named emp_query. emp_query_caller is currently at the top of the call stack.

After the call to emp_query executes, emp_query is displayed at the top of the Stack tab, and its code is displayed in the Program Body panel.

After completing execution of the subprogram, control returns to the calling program (emp_query_caller), now displayed at the top of the Stack tab.

Debugging a program

You can perform the following operations to debug a program:

• Step through the program one line at a time.
• Execute the program until you reach a breakpoint.
• View and change local variable values within the program.

Stepping through the code

Use the tool bar icons to step through a program with the debugger. The icons serve the following purposes:

• Step into. Execute the currently highlighted line of code.
• Step over. Execute a line of code, stepping over any subfunctions invoked by the code. The subfunction executes but is debugged only if it contains a breakpoint.
• Continue/Start. Execute the highlighted code and continue until the program encounters a breakpoint or completes.
• Stop. Halt a program.

Using breakpoints

As the debugger executes a program, it pauses when it reaches a breakpoint. When the debugger pauses, you can observe or change local variables or navigate to an entry in the call stack to observe variables or set other breakpoints. The next step into, step over, or continue operation forces the debugger to resume executing with the next line of code following the breakpoint.

These are the two types of breakpoints:

• Local breakpoint — You can set a local breakpoint at any executable line of code in a program. The debugger pauses execution when it reaches a line where a local breakpoint was set.

• Global breakpoint — A global breakpoint triggers when any session reaches that breakpoint. Set a global breakpoint if you want to perform in-context debugging of a program. When you set a global breakpoint on a program, the debugging session that set the global breakpoint waits until that program is invoked in another session. Only a superuser can set a global breakpoint.

To create a local breakpoint, select the grey shaded margin to the left of the line of code where you want the local breakpoint set. The spot you select must be close to the right side of the margin as in the spot where the breakpoint dot is shown on source code line 12. When the breakpoint is created, the debugger displays a dark dot in the margin, indicating a breakpoint was set at the selected line of code.

You can set as many local breakpoints as you want. Local breakpoints remain in effect for the rest of a debugging session until you remove them.

Removing a local breakpoint

To remove a local breakpoint, select the breakpoint dot. The dot disappears.

To remove all of the breakpoints from the program that currently appears in the Program Body frame, select the Clear all breakpoints icon.

Setting a global breakpoint for in-context debugging

To set a global breakpoint for in-context debugging:

1. In the Browser panel, select the stored procedure, function, or trigger on which you want to set the breakpoint.

2. Select Object > Debugging > Set Breakpoint.

To set a global breakpoint on a trigger:

1. Expand the table node that contains the trigger.

2. Select the specific trigger you want to debug.

3. Select Object > Debugging > Set Breakpoint.

To set a global breakpoint in a package:

1. Select the specific procedure or function under the package node of the package you want to debug.

2. Select Object > Debugging > Set Breakpoint.

After you select Set Breakpoint, the Debugger window opens and waits for an application to call the program to debug.

The PSQL client invokes the select_emp function on which a global breakpoint was set.

$ psql edb enterprisedb
psql.bin (14.0.0, server 14.0.0)
Type "help" for help.

edb=# SELECT select_emp(7900);

The select_emp function doesn't finish until you step through the program in the debugger.

You can now debug the program using the operations like step into, step over, and continue. Or you can set local breakpoints. After you step through executing the program, the calling application (PSQL) regains control, the select_emp function finishes executing, and its output is displayed.

$ psql edb enterprisedb
psql.bin (14.0.0, server 14.0.0)
Type "help" for help.

edb=# SELECT select_emp(7900);
INFO: Number    : 7900
INFO: Name      : JAMES
INFO: Hire Date : 12/03/1981
INFO: Salary    : 950.00
INFO: Commission: 0.00
INFO: Department: SALES
 select_emp
------------
(1 row)

At this point, you can end the debugger session. If you don't end the debugger session, the next application that invokes the program encounters the global breakpoint, and the debugging cycle begins again.

• On this page
• Configuring the debugger
• Starting the debugger
• The Debugger window
• Main debugger window
• Debugging a program