Possibility to execute migration outside of transaction

Some SQL commands can't be executed in the scope of transaction. E.g. psql ALTER TYPE .... I suggest to add possibility to disable wrapping particular migration in the transaction. It can be configured via some flag in the migration file name, or via special comment inside the migration, e.g. -- tx off.

I'm ready to submit PR in case this changes are welcome and we agree on configuration option.

[megafinz]

@lecaillon any feedback on that?

[chrisandchris]

+1

We would also like to have that. Having issues with CREATE EVENT SESSION statements that can not run inside transactions. Also open to submit a pull-request.

[lecaillon]

I like the idea of a SQL comment at the very first line of a migration script. Something like:

-- evolve-tx-off

What do you think ?

[chrisandchris]

I think putting that option into the script is the wrong way. Because as of now, Evolve puts all scripts into a single transaction and it's either everything or nothing succeeds. Having an option to disable transactions would mean that a set of scripts could succeed while some others may fail.

I think this should be a global option where it's either all or none.

As Evolve uses one transaction per migration script, I agree with the idea to put a "control line" into the SQL script that can disable transactions. Maybe keep the format in a way that future configuration options would be possible (e.g. separated by semicolon).

Edit: I was misinformed - fixed comment.

[lecaillon]

Evolve uses one transaction per migration script. Every time a script succeeds it is commited. If one fails, it is rolled back and Evolve stops without executing the following scripts. See the documentation.

So having the possiblity to disable transaction at script level allow the user to exclude from the transaction only the very few statements that do not support it, by grouping them in a dedicated migration.

Thanks @chrisandchris for your feedback. I haven't been very reactive about that issue I guess :)

[Zelion]

Evolve uses one transaction per migration script. Every time a script succeeds it is commited. If one fails, it is rolled back and Evolve stops without executing the following scripts. See the documentation.

So having the possiblity to disable transaction at script level allow the user to exclude from the transaction only the very few statements that do not support it, by grouping them in a dedicated migration.

Thanks @chrisandchris for your feedback. I haven't been very reactive about that issue I guess :)

Hi,

I'm trying to execute 2 ALTER DATABASE in separates scripts. This will always throw a "ALTER DATABASE statement not allowed within multi-statement transaction" and the following scripts will not be executed. We tried with the -- evolve-tx-off comment, but no luck. Would you know what we could do to fix this issue?

The point of the ALTER DATABASE is to create a FILEGROUP and then ADD FILE to that FILEGROUP.

Thanks.

[lecaillon]

Hi, some ideas :

• what version of Evolve do you use ?
• do not use explicit BEGIN TRANSACTION in your script

[Zelion]

Hi, some ideas :

* what version of Evolve do you use ?

Evolve 2.4.0

* do not use explicit `BEGIN TRANSACTION` in your script

I'm not