Help Center> GaussDB> Distributed_8.x> Schemas> DBE_PLDEBUGGER Schema
Updated on 2024-06-03 GMT+08:00
View PDF

DBE_PLDEBUGGER Schema

System functions in DBE_PLDEBUGGER Schema are used to debug stored procedures. Only administrators have the permission to execute these debugging APIs, but do not have the permission to modify or create functions. The following table describes the supported APIs.

  • When a user is created in the function body, the plaintext password is returned when attach, next, continue, info_code, step, info_breakpoint, backtrace, or finish is called. You are advised not to create a user in the function body.
  • Stored procedures can be debugged only on CNs, and the debugging end and the debugged end must be connected to the same CN.
  • The debugging end and the debugged end must be connected to the same database. Otherwise, the API that uses the function OID to obtain function information is unavailable.
  • After you use the dbe_pldebugger.turn_on function to enable stored procedure debugging in the current session, all PL/SQL stored procedures of the current session cannot be pushed down to DNs. Therefore, after the debugging is complete, use the dbe_pldebugger.turn_off function to disable stored procedure debugging in the current session in a timely manner.
  • Triggers cannot be directly debugged. EXECUTE DIRECT ON cannot be used to deliver statements to DNs to trigger debugging.
  • Precompiled statements generated before the dbe_pldebugger.turn_on function is used to enable stored procedure debugging may fail to be debugged.
  • The dbe_pldebugger.error_* functions can be used only when an error is reported.
  • During the debugging of a stored procedure, if the stored procedure to be debugged involves lock operations, do not perform operations that may cause deadlocks on the debugging end.

The administrator can run the following command to grant the gs_role_pldebugger role and debugger permissions to a user:

GRANT gs_role_pldebugger to user;

Two clients are required to connect to the database. One client is responsible for executing the debugging API as the debug end, and the other client is responsible for executing the debugging function to control the execution of stored procedures on the server. The following is an example:

  • Prepare for debugging.

    Use PG_PROC to find the OID of the stored procedure to be debugged and execute DBE_PLDEBUGGER.turn_on(oid). In this case, the client functions as the server.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    		 
    gaussdb=# CREATE OR REPLACE PROCEDURE test_debug ( IN  x INT) 
AS  
BEGIN
    INSERT INTO t1 (a) VALUES (x);
    DELETE FROM t1 WHERE a = x;
END;
/
CREATE PROCEDURE
gaussdb=# SELECT OID FROM PG_PROC WHERE PRONAME='test_debug';
  oid
-------
 16389
(1 row)
gaussdb=# SELECT * FROM DBE_PLDEBUGGER.turn_on(16389);
 nodename | port
----------+------
 datanode |    0
(1 row)
  • Start debugging.

    When the server executes the stored procedure, the server hangs before the first SQL statement in the stored procedure and waits for the debugging message sent by the debug end. Debugging is supported only by directly executing a stored procedure and cannot be achieved by invoking an executed stored procedure through a trigger.

    1
    		 
    gaussdb=# call test_debug(1);

    Start another client as the debug end and invoke DBE_PLDEBUGGER.attach to attach with the stored procedure for debugging based on the data returned by turn_on.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.attach('datanode',0);
 funcoid |  funcname  | lineno |              query
---------+------------+--------+----------------------------------
   16389 | test_debug |      3 |   INSERT INTO t1 (a) VALUES (x);
(1 row)

    Execute the next statement on the client where the attach operation is performed.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.next();
 funcoid |  funcname  | lineno |        query
---------+------------+--------+----------------------
   16389 | test_debug |      0 | [EXECUTION FINISHED]
(1 row)

    Execute the following variable operations on the client where the attach command is performed.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.info_locals(); -- Print all variables.
 varname | vartype | value |  isconst
---------+---------+-------+---------
 x       | int4    | 1     | f
(1 row)
gaussdb=# SELECT * FROM DBE_PLDEBUGGER.set_var('x', 2); -- Assign a value to a variable.
 set_var
---------
 t
(1 row)
gaussdb=# SELECT * FROM DBE_PLDEBUGGER.print_var('x'); -- Print a single variable.
 varname | vartype | value | isconst
---------+---------+-------+---------
 x       | int4    | 2     | f
(1 row)

    Directly execute the stored procedure that is being debugged.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.continue();
 funcoid |  funcname  | lineno |        query
---------+------------+--------+----------------------
   16389 | test_debug |      0 | [EXECUTION FINISHED]
(1 row)

    When the stored procedure reports an error, the following information is displayed. In this case, the suspended process logic is triggered.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.continue();
 funcoid |  funcname  | lineno |            query
