Geek Answers Handbook

Liquibase idempotency in Oracle DDLs

I am studying using Liquibase for a new project using Oracle, and I am wondering how I can ensure that my changeSets are reliable enough to recover from any type of failure without manual intervention. Ideally, I would use the runInTransaction attribute, which would allow DDL to be rolled back in case of failures, but Oracle will automatically compile DDL. In this situation, the documentation recommends:

Therefore, it is usually best to have one change for changeSet if there are no groups of non-automatic commit changes that you want to apply as a transaction, such as data insertion.

Having one DDL per changeSet will reduce the likelihood of problems, but will not fix them. If DDL succeeds, but upgrading to DATABASECHANGELOG fails, from my tests it seems that Liquibase is just stuck and manual intervention is required.

Prerequisites must be used at every step to avoid this problem? This makes the resulting changeSets pretty verbose. This is one of the definitions of the Liquibase example table:

<changeSet author="jsmith" id="1"> <createTable tableName="departments" remarks="The departments of this company. Does not include geographical divisions."> <column name="id" type="number(4,0)"> <constraints nullable="false" primaryKey="true" primaryKeyName="DPT_PK"/> </column> <column name="dname" type="varchar2(14)" remarks="The official department name as registered on the internal website."/> </createTable> <addUniqueConstraint constraintName="departments_uk1" columnNames="dname" tableName="departments"/> <createSequence sequenceName="departments_seq"/> </changeSet>

To make this idempotent, I think it should change to the following:

 <changeSet author="jsmith" id="1"> <preConditions onFail="MARK_RAN"> <not> <tableExists tableName="departments" /> </not> </preConditions> <createTable tableName="departments" remarks="The departments of this company. Does not include geographical divisions."> <column name="id" type="number(4,0)" / column> <column name="dname" type="varchar2(14)" remarks="The official department name as registered on the internal website." /> </createTable> </changeSet> <changeSet author="jsmith" id="2"> <preConditions onFail="MARK_RAN"> <not> <primaryKeyExists primaryKeyName="pk_departments" /> </not> </preConditions> <addPrimaryKey tableName="departments" columnNames="id" constraintName="pk_departments" /> </changeSet> <changeSet author="jsmith" id="3"> <preConditions onFail="MARK_RAN"> <not> <uniqueConstraintExists constraintName="departments_uk1" /> </not> </preConditions> <addUniqueConstraint constraintName="departments_uk1" columnNames="dname" tableName="departments" /> </changeSet> <changeSet author="jsmith" id="4"> <preConditions onFail="MARK_RAN"> <not> <sequenceExists sequenceName="departments_seq" /> </not> </preConditions> <createSequence sequenceName="departments_seq" /> </changeSet>

Is there an easier way to achieve this? I would have thought that Liquibase could have created these premises.

thanks

+4
source share
1 answer

Unfortunately, in most DBMSs, DDL statements commit the transaction, and Liquibase responds to failure by simply rolling back from the transaction. Therefore, first I will make sure that each DDL statement carries a separate set of changes.

In what cases did the database update fail? I'm curious this should be pretty cool.

In any case, you can avoid repeating yourself by writing a small extension for Liquibase that does this automatically for all of your change sets. See the Precondition extension point.

0
source

More articles:


All Articles