Set default limit for pg_trgm

2020-07-09 08:31发布

This seems like a really basic question, but how do I change the default limit for the pg_trgm extension? Which is currently 0.3. I have done:

select set_limit(0.5)
select show_limit() => 0.5

Close the connection, reconnect:

select show_limit() => 0.3

Thanks for your help.

3条回答
forever°为你锁心
2楼-- · 2020-07-09 09:02

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)

查看更多
Rolldiameter
3楼-- · 2020-07-09 09:17

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

查看更多
ら.Afraid
4楼-- · 2020-07-09 09:21

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.

查看更多
登录 后发表回答