Set default limit for pg_trgm

Question

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.

Broadleaved

Answer 1

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.

Klaraklarika

Why is this marked as the answer? It is merely a hint. – Rauwolfia

Answer 2

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)

Meatus

Since Postgres 9.6, even. See: https://mcmap.net/q/28750/-finding-similar-strings-with-postgresql-quickly – Moleskin

Answer 3

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

Stainless