PG16RC1 Support - SQL changes, new features

[naisila]

Introduction

#6952 introduced PG16Beta3 compatibility with Citus's current features and regression tests by fixing #7017.
This issue tracks how Citus will handle PostgreSQL 16's SQL changes and new features. Resources that we used are the following:

• Official release notes
• pgPedia notes

Note: There is currently no priority order in the subtasks of this issue.

Features to support in Citus

We plan to support the following new options:

• CREATE COLLATION (rules = ...) postgres/postgres@30a53b7
  PG16 - Add rules option to CREATE COLLATION #7185
• TRUNCATE triggers on foreign tables postgres/postgres@3b00a94
  PG16 - Add citus_truncate_trigger for Citus foreign tables #7170
• GENERIC_PLAN option for EXPLAIN postgres/postgres@3c05284
  PG16 - Add GENERIC_PLAN option to EXPLAIN #7141
• VACUUM SKIP_DATABASE_STATS, ONLY_DATABASE_STATS postgres/postgres@a46a701
  PG16 compatibility - new options to vacuum and analyze #7114
• VACUUM PROCESS_MAIN postgres/postgres@4211fbd
  PG16 compatibility - new options to vacuum and analyze #7114
• VACUUM/ANALYZE BUFFER_USAGE_LIMIT postgres/postgres@1cbbee0
  PG16 compatibility - new options to vacuum and analyze #7114

Add regression tests or just test locally

Most of PG16's new additions simply work with Citus. However, we add tests for some of them to ensure consistency and maintainability for the future. For some other additions, testing locally is sufficient.

• JSON_ARRAYAGG and JSON_OBJECTAGG postgres/postgres@7081ac4
  Add tests with JSON_ARRAYAGG and JSON_OBJECTAGG aggregates #7186
• Publications with schema and table of the same schema postgres/postgres@13a185f
  Add tests with publications with schema and table of the same schema #7184
• random_normal() postgres/postgres@38d8176
  PG16 - Add tests with random_normal #7183
• CREATE DATABASE (rules = ...) postgres/postgres@30a53b7
  Works with our partially supported CREATE DATABASE for distributed databases
  PG16 - Add tests for createdb with ICU_RULES option #7161
• Changes in REINDEX DATABASE/SYSTEM postgres/postgres@2cbc3c1
  database name no longer mandatory
  These commands are already not manually propagated. When propagated with run_command_on_workers, they work fine.
  PG16 - Add reindex database/system tests #7167
• COPY ... FROM ... WITH (default ...) postgres/postgres@9f8377f
  Already supported in Citus, adding some tests
  PG16 - Add COPY FROM default tests #7143
• STORAGE attribute in CREATE TABLE postgres/postgres@784cedd
  Already supported in Citus, adding some tests
  Add tests for CREATE/ALTER TABLE .. STORAGE in PG16 #7140
• New SUBSCRIPTION parameter "run_as_owner" postgres/postgres@4826759
  Nothing to do here, the default value works for our implementation.
  Future work PG16 - no need for multiple replication slots for security #7171
• New SUBSCRIPTION parameter "origin" postgres/postgres@3662839
  Nothing to do here, the default value works for our implementation.
• ALTER DEFAULT PRIVILEGES require privileges, not membership postgres/postgres@48a257d
  Nothing to do here that is PG16 specific. See ALTER DEFAULT PRIVILEGES is not propagated, also doesn't error out #7144
• IS JSON predicate postgres/postgres@6ee3020
  PG16 - IS JSON predicate and SYSTEM_USER tests #7137
• SYSTEM_USER postgres/postgres@0823d06
  PG16 - IS JSON predicate and SYSTEM_USER tests #7137
• Accept "+infinity" in date and timestamp[tz] input postgres/postgres@2ceea5ad
  tested locally, it's about datetime types accepting certain inputs, no table tests added by PG
• array_sample() and array_shuffle() functions postgres/postgres@888f2ea
  tested locally
• date_add() and date_subtract() functions postgres/postgres@75bd846
  tested locally

Meaningful error messages for currently unsupported features

For now, we will not provide support for the following, but we will print error messages with possible hints/workarounds for the user:

• GRANT ... WITH INHERIT postgres/postgres@e3ce2de
  PG16 - Don't propagate GRANT ROLE with INHERIT/SET option #7190
• GRANT ... WITH SET postgres/postgres@3d14e17
  PG16 - Don't propagate GRANT ROLE with INHERIT/SET option #7190
• Batch insertion during COPY into a foreign table postgres/postgres@97da482
  COPY FROM is already not supported for Citus foreign tables
  Adds test for COPY FROM failure in Citus foreign tables #7160
• ALTER TABLE ... SET STORAGE DEFAULT postgres/postgres@b9424d0
  Already changing storage is not supported and errors out, adding some tests
  Add tests for CREATE/ALTER TABLE .. STORAGE in PG16 #7140
• CREATE STATISTICS without a user-specified name postgres/postgres@624aa2a
  PG16 - Throw meaningful error for stats without a name on Citus tables #7136

[naisila]

@francisjodi is working on propagating GRANT WITH SET/INHERIT
#7148
#7164