Install pldbgapi and Debug PL/pgSQL Functions - AnalyticDB for PostgreSQL

The pldbgapi extension adds interactive debugging to PL/pgSQL functions in AnalyticDB for PostgreSQL. Set breakpoints, step through code line by line, and inspect variable values—the same way you would use the GNU Debugger (GDB) for C programs.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL V6.0 instance running V6.3.10.19 or later. To check your instance version, see View the minor engine version
pgAdmin version 6.21. Later versions are not compatible with AnalyticDB for PostgreSQL. Download pgAdmin at pgAdmin

Install the extension

Install pldbgapi from the Extensions page of your AnalyticDB for PostgreSQL instance. For step-by-step instructions, see Install, update, and uninstall extensions.

Debug a function

Step 1: Create a function to debug

Connect to your AnalyticDB for PostgreSQL database and create a PL/pgSQL function. The following example creates add_numbers, which inserts rows into a temporary table, computes an average, and returns the sum:

CREATE OR REPLACE FUNCTION add_numbers(a int, b int)
RETURNS NUMERIC AS $$
DECLARE
    t1_b_avg NUMERIC;
BEGIN
    --DROP TABLE t1;
    CREATE TABLE t1 (a int, b int, c int, d int);
	RAISE NOTICE 'Finish CREATE ';
    FOR i IN 1..10 LOOP
	    INSERT INTO t1 VALUES (i, i, i, i);
	END LOOP;
	RAISE NOTICE 'Finish INSERT ';
    SELECT avg(t1.b) INTO t1_b_avg FROM t1 LIMIT 1;
	RAISE NOTICE 'Finish SELECT: avg=[%] ', t1_b_avg;
	DROP TABLE t1;
    RETURN a + b + t1_b_avg;
END;
$$ LANGUAGE plpgsql;

Verify the function runs correctly before debugging it:

SELECT add_numbers(1, 3);

Step 2: Connect pgAdmin to your instance

Open pgAdmin. In the top navigation bar, choose Object > Register > Server.
On the General tab of the Register-Server page, enter a server name. For example: test.

On the Connection tab, fill in the following fields and click Save.

Parameter	Description
Host name/address	The public endpoint of your AnalyticDB for PostgreSQL instance. For details, see Manage public endpoints.
Port	The port number of your instance.
Maintenace database	The database name. Set this to postgres.
Username	Your database account.
Password	The password for your database account.

Step 3: Start the debugger

In the left-side server list, expand test > Databases > postgres > Schemas > public > Functions.
Right-click the function you want to debug and choose Debugging > Debug.
On the Debugger page, specify input parameters for the function and click Debug. For this example, set a and b to 2 and leave the Null? checkbox unchecked.

Step 4: Step through the code

In the upper-left corner of the page, click the icon.

Step 5: Review the results

After execution finishes, check the Messages tab for output text messages during the execution process of the function, and the Result tab for the return value after the function completes.

AnalyticDB:pldbgapi