[Request]: Add PostgreSQL 17 Support to Hydra (Cloud Native Postgres Operator)

[Shah-Aayush]

What's wrong?

Description:
PostgreSQL 17 introduces several key features like enhanced incremental backups and recovery, which are highly relevant for modern workflows. We request support for PostgreSQL 17 in Hydra to leverage these capabilities within Kubernetes/Openshift environments.

Request:

1. Update Hydra to support PostgreSQL 17.
2. If possible, provide the original Hydra PostgreSQL image file so we can replicate steps in the Dockerfile and test PostgreSQL 17 integration ourselves.
3. Suggest steps or guidance for creating a custom image with PostgreSQL 17.
4. Share an approximate timeline for the official release of PostgreSQL 17 support in Hydra.

Thank you for your attention! Let us know how we can help or contribute to testing pre-release builds.

[Shah-Aayush]

I’m unsure if this is the right place to request this feature, as I couldn’t create a discussion thread. Please let me know if there’s a specific procedure I should follow for this type of request.
Thank you!

[Shah-Aayush]

I think pgxman is still not supporting postgres 17, so is there anyway to build the dockerfile without using pgxman ?

[ivan-v-kush]

The 1st issue is

checking for pg_config... /usr/bin/pg_config
configure: error: Citus is not compatible with the detected PostgreSQL version 17.

Then we get compilation errors.

/build/contrib/hydra/src/include/columnar/utils/listutils.h:57: warning: "foreach_ptr" redefined
 #define foreach_ptr(var, l) \
...
/build/src/include/nodes/pg_list.h:472: note: this is the location of the previous definition
 #define foreach_ptr(type, var, lst) foreach_internal(type, *, var, lst, lfirst)


columnar_customscan.c:2526:2: error: implicit declaration of function 'ExecFreeExprContext'; did you mean 'FreeExprContext'? [-Werror=i
mplicit-function-declaration]
  ExecFreeExprContext(&node->ss.ps);
  ^~~~~~~~~~~~~~~~~~~
  FreeExprContext

.....

As hydra is based on citus we can find there several MRs for support PG17, that can be used as a reference for Hydra PG17

citusdata/citus#7699
citusdata/citus#7700
citusdata/citus#7708
etc.

[ivan-v-kush]

@wuputah @owenthereal @JerrySievert, other hydra maintainers do you need any help from us to support PG17?

[ivan-v-kush]

1st MR #276

[JerrySievert]

unfortunately, the existing tests do not run on pg17, and will need to be redone - see JerrySievert@3614299 for all of the changes that were needed.

[ivan-v-kush]

thanks! I've got several such warnings with your commit

columnar_customscan.c:2510:19: warning: incompatible pointer types passing 'PlanState *' (aka 'struct PlanState *') to parameter of type 'ExprContext *' (aka 'struct ExprContext *') [-Wincompatible-pointer-types]
  FreeExprContext(&node->ss.ps, false);
                  ^~~~~~~~~~~~
/usr/include/postgresql/17/server/executor/executor.h:541:42: note: passing argument to parameter 'econtext' here
extern void FreeExprContext(ExprContext *econtext, bool isCommit);
                                         ^

[ivan-v-kush]

1. @JerrySievert I suggest you create PR from your commit.

You need to use such construction to get ExprContext

FreeExprContext(css->ss.ps.ps_ExprContext, false);

2. We also need PG16-PG17: rename macros foreach... to foreach_..._ptr  #276 to support PG17

[ivan-v-kush]

@JerrySievert do you mean this Broken Docker Hydra's pipeline for tests ?

the existing tests do not run on pg17,

Or you ran them and result is different from expected?

[JerrySievert]

correct. no PR's without tests to go with them. test systems changed in pg16, which I adapted columnar's testing system to. it changed again in pg17.

[ivan-v-kush]

The 2nd preparation PR
#278

[ivan-v-kush]

Now in PG17 hydra has an infinite loop problem when call ANALYZE sql command.
columnar_scan_analyze_next_block is called in analyze.
https://github.com/hydradatabase/hydra/blob/main/columnar/src/backend/columnar/columnar_tableam.c#L2066
It returns always true.

static bool
columnar_scan_analyze_next_block(TableScanDesc scan, BlockNumber blockno,
								 BufferAccessStrategy bstrategy)
{
	return true;
}

In PG17 code was refactored and now call of this function is in the loop (table_scan_analyze_next_block calls columnar_scan_analyze_next_block for hydra)
https://github.com/postgres/postgres/blame/master/src/backend/commands/analyze.c#L1233
Always true, infinite loop

while (table_scan_analyze_next_block(scan, stream))
{
...

At the current time, I'm beginning to fix it. Citus seems also haven't fixed it.
https://github.com/citusdata/citus/blame/main/src/backend/columnar/columnar_tableam.c#L1427

need to adopt to stream API, appeared in PG17 x__x