Indices are being dropped and recreated with every migration

[al-dpopowich]

I'm not sure when this started happening...an upgrade to 2.x sqla? Or alembic upgrade? But I have indices defined from years ago that are now being dropped and recreated with every migration. I think it may have to do with a function in the index?

sqlalchemy - 2.0.9
alembic - 1.11.1
PostgreSQL - 14.2

I've attached a simplification of my models which demonstrate the issue. Table Questionnaire has two foreign references, one to Company and one to Project. company_id is required. If project_id is NULL then it is a "Company Questionnaire". If set, it is a "Project Questionnaire". The multi-column foreign key constraint is our method of managing multi-tenancy - this ensures that a project-company tuple on the Questionnaire is for a project belonging to the same company.

The unique index in question, on table Questionnaire, ensures that no two company questionnaires or project questionnaires have the same title.

With no changes to the model, I keep getting this migration:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('questionnaire_uniq_title', table_name='questionnaire')
    op.create_index('questionnaire_uniq_title', 'questionnaire', ['company_id', sa.text('coalesce(project_id, -1)'), sa.text('(lower(title))')], unique=True)
    # ### end Alembic commands ###

My model module:

from sqlalchemy import (
   Column,
   ForeignKey,
   ForeignKeyConstraint,
   func,
   Index,
   Integer,
   String,
   Table,
   text,
   UniqueConstraint,
)
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()

Company = Table(
   'company',
   Base.metadata,
   Column('id', Integer, primary_key=True),
   Column('name', String),
)

Project = Table(
   'project',
   Base.metadata,
   Column('id', Integer, primary_key=True),
   Column('name', String),
   Column('company_id', Integer, ForeignKey('company.id', ondelete='CASCADE'),
          nullable=False),

   UniqueConstraint('id', 'company_id', name='project_uniq_id_cid'),
)

Qustionnaire = Table(
   'questionnaire',
   Base.metadata,
   Column('id', Integer, primary_key=True),
   Column('title', String),
   Column('company_id', Integer, ForeignKey('company.id', ondelete='CASCADE'),
          nullable=False),
   Column('project_id', Integer, ForeignKey('project.id', ondelete='CASCADE')),


   # (company_id, project_id) are denormalized...ensure the tuple
   # matches an existing (id, company_id) from the project table (as
   # opposed to individual foreign key references to each which would
   # allow a project_id whose project record does not having matching
   # company_id)
   ForeignKeyConstraint(('project_id', 'company_id'),
                        ('project.id', 'project.company_id'),
                        name='questionnaire_pid_cid_fk',
                        ondelete='CASCADE'),

   # ensure for any given Company Questionnaire or Project
   # Questionnaire have unique titles across all Company or Project
   Index('questionnaire_uniq_title',
         'company_id', text('coalesce(project_id, -1)'), text('lower(title)'),
         unique=True),

)

[al-dpopowich]

FYI...just upgraded to sqla 2.0.18 and same results.

[al-dpopowich]

$ alembic --version
alembic 1.11.3
$ alembic revision --auto -m foo
2023-08-16 22:02:52,700|INFO|alembic.runtime.migration|Context impl PostgresqlImpl.
2023-08-16 22:02:52,701|INFO|alembic.runtime.migration|Will assume transactional DDL.
/opt/app/src/alembic/env.py:52: SAWarning: Did not recognize type 'tyrange' of column 'years'
  context.run_migrations()
