# Architecture > Architecture, catalogs, replication, execution, I/O, roles, and server internals. --- LLMS index: [llms.txt](/en/llms.txt) ---
GASource pages: PDF 14-43Original PDF on GitHub

PDF page 14

#### 3.1.1 Modified System catalogs {#pg17-3-1-1-modified-system-catalogs} The following catalogs and views have been changed. [[1e68e43](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e68e43d3f0ff1dcf4a5926f9d6336b86bda034d), [007693f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=007693f2a3ac2ac19affcb03ad43cdb36ccff5b5), [46ebdfe](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46ebdfe164c61fbac961d1eb7f40e9a684289ae6), [78806a9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=78806a95095c4fb9230a441925244690d9c07d23), [13aeaf0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13aeaf0797e75a0c53abb66ac907ba14b4e47f6b), [3ee2f25](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3ee2f25d21faa123dc5bb2886a00a54bdddd1c3e), [b0e96f3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b0e96f311985bceba79825214f8e43f65afa653a), [e64c733](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e64c733bb1996e52a13f6fc4a1240703f607fa53) ,[e83d1b0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e83d1b0c40ccda8955f1245087f0697652c4df86), [96f0526](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=96f052613f35d07d001c8dd2f284ca8d95f82d1b), [bc3c8db](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bc3c8db8ae2f899cfd65edce99fe9cf65caf1ed7), [12915a5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=12915a58eec962f407a6c38ce2bf08a48dde57b5), [4f62250](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4f622503d6de975ac87448aea5cea7de4bc140d5), [46a0cd4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92), [c393308](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c393308b69d229b664391ac583b9e07418d411b6), [776621a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=776621a5e4796fa214b6b29a7ca134f6c138572a), [ddd5f4f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ddd5f4f54a026db6a6692876d0d44aef902ab686), [030e10f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=030e10ff1a365796bd4bcbbc5b6a8552f7efc765), [f696c0c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f696c0cd5f299f1b51e214efc55a22a782cc175d), [012460e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=012460ee93c304fbc7220e5b55d9d0577fc766ab), [6ae701b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6ae701b4378db2284c77314560e95a93d0ba9484), [a11f330](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a11f330b5584f2430371d68871e00f5c63735299), [6d49c8d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6d49c8d4b4f4a20eb5b4c501d78cf894fa13c0ea), [7294396](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=729439607ad210dbb446e31754e8627d7e3f7dda), [667e65a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=667e65aac354975c6f8090c6146fceb8d7b762d6), [74604a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=74604a37f2f9eb6e626a4ff3cedd02aef5a2ad59), [74604a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=74604a37f2f9eb6e626a4ff3cedd02aef5a2ad59), [f1affb6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f1affb67055c9b3f31a7ee7eb521a9ba64fff488)] **Table 6.** Add system catalogs and views | View name | Description | | --- | --- | | pg_wait_events | The name and description of the waiting event will be output | | pg_stat_checkpointer | The execution statistics of checkpointer process is output | **Table 7.** System catalogs\/views with additional columns | View name | Add column | Data type | | Description | | --- | --- | --- | --- | --- | | Catalog/View name | Add column | Data type | | Description | | pg_database | dathasloginevt | boolean | | Defined login event triggers | | pg_replication_slots | failover | boolean | | Logical slot enabled to be synced to the standbys | | | synced | boolean | | Logical slot that was synced from a primary server | | | invalidation_reason | text | | The reason for the slot's invalidation | | | inactive_since | timestamp time zone | with | The time since the slot has become inactive | | pg_stat_progress_copy | tuples_skipped | bigint | | Number of tuples skipped | | pg_stat_progress_vacuum | indexes_total | bigint | | Total number of indexes that will be vacuumed | | | indexes_processed | bigint | | Number of indexes processed | | | dead_tuple_bytes | bigint | | Amount of dead tuple data | | Catalog/View name | Add column | Data type | | Description | | | max_dead_tuple_bytes | bigint | | Amount of dead tuple data based on maintenance_work_mem | | | num_dead_item_ids | bigint | | Number of dead item identifiers collected | | pg_stat_subscription | worker_type | text | | Type of the subscription worker process | | pg_stats | range_length_histogram | anyarray | | A histogram of the lengths of non-empty values | | | range_empty_frac | real | | Fraction of column entries whose values are empty ranges | | | range_bounds_histogram | anyarray | | A histogram of lower and upper bounds of non-empty values | | pg_subscription | subfailover | boolean | | The associated replication slots are enabled to be synchronized to the standbys |

PDF page 15

**Table 8.** System catalogs\/views with removed columns | View name | Remove column | Description | | --- | --- | --- | | pg_stat_bgwriter | buffers_backend | It duplicates the pg_stat_io view. | | | buffers_backend_fsync | It duplicates the pg_stat_io view. | | | checkpoints_timed | Move to pg_stat_checkpointer | | | checkpoint_req | Move to pg_stat_checkpointer | | | checkpoint write time__ | Move to pg_stat_checkpointer | | | checkpoint sync time__ | Move to pg_stat_checkpointer | | | buffers_checkpoint | Move to pg_stat_checkpointer | | pg_stat_progress_vac | num_dead_tuples | Renamed to dead_tuple_bytes | | uum | max_dead_tuples | Renamed to max_dead_tuple_bytes | **Table 9.** System catalogs\/views with changed columns name | View name | Before | After | Note | | --- | --- | --- | --- | | pg_database | daticulocale | Datlocale | | | pg_collation | coliculocale | Collocate | |

PDF page 16

