After installed Prefect Orion 2.4, getting the fol...
# ask-community
x
After installed Prefect Orion 2.4, getting the following error while starting Prefect Orion. Please help me to fix this issue. sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.UndefinedObjectError'>: operator class "gin_trgm_ops" does not exist for access method "gin" [SQL: CREATE INDEX CONCURRENTLY trgm_ix_flow_name ON flow USING gin (name gin_trgm_ops); ]
2
r
Hi Xavier, when using Orion with a Postgres database, you'll need to have the
pg_tgrm
extension installed in one of the schemas visible to the database user that you put in the connection string.
x
yes, it is already installed. CREATE EXTENSION pg_trgm with schema prefect_orion_dev;
r
Thanks, that is helpful. Is that schema visible to the user account Orion connects with? I got this error once when the schema I added
pg_tgrm
to was not in the user account's search path in Postgres.
x
postgresql+asyncpg://generic_as_tools_user_dev:***@astools.db.cnvr.net:5490/as_tools"
the default schema for this user account is prefect_orion_dev
user id is generic_as_tools_user_dev
how can I make sure pg_tgrm is in the user account search path
can I change the database URL to specify the same?
r
If you connect to Postgres as
generic_as_tools_user_dev
and run
show search_path;
it should tell you
If the schema with
pg_trgm
is missing from the search path, you'd probably want to use the
alter role
example from that link to permanently add the schema to the user's search path
x
I ran this command when I as settting it up for a couple of months ago and it worked fine until I upgraded today. ALTER USER generic_as_tools_user_dev SET search_path to 'prefect_orion_dev'
I even executed just now the following command, still I get the error while starting prefect orion ALTER role generic_as_tools_user_dev SET search_path to 'prefect_orion_dev'
r
Ah, got it. So upgrading Prefect was the only change you made before this started? What version of Postgres are you running?
x
My DBA took care of the pg_trgm issue. We are good. Sorry for the late response.