6085 + #6022 + #5897 introduced PG15 support.

Postgres 15 brings numerous new features, see Release Notes here. In this issue, we wanted to list some of the items that Citus may need to implement to be 100% compatible with all the Postgres 15 features.

Resources:

PG Release changelog items
PG Release Notes
Waiting for PostgreSQL 15 blog post series authored by @depesz
PostgreSQL 15 New Features With Examples from @hpe

We have divided the work items for the Citus distributed database into three categories:

Must haves
[x] Allow the creation of unlogged sequences @naisila #6292
[x] Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut) Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT. @hanefi #6308
[x] Add support for security invoker views. blog post @naisila We support security invoker views #6362 We sometimes get permission errors with security definer views. See #6161 for details.
[x] Allow numeric scale to be greater than precision (Dean Rasheed, Tom Lane) @hanefi #6256
[x] Allow CLUSTER on partitioned tables (Justin Pryzby) @hanefi #6306
[x] Allow extended statistics to record statistics for a parent with all its children (Tomas Vondra, Justin Pryzby) Regular statistics already tracked parent and parent/all-children statistics separately. @onderkalaci @naisila #6344 #6329
[x] Fix ALTER TRIGGER RENAME on partitioned tables to properly rename triggers an all partitions (Arne Roland, Álvaro Herrera) Also prohibit cloned triggers from being renamed. @naisila
- 6265
- 6290
- 6291
[x] Add multirange input to range_agg() (Note: add tests where the function is executed on Coordinator.) @onderkalaci #6327
[x] Allow foreign key ON DELETE SET actions to affect only specified columns (Paul Martinez) Previously, all of the columns in the foreign key were always affected. @onderkalaci #6319
[x] Prevent CREATE OR REPLACE VIEW from changing the collation of an output column (Tom Lane) @hanefi Works. No need to add tests.

Should haves

[x] Add MIN() and MAX() aggregates for the xid8 data type. Needs tests. @onderkalaci #6327
[x] Adjust JSON numeric literal processing to match the SQL/JSON-standard (Peter Eisentraut) This accepts numeric formats like .1 and 1., and disallows trailing junk after numeric literals, like 1.type(). @hanefi #6368
[x] Allow logical replication to run as the owner of the subscription (Mark Dilger) Because row-level security policies are not checked, only superusers, roles with bypassrls, and table owners can replicate into tables with row-level security policies. @naisila #6369
[x] Reduce casting of constants in postgres_fdw queries (Dian Fay) If column types were mismatched between local and remote databases, such casting could cause errors. @naisila #6370
[x] Allow CREATE DATABASE to set the database OID (Shruthi KC, Antonin Houska) (Note: test if we error out properly. Ask Marco if unsure.) @hanefi #6376
[x] Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner. blog post @naisila @onderkalaci #6180 and #6157 provide enough test coverage for now.
[x] Add assorted new regexp_xxx SQL functions. blog post missing regression tests. @hanefi #6256
[x] Add HEADER support to COPY text format. blog post Missing regression tests. @hanefi #6256
[x] Introduce log_destination=jsonlog. blog post @hanefi Works. No need to add tests.
[x] Allow archiving via loadable modules. blog post @hanefi Works. No need to add tests.

Nice to haves

[x] Prevent Unicode zero-length identifiers, e.g., U&"" (Peter Eisentraut) Non-Unicode zero-length identifiers were already prevented. @naisila Works. No need to add tests.
[x] When EXPLAIN references the temporary object schema, refer to it as pg_temp (Amul Sul) Previously the actual schema name was used. @naisila Works. No need to add tests. Objects with temp schema already cannot be distributed. If a temp object is part of the EXPLAIN of a distributed query, pg_temp will appear, as for plain PG tests.
[x] Add support for SET ACCESS METHOD in ALTER TABLE @TsinghuaLucky912 #6525
[ ] Add server variable shared_memory_size to report the size of allocated shared memory (Nathan Bossart)
[ ] Allow WAL full page writes to use LZ4 and Zstandard compression (Andrey Borodin, Justin Pryzby) This is controlled by the wal_compression server setting.
[ ] Allow GRANT to assign permission to change server variables via SET and ALTER SYSTEM (Mark Dilger). The new function has_parameter_privilege() reports on this privilege. (Hanefi: we do not propagate this. Maybe error out for dist roles)
[ ] Allow GRANT on pg_log_backend_memory_contexts() (Jeff Davis) We don't propagate this GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer) TO distributed_role; We should open a PR to throw error or propagate the grant.
[ ] Allow publishing the tables of schema. blog post In general for publications, we might want to error out if we include a Citus table in a publication since it's not yet supported.

Unsupported features:

These features are not supported due to current limitations in Citus. We may still need to add some tests for coverage and ensuring that we show meaningful error messages.

We mark these items as completed because we show meaningful error messages. However, we may want to add support for these in the future.

[x] Add support for MERGE SQL command. blog post @onderkalaci #6238
[x] Allow numeric scale to be negative (Dean Rasheed, Tom Lane) @hanefi #6297

PG15 reverted some of the changes before beta4

As of PG15beta4, the SQL/JSON features proposed for this PG release have been removed. We also reverted our commits for these features.

[x] (Revert) Various JSON improvements. @onderkalaci for main code changes @naisila for ruleutils changes #6135 #6241 Changes are reverted here #6317

citusdata / citus

Postgres 15 support - Missing features in Citus #6162

6085 + #6022 + #5897 introduced PG15 support.

PostgreSQL 15 New Features With Examples from @hpe

Must haves

6265

6290

6291

Should haves

Nice to haves

Unsupported features:

PG15 reverted some of the changes before beta4