Fixing a PG15 Upgrade Pre-Check Failure Caused by a Missing pg_hint_plan Hint Table

During a YugabyteDB PostgreSQL 15 upgrade pre-check, you may encounter an error similar to:

				
					Performing Consistency Checks on Old Live Server

SQL command failed:
SELECT pg_catalog.set_config('search_path', '', false);

ERROR: relation "hint_plan.hints" does not exist

Although the error references set_config() and search_path, those are not the underlying problem.

Root Cause: pg_hint_plan.enable_hint_table is enabled, but the required hint_plan.hints table does not exist in the database being checked.

Why This Happens

pg_hint_plan supports both normal comment-based query hints and hints stored in a database table.

Setting	Purpose	Default
`pg_hint_plan.enable_hint`	Enables normal `pg_hint_plan` query hints.	`on`
`pg_hint_plan.enable_hint_table`	Enables lookup of hints stored in `hint_plan.hints`.	`off`

The problematic combination is:

				
					pg_hint_plan.enable_hint_table = on

while:

				
					hint_plan.hints

does not exist.

When the upgrade pre-check executes SQL, pg_hint_plan attempts to consult the missing hint table and the statement fails.

Check the Database

First, verify whether hint-table support is enabled:

				
					SHOW pg_hint_plan.enable_hint_table;

Then check whether the table exists:

				
					SELECT to_regclass('hint_plan.hints');

You can check both at once:

				
					SELECT
    current_database() AS database_name,
    current_setting('pg_hint_plan.enable_hint_table', true) AS hint_table_enabled,
    to_regclass('hint_plan.hints') AS hint_table;

A problematic result might look like:

				
					.database_name | hint_table_enabled | hint_table
---------------+--------------------+------------
 yugabyte      | on                 |

Fix Option 1: Disable the Hint Table

If your workload does not intentionally use table-based hints, disable the feature.

For example:

				
					ALTER DATABASE <database_name>
RESET pg_hint_plan.enable_hint_table;

or, if configured for a role:

				
					ALTER ROLE <role_name>
RESET pg_hint_plan.enable_hint_table;

Also check your YB-TServer/YBA configuration for:

				
					pg_hint_plan.enable_hint_table=on

If it was configured globally through ysql_pg_conf_csv, remove it or change it to:

				
					pg_hint_plan.enable_hint_table=off

Recommended: If you are not intentionally using hint_plan.hints, disabling pg_hint_plan.enable_hint_table is generally preferable to creating an unused hint table simply to satisfy the upgrade pre-check.

Fix Option 2: Create the Hint Table

If your application intentionally uses table-based hints, initialize pg_hint_plan in the affected database:

				
					CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

Then verify:

				
					SELECT to_regclass('hint_plan.hints');

Expected:

				
					hint_plan.hints

Remember that CREATE EXTENSION is database-specific, so check each database where hint-table support is intentionally enabled.

What About `set_config('search_path', '', false)`?

The statement shown in the upgrade error:

				
					SELECT pg_catalog.set_config('search_path', '', false);

simply clears the session’s schema search path.

It is not the cause of the failure.

It happened to be the SQL statement being processed when pg_hint_plan attempted to access the missing hint_plan.hints table.

Verify the Fix

After correcting the configuration, reconnect and run:

				
					SELECT pg_catalog.set_config('search_path', '', false);

It should execute without the hint_plan.hints error.

Then rerun the PostgreSQL 15 upgrade pre-check from YugabyteDB Anywhere.

Final Takeaway

If a PG15 upgrade pre-check reports:

				
					ERROR: relation "hint_plan.hints" does not exist

check for this mismatch:

				
					pg_hint_plan.enable_hint_table = on
hint_plan.hints                = missing

If table-based hints are not needed, disable the setting. If they are needed, initialize pg_hint_plan so the hint table exists.

And don’t chase the search_path statement… it merely exposed the real pg_hint_plan configuration problem.

Have Fun!

Finally got around to setting up my new Galaxy Watch Ultra 2! Samsung has a great trade-in program, which means my trusty Ultra 1 is now headed back to Samsung.

At first glance, I almost forgot which one was which (minus the band). Side-by-side, though, there are just enough little differences that I should be able to avoid accidentally sending the brand-new one back. ⌚