PG18 integration: syntax & behavior changes to handle in Citus

[m3hm3t]

Introduction

This issue tracks how Citus will handle PostgreSQL 18's SQL changes and new features. The resource that we used is the following:

• Official release notes

New/changed SQL surface area

• Allow MIN()/MAX() aggregates on arrays and composite types @m3hm3t
  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a0f1fce80
  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2d24fd942

• Add libpq connection parameter sslkeylogfile which dumps out SSL key material @naisila
  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2da74d8d6
  Allow configuring sslkeylogfile using citus.node_conn_info #8437

• Add macro PG_MODULE_MAGIC_EXT to allow extensions to report their name and version @naisila
  https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9324c8c58
  Add PG_MODULE_MAGIC_EXT macro for Citus and citus columnar #8438

• Propagate publish_generated_columns: CREATE PUBLICATION pub1 FOR TABLE tab_gencol WITH (publish_generated_columns = true); @m3hm3t
  postgres/postgres@7054186c4
  PG18 - Handle publish_generated_columns in distributed publications #8360

• Virtual generated columns are now supported and the default: Propagate ... GENERATED ALWAYS AS (...) VIRTUAL
  postgres/postgres@83ea6c540 PG18: Add support for virtual generated columns. #8346

• Add ONLY support for VACUUM and ANALYZE @m3hm3t
  postgres/postgres@62ddf7ee9
  PG18 – Respect VACUUM/ANALYZE ONLY semantics for Citus tables #8365

• Add functions to modify per-relation and per-column optimizer statistics: need to error out or propagate for distributed tables. pg_clear_relation_stats(), pg_restore_relation_stats(), pg_clear_attribute_stats(), pg_restore_attribute_stats()
  postgres/postgres@d32d14639
  postgres/postgres@e839c8ecc
  postgres/postgres@650ab8aaf

Verified (using pg_upgrade) in #8366

• CREATE DATABASE: Add server variable file_copy_method to control the file copying method
  postgres/postgres@f78ca6f3e PG18: GUC file_copy_method behaves as expected. #8379

• Add support for NOT ENFORCED in CHECK constraints
  postgres/postgres@ca87c415e PG18: CHECK constraints can be ENFORCED / NOT ENFORCED. #8349

• Add support for NOT ENFORCED in foreign key constraints
  postgres/postgres@eec0040c4 PG18: Foreign key constraint can be specified NOT ENFORCED. #8347

• Add support for LIKE in CREATE FOREIGN TABLE
  postgres/postgres@302cf1575 PG18: syntax & semantics behavior in Citus, part 1. #8335

• Add REJECT_LIMIT option to the COPY command
  postgres/postgres@4ac2a9bec PG18: syntax & semantics behavior in Citus, part 1. #8335

• Add OLD/NEW support to RETURNING in DML queries
  postgres/postgres@80feb727c PG18: syntax & semantics behavior in Citus, part 1. #8335

• Allow "COPY table TO" command to copy rows from materialized views
  postgres/postgres@534874fac PG18: syntax & semantics behavior in Citus, part 1. #8335

• JSON table support - COLUMNS clause
  postgres/postgres@bb766cd PG18: syntax & semantics behavior in Citus, part 1. #8335

• Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints.
  postgres/postgres@fc0438b4e PG18: syntax & semantics behavior in Citus, part 1. #8335

• Add PERIOD clause to foreign key constraint definitions.
  postgres/postgres@89f908a6d PG18: syntax & semantics behavior in Citus, part 1. #8335

---

Extend Citus test coverage

• Allow the values of generated columns to be logically replicated
  postgres/postgres@745217a05 @m3hm3t

• Allow LIKE with nondeterministic collations
  postgres/postgres@85b7efa1c PG18: verify text search and LIKE with nondeterministic collations. #8350

• Allow text position search functions with nondeterministic collations
  postgres/postgres@329304c90 PG18: verify text search and LIKE with nondeterministic collations. #8350

• Allow NOT VALID foreign key constraints on partitioned tables
  postgres/postgres@b663b9436 PG18: drop constraints ONLY and NOT VALID fks on partitioned tables #8374

• Allow dropping of constraints ONLY on partitioned tables
  postgres/postgres@4dea33ce7 PG18: drop constraints ONLY and NOT VALID fks on partitioned tables #8374

---

Local testing

• Add full WAL buffer count to EXPLAIN (WAL) output @m3hm3t
  postgres/postgres@320545bfc

---

Planner/behavioral changes

• Join between distributed and local table: make Citus work with enable_self_join_elimination
  postgres/postgres@fc069a3a6
  PG18 regress sanity: disable enable_self_join_elimination on queries #8242
  PG18 - wrong result in join between distributed and local table #8217

• Missing index scan in pg_class Pg18 - different scan type on pg_class #8353