2023-08-16 22:02:53,817|INFO|alembic.ddl.postgresql|Detected sequence named 'project_id_seq' as owned by integer column 'project(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,902|INFO|alembic.ddl.postgresql|Detected sequence named 'questionnaire_id_seq' as owned by integer column 'questionnaire(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,908|INFO|alembic.ddl.postgresql|Detected sequence named 'supplies_summary_id_seq' as owned by integer column 'supplies_summary(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,926|INFO|alembic.ddl.postgresql|Detected sequence named 'qtemplate_id_seq' as owned by integer column 'qtemplate(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,932|INFO|alembic.ddl.postgresql|Detected sequence named 'supplies_id_seq' as owned by integer column 'supplies(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,937|INFO|alembic.ddl.postgresql|Detected sequence named 'question_id_seq' as owned by integer column 'question(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,963|INFO|alembic.ddl.postgresql|Detected sequence named 'supplies_assignments_id_seq' as owned by integer column 'supplies_assignments(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,984|INFO|alembic.ddl.postgresql|Detected sequence named 'q_assignments_id_seq' as owned by integer column 'q_assignments(id)', assuming SERIAL and omitting
2023-08-16 22:02:53,993|INFO|alembic.autogenerate.compare|Couldn't determine database type for column 'contractor.years'
2023-08-16 22:02:54,005|INFO|alembic.autogenerate.compare|Couldn't determine database type for column 'project.years'
2023-08-16 22:02:54,009|INFO|alembic.autogenerate.compare|Detected changed index 'q_assignments_one_incomplete_per_q' on 'q_assignments': expression ('INDEX', 'questionnaire_id', 'coalesce(completed, 1899-12-31 00:00:00+00)') to ('INDEX', 'questionnaire_id', 'coalesce(completed, 1899-12-31)')
2023-08-16 22:02:54,016|INFO|alembic.autogenerate.compare|Couldn't determine database type for column 'questionnaire.years'
2023-08-16 22:02:54,018|INFO|alembic.autogenerate.compare|Couldn't determine database type for column 'study.years'
2023-08-16 22:02:54,022|INFO|alembic.autogenerate.compare|Detected changed index 'supplies_assignments_one_incomplete_per_study' on 'supplies_assignments': expression ('INDEX', 'study_id', 'coalesce(completed, 1899-12-31 00:00:00+00)') to ('INDEX', 'study_id', 'coalesce(completed, 1899-12-31)')
  Generating /opt/app/src/alembic/versions/c8696b2a917a_foo.py ...  done
$ cat /opt/app/src/alembic/versions/c8696b2a917a_foo.py
"""foo

Revision ID: c8696b2a917a
Revises: b3a8c4842f57
Create Date: 2023-08-16 22:02:54.052439

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'c8696b2a917a'
down_revision = 'b3a8c4842f57'
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('q_assignments_one_incomplete_per_q', table_name='q_assignments')
    op.create_index('q_assignments_one_incomplete_per_q', 'q_assignments', ['questionnaire_id', sa.text("coalesce(completed, '1899-12-31')")], unique=True)
    op.drop_index('supplies_assignments_one_incomplete_per_study', table_name='supplies_assignments')
    op.create_index('supplies_assignments_one_incomplete_per_study', 'supplies_assignments', ['study_id', sa.text("coalesce(completed, '1899-12-31')")], unique=True)
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index('supplies_assignments_one_incomplete_per_study', table_name='supplies_assignments')
    op.create_index('supplies_assignments_one_incomplete_per_study', 'supplies_assignments', ['study_id', sa.text("COALESCE(completed, '1899-12-31 00:00:00+00'::timestamp with time zone)")], unique=False)
    op.drop_index('q_assignments_one_incomplete_per_q', table_name='q_assignments')
    op.create_index('q_assignments_one_incomplete_per_q', 'q_assignments', ['questionnaire_id', sa.text("COALESCE(completed, '1899-12-31 00:00:00+00'::timestamp with time zone)")], unique=False)
    # ### end Alembic commands ###
$ 

[CaselIT]

That expression gets reflected as

Detected changed index 'q_assignments_one_incomplete_per_q' on 'q_assignments': expression ('INDEX', 'questionnaire_id', 'coalesce(completed, 1899-12-31 00:00:00+00)') to ('INDEX', 'questionnaire_id', 'coalesce(completed, 1899-12-31)')

Other that hard coding the removal of the 00:00:00+00 I'm not sure how to fix.

@zzzeek do you have suggestions? I'm thinking if we have a way of making the db do the comparison for us, but I can't think of a way that works effectively

[zzzeek]

I can't see the model here but why not ensure the model definition uses the full datetime syntax including the "00:00:00+00" string ? that way it matches

[CaselIT]

Yes, that's probably the best solution.
@al-dpopowich try changing the text to have also the time part of the date, formatted as pg does.
That should solve the issue. Sorry but there isn't really a better way of matching things in this case

[al-dpopowich]

Thanks! We're definitely getting closer!

My columns / index are now defined as:

    Column('study_id', Integer, ForeignKey('study.id', ondelete='CASCADE'),
           nullable=False),
    Column('completed', DateTime(timezone=True)),
    Index('supplies_assignments_one_incomplete_per_study',
          'study_id', text("coalesce(completed, '1899-12-31 00:00:00+00')"),
          unique=True),

The point of the index: in my data model, I want to ensure I have only one column per study_id where completed is NULL. I use the coalesce to give NULL columns a value, since NULLs are effectively ignored in unique indices.

With this, --auto perpetually generates:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

Something seems to be triggering the need for a migration, albeit empty.

Now, this, I can probably live with as I don't attempt to generate a migration in any workflow pipeline automatically, it's always a manual process. That is, I only run alembic when I know I need to. It just means I won't be able to run alembic to see if I need a migration without doing some minor clean up of removing the no-op migration.

Whether you want to explore this more now, I leave in your good hands.

Thank you!

Daniel

[zzzeek]

the empty migration is normal, as autogenerate is not intended to catch everything that's changed; that's why the file has a comment "commands auto generated by Alembic - please adjust!" , since you may have constructs such as EXCLUDE constraints that arent detected.

To disable the generation of an empty migration file, use the recipe https://alembic.sqlalchemy.org/en/latest/cookbook.html#don-t-generate-empty-migrations-with-autogenerate

[al-dpopowich]

Wasn't aware of that for some reason.

Then we're good to go!

Thanks, again!

[CaselIT]

I use the coalesce to give NULL columns a value, since NULLs are effectively ignored in unique indices.

If you use pg15+ they added support for nulls not distinct for indexes and unique constraints and there is support in sqlalchemy and alembic in the latest versions https://www.postgresql.org/docs/current/indexes-unique.html