PG17.0 Support - SQL changes, new features
Opened this issue · 0 comments
Introduction
This issue tracks how Citus will handle PostgreSQL 17's SQL changes and new features. Resources that we used are the following:
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:
- @naisila Supporting MEMORY option when extracting EXPLAIN output from workers. postgres/postgres@5de890e36
For now we will not be showing memory usage from workers if we do not add support for this. - @naisila Add EXPLAIN option SERIALIZE to report the cost of converting data for network transmission postgres/postgres@06286709e
- Add DEFAULT setting for ALTER TABLE .. SET ACCESS METHOD postgres/postgres@d61a6cad6
- Allow specification of table access methods on partitioned tables postgres/postgres@374c7a229
- Add clearer ALTER TABLE method to set a column to the default statistics target postgres/postgres@4f622503d
- Allow exclusion constraints on partitioned tables postgres/postgres@8c852ba9a
#7733 - Allow partitioned tables to have identity columns postgres/postgres@699586315
#7785
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:
- MergeAction can have 3 merge kinds (now enum) in PG17, write compat postgres/postgres@0294df2f1
Relevant PG commit:
0294df2f1f842dfb0eed79007b21016f486a3c6c
postgres/postgres@0294df2
New merge kinds should be handled in deparser and Merge logic in Citus.
- MATCHED BY SOURCE
- MATCHED_BY_TARGET - COPY adds a new option, ON_ERROR ignore postgres/postgres@9e2d87011
- Add new COPY option LOG_VERBOSITY which reports COPY FROM ignored error rows postgres/postgres@f5a227895
- In COPY FROM, allow easy specification that all columns should be forced null or not null postgres/postgres@f6d4c9cf1
- Allow ALTER TABLE to change a column's generation expression postgres/postgres@5d06e99a3
- Allow the session time zone to be specified by AT LOCAL postgres/postgres@97957fdba
- Allow MERGE to modify updatable views postgres/postgres@5f2e179bd
- Allow MERGE to use the RETURNING clause postgres/postgres@c649fa24a
#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.
- Add event trigger support for REINDEX postgres/postgres@f21848de2
- New SQL/JSON capabilities JSON_TABLE(), JSON(), JSON_SCALAR(), and JSON_SERIALIZE(), JSON_EXISTS(), JSON_QUERY(), and JSON_VALUE(), jsonpath methods
- Allow COPY FROM to report the number of skipped rows during processing postgres/postgres@729439607
- New client-side connection option, sslnegotiation=direct postgres/postgres@d39a49c1e
- Allow the grouping of file system reads with the new system variable io_combine_limit postgres/postgres@210622c60
- Add system variable allow_alter_system to disallow ALTER SYSTEM postgres/postgres@d3ae2a24f
- Add logical replication failover control to CREATE/ALTER SUBSCRIPTION postgres/postgres@776621a5e
- Allow parenthesized syntax for CLUSTER options if a table name is not specified postgres/postgres@cdaedfc96
- Add support for event triggers that fire at connection time postgres/postgres@e83d1b0c4
- Allow the interval data type to support +/-infinity values postgres/postgres@519fc1bd9
- Allow the use of an ENUM added via ALTER TYPE if the type was created in the same transaction postgres/postgres@af1d39584
- to_timestamp(), uuid_extract_timestamp() , uuid_extract_version() , random(min, max) , to_bin(), to_oct(), unicode_version(), icu_unicode_version(), xmltext() , to_regtypemod(), pg_basetype(), pg_column_toast_chunk_id()