ERROR : Postgres backed column with a non-deterministic collation

[mjelks]

• What were you trying to do? Perform a search on a dashboard with a Postgres backed column with a non-deterministic collation

• What did you end up with (logs, or, even better, example apps are great!)?

PG::FeatureNotSupported: ERROR:  nondeterministic collations are not supported for LIKE

• What versions are you running?
  • Rails (6.1.4)
  • administrate (0.17.0)

We have currently worked around the issue locally by doing a class_eval on Administrate::Search and re-defining the query_template

Administrate::Search.class_eval do
  # we need to override the LIKE query to explicitly set the COLLATION so Postgres doesn't complain
  def query_template
    search_attributes.map do |attr|
      table_name = query_table_name(attr)
      searchable_fields(attr).map do |field|
        column_name = column_to_query(field)
        "LOWER(CAST(#{table_name}.#{column_name} AS CHAR(256))) LIKE ? COLLATE \"default\""
      end.join(" OR ")
    end.join(" OR ")
  end
end

Not sure if there's a more agnostic approach that would allow for collation support?

[pablobm]

I agree that the current solution is not ideal. For the moment, the recommended workaround is to override the index action of your controller so that you can implement your custom search solution.

However this is also a bit inconvenient now as the search code is not factored out into its own method in the latest release of Administrate. It will be in the next release, as https://github.com/thoughtbot/administrate/pull/2096 will be included. That PR may give an idea of how to do this for the time being.

Does that help?

[pablobm]

I'm thinking that a possible way to go about this would be to provide a way to customize the clause used to search a specific field. So for example something like:

description: Field::Text.with_options(search_with: %[%{table_name}.%{column_name} LIKE ? COLLATE "en_EN"])

So when a field like this comes along, Administrate can be told to use a different collation, or use a different operator (instead of LIKE).

Or perhaps this is a bit too much, and should be de-scoped and addressed with custom code, as described in my previous comment.