0

I'm trying to use the pg_search gem in combination with a multi tenancy application enabled by the apartment gem. Apartment seperates my data with a schema per tenant. Searching withing each tenant works well with the pg_search default settings, but I'm having trouble using it with pg_trgm enabled.

I have enabled the pg_trgm extension on my database by adding a seperate schema called 'shared extensions' and enabling the pg_trgm extension on that. This schema is always included in the search path by configuring apartment:

config.persistent_schemas = %w{ shared_extensions }

But when I try to do a trigram search for a model I get an error.

pg_search_scope :search_by_name, against: :name, using: :trigram

Meeting.search_by_name('blabla').first

PG::UndefinedFunction: ERROR:  operator does not exist: text % unknown

This makes me think pg_trgm has not been enabled correctly, however I can execute the following query on my database just fine:

SELECT name, similarity(name, 'blabla') AS sml
FROM aa.meetings
WHERE name % 'blabla'
ORDER BY sml DESC, name;

Any help would be appreciated! Thanks

wiiiim
  • 63
  • 6

1 Answers1

3

I found out what was wrong. Perhaps it can someday help someone.

While you can have many schemas for a Postgres database, you can only enable extensions on one of the schemas. I already had pg_trgm enabled for a schema that was not included in every search path, therefore installing the extension for the shared_extensions schema failed.

I changed my rake task to this:

namespace :db do

desc 'Create shared_extensions Schema'
  task :extensions => :environment  do
    ActiveRecord::Base.connection.execute 'DROP EXTENSION IF EXISTS "pg_trgm"'
    ActiveRecord::Base.connection.execute 'CREATE SCHEMA IF NOT EXISTS shared_extensions;'
    ActiveRecord::Base.connection.execute 'CREATE EXTENSION "pg_trgm" SCHEMA shared_extensions;'
  end
end

Rake::Task["db:create"].enhance do
  Rake::Task["db:extensions"].invoke
end

Rake::Task["db:test:purge"].enhance do
  Rake::Task["db:extensions"].invoke
end
wiiiim
  • 63
  • 6
  • very helpful :) if anyone stumble upon it, to state something obviou, you need to call `SELECT shared_extensions.similarity(field, value)` – user3605025 Mar 29 '23 at 09:38