**Table 10.** View which in information\_schema schema with removed column name | View name | Removed column | Description | | --- | --- | --- | | element_types | domain_default | Due to defects in standard specifications | **Table 11.** System catalogs\/views with modified output | View name | Description | | --- | --- | | pg_attribute | The NOT NULL constraint on the attstattarget column was removed, and the value indicating the default statistics was changed from -1 to NULL. | | pg_backend_memory_contexts | The "search_path processing cache" is now output. | | pg_statistic_ext | The data type of the stxstattarget column was changed from integer to smallint. stxstattarget column NOT NULL constraints were removed. | | pg_stat_wal | WAL information output by the bgwriter process is added. This change is backported to PostgreSQL 14 or later. | ##### Pg_wait_events view {#pg17-p16-pg-wait-events-view} The following is the detailed description of the pg_wait_events view added to PostgreSQL 17. The pg_wait_events view provides the names and descriptions of registered wait events. [[1e68e43](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e68e43d3f0ff1dcf4a5926f9d6336b86bda034d)] **Table 12.** The pg\_wait\_events view | Column name | Data type | Description | Note | | --- | --- | --- | --- | | type | text | Type of Wait Event | Activity, Lock, IO, etc. | | name | text | Name of Wait Event | | | description | text | Wait Event description | | **Example 6.** Reference to the pg\_wait\_events view ```text postgres=> SELECT name, description FROM pg_wait_events ; name | description -----------------------+------------------------------------------------------ ArchiverMain | Waiting in main loop of archiver process AutoVacuumMain | Waiting in main loop of autovacuum launcher process BgWriterHibernate | Waiting in background writer process, hibernating BgWriterMain | Waiting in main loop of background writer process … ```

PDF page 17

##### Pg_stat_checkpointer view {#pg17-p17-pg-stat-checkpointer-view} The following is the detail of the pg_stat_checkpointer view, which is new to PostgreSQL 17. [[96f0526](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=96f052613f35d07d001c8dd2f284ca8d95f82d1b), [12915a5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=12915a58eec962f407a6c38ce2bf08a48dde57b5)] **Table 13.** The pg\_stat\_checkpointer view | Column name | Data type | | Description | | --- | --- | --- | --- | | num_timed | bigint | | Number of scheduled checkpoints | | num_requested | bigint | | Number of checkpoints requested | | restartpoints_timed | bigint | | Number of restart points timed out or rescheduled | | restartpoints_req | bigint | | Number of restart points requested | | restartpoints_donewrite_time | bigint | | Number of restart points executed | | | double precision | double precision | Total write time | | sync_timebuffers_writtenstats_reset | double precision bigint | double precision | Total synchronization time Number of buffers written | | | timestamp time zone | with | Reset timestamps | **Example 7.** Reference to the pg\_stat\_checkpointer view ```text postgres=> SELECT * FROM pg_stat_checkpointer ; -[ RECORD 1 ]-------+------------------------------ num_timed | 84 num_requested | 1 restartpoints_timed | 0 restartpoints_req | 0 restartpoints_done | 0 write_time | 93982 sync_time | 5 buffers_written | 935 stats_reset | 2024-09-27 23:44:02.608669+09 ```

PDF page 18

#### 3.1.2 Logical Replication Enhancements {#pg17-3-1-2-logical-replication-enhancements} The following features have been added to Logical Replication. ##### Integration with Streaming Replication {#pg17-p18-integration-with-streaming-replication} The WAL Sender (walsender) process of logical replication can be configured to send any update data to the plugin after confirming that the replication slot in streaming replication has received the WAL. The primary server parameter synchronized_standby_slots specifies the name of the streaming replication slot that should confirm the transfer. To use this feature, the 'failover' attribute of the logical replication slot must be set as described below. If the replication slot specified in synchronized_standby_slots becomes inactive Logical replication transfers are also stopped and the following message is periodically output to the primary server log. [[bf279dd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bf279ddd1c28ce0251446ee90043a4cb96e5db0f)] **Example 8.** Log when streaming replication standby instance downs ```text WARNING: replication slot "slot1" specified in parameter synchronized_standby_slots does not have active_pid DETAIL: Logical replication is waiting on the standby associated with "slot1". HINT: Consider starting standby associated with "slot1" or amend parameter synchronized_standby_slots. ``` ##### Replication slot new attribute {#pg17-p18-replication-slot-new-attribute} The parameter 'failover' has been added to the pg_create_logical_replication_slot function. The default value of this parameter is 'false'. Replication slots with this parameter set to 'true' can synchronize LSN information to the standby instance of streaming replication. The 'failover' column has been added to the pg_replication_slots catalog to store the added attribute values. [[c393308](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c393308b69d229b664391ac583b9e07418d411b6)]

PDF page 19

**Example 9.** Creating the Replication Slot with Failover Attribute ```text postgres=# \df pg_create_logical_replication_slot List of functions -[ RECORD 1 ]-------+--------------------------------------------------------- Schema | pg_catalog Name | pg_create_logical_replication_slot Result data type | record Argument data types | slot_name name, plugin name, temporary boolean DEFAULT false, twophase boolean DEFAULT false, failover boolean DEFAULT false, OUT slot_name name, OUT lsn pg_lsn Type | func postgres=# SELECT pg_create_logical_replication_slot ('logislot1', 'test_decoding', false, false, true) ; -[ RECORD 1 ]----------------------+---------------------- pg_create_logical_replication_slot | (logislot1,0/70004C0) ``` ##### Subscription new attribute {#pg17-p19-subscription-new-attribute} The new attribute FAILOVER can now be specified for subscriptions. To store this attribute, a subfailover column has been added to the pg_subscription catalog. If this attribute is set to TRUE for the subscription, replication slots created on the primary server will also have the FAILOVER attribute at the same time. **Example 10.** Create SUBSCRIPTION with FAILOVER attribute ```text postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=dbsvr1 port=5432 dbname=postgres' PUBLICATION pub1 WITH (FAILOVER = TRUE) ; NOTICE: created replication slot "sub1" on publisher CREATE SUBSCRIPTION postgres=# SELECT subname, subfailover FROM pg_subscription ; subname | subfailover ---------+------------- sub1 | t (1 row) ```

PDF page 20

