Problems dropping an index in postgres using liquibase

Question

I'm hitting an issue in liquibase where I can't drop an index in my postgres db. The error reported by liquibase is

Unexpected error running Liquibase: ERROR: index "value_idx" does not exist [Failed SQL: DROP INDEX VALUE_IDX]

I have connected to the database using psql and verified that the index does indeed exist (if the changeset is run without the drop index stanza)

\d data
          Table "someschema.data"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 value  | character varying(36) | not null
Indexes:
    "value_idx" UNIQUE, btree (value)

When running Liquibase updateSQL the DROP INDEX statement it generates is:

DROP INDEX VALUE_IDX;

My changelog is as follows:

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <preConditions>
        <dbms type="postgresql"/>
    </preConditions>

    <changeSet author="beresfordt" id="1">

        <sql>
            CREATE SCHEMA SomeSchema;
        </sql>

        <createTable tableName="data" schemaName="someschema">
            <column name="value" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="VALUE_IDX" schemaName="someschema"
                     tableName="data" unique="true">
            <column name="value" type="varchar(36)"/>
        </createIndex>

        <dropIndex catalogName="someschema"
                schemaName="someschema"
                tableName="data"
                indexName="VALUE_IDX"/>
    </changeSet>
</databaseChangeLog>

I have also tried the following dropindex stanza:

        <dropIndex catalogName="someschema"
                schemaName="someschema"
                tableName="data"
                indexName="someschema.VALUE_IDX"/>

But I get a similar error:

Unexpected error running Liquibase: ERROR: index "someschema.VALUE_IDX" does not exist [Failed SQL: DROP INDEX "someschema.VALUE_IDX"]

I am using Liquibase: 3.4.2 and Postgres: 9.5.1

Edit:

Tried on 3.3.0 and it works.

Bug raised in liquibase's jira: https://liquibase.jira.com/browse/CORE-2677

As the table is stored in the schema someschema the index needs to be qualified with the schema as well (unless someschema is in the search_path for the user). So it needs to be drop index someschema.value_idx; if I'm not mistaken - that seems like a bug in Liquibase — a_horse_with_no_name
I tried explicitly putting the schema in the indexName attribute of the dropIndex stanza, but it didn't like that either. Will update the question — beresfordt
You already specified the schema in the schemaName attribute. If you put the schema name into the index name, Liquibase assumes the index is named "someschema.VALUE_IDX" which is something different then "someschema"."value_idx". As I said: I do think this is a Liquibase bug. Short of use a custom <sql> tag, I don't think there is much you can do (and of course report the bug) — a_horse_with_no_name
So this bug appears to have been introduced in 3.4.1; have raised liquibase.jira.com/browse/CORE-2677 — beresfordt

beresfordt beresfordt · Accepted Answer · 2016-02-24T16:02:33

This is caused by a bug which exists in, at time of writing 3.4.1 and 3.4.2 of Liquibase.

This has been raised on Liquibase jira: https://liquibase.jira.com/browse/CORE-2677

edit:

This seems to have been resolved in the current head: '03b020ab895eb02c0e0aba90461cb7c628fa033f'

edit 2:

Update from Liquibase:

The fix should be out with 3.5

Problems dropping an index in postgres using liquibase

1 Answers