sqlalchemy / sqlalchemy/alembic

postgres ExcludeConstraint not generating correctly

Open
#958 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

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

Description

Describe the bug
Using the postgres dialect the ExcludeConstraint drops the ops when creating the migration file

Expected behavior
The constraint should be transferred to the migration file

To Reproduce
Using the following model

class Network(Base):
    __tablename__ = 'network'
    network_id: uuid.UUID = sqlalchemy.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid.uuid4,
                                              server_default=sql.func.gen_random_uuid(), nullable=False)
    networka: str = sqlalchemy.Column("networka", pg.INET)
    __table_args__ = (
        pg.ExcludeConstraint(('networka', '&&'), using="gist", ops={'networka': 'inet_ops'}),
    )

run the command alembic revision --autogenerate the output is generated for that model

    op.create_table('network',
    sa.Column('network_id', postgresql.UUID(as_uuid=True), server_default=sa.text('gen_random_uuid()'), nullable=False),
    sa.Column('networka', postgresql.INET(), nullable=True),
    postgresql.ExcludeConstraint((sa.column('networka'), '&&'), using='gist'),
    sa.PrimaryKeyConstraint('network_id'),
    )

The postgresql.ExcludeConstraint part has dropped the ops, it should have rendered

    postgresql.ExcludeConstraint((sa.column('networka'), '&&'), using='gist', ops={'networka': 'inet_ops'}),

Error
No errors occur after generation, but Postgres will fail to create a table when you attempt to upgrade.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) data type inet has no default operator class for access method "gist"                                   
HINT:  You must specify an operator class for the index or define a default operator class for the data type.     

Versions.

  • OS: Linux
  • Python: 3.9
  • Alembic: 1.7.4
  • SQLAlchemy: 1.4.20
  • Database: Postgres 13

Additional context
Not a show-stopper for me, I just needed to manually add in the code.

Have a nice day!

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 reported model with PostgreSQL and running alembic revision --autogenerate. Inspect the generated postgresql.ExcludeConstraint and the autogeneration path that renders it; done means the migration preserves ops={'networka': 'inet_ops'} and the table upgrade succeeds on PostgreSQL.

Written by the indexing model from the issue text.

Assessment

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.