##### Logical Replication Slot Synchronization {#pg17-p20-logical-replication-slot-synchronization} The information of logical replication slots created on the primary server can be periodically synchronized to the standby server in streaming replication. [[93db6cb](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=93db6cbda037f1be9544932bd9a785dabf3ff712)] The following conditions are required on the streaming replication standby server to use this feature. - Set the parameter sync_replication_slots to 'on' (default 'off') - Set the parameter wal_level to 'logical' (default 'replica') - Use the replication slot with the 'failover' option specified - Setting of the parameter primary_slot_name - Parameter hot_standby_feedback set to 'on' (default 'off') - Include 'dbname' setting in parameter primary_conninfo If the above conditions are met, the "slotsync worker" process is launched on the streaming replication instance. If the conditions are not met, the following log is output. **Example 11.** Synchronization error log for replication slots ```text LOG: slot synchronization requires hot_standby_feedback to be enabled LOG: slot synchronization requires primary_slot_name to be defined ERROR: slot synchronization requires dbname to be specified in primary_conninfo ``` ```text The replication slot synchronization status is output to the log of the standby server. Below is a log of the slot synchronization worker being started and the new replication slot being automatically created. ``` **Example 12.** Replication slot synchronization success log ```text LOG: slot sync worker started LOG: newly created replication slot "sub1" is sync-ready now ``` ##### Pg_sync_replication_slots function {#pg17-p20-pg-sync-replication-slots-function} PostgreSQL 17 adds the function pg_sync_replication_slots to synchronize logical replication slots on streaming replication standby servers. This function forces synchronization of replication slot information even in environments where the parameter sync_replication_slots is set to 'off'. [[ddd5f4f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ddd5f4f54a026db6a6692876d0d44aef902ab686)]

PDF page 21

**Example 13.** Forced synchronization of replication slots ```text postgres=# SELECT pg_sync_replication_slots() ; pg_sync_replication_slots --------------------------- (1 row) ``` ##### Use hash the index on subscribers {#pg17-p21-use-hash-the-index-on-subscribers} The subscribers can now use not only Btree indexes but also Hash indexes for update processing. [[edca342](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=edca3424342da323499a1998d18a888283e52ac7)] ##### Pg_logical_emit_message function {#pg17-p21-pg-logical-emit-message-function} Added parameter 'flush' to control WAL flush. The default value is 'false', which means no messages will be flushed. [[173b56f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=173b56f1ef597251fe79d8e71a0df7586ea12549)] #### Syntax 1 - Pg_logical_emit_message function {#pg17-syntax-1} ```sql pg_lsn pg_logical_emit_message(transactional boolean, prefix text, message bytea|text, flush boolean DEFAULT false) ``` #### 3.1.3 Streaming Replication Enhancements {#pg17-3-1-3-streaming-replication-enhancements} Data to be transferred to the standby instance is now retrieved from the WAL buffer if possible. Previously, the data was reloaded from the WAL file after the writing was completed. [[91f2cae](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=91f2cae7a4e664e9c0472b364c7db29d755ab151)] #### 3.1.4 Incremental Backup {#pg17-3-1-4-incremental-backup} The incremental backups are now available as a standard feature of PostgreSQL. [[174c480](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=174c480508ac25568561443e6d4a82d5c1103487), [dc21234](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc212340058b4e7ecfc5a7a81ec50e7a207bf288), [ee1bfd1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ee1bfd168390bc843c6704d16e909692c0a79f27), [d9ef650](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d9ef650fca7bc574586f4171cd929cfd5240326e), [f8ce4ed](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f8ce4ed78ca6e35bf135545e34bd49cd65d88ea2)] ##### WAL summary {#pg17-p21-wal-summary} To use incremental backup, the WAL summarize feature must be enabled. The WAL summarize feature can be enabled by setting the parameter wal_level to 'replica' or 'logical' and changing the parameter summarize_wal (default value 'off') to 'on'. When this feature is enabled, a "walsummarizer" process is invoked in the instance and summary data of the WAL file is stored in the ${PGDATA}/pg_wal/summaries directory.

PDF page 22

**Example 14.** WAL summary files ```bash $ ls data/pg_wal/summaries/ 00000001000000000100002800000000010B2D50.summary 0000000100000000010B2D5000000000014E8508.summary 0000000100000000014E850800000000014E8608.summary 0000000100000000014E860800000000014EEF98.summary 0000000100000000014EEF9800000000014EF098.summary 0000000100000000014EF09800000000014EF3D0.summary 0000000100000000014EF3D00000000002000028.summary 0000000100000000020000280000000008000028.summary … ``` ```text The WAL summary file is automatically removed when the parameter wal_summary_keep_time (default value '10d') is exceeded. The following functions are provided to retrieve WAL summary information. ``` **Table 14.** WAL summary retrieval functions | Function name | Description | | --- | --- | | pg_available_wal_summaries | Returns the starting LSN and ending LSN for each WAL summary file | | pg_wal_summary_contents | Return update information between specified LSNs | | pg_get_wal_summarizer_state | Returns status of WAL summary creation | #### Syntax 2 - WAL summary retrieval functions {#pg17-syntax-2} ```sql record pg_available_wal_summaries() record pg_wal_summary_contents(tli bigint, start_lsn pg_lsn, end_lsn pg_lsn) record pg_get_wal_summarizer_state() ```

PDF page 23

**Example 15.** Functions for WAL summary information ```text postgres=> SELECT * FROM pg_available_wal_summaries() ; -[ RECORD 1 ]-------- tli | 1 start_lsn | 0/5A77BB0 end_lsn | 0/BF29980 … postgres=> SELECT * FROM pg_wal_summary_contents(1, '0/5A77BB0', '0/BF29980') ; -[ RECORD 1 ]--+----- relfilenode | 1259 reltablespace | 1663 reldatabase | 1 relforknumber | 0 relblocknumber | 3 is_limit_block | f … postgres=> SELECT * FROM pg_get_wal_summarizer_state() ; -[ RECORD 1 ]--+---------- summarized_tli | 1 summarized_lsn | 0/BF11AE8 pending_lsn | 0/BF11BF0 summarizer_pid | 9876 ``` ##### Incremental Backup {#pg17-p23-incremental-backup} The incremental backup is a feature that retrieves the difference the previous backup. The manifest file output during the previous backup is used to obtain an incremental backup. To execute the incremental backup, specify the manifest file of the previous backup in the --incremental option (abbreviated form -i) of the pg_basebackup command.

PDF page 24

