SQLAlchemy+Alembic创建带权重全文检索索引时出现语法错误

问题描述 #

升级至SQLAlchemy 2.0和PostgreSQL 15后，使用Alembic生成修订版本为Objects表的多列创建带权重的全文检索GIN索引时，执行alembic upgrade head始终触发语法错误。

使用的代码 #

Alembic修订代码 #

op.create_index('idx_obj_eng_ts_vector', 'Objects', [sa.text('(setweight(to_tsvector("english", col_name_1), \'A\') || setweight(to_tsvector("english", col_name_2), \'B\')) || setweight(to_tsvector("english", col_name_3), \'C\')')], unique=False, postgresql_using='gin')

模型中的tsvector生成函数 #

def create_tsvector(
    field_with_weights: list[tuple[str, str]], ts_config: str = "english"
):
    field, weight = field_with_weights[0]
    exp = func.setweight(
        func.to_tsvector(literal_column(f'"{ts_config}"'), field), weight
    )

    for field, weight in field_with_weights[1:]:
        exp = op(
            exp,
            "||",
            func.setweight(
                func.to_tsvector(literal_column(f'"{ts_config}"'), field), weight
            ),
        )

    return exp

模型类定义 #

class Objects(Base):
    __tablename__ = "Objects"
    
    col_name_1 = Column(String)
    col_name_2 = Column(String)
    col_name_3 = Column(String)

    ...

    __object_eng_ts_vector__ = create_tsvector(
        field_with_weights=[(col_name_1, "A"), (col_name_2, "B"), (col_name_3, "C")],
        ts_config="english",
    )

    __table_args__ = (
        Index("idx_object_eng_ts_vector", __object_eng_ts_vector__, postgresql_using="gin"),
    )

错误信息 #

psycopg2.errors.SyntaxError: syntax error at or near "||"
Sep 26 02:11:28 AM  LINE 1: ...| setweight(to_tsvector("english", col_name_2), 'B')) || setweig...
Sep 26 02:11:28 AM                                                               ^

The above exception was the direct cause of the following exception:
...
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "||"
Sep 26 02:11:28 AM  LINE 1: ...| setweight(to_tsvector("english", col_name_2), 'B')) || setweig...
Sep 26 02:11:28 AM                                                               ^

[SQL: CREATE INDEX idx_object_eng_ts_vector ON "Objects" USING gin ((setweight(to_tsvector("english", col_name_1), 'A') || setweight(to_tsvector("english", col_name_2), 'B')) || setweight(to_tsvector("english", col_name_3), 'C'))]
(Background on this error at: https://sqlalche.me/e/20/f405)

解决方案 #

1. 修复模型中op函数的导入问题 #

模型文件中create_tsvector函数里的op是SQLAlchemy用于构造自定义操作符的工具，需确保正确导入：

from sqlalchemy.sql.expression import op

若未正确导入，会导致生成的SQL表达式语法错误。

2. 重新生成Alembic修订 #

删除现有错误的修订文件，重新执行自动生成命令：

alembic revision --autogenerate -m "add full text search index for Objects table"

检查新生成的修订代码，确保索引表达式结构正确。

3. 手动修正Alembic代码（可选） #

若自动生成仍有问题，可手动用SQLAlchemy表达式构造索引，避免字符串转义错误：

from sqlalchemy import func, text

# 构造带权重的tsvector表达式
ts_vector_expr = func.setweight(
    func.to_tsvector(text('"english"'), 'col_name_1'), 'A'
).op('||')(
    func.setweight(func.to_tsvector(text('"english"'), 'col_name_2'), 'B')
).op('||')(
    func.setweight(func.to_tsvector(text('"english"'), 'col_name_3'), 'C')
)

# 创建GIN索引
op.create_index(
    'idx_obj_eng_ts_vector',
    'Objects',
    [ts_vector_expr],
    unique=False,
    postgresql_using='gin'
)

4. 验证SQL合法性 #

可将生成的SQL直接在PostgreSQL客户端执行，确认语法是否正确：

CREATE INDEX idx_obj_eng_ts_vector ON "Objects" USING gin (
    (setweight(to_tsvector("english", col_name_1), 'A') || setweight(to_tsvector("english", col_name_2), 'B')) || setweight(to_tsvector("english", col_name_3), 'C')
);

若客户端执行正常，说明问题出在SQLAlchemy/Alembic的表达式生成环节，需检查代码中的表达式构造逻辑。

内容的提问来源于stack exchange，提问作者LLYX