ApsaraDB RDS:Debug stored procedures (pldebugger)

Prerequisites

• Your RDS instance runs one of the following database engine versions:

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

  Important

  This plug-in was supported in minor engine versions earlier than 20230830. To standardize plug-in management and enhance security, RDS is optimizing plug-ins that pose security risks in newer engine versions. As a result, creating these plug-ins is restricted on certain minor engine versions. For more information, see Plug-in creation restrictions.

  • If your instance runs a minor engine version earlier than 20230830 and you already use this plug-in, your workloads are not affected.

  • If you create this plug-in for the first time or recreate it, upgrade the minor engine version to the latest version.

• You have a privileged account for your ApsaraDB RDS for PostgreSQL instance. For more information, see Create an account.

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

Install and uninstall the extension

1. Set instance parameters, and add plugin_debugger to the running parameter value of shared_preload_libraries. For example, change the running parameter value to 'pg_stat_statements,auto_explain,plugin_debugger'.

2. Use a privileged account to connect to the target database, and run the following SQL statements to manage the extension.

   • Create the extension.

     CREATE EXTENSION pldbgapi;

   • Delete the extension.

     DROP EXTENSION pldbgapi;

Examples

1. Use a pgAdmin client to connect to your ApsaraDB RDS for PostgreSQL instance. For more information, see Connect to a PostgreSQL instance.

2. Create a test table and a stored procedure.

   Sample SQL statements:

   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. In the left-side Browser pane, expand Functions, right-click the testcount() function, and then choose Debugging > Debug to enter debugging mode.

4. In the function debugging panel on the right side of the pgAdmin interface, you can perform step-by-step debugging of the target function. The debugger toolbar at the top provides buttons to control execution, such as Step Into, Step Over, Continue, and Stop. Use the Toggle breakpoint and Clear all breakpoints buttons to manage breakpoints. The current line of execution is highlighted in the code editor. At the bottom, the Parameters, Local variables, Messages, Results, and Stack tabs let you inspect variable values, messages, and the function stack.