pgAdmin4

Before you begin

• This feature is supported in ApsaraDB for PolarDB for PostgreSQL (Oracle-compatible) 2.0.

• You have created a database account for your PolarDB cluster. For more information, see Create a database account.

• You have added the IP address of your client machine to the IP whitelist of the PolarDB cluster. For more information, see Configure an IP whitelist for a cluster.

• For versions of ApsaraDB for PolarDB for PostgreSQL (Oracle-compatible) earlier than 2.0.14.22.0, the line number displayed for a PL/SQL breakpoint is one less than the actual position.

  For example, if a breakpoint is set on line 10 of a stored procedure, the debugger displays it on line 9.

Downloads

• Windows:

  pgadmin4-polardb-x64.zip (The pgAdmin executable is in the runtime folder.)

• macOS:

  • Apple silicon:

    pgadmin4-polardb-arm64.dmg (MD5: e195cf7eb03da5e0bf0814ccf7f4e13b)

  • Intel:

    pgadmin4-polardb-x86_64.dmg (MD5: e730dce5e1b2648bc248a73dbaf2571b)

Procedure

1. Register a PolarDB cluster:

   1. In the Object Browser on the left, right-click the Servers node and select Register > Server....

   2. In the Register - Server dialog box, switch to the General tab and enter a server name in the Name field. This field is required.

   3. In the Register - Server dialog box, on the Connection tab, configure the following parameters:

      Parameter	Description
      Host name/address	The connection endpoint of your ApsaraDB for PolarDB for PostgreSQL (Oracle-compatible) cluster.
      Port	1521
      Maintenance database	Your database name.
      User name	Your database account name.
      Password	The password for your database account.

2. Execute database commands in pgAdmin4:

   1. On the toolbar, click the Query Tool button to connect to your database.

   2. In the query editor, enter the following commands and click the Execute button.

      -- 1. Create the package declaration
      CREATE OR REPLACE PACKAGE testpkg IS
          PROCEDURE print_a();
          PROCEDURE sign_a(val INT);
      END;
      
      -- 2. Create the package body
      CREATE OR REPLACE PACKAGE BODY testpkg IS
          a INT := 100;
          
          PROCEDURE print_a() IS
          BEGIN
              RAISE NOTICE 'a is: %', a;
          END;
      
          PROCEDURE sign_a(val INT) IS
          BEGIN
              a := val;
          END;
      END;
      
      -- 3. Call the procedures to test
      CALL testpkg.sign_a(100);
      CALL testpkg.print_a();
      

   3. Check the Messages pane at the bottom to verify that it displays CREATE PACKAGE BODY.

3. Debug a PL/SQL stored procedure: ApsaraDB for PolarDB for PostgreSQL (Oracle-compatible) allows you to debug PL/SQL stored procedures, functions, and packages. Packages are displayed under the Schemas node in the left navigation pane, where you can find the testpkg package you just created.

   • Direct debugging: For PL/SQL stored procedures, functions, and packages, you can start a debugging session from the Schemas node in the navigation pane. Find the testpkg package that you created, right-click one of its Procedures, and then click Debugging > Debug to start debugging.

     Note

     • For stored procedures that contain OUT parameters, use the indirect debugging method.

     • During a debugging session, you can add or remove breakpoints by clicking in the left margin of the code. The parameters and package variables are displayed in the Local variables pane at the bottom.

     • You can add or remove breakpoints only during an active debugging session.

   • Indirect debugging:

     1. From the Schemas node in the navigation pane, find the testpkg package that you created, right-click one of its Procedures, and then click Debugging > Set Breakpoint. The current session (Session A) now waits for another session to call the procedure.

     2. Click the Query Tool button to open a new connection (Session B). In the query editor, run CALL testpkg.print_a(); to call the procedure from the new session and trigger the breakpoint.

     3. Return to Session A. After another session (for example, Session B) calls the procedure, the debugger captures the call and displays the source code of the stored procedure. You can view current variables, such as a, an integer with a value of 100, in the Local variables pane at the bottom. You can now perform debugging operations, such as adding more breakpoints.

Release notes