sqlalchemy / sqlalchemy/alembic

Autogenerate cannot detect partial indexes (with postgresql_where) on postgres

Open
#750 5 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

autogenerate - detection postgresql use case
Dominant language
Python
Stars
4.4k
Forks
375
PR merge metrics
No merged PRs in 30d

Description

alembic==1.4.3
SQLAlchemy==1.3.20

I have a feeling that I had alembic autogenerate working for following models:


class UTCNow(expression.FunctionElement):
    type = DateTime()


@compiles(UTCNow, 'postgresql')
def pg_utcnow(element, compiler, **kw):
    return "TIMEZONE('utc', CURRENT_TIMESTAMP)"


@compiles(CreateColumn, 'postgresql')
def use_identity(element, compiler, **kw):
    return compiler.visit_create_column(element, **kw).replace('SERIAL', 'INT GENERATED BY DEFAULT AS IDENTITY')


class CharacterStatus(enum.IntEnum):
    DEAD = 0
    ALIVE = 1


class BaseDBModel:
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, server_default=UTCNow())
    updated_at = Column(DateTime, onupdate=UTCNow())


class Characters(BaseDBModel, Base):
    __tablename__ = 'characters'

    deleted_at = Column(DateTime)

    name = Column(String)
    birthday = Column(Date)
    img = Column(String, nullable=True)
    status = Column(Enum(CharacterStatus), default=CharacterStatus.ALIVE)
    nickname = Column(String)
    occupation = Column(ARRAY(String))
    appearance = Column(ARRAY(String))
    portrayed = Column(String)
    category = Column(ARRAY(String))

    __table_args__ = (
        Index('uq_characters_name', text('lower(name)'), unique=True, postgresql_where=(deleted_at.is_(None))),
        Index('uq_characters_nickname', text('lower(nickname)'), unique=True, postgresql_where=(deleted_at.is_(None))),
    )

but for some reason I don't get Indexes in migration file:
alembic revision --autogenerate -m "initial"

"""initial

Revision ID: f4eb64f96910
Revises: 
Create Date: 2020-10-27 22:01:11.634916+00:00

"""
from alembic import op
import sqlalchemy as sa


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


def upgrade():
    schema_upgrades()
    data_upgrades()


def downgrade():
    data_downgrades()
    schema_downgrades()


def schema_upgrades():
    """schema upgrade migrations go here."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('characters',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('deleted_at', sa.DateTime(), nullable=True),
    sa.Column('name', sa.String(), nullable=True),
    sa.Column('birthday', sa.Date(), nullable=True),
    sa.Column('img', sa.String(), nullable=True),
    sa.Column('status', sa.Enum('DEAD', 'ALIVE', name='characterstatus'), nullable=True),
    sa.Column('nickname', sa.String(), nullable=True),
    sa.Column('occupation', sa.ARRAY(sa.String()), nullable=True),
    sa.Column('appearance', sa.ARRAY(sa.String()), nullable=True),
    sa.Column('portrayed', sa.String(), nullable=True),
    sa.Column('category', sa.ARRAY(sa.String()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('locations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('created_at', sa.DateTime(), server_default=sa.text("TIMEZONE('utc', CURRENT_TIMESTAMP)"), nullable=True),
    sa.Column('updated_at', sa.DateTime(), nullable=True),
    sa.Column('character_id', sa.Integer(), nullable=False),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.Column('coordinates', geoalchemy2.types.Geography(geometry_type='POINT', srid=4326, from_text='ST_GeogFromText', name='geography'), nullable=True),
    sa.ForeignKeyConstraint(['character_id'], ['characters.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    # ### end Alembic commands ###


def schema_downgrades():
    """schema downgrade migrations go here."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('locations')
    op.drop_table('characters')
    # ### end Alembic commands ###


def data_upgrades():
    """Add any optional data upgrade migrations here!"""
    pass


def data_downgrades():
    """Add any optional data downgrade migrations here!"""
    pass


Is there a way to pick them automagically? Thanks

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the supplied models with alembic revision --autogenerate -m "initial" and inspect the generated migration file. Trace autogenerate's PostgreSQL index comparison and reflection path; done means the partial indexes using postgresql_where appear correctly in the migration.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python, sqlalchemy
Domain
databases, tooling
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.