From bf71ce65307dbfd8b9b58c2dc4e844bb423dce21 Mon Sep 17 00:00:00 2001 From: Federico Caselli Date: Mon, 17 Jul 2023 22:45:00 +0200 Subject: [PATCH] Improve index autogenerare detection on PostgreSQL Improved autogenerate compare of expression based indexes on PostgreSQL to produce fewer wrong detections. Fixes: #1270 Change-Id: I468e27c6311c5449adfb92d0da07180b1c11cdd6 --- alembic/ddl/postgresql.py | 3 +-- docs/build/changelog.rst | 2 +- docs/build/unreleased/1270.rst | 6 ++++++ tests/test_autogen_indexes.py | 5 +++++ 4 files changed, 13 insertions(+), 3 deletions(-) create mode 100644 docs/build/unreleased/1270.rst diff --git a/alembic/ddl/postgresql.py b/alembic/ddl/postgresql.py index f8ae9704..af0d156e 100644 --- a/alembic/ddl/postgresql.py +++ b/alembic/ddl/postgresql.py @@ -256,8 +256,7 @@ class PostgresqlImpl(DefaultImpl): self, index: Index, expr: str, remove_suffix: str ) -> str: # start = expr - expr = expr.lower() - expr = expr.replace('"', "") + expr = expr.lower().replace('"', "").replace("'", "") if index.table is not None: # should not be needed, since include_table=False is in compile expr = expr.replace(f"{index.table.name.lower()}.", "") diff --git a/docs/build/changelog.rst b/docs/build/changelog.rst index 3971c977..9b114e85 100644 --- a/docs/build/changelog.rst +++ b/docs/build/changelog.rst @@ -215,7 +215,7 @@ Changelog Added support for autogenerate comparison of indexes on PostgreSQL which include SQL sort option, such as ``ASC`` or ``NULLS FIRST``. The sort options are correctly detected only when defined using the - sqlalchemy modifier functions, such as ``asc()`` or ``nulls_first``, + sqlalchemy modifier functions, such as ``asc()`` or ``nulls_first()``, or the equivalent methods. Passing sort options inside the ``postgresql_ops`` dict is not supported. diff --git a/docs/build/unreleased/1270.rst b/docs/build/unreleased/1270.rst new file mode 100644 index 00000000..9133323d --- /dev/null +++ b/docs/build/unreleased/1270.rst @@ -0,0 +1,6 @@ +.. change:: + :tags: but, autogenerate, postgresql + :tickets: 1248 + + Improved autogenerate compare of expression based indexes on PostgreSQL + to produce fewer wrong detections. diff --git a/tests/test_autogen_indexes.py b/tests/test_autogen_indexes.py index 9ec33d0f..01a19d59 100644 --- a/tests/test_autogen_indexes.py +++ b/tests/test_autogen_indexes.py @@ -13,6 +13,7 @@ from sqlalchemy import Numeric from sqlalchemy import PrimaryKeyConstraint from sqlalchemy import String from sqlalchemy import Table +from sqlalchemy import text from sqlalchemy import UniqueConstraint from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION from sqlalchemy.sql.expression import asc @@ -1402,6 +1403,10 @@ def _lots_of_indexes(flatten: bool = False): lambda t: Index("SomeIndex", t.c.ff + 42), lambda t: Index("SomeIndex", 42 + t.c.ff), ), + ( + lambda t: Index("SomeIndex", text("coalesce(z, -1)"), _table=t), + lambda t: Index("SomeIndex", text("coalesce(q, '-1')"), _table=t), + ), ] with_sort = [ -- 2.47.2