**Example 16.** Execute the incremental backup ```text $ pg_basebackup -D back.1 $ psql postgres demo psql (17.0) Type "help" for help. postgres=> INSERT INTO data1 VALUES (generate_series(1, 1000000), 'data1') ; INSERT 0 1000000 postgres=> \q $ pg_basebackup -D back.inc1 --incremental=back.1/backup_manifest ``` ```text The pg_combinebackup command has been added to merge base and incremental backups. To merge incremental backups, the pg_combinebackup command specifies the directories where base and incremental backup are stored, and the --output option (shortened -o) specifies the directory to be merged. The directory to be merged must not exist or must be an empty directory. ``` **Example 17.** Merge backup data ```bash $ pg_combinebackup back.1 back.inc1 --output=data.2 $ ls data.2 backup_label pg_dynshmem pg_serial PG_VERSION backup_manifest pg_hba.conf pg_snapshots pg_wal base pg_ident.conf pg_stat pg_xact current_logfiles pg_logical pg_stat_tmp postgresql.auto.conf global pg_multixact pg_subtrans postgresql.conf log pg_notify pg_tblspc pg_commit_ts pg_replslot pg_twophase ``` ##### Manifest file enhancements {#pg17-p24-manifest-file-enhancements} The System Identifier is now output in the manifest file. The version of the manifest file has been changed to version 2. [[2041bc4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2041bc4276c95ac014510032e622a4baf70b29f1)]

PDF page 25

**Example 18.** Manifest file enhancements ```bash $ pg_basebackup -D back.1 $ grep System-Identifier back.1/backup_manifest "System-Identifier": 7345983777657046843, $ grep Manifest-Version back.1/backup_manifest { "PostgreSQL-Backup-Manifest-Version": 2, ``` ```text The pg_combinebackup command must specify a backup with the same System Identifier. In the following example, an error occurs when specifying a difference retrieved from a different database. ``` **Example 19.** Check the system ID ```bash $ pg_combinebackup back.a1 inc.b2 --output=data.1 pg_combinebackup: error: back.a1/global/pg_control: expected system identifier 7347556981648665444, but found 7347557495025884081 ``` #### 3.1.5 Partition Table Enhancements {#pg17-3-1-5-partition-table-enhancements} The following new features have been implemented for partition tables. ##### Enhancements to Partition Pruning {#pg17-p25-enhancements-to-partition-pruning} The IS UNKNOWN, IS NOT UNKNOWN clause for boolean columns now supports partition pruning. [[07c36c1](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07c36c1333e00df5a4baf6b04ba2f3f300f57d69)] ##### Allow exclude constraints {#pg17-p25-allow-exclude-constraints} Previously, only unique constraints using Btree were allowed for partitions. PostgreSQL 17 now also allows EXCLUDE CONSTRAINT with the same restrictions. However, column comparisons must be performed for equality. In the second example below, an error occurs because the partition key does not include the constraint target column. [[8c852ba](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8c852ba9a4347c4778cc610ad5a9cb50ea701b5c)]

PDF page 26

**Example 20.** Allow exclude constraints ```text postgres=> CREATE TABLE partc1 (col1 INT4RANGE, EXCLUDE USING GIST (col1 WITH = )) PARTITION BY RANGE (col1) ; CREATE TABLE postgres=> CREATE TABLE partc2 (col1 INT4RANGE, col2 INT4RANGE, EXCLUDE USING GIST (col2 WITH =)) PARTITION BY RANGE (col1) ; ERROR: unique constraint on partitioned table must include all partitioning columns DETAIL: EXCLUDE constraint on table "partc3" lacks column "col1" which is part of the partition key. ``` ##### Support for IDENTITY column {#pg17-p26-support-for-identity-column} The INSERT statements are now supported on partitions of partitioned tables with IDENTITY columns. The second INSERT statement in the example below will cause an error in PostgreSQL 16. [[6995863](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=699586315704a8268808e3bdba4cb5924a038c49)] **Example 21.** Supports for IDENTITY column ```text postgres=> CREATE TABLE part1(c1 INT, c2 INT GENERATED ALWAYS AS IDENTITY) PARTITION BY RANGE(c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (1000000) ; CREATE TABLE postgres=> INSERT INTO part1(c1) VALUES (100) ; INSERT 0 1 postgres=> INSERT INTO part1v1(c1) VALUES (200) ; ← PostgreSQL 16 でエラー INSERT 0 1 postgres=> SELECT * FROM part1 ; c1 | c2 -----+---- 100 | 1 200 | 2 (2 rows) ```

PDF page 27

**Example 22.** Behavior of PostgreSQL 16 ```text postgres=> INSERT INTO part1v1(c1) VALUES (200) ; ERROR: null value in column "c2" of relation "part1v1" violates not-null constraint ``` #### 3.1.6 Roles and Privileges {#pg17-3-1-6-roles-and-privileges} Predefined roles and privileges that can be granted have been added. ##### MAINTAIN privilege {#pg17-p27-maintain-privilege} The MAINTAIN privilege can now be granted on tables and materialized views. A role granted this privilege can execute VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW, CLUSTER, and LOCK TABLE statements on the target relation. The result of the \dp meta-command in the psql command will output 'm'. [[ecb0fd3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ecb0fd33720fab91df1207e85704f382f55e1eb7)] **Example 23.** Grant MAINTAIN privilege ```text postgres=# CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) ; CREATE TABLE postgres=# GRANT MAINTAIN ON data1 TO demo ; GRANT postgres=# \dp data1 Access privileges Schema | Name | Type | Access privileges | Column privileges | … --------+-------+-------+----------------------------+-------------------+-… public | data1 | table | postgres=arwdDxtm/postgres+| | | | | demo=m/postgres | | (1 row) postgres=# \connect postgres demo You are now connected to database "postgres" as user "demo". postgres=> VACUUM data1 ; VACUUM ```

PDF page 28

