Debugging stored procedures in Amazon RDS for PostgreSQL might pose a challenge if you are accustomed to using a GUI tool. The absence of support for the pldbgapi extension in Amazon RDS for PostgreSQL complicates this process. In this blog post, I suggest a workaround based on deploying an additional PostgreSQL on an Amazon Windows EC2 instance as a debugging platform.
Assuming that you used AWS Schema Conversion Tool (SCT) with the Oracle extension pack, the code converted from Oracle to PostgreSQL will contain several calls to objects in schemas named aws_operators, aws_oracle_context, aws_oracle_data, and aws_oracle_ext. These modules emulate functions present in Oracle that are required for the code to execute in PostgreSQL. However, after the conversion by AWS SCT, you may still need to debug a stored procedure to resolve an issue.
The first step is to get the DDL for the objects created as part of the extension pack. The process is as follows:
– Start the AWS SCT tool,
– connect to the target database,
– right click on the target schema in the right-hand box,
– click on the “Apply Extension Pack for” Oracle in this case
In the “Apply extension pack for Oracle” dialogue box, progress to “Step 9: Function emulation” and click on the “Save scripts to” button on the bottom right.
Provide the name of a folder (preferably a new empty folder) as the target for the SQL files. The DDL files will be created as shown below
As of January 2024, this process will create 1506 files. This is subject to change as AWS releases newer versions of SCT.
The next step is to deploy an Amazon EC2 Windows instance. After the instance has been created, connect to the instance and download the PostgreSQL software from EnterpriseDB (EDB). I chose the EDB distribution as it includes several extensions preinstalled including pldbgapi.
The PostgreSQLTutorial website provides the steps to be followed to install PostgreSQL. After PostgreSQL has been installed, create a new database for this test. In this new database, create the pldbgapi extension with the SQL command
CREATE EXTENSION IF NOT EXISTS pldbgapi;
Confirm that the extension has been created. I am using PGAdmin to connect to my Amazon RDS PostgreSQL instance
After the extension has been created, copy the DDL files created from SCT to a location on the Windows server
(There are 1,509 files shown above as I created three extra files. Please ignore them.)
After some testing, I identified that the best way to deploy them was via Windows batch file with commands as shown below
@echo off setlocal set PGPASSWORD=password-goes-here "C:\Program Files\PostgreSQL\15\bin\psql.exe" -h localhost -U postgres -d dctestdb -f "C:/dean/ssct-oracle-extension-pack-ddl/0001-aws_lambda.sql" -L "C:/dean/ssct-oracle-extension-pack-ddl/sct-ddl.log" "C:\Program Files\PostgreSQL\15\bin\psql.exe" -h localhost -U postgres -d dctestdb -f "C:/dean/ssct-oracle-extension-pack-ddl/0002-dblink_transaction.sql" -L "C:/dean/ssct-oracle-extension-pack-ddl/sct-ddl.log" . . . All other files here . . .
The output of the DDL operations will be in the log file specified in the above batch file
After the DDL files have been executed, the database has the aws_operators, aws_oracle_context, aws_oracle_data, and aws_oracle_ext schemas defined
At this point, you can extract the DDL for a stored procedure (and related objects) from Amazon RDS and create it in the Windows EC2 PostgreSQL database and you will be able to debug the stored procedure via the GUI.
