This topic describes how to debug the stored procedures of an ApsaraDB RDS for PostgreSQL instance by using the pldebugger plug-in.
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.
- 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.
- 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.
- Use pgAdmin to connect to your RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.
- 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;
- Right-click the function that you want to debug and choose Debugging > Debug.
- 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.