##### Addition of pg_maintain role {#pg17-p28-addition-of-pg-maintain-role} The pg_maintain predefined role has been added. This role is able to grant MAINTAIN privilege on all relations. ##### Modification of pg_monitor role {#pg17-p28-modification-of-pg-monitor-role} The predefined role pg_monitor has been granted permission to execute the pg_current_logfile function. [[8d8afd4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8d8afd48d3f298bc4d8ab2b115cc39550132bde7)] **Example 24.** Execution privilege of pg\_current\_logfile function ```text postgres=# CREATE USER demo PASSWORD '<>' ; CREATE ROLE postgres=# GRANT pg_monitor TO demo ; GRANT ROLE postgres=# \connect postgres demo You are now connected to database "postgres" as user "demo". postgres=> SELECT pg_current_logfile() ; pg_current_logfile -------------------------------------- log/postgresql-2024-09-27_085557.log (1 row) postgres=> SELECT pg_current_logfile('csvlog') ; pg_current_logfile -------------------- (1 row) ``` #### 3.1.7 Built-in Locale Provider {#pg17-3-1-7-built-in-locale-provider} PostgreSQL 17 has added a 'built-in' locale provider that is independent of external environments such as Libc or icu. The BUILTIN can now be specified in commands and SQL statements that specify a locale provider. [[2d819a0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2d819a08a1cbc11364e36f816b02e33e8dcc030b), [f69319f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f69319f2f1fb16eda4b535bcccec90dff3a6795e)]

PDF page 29

**Example 25.** Specifying a builtin locale provider ```text postgres=# CREATE DATABASE builtdb LOCALE_PROVIDER BUILTIN LOCALE 'C.UTF-8' TEMPLATE template0 ; CREATE DATABASE postgres=# SELECT datcollate, datctype, datlocale FROM pg_database WHERE datname='builtdb' ; -[ RECORD 1 ]------- datcollate | C.UTF-8 datctype | C.UTF-8 datlocale | C.UTF-8 ``` ```text Currently, the only locales offered by this locale provider are C and C.UTF-8. The built-in locale C.UTF-8 provides advantages versus the libc locale, such as faster sorting, faster case conversion, and is platform-independent. ``` #### 3.1.8 Event Trigger {#pg17-3-1-8-event-trigger} The following features have been added to event triggers. ##### Disabling Event Triggers {#pg17-p29-disabling-event-triggers} ```text The parameter event_triggers has been added to disable the operation of event triggers. The ``` default value is 'on', which enables event triggers. ##### Login event trigger {#pg17-p29-login-event-trigger} The login event trigger, which is executed on successful authentication from the client, has been added. The following is an example of the event trigger that appends the connection time to the table. [[e83d1b0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e83d1b0c40ccda8955f1245087f0697652c4df86)]

PDF page 30

**Example 26.** Publishing login event trigger ```text postgres=# CREATE OR REPLACE FUNCTION save_login() RETURNS event_trigger SECURITY DEFINER LANGUAGE plpgsql AS $$ BEGIN INSERT INTO public.login_history VALUES(current_timestamp); END; $$ ; CREATE FUNCTION postgres=# CREATE EVENT TRIGGER login_trigger ON login EXECUTE FUNCTION save_login() ; CREATE EVENT TRIGGER postgres=# ALTER EVENT TRIGGER login_trigger ENABLE ALWAYS ; ALTER EVENT TRIGGER postgres=# \connect postgres demo You are now connected to database "postgres" as user "demo". postgres=> SELECT * FROM login_history ; login ---------------------------- 2024-09-27 15:44:21.889142 (1 row) ``` ##### Reindex DDL trigger {#pg17-p30-reindex-ddl-trigger} The DDL event triggers can now be generated at the start and end of the execution of REINDEX statements. [[f21848d](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f21848de20130146bc8039504af40bd24add54cd)]

PDF page 31

**Example 27.** Generate REINDEX event trigger \(REINDEX start\) ```text postgres=# CREATE OR REPLACE FUNCTION reindex_start_func() RETURNS event_trigger AS $$ BEGIN RAISE NOTICE 'START REINDEX: % %', tg_event, tg_tag ; END; $$ LANGUAGE plpgsql ; CREATE FUNCTION postgres=# CREATE EVENT TRIGGER trg_reindex_start ON ddl_command_start WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_start_func() ; CREATE EVENT TRIGGER postgres=# REINDEX INDEX idx1_data1 ; NOTICE: START REINDEX: ddl_command_start REINDEX REINDEX ```

PDF page 32

**Example 28.** Generate REINDEX event trigger \(REINDEX finish\) ```text postgres=# CREATE FUNCTION reindex_end_func() RETURNS event_trigger AS $$ DECLARE obj record ; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'REINDEX END: command_tag=% type=% identity=%', obj.command_tag, obj.object_type, obj.object_identity ; END LOOP ; END; $$ LANGUAGE plpgsql ; CREATE FUNCTION postgres=# CREATE EVENT TRIGGER trg_reindex_end ON ddl_command_end WHEN TAG IN ('REINDEX') EXECUTE PROCEDURE reindex_end_func() ; CREATE EVENT TRIGGER postgres=# REINDEX TABLE data1 ; NOTICE: START REINDEX: ddl_command_start REINDEX NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.idx1_data1 NOTICE: REINDEX END: command_tag=REINDEX type=index identity=public.idx2_data1 REINDEX ``` #### 3.1.9 Configuration File {#pg17-3-1-9-configuration-file} The maximum length of tokens in the pg_hba.conf and pg_ident.conf files has been extended from 256 bytes to unlimited. In complex environments configured with LDAP, there were problems with exceeding the previous maximum length. [[38df84c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=38df84c65ea4aa9110cbc797d07225fb0fac44f7)] #### 3.1.10 Log files {#pg17-3-1-10-log-files} The following new changes have been implemented in the output of the server logs. ##### Logging of trust connections {#pg17-p32-logging-of-trust-connections} When the parameter log_connections is set to "on", logs are output for trust connections as follows. [[38df84c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=38df84c65ea4aa9110cbc797d07225fb0fac44f7)]

PDF page 33

