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 can be debugged are SPL stored procedures, functions, triggers, and packages as well as PL/pgSQL functions and triggers.
The Debugger is integrated with and invoked from the Postgres Enterprise Manager client. There are two basic ways the Debugger can be used to test programs:
- Standalone Debugging. The Debugger is used to start the program to be tested. You supply any input parameter values required by the program and 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. The program to be tested is initiated by an application other than the Debugger. You first set a global breakpoint on the program to be tested. The application that makes the first call to the program encounters the global breakpoint. The application suspends execution at which 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 within 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 execution. In-context debugging is useful if it is difficult to reproduce a problem using standalone debugging due to complex interaction with the calling application.
The debugging tools and operations are the same whether using standalone or in-context debugging. The difference is in how the program to be debugged is invoked.
The following sections discuss the features and functionality of the Debugger using the standalone debugging method. The directions for starting the Debugger for in-context debugging are discussed in the Setting Global Breakpoint for In-Context Debugging.
Before using the Debugger, edit the
postgresql.conf file (located in the
data subdirectory of your Advanced Server home directory), adding
$libdir/plugin_debugger to the libraries listed in the
shared_preload_libraries configuration parameter:
After modifying the
shared_preload_libraries parameter, you must restart the database server.
You can use the Postgres Enterprise Manager (PEM) client to access the Debugger for standalone debugging. To open the Debugger, highlight the name of the stored procedure or function you wish to debug in the PEM
Object browser panel. Then, navigate through the
Tools menu to the
Debugging menu and select
Debug from the submenu.
You can also right-click on the name of the stored procedure or function in the PEM client
Object Browser, and select
Debugging, and the
Debug from the context menu.
Note that triggers cannot be debugged using standalone debugging. Triggers must be debugged using in-context debugging. See the Setting Global Breakpoint for In-Context Debugging for information on setting a global breakpoint for in-context debugging.
To debug a package, highlight the specific procedure or function under the package node of the package you wish to debug and follow the same directions as for stored procedures and functions.
You can use the
View Data Options window to pass parameter values when you are standalone-debugging a program that expects parameters. When you start the debugger, the
View Data Options window opens automatically to display any
IN OUT parameters expected by the program. If the program declares no
IN OUT parameters, the
View Data Options window does not open.
Use the fields on the
Debugger window to provide a value for each parameter:
Namefield contains the formal parameter name.
Typefield contains the parameter data type.
- Check the
Null?checkbox to indicate that the parameter is a
- Check the
Expression?checkbox if the
Valuefield contains an expression.
Valuefield contains the parameter value that will be passed to the program.
- Check the
Use Default?checkbox to indicate that the program should use the value in the
Default Valuefield contains the default value of the parameter.
Enter key to select the next parameter in the list for data entry, or click on a
Value field to select the parameter for data entry.
If you are debugging a procedure or function that is a member of a package that has an initialization section, check the
Debug Package Initializer check box to instruct the Debugger to step into the package initialization section, allowing you to debug the initialization section code before debugging the procedure or function. If you do not 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 are executed.
After entering the desired parameter values, click the
OK button to start the debugging process. Click the
Cancel button to terminate the Debugger and return control to the PEM client.
View Data Options window does not open during in-context debugging. Instead, the application calling the program to be debugged must supply any required input parameter values.
When you have completed a full debugging cycle by stepping through the program code, the
View Data Options window re-opens, allowing you to enter new parameter values and repeat the debugging cycle, or end the debugging session.
The Main Debugger window contains three panes:
Use the debugger menu bar or tool bar icons located at the top of the debugger window to access debugging functions.
Status and error information is displayed in the status bar at the bottom of the Debugger window.
Program Body pane in the upper-left corner of the Debugger window displays the source code of the program that is being debugged.
The figure shows that the Debugger is about to execute the
SELECT statement. The green indicator in the program body highlights the next statement to execute.
Stack pane displays a list of programs that are currently on the call stack (programs that have been invoked, but which have not yet completed). When a program is called, the name of the program is added to the top of the list displayed in the
Stack pane. When the program ends, its name is removed from the list.
Stack pane also displays information about program calls. The information includes:
- The location of the call within 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 above figure shows that
emp_query_caller is about to call a subprogram named
emp_query_caller is currently at the top of the call stack.
After the call to
emp_query is displayed at the top of the
Stack pane, and its code is displayed in the
Program Body frame.
Upon completion of execution of the subprogram, control returns to the calling program
(public.emp_query_caller), now displayed at the top of the
Highlight an entry in the call stack to review detailed information about the selected entry on the tabs in the
Output pane. Using the call stack to navigate to another entry in the call stack will not alter the line that is currently executing.
You can use tabs in the
Output pane to view or modify parameter values or local variables, or to view messages generated by
RAISE INFO and function results.
Each tab contains a different type of information:
Parameterstab displays the current parameter values.
Local Variablestab displays the value of any variables declared within the program.
DBMS Messagestab displays any results returned by the program as it executes.
Resultstab displays program results (if applicable).
The status bar displays a message when the Debugger pauses, when a runtime error message is encountered, or when execution completes.
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
Use the tool bar icons to step through a program with the Debugger:
The icons serve the following purposes:
- Step into. Use the
Step Intoicon to execute the line of code currently highlighted by the green bar in the
Program Bodypane, and then pause execution. If the executed code line is a call to a subprogram, the called subprogram is brought into the
Program Bodypane, and the first executable line of code of the subprogram is highlighted as the Debugger waits for you to perform an operation on the subprogram.
- Step over. Use the
Step Overicon to execute a line of code, stepping over any subprograms invoked by that line of code. The subprogram is executed, but not debugged. If the subprogram contains a breakpoint, the debugger will stop at that breakpoint.
- Continue. Use the
Continueicon to execute the line of code highlighted by the green bar, and continue execution until either a breakpoint is encountered or the last line of the program has been executed.
The debugging operations are also accessible through the
As the Debugger executes a program, it pauses whenever 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 execution with the next line of code following the breakpoint. There are two types of breakpoints:
Local Breakpoint - A local breakpoint can be set at any executable line of code within a program. The Debugger pauses execution when it reaches a line where a local breakpoint has been set.
Global Breakpoint - A global breakpoint will trigger when any session reaches that breakpoint. Set a global breakpoint if you want to perform in-context debugging of a program. When a global breakpoint is set on a program, the debugging session that set the global breakpoint waits until that program is invoked in another session. A global breakpoint can only be set by a superuser.
To create a local breakpoint, left-click in the grey shaded margin to the left of the line of code where you want the local breakpoint set. The Debugger displays a red dot in the margin, indicating a breakpoint has been set at the selected line of code.
You can also set a breakpoint by left-clicking in the
Program Body to place your cursor, and selecting
Toggle Breakpoint from
Debug menu or by clicking the
Toggle Breakpoint icon. A red dot appears in the left-hand margin indicating a breakpoint has been set as the line of code.
You can set as many local breakpoints as desired. Local breakpoints remain in effect for the duration of a debugging session until they are removed.
Removing a Local Breakpoint
To remove a local breakpoint, you can:
- Left-click the mouse on the red breakpoint indicator in the left margin of the
Program Bodypane. The red dot disappears, indicating that the breakpoint has been removed.
- Use your mouse to select the location of the breakpoint in the code body, and select
Debugmenu, or click the
You can remove all of the breakpoints from the program that currently appears in the
Program Body frame by clicking the
Clear all breakpoints from the
Debug menu or by clicking the
Clear All Breakpoints icon.
When you perform any of the preceding actions, only the breakpoints in the program that currently appears in the
Program Body frame are removed. Breakpoints in called subprograms or breakpoints in programs that call the program currently appearing in the
Program Body frame are not removed.
To set a global breakpoint for in-context debugging, highlight the stored procedure, function, or trigger on which you wish to set the breakpoint in the
Object browser panel. Navigate through the
Tools menu to select
Debugging, and then
Alternatively, you can right-click on the name of the stored procedure, function, or trigger on which you wish to set a global breakpoint and select
Set Breakpoint from the context menu as shown by the following.
To set a global breakpoint on a trigger, expand the table node that contains the trigger, highlight the specific trigger you wish to debug, and follow the same directions as for stored procedures and functions.
To set a global breakpoint in a package, highlight the specific procedure or function under the package node of the package you wish to debug and follow the same directions as for stored procedures and functions.
After you choose
Set Breakpoint, the Debugger window opens and waits for an application to call the program to be debugged.
The EDB-PSQL client invokes the
select_emp function (on which a global breakpoint has been set).
select_emp function does not complete until you step through the program in the Debugger.
You can now debug the program using any of the previously discussed operations such as step into, step over, and continue, or set local breakpoints. When you have stepped through execution of the program, the calling application (EDB-PSQL) regains control.
select_emp function completes execution and its output is displayed.
At this point, you can end the Debugger session by choosing
Exit from the
File menu. If you do not end the Debugger session, the next application that invokes the program will encounter the global breakpoint and the debugging cycle will begin again.
To end a Debugger session and exit the Debugger, select
File menu or press Alt-F4.