Set default limit for pg_trgm
Since Postgres 9.6, pg_trgm
makes use of the Grand Unified Configuration (GUC) system, so one can set a default at the cluster level adding pg_trgm.similarity_threshold = 0.5
in postgresql.conf
, or at the DB level (alter database myDB set pg_trgm.similarity_threshold = 0.5
) or at all other levels allowed by GUC (per user, per function etc)
This is probably not a solution, but rather a contribution to a potential solution...
(I am assuming that you want the pg_trgm parameter for all connections to the DB, not just interactive ones?)
It seems that the default 0.3 limit is hard coded in the function:
trgm_op.c:
PG_MODULE_MAGIC;
float4 trgm_limit = 0.3f;
I am not sure if it can be controlled through any configuration files, so one option could be to change the default in the source file, and re-build the extensions.
Stumbled upon this when looking up how to do this in a Ruby on Rails environment. Ended up monkey-patching my adapter:
require 'active_record/connection_adapters/postgresql_adapter'
class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
private
alias_method :default_configure_connection, :configure_connection
# Monkey patch configure_connection because set_limit() must be called on a per-connection basis.
def configure_connection
default_configure_connection
begin
execute("SELECT set_limit(0.1);")
rescue ActiveRecord::StatementInvalid
Rails.logger.warn("pg_trgm extension not enabled yet")
end
end
end
Went this route after seeing other people have this issue, e.g. https://github.com/textacular/textacular/issues/39