ApsaraDB RDS:Debug stored procedures (pldebugger)

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for PostgreSQL instance that meets the following version requirements:

  Major engine version	Minimum minor engine version
  PostgreSQL 14, 15, 16, and 17	20250630 or later
  PostgreSQL 10, 11, 12, and 13	20230830 or later

  Important

  To standardize extension management and improve security, RDS no longer allows creating this extension on instances running a minor engine version earlier than 20230830. If your instance runs an earlier version, upgrade the minor engine version before proceeding. Existing installations on older versions continue to work without changes. For details, see Limits on creating extensions.

• A privileged account on the instance. To create one, see Create an account.

• pgAdmin 4 v4.19 or later. Download it from pgAdmin 4.

Install the extension

1. Set the shared_preload_libraries parameter to include plugin_debugger. For example:

   'pg_stat_statements,auto_explain,plugin_debugger'

2. Connect to the database using a privileged account, then run:

   CREATE EXTENSION pldbgapi;

To remove the extension, run:

DROP EXTENSION pldbgapi;

Debug a stored procedure

The following example demonstrates how to debug a stored procedure.

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;

Step 2: Start the debugger

Right-click the function that you want to debug.

Step 3: Step through the function

The debugging pane opens on the right. Use the toolbar to control execution:

Action	Description
Step Into	Execute the highlighted line, stepping into any called functions
Step Over	Execute the highlighted line without entering called functions
Continue	Run until the next breakpoint or until the function completes
Add Breakpoint	Toggle a breakpoint on the current line
Stop	Halt execution

At the bottom, you can view information about local variables, debugging results, and the function stack.

What's next

• Connect to an ApsaraDB RDS for PostgreSQL instance

• Modify instance parameters

• Limits on creating extensions