Calling commit in postgresql procedure throws error

General Discussion
1

Simple example (spring boot + liquibase + postgresql): GitHub - zeroWiNNeR/liquibase-procedure-commit-error-example

You can find changeset in src/main/resources/liquibase

Executing the patch throws error “Reason: liquibase.exception.DatabaseException: Error: invalid transaction termination on commit” but when i called this code from db console everything is ok. What could be the reason?

2

Hi @zerowinner,
I am wondering if (and how) you have overcome this issue. I am facing the same issue.
Thanks!

3

I’ve not use the createProcedure change-type, but why not just put the statement in sql tags instead?
(This example is Oracle, but same rules should apply)

<changeSet id="create_procedure" author="BOB" runOnChange="true">
  <sql endDelimiter="/">
    create or replace procedure test_proc is
      v_dummy1 date;
    begin
      select sysdate into v_dummy1 from "DUAL";
      commit;
    end test_proc;
    /  
  </sql>
  <rollback>
    DROP PROCEDURE test_proc;
  </rollback>
</changeSet>

I think the issue might be the last of an alternate end-delimiter, so it’s ending the statement at the “commit;”. Also, you defined the alternate end-delimiter in the second block, but didn’t use it to end the statement, I’m guessing that is going to error next.