pg_hint_plan
pg_hint_plan copied to clipboard
Parser to read multiple hints at different places in the query
We have several cases where we need hints to be anywhere inside query and at the same time we need analysis to understand that hints are not part of string literals and we need to be able to disable some hints using nested comments.
We have compound queries composed from small queries with generated aliases that must be validly hinted too. The most convenient thing is to write hints directly in these parts of the queries when moving and merge hints to the top of the result query it is a too complex.
The new "hints_anywhere" flag that was introduced in the master branch is not suitable for us because it will disable validations (for example hints inside quoted literals or nested comments) and does not allow parse all hints in the query.
This pull request provides an parsing algorithm that extracts all hints in query (not just the first ones) with the following rules:
- Ignore hints inside text literals (single quotes)
- Ignore hints inside escaped text literals (like E'xxx\'xxx\''). Algorithm only check that last quote wasn't escaped. It is enough for most cases. Complex/deep escapes not analyzing.
- Ignore hints inside dollar-quoted literals. Nested dollar-quotes are not analyzing and ignoring.
- Nested multiline comments are allowed. All hints inside nested comments will be ignored.
- Single line comments allowed. Please note that default "psql" settings will remove singleline comments inside direct calls.
The parser does not check the syntax and proceeds with the assumption that current query is valid (In fact requests with invalid syntax do not reach the hint_planner hook). This allows us make only simple checks without deep validation.
This feature can be enabled by GUC pg_hint_plan.enable_state_hint_extractor.