---------+------------+--------+-------------------------------
   16389 | test_debug |      0 | [EXECUTION HAS ERROR OCCURRED!]
(1 row)

    If the suspended process logic is triggered, you can call the error_info_locals,error_backtrace, error_end, and print_var APIs to view information. Other APIs cannot be used anymore. You need to use error_end to end the suspended process logic.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.error_end();
 funcoid |  funcname  | lineno |      query
---------+------------+--------+------------------
   16389 | test_debug |      0 | [END HANG ERROR!]
(1 row)

    Exit the stored procedure that is being debugged (the suspended process logic is unavailable), and do not execute statements that have not been executed before.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.abort();
 abort
-------
 t
(1 row)

    View the code information on the client and identify the line number of the breakpoint that can be set.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.info_code(16389);
 lineno |                           query                           | canbreak
--------+-----------------------------------------------------------+----------
        | CREATE OR REPLACE PROCEDURE public.test_debug( IN  x INT) | f
      1 | AS  DECLARE                                               | f
      2 | BEGIN                                                     | f
      3 |     INSERT INTO t1 (a) VALUES (x);                        | t
      4 |     DELETE FROM t1 WHERE a = x;                           | t
      5 | END;                                                      | f
      6 | /                                                         | f
(7 rows)

    Sets a breakpoint.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.add_breakpoint(16389,4);
 breakpointno 
--------------
            0
(1 row)

    View the breakpoint information.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.info_breakpoints();
 breakpointno | funcoid | lineno |              query              | enable
--------------+---------+--------+---------------------------------+--------
            0 |   16389 |      4 |     DELETE FROM t1 WHERE a = x; | t
(1 row)

    Execute to the breakpoint.

    1
2
3
4
5
    		 
    gaussdb=# SELECT * FROM DBE_PLDEBUGGER.continue();
 funcoid |  funcname  | lineno |              query
---------+------------+--------+---------------------------------
   16389 | test_debug |      4 |     DELETE FROM t1 WHERE a = x;
(1 row)

    After the stored procedure is executed, the debugging automatically exits. To debug the stored procedure again, you need to attach again. If the server does not need to be debugged, run the turn_off command to disable the debugging or exit the session. Table 1 describes the debugging APIs.

    Table 1 DBE_PLDEBUGGER

    API

    Description

    DBE_PLDEBUGGER.turn_on

    Called by the server, indicating that the stored procedure can be debugged. After the API is called, the stored procedure is hung to wait for debugging information.

    DBE_PLDEBUGGER.turn_off

    Called by the server, indicating that debugging the stored procedure is disabled.

    DBE_PLDEBUGGER.local_debug_server_info

    Called by the server to print all stored procedures that have been turned on in the current session.

    DBE_PLDEBUGGER.attach

    Called by the debug end to attach with the stored procedure that is being debugged.

    DBE_PLDEBUGGER.info_locals

    Called by the debug end to print the current values of variables in the stored procedure that is being debugged.

    DBE_PLDEBUGGER.next

    Called by the debug end to execute the next step.

    DBE_PLDEBUGGER.continue

    Called by the debug end to continue the execution until the breakpoint or stored procedure ends.

    DBE_PLDEBUGGER.abort

    Called by the debug end to stop debugging. The server reports a long jump error.

    DBE_PLDEBUGGER.print_var

    Called by the debug end to print the current values of specified variables in the stored procedure that is being debugged.

    DBE_PLDEBUGGER.info_code

    Called by the debug end or server to print the source statement of a specified stored procedure and the line number corresponding to each line.

    DBE_PLDEBUGGER.step

    Called by the debug end to execute step by step.

    DBE_PLDEBUGGER.add_breakpoint

    Called by the debug end to add a breakpoint.

    DBE_PLDEBUGGER.delete_breakpoint

    Called by the debug end to delete a breakpoint.

    DBE_PLDEBUGGER.info_breakpoints

    Called by the debug end to view all breakpoints.

    DBE_PLDEBUGGER.backtrace

    Called by the debug end to check the current call stack.

    DBE_PLDEBUGGER.enable_breakpoint

    Called by the debug end to enable breakpoints.

    DBE_PLDEBUGGER.disable_breakpoint

    Called by the debug end to disable breakpoints.

    DBE_PLDEBUGGER.finish

    Called by the debug end to continue the debugging until the breakpoint is reached or the upper-layer call stack is returned.

    DBE_PLDEBUGGER.set_var

    Called by the debug end to assign a value to a variable.