**Example 29.** Logging for trust connection \(PostgreSQL 16\) ```text LOG: connection received: host=[local] LOG: connection authorized: user=postgres database=postgres application_name=psql ``` **Example 30.** Logging for trust connection \(PostgreSQL 17\) ```text LOG: connection received: host=[local] LOG: connection authenticated: user="postgres" method=trust (/postgres/data/pg_hba.conf:117) LOG: connection authorized: user=postgres database=postgres application_name=psql ``` ##### Replication Slot logging {#pg17-p33-replication-slot-logging} The logs about replication slots are output at the LOG level (usually at the DEBUG1 level) when the parameter log_replication_commands is set to 'on'. The following example shows the logs at the beginning and end of replication. [[7c3fb50](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7c3fb505b14e86581b6a052075a294c78c91b123)] **Example 31.** Replication slot logging ```text LOG: received replication command: START_REPLICATION SLOT "slot1" 0/3000000 TIMELINE 1 LOG: acquired physical replication slot "slot1" STATEMENT: START_REPLICATION SLOT "slot1" 0/3000000 TIMELINE 1 LOG: released physical replication slot "slot1" ``` ##### Recovery related logging {#pg17-p33-recovery-related-logging} The logs at the start and end of the recovery process have been added. The following example shows the log at instance startup for streaming replication. [[1d35f70](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1d35f705e191581784e9df89bb69b7867ac9c342)] **Example 32.** Recovery logging ```text LOG: starting backup recovery with redo LSN 0/2000028, checkpoint LSN 0/2000080, on timeline ID 1 LOG: entering standby mode LOG: redo starts at 0/2000028 LOG: completed backup recovery with redo LSN 0/2000028 and end LSN 0/2000120 ```

PDF page 34

#### 3.1.11 Hook {#pg17-3-1-11-hook} The following Object Access Type (OAT) hooks have been added to the ALTER TABLE statement. [[352ea3a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=352ea3acf8a2d6e0490eb005bce38303de4564dd)] - { ENABLE | DISABLE | [NO] FORCE } ROW LEVEL SECURITY - { ENABLE | DISABLE } TRIGGER - { ENABLE | DISABLE } RULE #### 3.1.12 Libpq {#pg17-3-1-12-libpq} The following APIs have been added and enhanced. ##### Connect string sslnegotiation {#pg17-p34-connect-string-sslnegotiation} The connection option sslnegotiation has been added. This option determines how the encryption method is negotiated during the client connection. This option can also be specified by the environment variable PGSSLNEGOTIATION. [[d39a49c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d39a49c1e459804831302807c724fa6512e90cf0), [fb5718f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fb5718f35ff667104ab0b9dace3a238113666237)] **Table 15.** Possible values for the option | Value | Description | Note | | --- | --- | --- | | postgres | Perform PostgreSQL protocol negotiation. | Default | | direct | First attempt to establish a standard SSL connection and if that fails reconnect and perform the negotiation. | | | requiredirect | Attempt to establish a standard SSL connection and if that fails return a connection failure immediately. | | ##### Close Portals and Statements {#pg17-p34-close-portals-and-statements} Functions to close portals and statements have been added. Functions with "send" in the name are non-blocking versions. [[28b5726](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=28b5726561841556dc3e00ffe26b01a8107ee654)] **Table 16.** Added functions | Function | Description | | --- | --- | | PQclosePrepared | Close the prepaid statement. | | PQclosePortal | Close the portal. | | PQsendClosePrepared | Close the Prepared statement. Do not wait for completion. | | PQsendClosePortal | Closes the portal. Do not wait for completion. |

PDF page 35

##### Password change {#pg17-p35-password-change} The function PQchangePassword has been added to change the password of a connected user. [[a7be2a6](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a7be2a6c262d5352756d909b29c419ea5e5fa1d9)] ##### Changes to chunk mode {#pg17-p35-changes-to-chunk-mode} The PGresults now retrieves multiple tuples at once. The function PQsetChunkedRowsMode has been added to change the mode accordingly. [[4643a2b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4643a2b265e967cc5f13ffa0c7c6912dbb3466d0)] ##### Pipeline Synchronization {#pg17-p35-pipeline-synchronization} The PQsendPipelineSync function has been added. This function is similar to the PQpipelineSync function, but it does not flush a synchronization message to the server unless the output buffer reaches a threshold value. [4794c2] ##### Sequence operation {#pg17-p35-sequence-operation} The functions have been added to manipulate sequences, such as the relation_open function. [[449e798](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=449e798c77ed9a03f8bb04e5d324d4e3cfbbae8e)] **Table 17.** Added functions | Function | Description | Note | | --- | --- | --- | | sequence_opensequence_close | Open the sequence Close the sequence | | ##### Injection Points {#pg17-p35-injection-points} The following functions have been added to support injection points. [[d86d20f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d86d20f0ba79451f19782dbb5fbf206746aaffd1)] **Table 18.** Added functions | Function | Description | Note | | --- | --- | --- | | InjectionPointAttach | Attaching Callbacks | | | InjectionPointDetach | Detaching a callback | | | InjectionPointRun | Run the injection point | | ##### Non-Blocking cancel functions {#pg17-p35-non-blocking-cancel-functions} Previously, the PQcancel API used blocking requests. It is now possible to send a cancel request without blocking. [[61461a3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=61461a300c1cb5d53955ecd792ad0ce75a104736)]

PDF page 36

**Table 19.** Added functions | Function | Description | | --- | --- | | PQcancelCreate | Prepare to send a cancel request. | | PQcancelBlocking | Requesting discard of the current command in blocking state. | | PQcancelPoll | Request discard of current command in non-blocking state. | | PQcancelStart | Request discard of current command in non-blocking state. | | PQcancelStatus | Retrieve the status of a canceled connection. | | PQcancelSocket | Retrieve the identifier of the canceled connection socket. | | PQcancelErrorMessage | Get the last error generated by the cancel operation. | | PQcancelFinish | Close the connection. | | PQcancelReset | Reset PGcancelConn and reuse it for a new connection. | ##### Background Worker {#pg17-p36-background-worker} The flag BGWORKER_BYPASS_ROLELOGINCHECK can now be specified in the functions BackgroundWorkerInitializeConnection and BackgroundWorkerInitializeConnectionBy-Oid. This flag allows bypassing the role login check. [[e768919](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e7689190b3d58404abbafe2d3312c3268a51cca3)] ##### DSM Registry {#pg17-p36-dsm-registry} The function GetNamedDSMSegment has been added to allow shared libraries to easily use shared memory. Previously, shared libraries specified in the parameter shared_preload_libraries had to request shared memory via the shmem_request_hook hook. [[8b2bcf3](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b2bcf3f287c79eaebf724cba57e5ff664b01e06)] ##### DSA Initial Size {#pg17-p36-dsa-initial-size} Previously, DSAs were created at 1 MB and the maximum is still extended to DSA_MAX_SEGMENT_SIZE. The dsa_create_ext and dsa_create_in_place_ext functions have been added to allow the initial and maximum sizes to be specified. [[bb952c8](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=bb952c8c8b13279eca039499249cb5dc60991603)] ##### Streaming I/O {#pg17-p36-streaming-i-o} Abstracts access to relations and allows relation data to be accessed as a stream of buffers. This modification will make it easier to make changes in the future when implementations such as asynchronous I/O are added. It is used to execute the ANALYZE statement and perform a sequential scans on the table. [[b7b0f3f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b7b0f3f27241e424b7103397489464d907cef2c4), [041b968](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=041b96802efa33d2bc9456f2ad946976b92b5ae1), [b5a9b18](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b5a9b18cd0)]

