HomeMogDBMogDB StackUqbar
v2.1

Documentation:v2.1

Supported Versions:

Other Versions:

Overview of DBE_PLDEBUGGER Schema

DBE_PLDEBUGGER system functions are used to debug stored procedures in a standalone system. This chapter describes the interfaces supported by DBE_PLDEBUGGER. Only the administrator has the permission to execute these debugging interfaces.

img NOTICE: 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 not advised to create a user in the function body.

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 interface 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.

    mogdb=# CREATE OR REPLACE PROCEDURE test_debug ( IN  x INT)
    AS
    BEGIN
          INSERT INTO t1 (a) VALUES (x);
    END;
    /
    CREATE PROCEDURE
    mogdb=# SELECT OID FROM PG_PROC WHERE PRONAME='test_debug';
      oid
    -------
     16389
    (1 row)
    mogdb=# 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.

    mogdb=# 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.

    mogdb=# 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.

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

    Output all the current variables on the client where the attach command is executed.

    mogdb=# SELECT * FROM DBE_PLDEBUGGER.info_locals();
     varname | vartype | value | package_name
    ---------+---------+-------+--------------
     $1      | int4    | 1     |
    (1 row)

    Directly execute the stored procedure that is being debugged.

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

    Exit the stored procedure that is being debugged and do not execute statements that have not been executed before.

    mogdb=# SELECT * FROM DBE_PLDEBUGGER.abort();
     abort
    -------
     t
    (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. For details about the debugging interfaces, see the following table.

    Table 1 DBE_PLDEBUGGER

    Interface Description
    DBE_PLDEBUGGER.turn_on Invoked by the server, indicating that the stored procedure can be debugged. After the interface is invoked, the stored procedure is hung to wait for debugging information.
    DBE_PLDEBUGGER.turn_off Invoked by the server, indicating that debugging the stored procedure is disabled.
    DBE_PLDEBUGGER.local_debug_server_info Invoked by the server to print all stored procedures that have been turned on in the current session.
    DBE_PLDEBUGGER.attach Invoked by the debug end to attach with the stored procedure that is being debugged.
    DBE_PLDEBUGGER.info_locals Invoked by the debug end to print the current values of variables in the stored procedure that is being debugged.
    DBE_PLDEBUGGER.next Invoked by the debug end to execute the next step.
    DBE_PLDEBUGGER.continue Invoked by the debug end to continue the execution until the breakpoint or stored procedure ends.
    DBE_PLDEBUGGER.abort Invoked by the debug end to stop debugging. The server reports a long jump error.
    DBE_PLDEBUGGER.print_var Invoked by the debug end to print the current values of specified variables in the stored procedure that is being debugged.
    DBE_PLDEBUGGER.info_code Invoked 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 Invoked by the debug end to execute step by step.
    DBE_PLDEBUGGER.add_breakpoint Invoked by the debug end to add a breakpoint.
    DBE_PLDEBUGGER.delete_breakpoint Invoked by the debug end to delete a breakpoint.
    DBE_PLDEBUGGER.info_breakpoints Invoked by the debug end to view all breakpoints.
    DBE_PLDEBUGGER.backtrace Invoked by the debug end to check the current call stack.
    DBE_PLDEBUGGER.enable_breakpoint Invoked by the debug end to enable breakpoints.
    DBE_PLDEBUGGER.disable_breakpoint Invoked by the debug end to disable breakpoints.
    DBE_PLDEBUGGER.finish Invoked 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 Invoked by the debug end to assign a value to a variable.
Copyright © 2011-2024 www.enmotech.com All rights reserved.