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.

[jfouse]

I hit something similar but realized the gem already provides a way to resolve it. Set the following somewhere (e.g. in an initializer):

PpSql.rewrite_to_sql_method = false

You'll still get formatted SQL in your logs, and you can still use relation.pp_sql in the console to get formatted output when you want it, but it won't touch what gets sent to the database. Personally I'd favor making this the default behavior anyway.

The core issue is actually the syntax formatting gem this one relies on, sonota88/anbt-sql-formatter, which doesn't accommodate the full scope of ANSI-compliant SQL syntax much less any platform-specific variants like postgres.

Note: setting this flag should also resolve #15