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

General Discussion
1

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.">
        <and>
            <indexExists tableName="table_name" indexName="IX_table_name_index"/>
            <not>
                <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
            </not>
        </and>
    </preConditions>
    <dropIndex tableName="table_name" indexName="IX_table_name_index" />
    <rollback/>
</changeSet>

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.">
        <not>
            <indexExists tableName="table_name" indexName="IX_table_name_index"
                         columnNames="column_one, column_two, column_three, column_four"/>
        </not>
    </preConditions>
    <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"/>
    </createIndex>
</changeSet>

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
2

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,
Eduard

3

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.