When unable to parse result is <truncated query>, even when queries are complete

[joekohlsdorf]

When the parser is unable to parse a query it returns <truncated query>. But the query isn't necessarily truncated - it is unparsable. We reviewed pg_stat_statements manually and these queries are complete. In pgAnalyze we see about 300 unparseable queries and we need some way to identify them. What can we do to help fixing these parser errors?

https://github.com/pganalyze/collector/blob/7969c3b4b0a9e2466aadd710d15502276c84d058/util/normalize.go#L6-L9

[lfittl]

Hi @joekohlsdorf

Thanks for the the report (and sorry for having missed this issue) - this is likely due to problems with the SQL text requiring a newer Postgres version. Our parser is currently locked at Postgres 10's SQL dialect. We are working to upgrade the parser, and are targeting to upgrade to Postgres 12 or 13's SQL dialect in the next 1-2 months.

To confirm, since you've reviewed these manually in pg_stat_statements, could you share an example SQL statement that is having this problem? (either here, or in the open support ticket)

Thanks!

[barillax]

We've upgraded to Postgres 12, which entailed adding the new MATERIALIZED keyword to many of our CTEs to preserve some intentional optimization fences. This issue has essentially crippled PgAnalyze for us, as almost half of our queries are now displayed as <truncated query>.

In the absence of a parser upgrade, may I suggest a fallback to the first N characters of the query, at least for display purposes in the UI?

[booleanbetrayal]

Also running into this issue and agree that @barillax 's tweak would be a good critical patch compromise. Please rush this!

[joekohlsdorf]

It's really sad to see that someone has taken the time to do the hardest part to get this fixed for free last year and the PR is just sitting there with no feedback: https://github.com/lfittl/libpg_query/pull/62

[lfittl]

@joekohlsdorf FWIW, that PR is unfortunately not in a state that can be merged at this time - but point taken.

We're prioritizing this and hope to have an update here soon. Totally understand that this is frustrating, and we'll update here as soon as we can.

[booleanbetrayal]

@lfittl - I don't mind volunteering for a beta version of the collector that may have a hotfix prior to parser fixes landing. Even having a small character sampling of ~300 characters for normalizedQuery would be a great improvement!

[booleanbetrayal]

Any update to this issue @lfittl ?

[lfittl]

@booleanbetrayal Parser work is 90% done (some minor outstanding items: https://github.com/lfittl/libpg_query/pull/67), I would say its realistic to have this available in the next 1-2 weeks.

[barillax]

@lfittl Any update on PG12 support ETA? Didn't see any changes to the linked ticket.

[lfittl]

@barillax Still actively working on it - the main pending item is some fingerprinting updates we need to make (so that all new SQL syntax is correctly identified/grouped together). Expecting to make good progress on it this week, I'll update here as soon as I can.

[booleanbetrayal]

@lfittl - Any chance of getting a normalizedQuery hotfix like @barillax had suggested?

In the absence of a parser upgrade, may I suggest a fallback to the first N characters of the query, at least for display purposes in the UI?

[Koronei]

What I normally do as a workaround is temporarily log all statements in postgres log and then look for related log entries to the truncated query. It has worked for statements above our log_min_duration_statement threshold, but have yet to see it work for faster queries.

[barillax]

@Koronei Thanks, that's a good idea; unfortunately, these queries are usually pretty fast, but are running often enough to warrant scrutiny in PgAnalyze, e.g.: as possible candidates for caching.

@lfittl I hate to keep bugging you, but it's been a month since PgAnalyze stopped working for us due to this issue. Just to set realistic expectations, should we assume this work is going to take another month (or two?) and check back in then?

I didn't spy any recent commits in https://github.com/lfittl/libpg_query , so I'm guessing other work has taken precedence. If you're thinking this is more like a 3-6 month timeline, it'd be helpful to know so our team can pursue alternative options in the meantime. Thanks!

[lfittl]

Hi @barillax @booleanbetrayal & @joekohlsdorf!

Thanks for your patience on this. First of all, the good news: We added a workaround in the latest collector release to unblock the use of pganalyze with Postgres 12 SQL syntax. Work is still actively ongoing on the Postgres 12 parser update (internal AST changes require an update of the fingerprinting mechanism, amongst other details).

Here is how you can get this working in the meantime:

• Update to version 0.32.0 of the collector
• When using pganalyze-collector.conf, add the following to the server configuration:

  filter_query_text: none
  

• When using Docker, set FILTER_QUERY_TEXT=none
• Restart the collector after changing the setting

If this is working you should see unparsable queries (such as newer syntax, truncated queries, etc) show up in the pganalyze UI. If this is not working, or you don't adjust the setting, with the new collector you should see unsupported syntax show as <unparsable query> (renamed from the prior <truncated query> name for clarity).

Please let us know if you run into any issues with this configuration.

[joekohlsdorf]

I still see the queries showing up as <truncated query> with the new collector version and FILTER_QUERY_TEXT set to none, is there some sort of cache for the query string on your side?

I have verified with strings collector|grep "<unparsable query>" that the collector we are running has the new code.

[booleanbetrayal]

@lfittl - We're also seeing what @joekohlsdorf is seeing. We're on 0.32.0 with the filter_query_text: none setting enabled in pganalyze-collector.conf.

We do not see any queries showing up as <unparsable query>. They all continue to show up as <truncated query> and if we drill down into the Index Check tab, we see:

Error: Could not parse query.

However, we are seeing a single query that contains a MATERIALIZED CTE, which we believe was previously showing up as <truncated query> but is now at least displaying the SQL. Viewing the Index Check tab for this query also displays the "Error: Could not parse query." text.

Hope this helps.

[lfittl]

@booleanbetrayal @joekohlsdorf Ah, I believe I know what the problem here is. All old queries that show up as <truncated query> need to be refreshed on our end (we can manually mark this as such, and then the next collector run updates the query texts).

For additional verification, could you open a quick support request from your account, granting account access and referencing this GitHub issue? Thanks!

[booleanbetrayal]

Seeing query text again after contacting @lfittl and having that cache cleared!