[Feature Request] Support versioned scripts AFTER repeatables

[morl-la]

I would like to be able to run post-repeatable one time scripts (or versioned upgrades).
My scenario is simple - I have a table filled by a function. If the function changes and upon my decision I want to refill the table. The function is created as part of repeatables - hence cannot be referenced in an upgrade script before it is changed.

I know there are many workaround, but none of them is perfect:

1. run 2 separate migrations - 1 to update the function, another to call it in a versioned script. this is inconvenient and lacks the "connection" or "context" between the 2.
2. have the upgrade script turn on a flag somewhere and have a callback to check this flag on every migration. while this sound reasonable it seems a waste to me if this check would be positive seldom, especially if the check itself is time consuming and not just a flag.
3. change the function in the versioned script (as well as in repeatables). might work, unless this function uses 10 other functions and then it becomes a mess.
4. do not refill the table using flyway - but do it manually after the migration finishes. this adds manual work - which flyway should help me avoid - so not a good one.
5. using a callback with a placeholder which would only act if the placeholder is true (false by default) and set this placeholder using an environment variable only when needed. a bit cumbersome but would probably work.

Basically would have been nice to add a script which would run ONCE (versioned) AFTER the repeatables are done.
Whether they affect the schema version or not - makes little difference to me (but they probably should).

[MikielAgutu]

If I understand your scenario correctly, you'd like to:

1. Run a versioned migration to create a table
2. Run a repeatable migration to define a function that fills the table
3. Somehow execute the function to fill the table

Then, for your next deployment:

1. Make a change to the function
2. Somehow re-execute the function to fill the table

One approach could be to include the function definition and execution in the same repeatable migration. When you change the function, the entire script, including the function execution, will be re-run.

Function FillTable()
  // Do something
End

FillTable()

Perhaps it's undesirable to have the function definition and execution in one migration. Note that for Flyway 6.2 we're working on functionality to force repeatable migrations to run, even when unchanged (#1453). This will allow you to define a repeatable migration which simply executes the function. You can then decide whether to execute it upon migrate.

I assume the afterMigrate.sql callback (https://flywaydb.org/documentation/callbacks.html#sql-callbacks) isn't desirable for you as you'd like to execute the function only when changed, not after every migrate.

Ultimately I suppose this a script dependency and ordering problem. We've encountered a few instances of this (e.g. #2541 (comment)). We're still thinking of a good way to solve the general problem.

Is this of any help to you?

[morl-la]

It is similar to the extended info provided in #2541 , but I think my solution is much simpler than specifying execution order based on input.
I want the execution order to be exactly as it is today (totally makes sense) plus an added execution phase of versioned scripts after repeatables are done.
Not sure it will answer the need of 2541 (function-procedure dependency, for that im actually disabling compile validation in beforeMigrate and then enabling it plus validating in afterMigrate, then compiled object order makes no difference) but in many cases I would like to run a function after it has been created or changed in the same migration run.
You suggested yet another alternative to the 5 I already mentioned, but I think its still a workaround.

[MikielAgutu]

Fair enough that none of the proposed approaches are satisfactory. We'll keep this feature request open to see if it gets more support.

Are you able to use any of the workarounds, or are you completely blocked by this issue?

[morl-la]

I'm currently using workaround 4 (change the function using flyway, then run it manually) - so I'm not blocked. Since I only have to do it seldom I don't want to invest any efforts in any of the alternatives.

[MikielAgutu]

General problem captured here #2698

[morl-la]

@MikielAgutu I understand that cherryPick is the new feature that is supposed to solve my requirement above - but it doesn't really.
Since I have flyway being run from Jenkins - I would prefer not to intervene with the actual commands that are being run (i.e cherry picking a versioned file for a specific migration). So this is yet another manual workaround to the actual problem at hand.

If only flyway would have supported versioned upgrades after repeatables... sigh.