Preconditions are unable to evaluate indexes by their columns, and columnNames attribute appears to be entirely nonfuctional/ignored

I am running into a situation where preconditions are not behaving properly when they deal with indexes that cover multiple columns.

On the one hand, I have this:

<changeSet id="1" author="me" context="test">
    <preConditions onFail="MARK_RAN" onFailMessage="This index does not exist to be removed.">
            <indexExists tableName="table_name" indexName="IX_table_name_index"/>
                <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
    <dropIndex tableName="table_name" indexName="IX_table_name_index" />

Essentially, if an index exists with the correct name, but WITHOUT with the correct contents, it should get dropped/deleted (but if it does exist with all of the correct columns, this changeset should be bypassed and ignored).

This DOES NOT WORK, mainly because it’s not catching correctly-named indexes that are missing one or more of the required columns. For example, if a pre-existing index with the name IX_table_name_index contains only columns column_two and column_three, it is not being dropped as per the very explicit instructions.

On the other hand, I have this:

<changeSet id="3" author="me" context="test">
    <preConditions onFail="MARK_RAN" onFailMessage="This index already exists and will not be created.">
            <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
    <createIndex clustered="false" tableName="table_name" indexName="IX_table_name_index">
        <column descending="false" name="column_one"/>
        <column descending="false" name="column_two"/>
        <column descending="false" name="column_three"/>
        <column descending="false" name="column_four"/>

Essentially, if an index with the correct name AND all of the correct columns exists, do not try to re-create it.

When run in isolation, this does work, but it works too well. As in, I am unable to create an index name collision. (Why a collision? Well, to prove that this precondition is actually being correctly parsed)

When I have a pre-existing index with the name IX_table_name_index, but without all of the required four columns, I am unable to generate an index name collision - the script fails to attempt an index creation, despite the pre-existing index not fulfilling the precondition definition for a MARK_RAN flag.

As such, I strongly believe that the columnNames attribute is entirely nonfunctional, and is actually being completely ignored - making it more than just useless, but an actual hindrance and a source of issues. Does this represent a flaw within Liquibase that would call for a bug report?

Edit, for clarity: MSSQL 2016/2019 on Windows, and utilizing a Java project. If it matters.

1 Like

Hi @renekabis ,

This is a valuable/proper use case I think you have here.
While this might not be the answer to your question, here are some notes and some past experiences working with indexes, Liquibase and SQL Server.

  • First of all, columnNames I don’t think is even an index related attribute. columnName it is. This might be the reason behind the ignore/precondition failure.
  • Liquibase does not support included columns feature that SQL Server offers. Be really careful with this, might impact the precondition / limit your index creation cases.
  • Always use in the create index or any index related precondition the tableName as well (paired with the indexName).
  • You can always use the sqlCheck precondtion type to cover any case that Liquibase does not cover. This would be a candidate case for using a sqlCheck precondition (and the actual SQL statement would check for the index in sys.indexes (joining sys.objects etc) with all the things you need).

Hope this helps,

Unfortunately, the XSD file for Liquibase does not agree with your description on that first point: columnNames is accepted as an attribute for the indexExists element, but the columnName attribute is flagged as an illegal element attribute for indexExists.