ERROR : Postgres backed column with a non-deterministic collation

Open
#2,177 2 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
postgresql, rails, ruby
Domain
backend, databases, search

Research direction

Start with Administrate::Search and its query_template method, using the reported PostgreSQL nondeterministic-collation search case as the reproduction. Compare the current LIKE query with the provided local override and investigate whether the behavior can remain agnostic across collations. Done means dashboard searches no longer raise the reported PostgreSQL error while retaining supported collation behavior.

Written by the indexing model from the issue text.

Description

bug search

  • 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?

Dominant language
JavaScript
Stars
6k
Forks
1.1k
No merged PRs in 30d

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from thoughtbot/administrate

All issues in thoughtbot/administrate

Similar issues

More JavaScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.