ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
696 stars 103 forks source link

Fix issue with ignoring prepared statements that have array parameters (PG14) #115

Closed Lerm closed 1 year ago

Lerm commented 1 year ago

This is continuation of the PR https://github.com/ossc-db/pg_hint_plan/pull/98 and change 7c03155c825e483d14ad1b2c619b0263339d8f7e.

Current version of the pg_hint_plan (PG15+) was updated in scope of the previous PR to allow square brackets ('[' and ']') located before the hint string. This is required to support hints for PREPARE statements with array type arguments (e.g. 'PREPARE test_query(numeric[]) AS SELECT /+ ... / ...).

In scope of the previous PR it was decided that previous behavior was a bug (i.e. it is hard to use PREPARE statement without such fix), but the fix was introduced only in the head branch (i.e. PG15+), as back-porting it to previous versions (PG14 or lower) may change behavior of existing queries. This is a valid consideration.

However, this still leaves older PG versions with the original problem - this is especially troublesome for public cloud environments where it is not always possible to install custom extension builds.

The proposal is to introduce this fix to older versions as well, but control it with a separate option (GUC), so it will be disabled by default. In this case, the existing behavior will not be changed by default, but anyone affected by this issue could solve it by setting this option.

This solution has its own drawbacks - it introduces a GUC variable in the older version (e.g. PG14), which is then removed in the next version (PG15). However, this will only affects users, which are explicitly using this parameter to get the desired behavior on PG14, and it could be just removed as part of the migration to PG15. It also slightly complicates the code and the documentation - however the change itself is fairly small and simple.

This PR implements following change for the PG14 branch:

The PR is targeted to PG14, as it's the version we are mostly interested for now (hitting this issue), but it could be aligned to lower versions as well if required (just let me know).

michaelpq commented 1 year ago

We basically argued in #98 that this is a new feature hence it would not be backpatched, even if this change attempts to cover it under a GUC.