Skip to content

3.x: Upgrade bundled pg_hint_plan from 1.3.9 to 1.6.3 #2173

Open

Description

@tuhaihe

Summary

The bundled gpcontrib/pg_hint_plan is still upstream 1.3.9 — a PostgreSQL 11–13 era, end-of-life release — which we have hand-adapted to compile on PostgreSQL 16. This proposes upgrading it to 1.6.3, the current patch release of the upstream 1.6 line (the PostgreSQL 16 line).

Why now

  • pg_hint_plan release lines track PostgreSQL majors: 1.4 → PG14, 1.5 → PG15, 1.6 → PG16, 1.7 → PG17, 1.8 → PG18, 1.9 → PG19. Cloudberry main is based on PG 16.9, so 1.6.x is the only upstream line that applies. 1.6.3 is the latest 1.6 patch release (this is what PGDG currently packages for PG16).
  • 1.3.9 is EOL upstream. Every PostgreSQL merge / toolchain bump forces another hand patch here. Recent in-tree commits that exist only because of this:
    • bd01547a0b0 Adapater pg_hint_plan extension to CBDB
    • 19a4b7e108b Fix pg_hint_plan build error with --disable-orca
    • 182cc62d16a Fix various c23 compile errors
    • 9c0a00e9b17 (2026-10-02) queryjumble adaptation
  • Test coverage is effectively absent: gpcontrib/pg_hint_plan/Makefile:11 disables 11 of the upstream ut-* tests ("not adapter to cbdb yet"), only init / base_plan / pg_hint_plan regress run, and there is no CI reference to pg_hint_plan anywhere under .github/ or devops/ — although the extension is built and installed by default (gpcontrib/Makefile:29,40).
  • Stale release artifacts: SPECS/pg_hint_plan12.spec (PG12-era packaging), META.json still says 1.3.9, and the bundled doc says "pg_hint_plan12 1.3 requires PostgreSQL 12" (gpcontrib/pg_hint_plan/doc/pg_hint_plan.html:486).

Important: this is not a drop-in upgrade

The in-tree copy carries Cloudberry-specific code that must be re-applied on top of upstream 1.6.3:

  1. planner_hook signature carries an extra OptimizerOptions * parameter (src/backend/optimizer/plan/planner.c:370).
  2. USE_ORCA integration: external_plan_hint_hook is registered through the backend plan_hint_hook so ORCA can consume parsed hints (src/include/optimizer/orca.h:28-30, src/backend/gpopt/utils/COptTasks.cpp:651-655); the module must also keep building with --disable-orca.
  3. queryjumble adaptation (functions generated by gen_node_support.pl).
  4. Greenplum-specific DDL in the base install script (pg_hint_plan--1.3.0.sql, "unique indexes must contain distribution keys").
  5. MPP/ICW test adaptations (e.g. 6c03bf0c544, bf7f7f1ddbf).

Good news: the extension's SQL objects are minimal (hint_plan.hints table + sequence + two GRANTs), so the ALTER EXTENSION ... UPDATE script barely needs content.

Work items

  • Import upstream tag REL16_1_6_3 into gpcontrib/pg_hint_plan.
  • Re-apply the Cloudberry delta above; keep it clearly marked (or as a patch set) so the next PG17 jump (1.7.x) stays cheap.
  • Bump HINTPLANVER, pg_hint_plan.control default_version and META.json; add pg_hint_plan--1.3.9--1.6.3.sql.
  • Remove PG12 leftovers (SPECS/pg_hint_plan12.spec, stale doc version claims).
  • Re-enable ut-* tests where MPP-compatible and add a CI job running with optimizer=on and optimizer=off.
  • Validate: regress + ICW + compare EXPLAIN output for hint users; note behavior changes in the release notes.

Acceptance criteria

  • pg_hint_plan reports 1.6.3 (\dx / pg_hint_plan_version()), and existing hint_plan.hints contents survive ALTER EXTENSION pg_hint_plan UPDATE.
  • The same hint set produces the same plan decisions as before, apart from documented upstream fixes.
  • CI job green on both optimizer settings; ut-* suite runs, or skipped tests are listed with a reason.
  • No PG12-era files or version references remain.

Risks

  • Hint semantics under ORCA must not change; the ORCA path is the main unknown.
  • Re-enabling ut-* in an MPP environment may require per-test skip lists.
  • Users with pg_hint_plan installed get an extension version bump; the upgrade path needs testing.

Alternatives considered

  • Defer to 3.1 and keep 1.3.9 in 3.0, but at minimum add CI coverage and fix the stale docs. This keeps the recurring PG16 hand-patch cost and leaves the module on an EOL branch.

References

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions