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.

4

Hi @daryldoak,
I don’t think the issue is related with the end-delimiter, but with your COMMIT statement inside your procedure. I was facing a similar issue in PostgreSQL with the partition_data_proc procedure of the pg_partman extension, as it has a COMMIT in the middle of its body.
As Liquibase attempts to execute each changeset in a transaction that is committed at the end (or rolled back if there is an error), it is like Liquibase assumes your COMMIT statement is the end of the changeset. Could it be your case?
To workaroubd this issue, in my case, I had to call the procedure from the outside, using psql tool within the executeCommand changeset.
Hope it helps.

5

@ic185037

I’m not having an issue, I provided my changeset as an example for @zerowinner .

That being said, it is perfectly valid to have a commit statement inside of stored procedure. Liquibase does not interpret the code inside of the procedure, it just looks for the end-delmiter to know where the CREATE statement ends.

6

Hi @daryldoak,

Thanks for your reply.

Allow me to disagree with you: As I mentioned above, the partition_data_proc procedure of the pg_partman extension in PostgreSQL includes a COMMIT in line 109 and if that line is executed, Liquibase fails. If the COMMIT statement is not executed (e.g., there are no rows/data to be moved by the partition_data_proc procedure) then you don’t see the error and it looks that the changeSet is working fine. I mean, the problem is not semantic as you pointed out, but functional.

Having said that, whether this is a Liquibase bug, it is another topic.

This is the changeSet I was trying to execute:

	<changeSet author="ic185037" dbms="postgresql" id="1712570379" context="part">
	<comment>COMMIT issue</comment>
        <sql>
            CALL partman.partition_data_proc('cxp.cxp_journal');
        </sql>
	</changeSet>

And this is the error I get:

image
image1870×602 67.1 KB

To workaroung this issue, I had to call that procedure in a different way, that is:

	<changeSet author="ic185037" dbms="postgresql" id="1712570380" context="part">
	<comment>Workaround call</comment>
        <executeCommand executable="psql">
            <arg value="--username=cxp_owner" />
            <arg value="--no-password" />
            <arg value="--dbname=postgres" />
            <arg value="--command=CALL partman.partition_data_proc('cxp.cxp_journal');" />
        </executeCommand>
	</changeSet>

Cheers

7

Ok, thanks for providing the actual error, now I see the difference. I am creating a procedure that contains a commit, not executing the procedure. You are executing the procedure that contains a commit and getting that error.

I think this has to do with the transaction control system in postgres, this error does not occur in Oracle.

Try adding runInTransaction=“false” to your changeset.