Unable to create function in Aurora postgres

Liquibase Development
1

I am working on a usecase in which I want to use DMS to migrate data from Aurora postgres. To be able to use any user other than the admin users, we need to create some resource in the Aurora postgres database for the DMS to function properly.
You can refer this page: Using a PostgreSQL database as an AWS DMS source - AWS Database Migration Service

I want to create the resources in the database using liquibase. I have created the following yaml file to create the resource:

databaseChangeLog:
  - preConditions:
        - runningAs: 
            username: inquiry-es-migration
  - changeSet:
      id: 66
      author: r.agrawal
      changes:
        - sql:
            dbms: postgres
            splitStatements: false
            endDelimiter: ';;'
            sql:
                CREATE OR REPLACE FUNCTION awsdms_intercept_ddl()
                  RETURNS event_trigger
                LANGUAGE plpgsql
                SECURITY DEFINER
                  AS $$
                  declare _qry text;
                BEGIN
                  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
                        SELECT current_query() into _qry;
                        insert into awsdms_ddl_audit
                        values
                        (
                        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
                        );
                        delete from awsdms_ddl_audit;
                end if;
                END;
                $$;

I used the endDelimiter as ‘;;’ because without that Liquibase was considering the SQL statement to be till the first ‘;’ and it was failing.

When I try to validate the above code, it passes. when I execute it, it shows no problem and an entry is also made in the databasechangelog table in the database but the resource is not created.
Please guide me, how can I fix it.

2

@daryldoak ?? could you please help me here?

3

I’m not seeing a double semicolon in your changeset. I would expect to see something like this:

$$
;;

Can you provide the Liquibase output showing the sql statement was executed?

4

@daryldoak
The script is

databaseChangeLog:
  - preConditions:
        - runningAs: 
            username: <responsible_user>
  - changeSet:
      id: 70
      author: user
      changes:
        - sql:
            dbms: postgres
            splitStatements: false
            endDelimiter: ';;'
            sql:
                CREATE OR REPLACE FUNCTION awsdms_intercept_ddl()
                  RETURNS event_trigger
                LANGUAGE plpgsql
                SECURITY DEFINER
                  AS $$
                  declare _qry text;
                BEGIN
                  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
                        SELECT current_query() into _qry;
                        insert into awsdms_ddl_audit
                        values
                        (
                        default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
                        );
                        delete from awsdms_ddl_audit;
                end if;
                END;
                $$;
                ;;

and the output is
Starting Liquibase at 22:41:20 (version 4.18.0 #5864 built at 2022-12-02 18:02+0000)
Liquibase Version: 4.18.0
Liquibase Community 4.18.0 by Liquibase
Running Changeset: changelogs/<FILE_NAME>::70::user
Liquibase command ‘update’ was executed successfully.

but no function is created in the database

5

Please remove the changeset row from the databasechangelog table, add the following to your liquibase execution so the sql is echoed to the output, and then execute again:

–sql-log-level=INFO