Postgres gives error while setting parameter in db level

Question

I dump a database from one postgres db using pg_dump and restore to another postgres database (In RDS) using pg_restore. But at that time I forget to using --no-owner. After restoring, I change owner to postgres user. I am using pgvector extension and when I try to set config parameter hnsw.ef_search on database level it gives me error permission denied to set parameter 'hnsw.ef_search'.

Command to set parameter on database level

ALTER DATABASE mydb SET hnsw.ef_search = 500;

Please help me with this issue. Thanks in advance.

PS : when I create new db and try to run same query, it will execute without any error.

Answer 1

You get that error because the shared library for the extension vector (which is vector.so) is not loaded in the current session. Until the library is loaded, hnsw.ef_search is not defined and treated as a “placeholder parameter” by PostgreSQL. Only a superuser can use ALTER DATABASE to set a value for a placeholder parameter.

hnsw.ef_search gets redefined as a regular parameter as soon as vector.so is loaded in the current session. So you should call a function from the extension, SELECT from a table that uses the vector data type or do something else that causes vector.so to be loaded into the backend process of the current session. Then run your ALTER DATABASE statement in that same session, and it should succeed.