0

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: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html

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: my-user
  - changeSet:
      id: 1
      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.

I tried only executing the SQL, which did not work, I tried this Yaml without the endDelimiter and it fails as it stops reading the sql statement post ';' character. I tried with this delimiter, everything works but the resource is not created in the database.

I expect that someone helps me fix this issue and on executing the resource is created in the database.

agrawalramakant
  • 160
  • 1
  • 5
  • How did you determine the resource was not created? Did you actually check the database or just check the audit table? If just the audit table you will never find anything this function executes. The `delete` statement will remove the just inserted row and any other row that existed from another process. BTW your endDelimiter does not appear in the posted code. – Belayer Mar 23 '23 at 18:33

0 Answers0