PDF page 37

**Table 20.** Added functions | Function | Description | Note | | --- | --- | --- | | read_stream_begin_relation | Create a new read streaming object. | | | read_stream_next_buffer | Retrieve a single fixed buffer from the stream. | | | read_stream_resetread_stream_end | Release the queued buffer and reset the stream. Release the stream. | | ##### Multi-block read {#pg17-p37-multi-block-read} The multi-block version of the ReadBuffer API is now provided. The maximum number of blocks is determined by the new parameter io_combine_limit. In the future, asynchronous I/O may be used internally. [[210622c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=210622c60e1a9db2e2730140b8106ab57d259d15)] **Table 21.** Added functions | Function | Description | Note | | --- | --- | --- | | StartReadBufferStartReadBuffersWaitReadBuffers | Start reading a single block. Start reading multi blocks. Wait for the completion of block read. | | ##### Bump Memory Allocation {#pg17-p37-bump-memory-allocation} The memory allocator BumpContext has been added, which is suitable for memory allocations with short life times. BumpContext memory is now used for sorting tuples and for VACUUM processing. [[29f6a95](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=29f6a959cfd8ffa7d6db2c0629439c5329e2853e), [6ed83d5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6ed83d5fa55cf6e6c9d1be34ec10730c48eba763), [8a1b31e](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8a1b31e6e59631807a08a4e9465134c343bbdf5e)]

PDF page 38

**Table 22.** Added functions | Function | Description | | --- | --- | | BumpContextCreate | Create Bump MemoryContext. | | BumpAlloc | Allocate Bump memory. | | BumpFree | Free Bump memory. | | BumpRealloc | Re-allocate Bump memory. | | BumpReset | Frees all memory which is allocated in the given set. | | BumpDelete | Frees all memory which is allocated in the given context. | | BumpGetChunkContext | Un-supported. | | BumpGetChunkSpace | Un-supported. | | BumpIsEmpty | Returns true if 'block' contains no chunk. | | BumpStats | Compute stats about memory consumption of the context. | | BumpCheck | Walk through chunks and check consistency of memory. | ##### Polling file descriptor {#pg17-p38-polling-file-descriptor} The PQsocketPoll function has been provided to monitor file descriptors. [[f5e4ded](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f5e4dedfa81f00de93b1b90d06c44cc50e052eaf)] #### 3.1.13 Access Method {#pg17-3-1-13-access-method} The following enhancements have been added to the access methods. ##### Parallel build of BRIN index {#pg17-p38-parallel-build-of-brin-index} The multiple worker processes can now be used to build BRIN indexes. The amcanbuildparallel flag has been added to the index access method structure IndexAmRoutine. [[b437571](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b437571714707bc6466abde1a0af5e69aaade09c)] ##### heap_page_prune function {#pg17-p38-heap-page-prune-function} The parameters of the HEAP_PAGE_PRUNE function have been changed to allow multiple options to be specified in a bitmap. Currently, only HEAP_PAGE_PRUNE_MARK_UNUSED_NOW and HEAP_PAGE_PRUNE_FREEZE options are available. [[3d0f730](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d0f730bf19fbc70cca58818c1ad7415e5aa7d62), [6dbb490](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6dbb490261a6170a3fc3e326c6983ad63e795047)] #### 3.1.14 Wait Events {#pg17-3-1-14-wait-events} The following features have been added for wait events.

PDF page 39

