PG17.1 Support - SQL changes, new features

[naisila]

Introduction

This issue tracks how Citus will handle PostgreSQL 17's SQL changes and new features. Resources that we used are the following:

• Official release notes

Note 1: We should finish "Features to support" and "Meaningful error messages" first, then the ones that require only testing.
Note 2: You can refer to the PRs in #7138 to understand better how we should extend our test suite. Add your name to the subtask you are working on.

Features to support in Citus

We plan to support the following new options:

• Supporting MEMORY option when extracting EXPLAIN output from workers. postgres/postgres@5de890e36
  @naisila
  PG17 - Propagate EXPLAIN options: MEMORY and SERIALIZE #7802
• Add EXPLAIN option SERIALIZE to report the cost of converting data for network transmission postgres/postgres@06286709e
  @naisila
  PG17 - Propagate EXPLAIN options: MEMORY and SERIALIZE #7802
• Add support for MERGE ... WHEN NOT MATCHED BY SOURCE postgres/postgres@0294df2f1
  @naisila
  PG17 - Add tests for MERGE ... WHEN NOT MATCHED BY SOURCE #7807
• Allow specification of table access methods on partitioned tables postgres/postgres@374c7a229
  @m3hm3t
  Probably just needs adding tests. EDIT: needs code changes
  PG17 - Add Regression Test for Access Method Behavior on Partitioned Tables #7818
• New client-side connection option, sslnegotiation=direct postgres/postgres@d39a49c1e
  @naisila
  Allow configuring sslnegotiation using citus.node_conn_info #7821
• JSON_TABLE() support postgres/postgres@de3600452 postgres/postgres@bb766cde6
  @naisila
  We treat JSON_TABLE the same as correlated functions (e.g., recurring tuples). In the end, for multi-shard JSON_TABLE commands, we apply the same restrictions as reference tables (e.g., cannot be in the outer part of an outer join etc.)
  Adds JSON_TABLE() support, and SQL/JSON constructor/query functions tests #7816
• Allow the session time zone to be specified by AT LOCAL postgres/postgres@97957fdba
  @naisila
  Here we support inserting values that have AT LOCAL operator because the result value is calculated in the node before propagating the insert command.
  Add tests for inserting with AT LOCAL operator #7815
• In COPY FROM, allow easy specification that all columns should be forced null or not null postgres/postgres@f6d4c9cf1
  @naisila
  Add tests for FORCE_NULL * and FORCE_NOT_NULL * options for COPY FROM #7812
• Allow exclusion constraints on partitioned tables postgres/postgres@8c852ba9a
  @m3hm3t
  PG17 compatibility: Fix Test Failure in multi_alter_table_add_const #7733
• Allow partitioned tables to have identity columns postgres/postgres@699586315
  @colm-mchugh
  PG17 compatibility: account for identity columns in partitioned tables. #7785
• Add clearer ALTER INDEX/TABLE method to set a column to the default statistics target postgres/postgres@4f622503d
  @naisila
  Citus doesn't support altering the column of a distributed table for any reason. Therefore altering a table's column's statistics is not allowed by default. Citus however allows altering the statistics of a column of an index.
  Tests for ALTER INDEX ALTER COLUMN SET STATISTICS DEFAULT #7808

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:

• Allow the interval data type to support +/-infinity values postgres/postgres@519fc1bd9
  @naisila
  Disallow infinite values for partition interval in create_time_partitions udf #7822
• Allow ALTER TABLE to change a column's generation expression postgres/postgres@5d06e99a3
  @m3hm3t
  PG17 - ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION command #7814
• Add DEFAULT setting for ALTER TABLE .. SET ACCESS METHOD postgres/postgres@d61a6cad6
  @m3hm3t
  PG17 Add Regression Test for ALTER TABLE ... SET ACCESS METHOD DEFAULT #7803
• Add new COPY option ON_ERROR postgres/postgres@9e2d87011
  @naisila
  Error for COPY FROM ... on_error, log_verbosity with Citus tables #7811
• Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows postgres/postgres@f5a227895
  @naisila
  Error for COPY FROM ... on_error, log_verbosity with Citus tables #7811
• Allow MERGE to modify updatable views postgres/postgres@5f2e179bd
  @tejeswarm
  Support PostgreSQL 17 MERGE features in Citus with clean exceptions #7781
• Allow MERGE to use the RETURNING clause postgres/postgres@c649fa24a
  @tejeswarm
  Support PostgreSQL 17 MERGE features in Citus with clean exceptions #7781

Add regression tests or just test locally

Most of PG17'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.

• random(min, max), xmltext() postgres/postgres@e6341323a postgres/postgres@526fe0d79
  @naisila
  Add tests with xmltext() and random(min, max) #7824
• Add event trigger support for REINDEX postgres/postgres@f21848de2
  @m3hm3t
  PG17 - Add Regression Test for REINDEX support in event triggers #7819
• jsonpath methods
  @naisila
  Add pg17 jsonpath methods tests #7820
• New SQL/JSON capabilities JSON(), JSON_SCALAR(), and JSON_SERIALIZE(), JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE()
  @naisila
  Adds JSON_TABLE() support, and SQL/JSON constructor/query functions tests #7816
• Allow the use of an ENUM added via ALTER TYPE if the type was created in the same transaction postgres/postgres@af1d39584
  @naisila
  Tested locally.
• Allow COPY FROM to report the number of skipped rows during processing postgres/postgres@729439607
  @naisila
  This can't be actually tested with Citus tables for now because it only reports for ON_ERROR ignore, which is not currently supported in Citus.
• Allow the grouping of file system reads with the new system variable io_combine_limit postgres/postgres@210622c60
  @naisila
  New GUC io_combine_limit limits on how many blocks should be handled in single I/O operations. Citus doesn't interfere.
• Add logical replication failover control to CREATE/ALTER SUBSCRIPTION postgres/postgres@776621a5e
  @naisila
  Nothing broken here, the default value failover = false works for Citus' implementation.
• Add system variable allow_alter_system to disallow ALTER SYSTEM postgres/postgres@d3ae2a24f
  @naisila
  I tried setting this option off for our tests (add push(@pgOptions, "allow_alter_system=off"); to pg_regress_multi.pl) and they fail because we run citus_setup_ssl() which is using alter system commands. However, there is not much we can do here for now. This setting is on by default which works for us. Can be set to off by the user for environments where global configuration changes should be made using a different method, so that is up to the user.
• Allow parenthesized syntax for CLUSTER options if a table name is not specified postgres/postgres@cdaedfc96
  @naisila
  Tested locally. CLUSTER without a table name is not propagated anyway.

-- cluster with parenthesized syntax without a table name
CLUSTER (VERBOSE);
WARNING:  not propagating CLUSTER command to worker nodes
HINT:  Provide a specific table in order to CLUSTER distributed tables.

• Add support for event triggers that fire at connection time postgres/postgres@e83d1b0c4
  @naisila
  Locally tested. CREATE EVENT TRIGGER is not propagated by Citus, so there is no behavior change in this one for Citus.
• to_timestamp() ✅ , uuid_extract_timestamp() ✅ , uuid_extract_version() ✅ , to_bin() ✅, to_oct()✅, unicode_version()✅, icu_unicode_version()✅, to_regtypemod() ✅, pg_basetype() ✅
  @naisila
  Tested locally.
• Add function pg_column_toast_chunk_id() to return a value's TOAST identifier postgres/postgres@d1162cfda
  @naisila
  pg_column_toast_chunk_id() for columns of distributed tables wouldn't give the correct result. Actually, the correct chunk id would be in the toast table of the shard, which is an id that belongs to another node. Therefore, it is expected that this doesn't work properly for Citus when used from the coordinator. The user should expect to use this function on the shard.