This gem breaks postgres full text search functionality

[mithucste30]

After hours of debugging and no finding no clue why search is not working in my local env, finally when i commneted out this gem out of my dev gem group my postgres full text search queries started to working, before that i was having this issue

Started GET "/api/search?auth=P2efdRFjNK1nJteqvf0h8fo0d4GyPWw1&keyword=finans" for 172.31.0.1 at 2020-06-16 15:20:47 +0000
Cannot render console from 172.31.0.1! Allowed networks: 127.0.0.1, ::1, 127.0.0.0/127.255.255.255
Processing by Api::SearchController#content as */*
  Parameters: {"auth"=>"xxxxxxxx", "keyword"=>"finans"}
  User Load (0.3ms)  SELECT
    "users" . *
  FROM
    "users"
  WHERE
    "users" . "session_token" = $1
  ORDER BY
    "users" . "id" ASC LIMIT 1  [["session_token", "P2efdRFjNK1nJteqvf0h8fo0d4GyPWw1"]]
Completed 500 Internal Server Error in 39ms (ActiveRecord: 0.3ms)

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near ":"
LINE 9:               "audio_clips" . "title" : : text
                                              ^
: SELECT COUNT(*) FROM "audio_clips" INNER JOIN (SELECT
    "audio_clips" . "id" AS pg_search_id
    ,(
      ts_rank (
        (
          to_tsvector (
            'simple'
            ,COALESCE (
              "audio_clips" . "title" : : text
              ,''
            )
          ) || to_tsvector (
            'simple'
            ,COALESCE (
              pg_search_b229fd995799cea4ca76c4.pg_search_93f916f001b6b057ee2922 : : text
              ,''
            )
          )
        )
        ,(
          to_tsquery (
            'simple'
            ,''' ' || 'finans' || ' '''
          )
        )
        ,0
      )
    ) AS rank
  FROM
    "audio_clips" LEFT OUTER JOIN (
      SELECT
          "audio_clips" . "id" AS id
          ,string_agg (
            "tags" . "name" : : text
            ,' '
          ) AS pg_search_93f916f001b6b057ee2922
        FROM
          "audio_clips" INNER JOIN "taggings"
            ON "taggings" . "audio_clip_id" = "audio_clips" . "id" INNER JOIN "tags"
            ON "tags" . "id" = "taggings" . "tag_id"
        GROUP BY
          "audio_clips" . "id"
    ) pg_search_b229fd995799cea4ca76c4
      ON pg_search_b229fd995799cea4ca76c4.id = "audio_clips" . "id"
  WHERE
    (
      (
        to_tsvector (
          'simple'
          ,COALESCE (
            "audio_clips" . "title" : : text
            ,''
          )
        ) || to_tsvector (
          'simple'
          ,COALESCE (
            pg_search_b229fd995799cea4ca76c4.pg_search_93f916f001b6b057ee2922 : : text
            ,''
          )
        )
      ) @@ (
        to_tsquery (
          'simple'
          ,''' ' || 'finans' || ' '''
        )
      )
    )) AS pg_search_627d4ee04ccb6d014d9a06 ON "audio_clips"."id" = pg_search_627d4ee04ccb6d014d9a06.pg_search_id WHERE ("audio_clips"."published" = $1 AND "audio_clips"."published" = $2 AND (audio_clips.publish_time < '2020-06-16 15:20:47.938365') AND "audio_clips"."published" = $3 AND ('2020-06-16 15:20:47.938575' < audio_clips.expires_at) AND "audio_clips"."category_id" IS NULL OR "audio_clips"."published" = $4 AND "audio_clips"."published" = $5 AND (audio_clips.publish_time < '2020-06-16 15:20:47.938365') AND "audio_clips"."published" = $6 AND ('2020-06-16 15:20:47.938575' < audio_clips.expires_at) AND ("audio_clips"."category_id" NOT IN (SELECT "category_unsubscriptions"."category_id" FROM "category_unsubscriptions" WHERE "category_unsubscriptions"."user_id" = $7)))):


  Rendered /usr/local/bundle/gems/actionpack-4.2.11.1/lib/action_dispatch/middleware/templates/rescues/_source.erb (3.7ms)
  Rendered /usr/local/bundle/gems/actionpack-4.2.11.1/lib/action_dispatch/middleware/templates/rescues/_trace.html.erb (3.5ms)
  Rendered /usr/local/bundle/gems/actionpack-4.2.11.1/lib/action_dispatch/middleware/templates/rescues/_request_and_response.html.erb (0.6ms)
  Rendered /usr/local/bundle/gems/actionpack-4.2.11.1/lib/action_dispatch/middleware/templates/rescues/diagnostics.html.erb within rescues/layout (14.9ms)

[jonathan-at-leavelogic]

Running into the same issue as @mithucste30 (nearly three years later!). I spent far too much troubleshooting why Postgres full-text search was broken. Removing the pp_sql gem resolved the problem.

The gem is rewriting the Postgres && operator as & &, which of course does not exist.