##### Modified Wait Events {#pg17-p39-modified-wait-events} The following wait events have been added or changed. [[fa88928](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fa88928470b538c0ec0289e4d69ee12356c5a8ce), [c9af054](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c9af054653077699189884c336a65e23a7c8aebb), [c8e318b](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c8e318b1b8d9b699a96b96db861b362550456108), [0013ba2](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0013ba290b6684d095d93517ff2ca1fadf79bdb9), [5c430f9](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5c430f9dc559ecd3bda8bebf4854f3f8d8bd86f3), [d86d20f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d86d20f0ba79451f19782dbb5fbf206746aaffd1)] **Table 23.** Added Wait Events | Wait Event Name | Class | Description | | --- | --- | --- | | CheckpointDelayComplete | IPC | Waiting for the backend to complete checkpoint | | CheckpointDelayStart | IPC | Waiting for backend to start checkpoint | | MultixactCreation | IPC | Waiting for multi-transaction creation | | ReplicationSlotsyncMain | Activity | Replication slot synchronization wait | | ReplicationSlotsyncShutdown | Activity | Waiting for replication slot to stop | | WaitForStandbyConfirmation | Client | Waiting for standby confirmation | | WalSummarizerError | Timeout | WAL summary error | | WalSummaryRead | IO | Waiting for WAL summary to load | | WalSummaryReady | IPC | Waiting for WAL summary generation | | WalSummaryWrite | IO | Waiting for WAL summary writing | | InjectionPoint | LWLock | Waiting for injection point loading | **Table 24.** Renamed wait event | change | change | Note | | --- | --- | --- | | AutoVacuumMainBaseBackupReadBaseBackupSyncBaseBackupWriteBgWorkerShutdownBgWorkerStartup | AutovacuumMain BasebackupRead BasebackupSync BasebackupWrite BgworkerShutdown BgworkerStartup | | | BgWriterHibernateBgWriterMainBufferIOBufFileReadBufFileTruncateBufFileWriteDSMAllocateDSMFillZeroWriteGSSOpenServer | BgwriterHibernate BgwriterMain BufferIo BuffileRead BuffileTruncate BuffileWrite DsmAllocate DsmFillZeroWrite GssOpenServer | | | LibPQWalReceiverConnect | LibpqwalreceiverConnect | | | LibPQWalReceiverReceive | LibpqwalreceiverReceive | | | LockFileAddToDataDirRead | LockFileAddtodatadirRead | | | LockFileAddToDataDirSync | LockFileAddtodatadirSync | | | LockFileAddToDataDirWrite | LockFileAddtodatadirWrite | | | LockFileReCheckDataDirRead | LockFileRecheckdatadirRead | | | ProcArrayGroupUpdateSLRUFlushSyncSLRUReadSLRUSyncSLRUWriteSSLOpenServerSysLoggerMainWALBootstrapSync | ProcarrayGroupUpdate SlruFlushSync SlruRead SlruSync SlruWrite SslOpenServer SysloggerMain WalBootstrapSync | | | WALBootstrapWriteWALCopyReadWALCopySyncWALCopyWriteWALInitSyncWALInitWriteWALRead | WalBootstrapWrite WalCopyRead WalCopySync WalCopyWrite WalInitSync WalInitWrite WalRead | | | WALSenderTimelineHistoryRead | WalsenderTimelineHistoryRead | | | WalSenderWaitForWALWALSync | WalSenderWaitForWal WalSync | | | WALSyncMethodAssignWALWrite | WalSyncMethodAssign WalWrite | |

PDF page 40

##### List of Wait Events {#pg17-p40-list-of-wait-events} The list of wait events is now written in the file src/backend/utils/activity/wait_event_names.txt and the code and documentation are generated automatically. [[59cbf60](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=59cbf60c0f2b5723ce4a939077c99bf4e4c433d3)]

PDF page 41

**Example 33.** The part of wait\_event\_names\.txt ```text … Section: ClassName - WaitEventClient CLIENT_READ "Waiting to read data from the client." CLIENT_WRITE "Waiting to write data to the client." … ``` ##### Custom Type {#pg17-p41-custom-type} The method of registering custom wait events has been changed. New API "uint32 WaitEventExtensionNew(const char *wait_event_name)" is provided. With this, the shared memory hook is no longer required. [[af720b4](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=af720b4c50a122647182f4a030bb0ea8f750fe2f)] #### 3.1.15 FILLFACTOR {#pg17-3-1-15-fillfactor} The FILLFACTOR is now considered in the estimated number of tuples when table statistics are not gathered. Previously, FILLFACTOR was not considered, which could result in an overestimation of the number of tuples. [[29cf61a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=29cf61ade3f245aa40f427a1d6345287ef77e622)] **Example 34.** Estimated number of tuples in PostgreSQL 17 ```text postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) WITH (FILLFACTOR=50) ; CREATE TABLE postgres=> INSERT INTO data1 VALUES (generate_series(1, 100000), 'data1') ; INSERT 0 100000 postgres=> EXPLAIN SELECT COUNT(*) FROM data1 ; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=1875.08..1875.09 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..1717.46 rows=63046 width=0) (2 rows) ```

PDF page 42

**Example 35.** Estimated number of tuples in PostgreSQL 16 ```text postgres=> CREATE TABLE data1(c1 INT, c2 VARCHAR(10)) WITH (FILLFACTOR=50) ; CREATE TABLE postgres=> INSERT INTO data1 VALUES (generate_series(1, 100000), 'data1') ; INSERT 0 100000 postgres=> EXPLAIN SELECT COUNT(*) FROM data1 ; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=2663.15..2663.16 rows=1 width=8) -> Seq Scan on data1 (cost=0.00..2347.92 rows=126092 width=0) (2 rows) ``` #### 3.1.16 VACUUM {#pg17-3-1-16-vacuum} The TidStore is now used to store tuple IDs for VACUUM instead of a simple array. This change reduces the amount of memory used, which used to be larger, and eliminates the 1 GB limit. In addition, some column names in the pg_stat_progress_vacuum view have been changed. [[667e65a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=667e65aac354975c6f8090c6146fceb8d7b762d6)] #### 3.1.17 LLVM {#pg17-3-1-17-llvm} PostgreSQL 17 now supports LLVM 17, 18. Backported to supported older versions of PostgreSQL. [[76200e5](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=76200e5ee469e4a9db5f9514b9d0c6a31b496bff), [d282e88](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d282e88e50521a457fa1b36e55f43bac02a3167f)] #### 3.1.18 Acceleration {#pg17-3-1-18-acceleration} The CRC calculation function COMP_CRC32C can now be executed natively in environments that support LoongArch. [[4d14ccd](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4d14ccd6af6e788a7b79ff3ed77bda5bc71d2edc)] In environments where the Intel AVX-512 instruction set can be used, visibilitymap_count and pg_popcount functions can now be executed faster. [[41c51f0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=41c51f0c68b21b4603bd2a9c3d3ad017fdd22627), [792752a](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=792752af4eb5cf7b5b8b0470dbf22901c5178fe5)] #### 3.1.19 UNICODE {#pg17-3-1-19-unicode} PostgreSQL 17 has the following changes in Unicode support. [[9d17e5f](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9d17e5f16fbe679b851f060a72b1e71f7cd3eba8), [9e2e4f0](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e2e4f08bb6cc86710cb84459b7fd0f407e596a7), [ad49994](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ad49994538c57270c244fab4e1de040568066313)] - Unicode version changed from 15.0.0 to 15.1.0 ([https://unicode.org/versions/Unicode15.1.0/](https://unicode.org/versions/Unicode15.1.0/))

PDF page 43

- Unicode CLDR version changed from 43 to 45 ([https://cldr.unicode.org/index/downloads/cldr-45](https://cldr.unicode.org/index/downloads/cldr-45)) - Add update-unicode build target to update UNICODE table