ApsaraDB RDS:Use the pldebugger extension

Prerequisites

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

  • Major engine version: PostgreSQL 10, PostgreSQL 11, PostgreSQL 12, or PostgreSQL 13.

  • The RDS instance runs a minor engine version of 20230830 or later.

    Important

    The extension is supported in minor engine versions that are earlier than 20230830. To standardize extension management and enhance extension security for ApsaraDB RDS for PostgreSQL, ApsaraDB RDS plans to optimize vulnerable extensions in minor engine version iterations. As a result, some extensions can no longer be created for RDS instances that run earlier minor engine versions. For more information, see [Product changes/Feature changes] Limits on extension creation for ApsaraDB RDS for PostgreSQL instances.

    • If you have created the extension for your RDS instance that runs a minor engine version earlier than 20230830, the extension is not affected.

    • If this is the first time you create the extension for your RDS instance or re-create the extension, you must update the minor engine version of the RDS instance to the latest version. For more information, see Update the minor engine version.

• 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 Modify 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.

Use the extension

• Create the extension.

  CREATE EXTENSION pldbgapi;

  Note

  Only privileged accounts have the permissions to execute the statement.

• Delete the extension.

  DROP EXTENSION pldbgapi;

  Note

  Only privileged accounts have the permissions to execute the 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.

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