Use the pldebugger plug-in to debug stored procedures

Prerequisites

• Your RDS instance runs one of the following database engine versions:
  • Major engine version: PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, or PostgreSQL 13.
  • Minor engine version: 20211130 or later. For more information about how to update the minor engine version, see Update the minor engine version of an ApsaraDB RDS for PostgreSQL instance.
• 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.
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.