Liquibase idempotency on Oracle DDLs

Question

I am investigating using Liquibase for a new project using Oracle and I am wondering how I can ensure my changeSets are robust enough to recover from any types of failures without manual intervention. Ideally I would use the runInTransaction attribute, which would allow DDLs to be rolled back on failures, but Oracle auto-commits on DDLs. For this situation, the documentation recommends:

Therefore, it is usually best to have just one change per changeSet unless there is a group of non-auto-committing changes that you want applied as a transaction such as inserting data.

Having one DDL per changeSet would reduce the chances of problems, but doesn't eliminate them. If the DDL succeeds, but the update to DATABASECHANGELOG fails, from my tests it seems that Liquibase just gets stuck and manual intervention is required.

It is necessary to use preconditions on each step to avoid this problem? This makes the resulting changeSets pretty verbose. This is one of the Liquibase example table definitions:

<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 would have to change to something like 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 some simpler way to achieve this? I would have thought that Liquibase would have been able to generate these preconditions.

Thanks

Running each DDL within it's own changeset is the recommended way to go. What you're trying to defend against (missing update to the CHANGELOG table) is pretty paranoid and is solved by inserting pre-conditions into your changset, as you've done.... What alternative strategy would you expect from liquibase? — Mark O'Connor
Defending against plausible failure conditions is robust, not paranoid. I have hit this exact issue with proprietary DB upgrade frameworks (it's particularly likely after long-running operations such as new index creations against very large tables, after which point the DB session may have expired.) I was hoping that Liquibase would support the option of automatically generating the preconditions (e.g. for every <createTable>, implicitly include a precondition of <not><tableExists>). — brucet
Sounds like a great feature to propose: liquibase.org/community — Mark O'Connor

Mirko Adari Mirko Adari · Accepted Answer · 2013-05-03T19:46:00

Unfortunately in most RDBMS, DDL statements commit the transaction and Liquibase reacts to failure by just rolling back the transaction. So first what I would do is wrap each DDL statement in a separate changeset.

In which cases did updating databaschangelog fail? I'm curious as this should be pretty robust.

Anyways, you can avoid repeating yourself by writing a small extension for Liquibase that does it automatically for all of your changesets. Take a look at Precondition extension point.

Liquibase idempotency on Oracle DDLs

1 Answers