Queries containing embedded comments may fail because they contain a semicolon

[burggraf]

Queries obtained from pg_stat_statement often contain embedded comments which often contain a semicolon. This causes index_advisor to throw an error even though the semicolon doesn't exist in the actual SQL statement.

To fix this, queries should be run through a function to strip embedded comments:

trim(regexp_replace(
         regexp_replace(
            regexp_replace(query,'\/\*.+\*\/','','g'),
         '--[^\r\n]*', ' ', 'g')
      , '\s+', ' ', 'g'))

[olirice]

I'm surprised that comments with semicolons is that common, could you give me an example for context?

the solution sounds good though, are you 100% confident on that regex?

[burggraf]

I've tested the regex and it's been solid for me. These comments happen 100% of the time if you look at pg_stat_statements for a project that uses the javascript library (PostgREST).