Tolerating preceding single-line comments

[LukeNotable]

(Raising this here from my StackOverflow.)

Basically, I would like to allow us to use pg_hint_plan through Metabase, and the obstacle is that this tool prepends a single-line comment, so that the database sees a query like this, for example:

-- Metabase:: userID: 123 queryType: native queryHash: 4567890
explain analyze
/*+ SeqScan(users) */
select id from users where email = 'test@me'

The docs say:

pg_hint_plan reads hints from only the first block comment

That sounds like the single-line comment ought to be simply ignored, but when I try it, it seems to prevent pg_hint_plan from doing anything. (Same under Postgres 15 and 16.)

Is there any workaround? And if not, assuming there's no intention to support hinting in such single-line comments, can they just be ignored?

[michaelpq]

Hmm. Does https://github.com/ossc-db/pg_hint_plan/commit/bfb45447c9d4181790519c8ef4041c6b9e98fbff help with your case? It is not something that I would backpatch to a stable branch, unfortunately.

[LukeNotable]

It probably would, if only I could get it for Postgres 15. 😆 Sounds like that will solve a lot of problems. Meanwhile, I was asking about this one with the faint hope it might be a trivial and backportable fix.

[michaelpq]

It has required a lot of work to get that done properly in the version of the module for Postgres 17~, so I am afraid that the answer is that this is not trivial. And I know that the yyac layer I have added in PG17 should handle this case properly because it detects hints when these begin with /*+ and only that.