Problem validating state of database before commit of version

Question

I'm playing around with Flyway and amongst other things, I would like to verify certain things in the database before committing the version. As an example, I would like the following migration to fail:

--#SET TERMINATOR @
CREATE TABLE TMP.DUMMY(x int) @

create or replace procedure TMP.DOUBLE_DUMMY()
begin
    update TMP.DUMMY set x = 2*x;
end @

-- TMP.DOUBLE_DUMMY() is invalidated
DROP TABLE TMP.DUMMY @

This is easy enough to detect since I can try to recompile invalidated procedures as:

for c1 as c1 cursor for
    select rtrim(procschema) || '.' || rtrim(procname) as p
        ,  rtrim(procschema) || '.' || rtrim(specificname) as s
        ,  text
    from syscat.procedures
    where procschema = in_schema
      and valid <> 'Y'
      and language = 'SQL'
    order by create_time
do
    execute immediate 'drop specific procedure ' || s;
    execute immediate text;
end for;

and if something is broken, recompilation of the procedure fails. However, I can't seem to find a callback that can be used after the last statement in the file, but before the new version is inserted and committed.

The next idea I got was to create a trigger on the flyway version table. Something like:

CREATE OR REPLACE TRIGGER FLYWAY.IRA_FLYWAY_SCHEMA_HISTORY
AFTER INSERT ON FLYWAY.FLYWAY_SCHEMA_HISTORY
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
    CALL COMPILE_SCHEMAS();
END @

However, it turns out that flyway uses one connection for the statements in the file and one connection for the version table. This leads to a lock timeout since there is an X lock on the object when the recompile appears.

I can't seem to find some property that would prevent Flyway to use two connections. From a logical perspective, it seems counter-intuitive to use two transactions for one unit of work. I can't imagine a situation where you would like to commit one and rollback the other.

Any other thoughts on how to inject such a control mechanism, without having to put the control inside the migration files?

Answer 1

afterEachMigrate actually works that way. The trick is that the commit of the version is a separate transaction, which waits for afterEachMigrate to finish.