function tsq_parse does not exist

[nikordaris]

python 3.6.3
PostgreSQL 10.1
psycopg2 2.7.4
SQLAlchemy 1.2.5
SQLAlchemy-Searchable 1.0.3

alembic

def upgrade():
    conn = op.get_bind()
    op.add_column('foo', sa.Column('search_vector', sqlalchemy_utils.types.ts_vector.TSVectorType(), nullable=True))
    op.create_index('ix_foo_search_vector', 'foo', ['search_vector'], unique=False, postgresql_using='gin')
    sync_trigger(conn, 'foo', 'search_vector', ['name', 'description'], Base.metadata)

models

Base = declarative_base()
make_searchable(Base.metadata)

class Foo(Base):
    __tablename__ = 'foo'
    name = Column(String, index=True)
    description = Column(String, index=True)
    search_vector = Column(TSVectorType('name', 'description'))

search

search_query = 'foobar'
query = search(session.query(Foo), search_query, sort=True)

Error

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) function tsq_parse(unknown, unknown) does not exist

[nikordaris]

The sync_trigger seems to work it is just the search(). I ended up just manually searching on the vector field.

query.filter(my_model.search_vector.op('@@')(func.to_tsquery(search)))

[nikordaris]

it appears if tsq_parse just doesn't work. i don't know if its a v10 thing but the search() works if i replace tsq_parse with to_tsquery

[kvesteri]

tsq_parse along with other SQL expressions are created before tables are created, see https://github.com/kvesteri/sqlalchemy-searchable/blob/master/sqlalchemy_searchable/__init__.py#L549

You need to create all the functions within expressions.sql in your alembic migration.

[riccardocagnasso]

@kvesteri could you please elaborate on that and provide examples? I encountered this bug when upgraded sqlalchemy-searchable to last version on pre existing database

[kvesteri]

@riccardocagnasso in your alembic migration you need to create all functions found in https://github.com/kvesteri/sqlalchemy-searchable/blob/master/sqlalchemy_searchable/expressions.sql

So just copy everything in that file and run it using op.execute() in your alembic migration

[riccardocagnasso]

@kvesteri oh... OH! ok.

[nikordaris]

Is it recommended to use make_searchable and remove_listeners in my alembic upgrade/downgrade functions or do I need to manually execute the sql? Also, can we update the docs to reflect this requirement?

[kvesteri]

Is it recommended to use make_searchable and remove_listeners in my alembic upgrade/downgrade functions or do I need to manually execute the sql? Also, can we update the docs to reflect this requirement?

Definitely not recommended to use make_searchable in alembic migrations as that is specific to certain version of SA-Searchable. If you have long chain of alembic upgrades some of those might use an older version of SA-Searchable, hence you need to manually execute the SQL.

Yes the docs need to be updated. 👍

[nikordaris]

Cool, thanks. It would be nice to have some functions to run the SQL for us and drop what it creates so we could just call those functions in our upgrade/downgrade sections of alembic and not have to worry about what is going on under the hood. Thanks for the lib though! Makes search a lot easier.

[kvesteri]

The thing that makes that hard is that the SQL that the functions should run is tied to certain version of SQLAlchemy-Searchable. For that reason we simply can't import a function from SA-Searchable and call it in migrations.

[nikordaris]

Ya i see the problem. Its also probably not wise for people to blindly run SQL blobs like that without looking it over.

[rhymes]

I had to run the script manually on each database :(

[wassname]

Could you please give a specific example of what the alembic files should look like? I'm not that used alembic & raw SQL and am struggling with the small details of implementing yours suggestion.

Do we need something like this in script.py.mako?

It did work for me after doing an alembic migration with this template, but I imagine I may be missing things, especially on downgrade.

...
import os
from alembic import op
import sqlalchemy as sa
import sqlalchemy_utils
from sqlalchemy_searchable import sync_trigger
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()
${imports if imports else ""}

...

def run_sqlalchemy_searchable_sql():
    """
    With alembic and sqlalchemy_searchable we run SQL statements before table creation. These statements enable searching
    See:
    - https://conorliv.com/alembic-migration-execute-raw-sql.html
    - https://github.com/kvesteri/sqlalchemy-searchable/issues/67
    """
    sql_expressions = Path('alembic').joinpath('searchable_expressions.sql').open().read()
    bind = op.get_bind()
    session = Session(bind=bind)
    session.execute(sql_expressions)


def upgrade():
    run_sqlalchemy_searchable_sql()

    ${upgrades if upgrades else "pass"}

    # see https://sqlalchemy-searchable.readthedocs.io/en/latest/alembic_migrations.html
    conn = op.get_bind()
    sync_trigger(conn, 'comment', 'search_vector', ['body'])


def downgrade():
    run_sqlalchemy_searchable_sql()

    ${downgrades if downgrades else "pass"}

    # see https://sqlalchemy-searchable.readthedocs.io/en/latest/alembic_migrations.html
    conn = op.get_bind()
    sync_trigger(conn, 'comment', 'search_vector', ['body'])

Thanks for making and sharing the library :)

[command-tab]

Here's an example of a migration I've used to add the search vector:

from alembic import op
import sqlalchemy as sa
from sqlalchemy_utils.types import TSVectorType
from sqlalchemy_searchable import sync_trigger

# Revision identifiers used by Alembic
revision = 'xxxx'
down_revision = 'yyyy'

def upgrade():
    op.add_column('table_name', sa.Column('search_vector', TSVectorType('name')))
    sync_trigger(op.get_bind(), 'table_name', 'search_vector', ['source_column'])

def downgrade():
    op.drop_column('table_name', 'search_vector')

These docs were helpful:
https://sqlalchemy-searchable.readthedocs.io/en/latest/alembic_migrations.html