Is it possible to operate inside pg_hint_plan function?

[seunofk]

Dear Developers.

I am currently using a version of Postgresql 11.2 in CentoOS 7.4 pg_hint_plan is version 1.3.4.

Create a function as shown below. ###################################################################### CREATE OR REPLACE FUNCTION test_fnc_2() returns table (bid int, bbalance int, filler bpchar, aid int, a_bid int, abalance int, a_filler bpchar) language sql as $$ /*+ seqscan (a) seqscan (b) */ select b.bid , b.bbalance , b.filler , a.aid , a.bid, a.abalance , a.filler FROM pgbench_branches b JOIN pgbench_accounts a ON b.bid = a.bid ORDER BY a.aid; $$; ####################################################################### I use auto_explan to see the result of executing the function, pg_hint_plan does not work.

Normal operation is possible when executed with sql instead of a function.

Can I get a description and documentation of the issue?

[horiguti]

I doubt that EXPLAIN or auto_exaplain shows that but setting pg_hint_plan.debug_print=verbose or details shows the log like the follows if it works correctly.

2020-02-14 13:36:59.956 JST [13475] LOG: pg_hint_plan[qno=0xd]: HintStateDump: {used hints:SeqScan(a)SeqScan(b)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}

But, I should have made a bug here. The hints in the function should apply to the following query but it has no effect at 1.3.4. The same bug is found in back to PG10 and backpatched to the branch.

https://github.com/ossc-db/pg_hint_plan/commit/4e3c9871ca22d4a5916c1d659787bca3cb09a184

[horiguti]

(That commit for the master branch is the result of wrongly split of the changes. Code change is contained in the previous commit https://github.com/ossc-db/pg_hint_plan/commit/0576a9485c3289d9a3bc49b63654cf8dcbe3fd04 )

[michaelpq]

Looks like there is nothing left to do here, so closing.