This topic describes how to debug the stored procedures of an ApsaraDB RDS for PostgreSQL instance by using the pldebugger plug-in.

Background information

ApsaraDB RDS for PostgreSQL supports various stored procedure languages, such as PL/pgSQL, PL/Python, PL/Perl, and PL/Tcl. You can use these languages to create functions or stored procedures.

Prerequisites

  • Your RDS instance runs one of the following database engine versions:
  • plugin_debugger is added to the value of the shared_preload_libraries parameter of your RDS instance.

    For more information about how to add plugin_debugger to the value of the shared_preload_libraries parameter, see Manage the parameters of an ApsaraDB RDS for PostgreSQL instance.

  • The version of pgAdmin4 on your database client is 4.19 or later. You can download pgAdmin4 at pgAdmin4.

Procedure

  • Enable the pldebugger plug-in.
    CREATE EXTENSION pldbgapi;
    Note Only privileged accounts are authorized to execute the preceding statement.
  • Disable the pldebugger plug-in.
    DROP EXTENSION pldbgapi;
    Note Only privileged accounts are authorized to execute the preceding statement.

Examples

  1. Use pgAdmin to connect to your RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.
  2. Create a database and a stored procedure that are used for testing.
    Example:
    CREATE TABLE test(
        id int,
        name VARCHAR(50));
    CREATE OR REPLACE FUNCTION public.testcount()
    RETURNS integer AS $$
    
    DECLARE
        postgres text;
        counts integer;
    
    BEGIN
    INSERT INTO test VALUES(1, 'a');
    
    postgres:='SELECT COUNT(*) FROM test';
    EXECUTE postgres INTO counts;
    
    IF counts > 100 THEN
        RETURN counts;
    ELSE
        RETURN 0;
    END IF;
    END;
    $$ language plpgsql;
  3. Right-click the function that you want to debug and choose Debugging > Debug. Select the Debug menu item
  4. In the right-side debugging section of the page, perform step-by-step operations to debug the function. These operations include step into/over, continue, checkpointing, and stop. In the lower part of the page, you can view the local variables, debugging results, and function stack. Buttons for debugging