PDF page 14
#### 3.1.1 Modified catalogs {#pg14-3-1-1-modified-catalogs} The following catalogs have been changed. **Table 5.** Added system catalogs and views | View name | Description | | --- | --- | | pg_backend_memory_contexts | Displays all the memory contexts of the server process attached to the current session. | | pg_stat_progress_copy | Outputs the execution status of the COPY statement. | | pg_stat_replication_slots | Outputs the usage status of the replication slot. | | pg_stat_wal | Outputs the WAL data output status. | | pg_stats_ext_exprs | Provides information about all expressions included in extended statistics objects | **Table 6.** Added views in the information schema \(information\_schema\) | View name | Description | | --- | --- | | routine_column_usage | Identifies the column used by the function or procedure. Currently not tracked. | | routine_routine_usage | Identifies the function used by the function or procedure. Currently, some are not tracked. | | routine_sequence_usage | Identifies the sequence used by a function or procedure. Currently, some are not tracked. | | routine_table_usage | Identifies the table used by the function or procedure. Currently not tracked. |PDF page 15
**Table 7.** System catalogs\/views with additional columns | View name | Added column | Data type | Description | | --- | --- | --- | --- | | pg_attribute | attcompression | char | Current compression method of the column. | | pg_inherits | inhdetachpendin g | boolean | True for a partition that is in the process of being detached | | pg_locks | waitstart | timestamp with time zone | Time when the server process time started waiting for this lock. | | pg_prepared_statements | pg_prepared_statementsgeneric_plans | bigint | Number of times generic plan was chosen. | | | custom_plans | bigint | Number of times custom plan was chosen | | pg_proc | prosqlbody | pg_node_tre e | Pre-parsed SQL function body. | | pg_range | rngmultitypid | oid | OID of the multirange type. | | pg_replication_slots | two_phase | boolean | Enabled for decoding prepared transactions. | | pg_stat_activity | query_id | bigint | Identifier of this backend's most recent query. | | pg_stat_database | session_time | double precision | Time spent by database sessions in this database. | | | active_time | double precision | Time spent executing SQL statements in this database. | | | idle_in_transacti | double | Time spent idling while in a | | | on_time | precision | transaction in this database. | | | sessions | bigint | Total number of sessions. | | | sessions_abandoned | bigint | Number of lost sessions. | | | sessions_fatal | bigint | Number of sessions caused by fatal error. | | | sessions_killed | bigint | Number of sessions terminated by the operator. | | pg_stats_ext | exprs | text[] | Expression to get extended statistics | | pg_statistic_ext | stxexprs | pg_node_tre e | Expression trees for statistics object attributes. | | pg_statistic_ext_data | stxdexpr | pg_statistic [] | A list of any expressions covered by this statistics object. | | pg_subscription | subbinary | boolean | Publisher send data in binary format. | | | substream | boolean | Streaming of in-progress transactions. | | pg_type | typsubscript | regproc | Subscripting handler function's OID. |PDF page 16
**Table 8.** System catalogs\/views with columns removed | Catalog name | Column name | Description | | --- | --- | --- | | pg_stat_ssl | compression | It was deleted because compression was disabled. | **Table 9.** System catalogs\/views with modified output | View name | Description | | --- | --- | | pg_attribute | The column definition order has changed. | | pg_class | The initial value of the reltuples column has been changed to -1. | | pg_settings | pending_restart column shows as true when a pertinent entry in postgresql.conf is removed. | | pg_statistic_ext | Extended statistics with the 'e'= expression may be output in the stxkind column. | | pg_stat_activity | Information of the archiver process and the walsender process will be output. walsender now show their latest replication command. This feature is backported to older versions. | | pg_subscription_rel | 'f' = finished table copy may be output in the srsubstate column. | | pg_type | The typtype column may output 'm' to indicate a multi-range type. | Among the modified system catalogs, the details of the major catalogs are described below.PDF page 17
##### Pg_backend_memory_contexts {#pg14-p17-pg-backend-memory-contexts} The pg_backend_memory_contexts view provides a view of the memory context usage of the server process corresponding to the current session. This view can only be viewed by users with the SUPERUSER attribute. **Table 10.** Pg\_backend\_memory\_contexts view | Column name | Data type | Description | | --- | --- | --- | | name | text | Name of the memory context. | | ident | text | Identification information of the memory context. | | parent | text | Name of the parent of this memory context. | | level | integer | Distance from TopMemoryContext. | | total_bytes | bigint | Total bytes allocated for this memory context. | | total_nblocks | bigint | Total number of blocks allocated for this memory context. | | free_bytes | bigint | Free space in bytes. | | free_chunks | bigint | Total number of free chunks. | | used_bytes | bigint | Used space in bytes. | **Example 9.** Query pg\_backend\_memory\_contexts view ```text postgres=# SELECT name, parent, level, total_bytes FROM pg_backend_memory_contexts ; name | parent | level | total_bytes --------------------------+--------------------+-------+------------ TopMemoryContext | | 0 | 68704 TopTransactionContext | TopMemoryContext | 1 | 8192 Record information cache | TopMemoryContext | 1 | 8192 TableSpace cache | TopMemoryContext | 1 | 8192 Type information cache | TopMemoryContext | 1 | 24376 Operator lookup cache | TopMemoryContext | 1 | 24576 RowDescriptionContext | TopMemoryContext | 1 | 8192 MessageContext | TopMemoryContext | 1 | 32768 Operator class cache | TopMemoryContext | 1 | 8192 … ``` ##### Pg_stat_progress_copy {#pg14-p17-pg-stat-progress-copy} This view will output the execution status of the COPY statement.PDF page 18
**Table 11.** Pg\_stat\_progress\_copy view | Column name | Data type | Description | | --- | --- | --- | | pid | integer | Process ID of backend. | | datid | oid | OID of the database | | datname | name | Name of the database | | relid | oid | OID of the table on which the COPY command is executed. | | command | text | Command name, COPY FROM or COPY TO. | | type | text | The IO types. | | bytes_processed | bigint | Number of bytes already processed. | | bytes_total | bigint | Size of source file for COPY FROM command. | | tuples_processed | bigint | Number of tuples already processed. | | tuples_excluded | bigint | Number of tuples not processed because they were excluded. | **Example 10.** Query pg\_stat\_progress\_copy view ```text postgres=> SELECT * FROM pg_stat_progress_copy ; -[ RECORD 1 ]---+---------- pid | 83409 datid | 13887 datname | postgres relid | 16385 command | COPY FROM type | FILE bytes_processed | 22806528 bytes_total | 138888897 tuples_processed | 1703999 tuples_excluded | 0 ``` ##### Pg_stat_replication_slots {#pg14-p18-pg-stat-replication-slots} This view outputs the state of the logical replication slot. To reset the contents of the view, run the pg_stat_reset_replication_slot function.PDF page 19
**Table 12.** Pg\_stat\_replication\_slots view | Column name | Data type | Description | | --- | --- | --- | | slot_name | text | Identifier for the replication slot. | | spill_txns | bigint | Number of transactions spilled to disk. | | spill_count | bigint | Number of times transactions were spilled to disk. | | spill_bytes | bigint | Amount of decoded transaction data spilled to disk. | | stream_txns | bigint | Number of in-progress transactions streamed. | | stream_count | bigint | Number of times in-progress transactions. | | stream_bytes | bigint | Amount of decoded in-progress transaction data streamed. | | total_txns | bigint | Number of decoded transactions. | | total_bytes | bigint | Amount of decoded transactions data. | | stats_reset | timestamp with time zone | timestamp with Time at which these statistics were last reset | **Example 11.** Query pg\_stat\_replication\_slots view ```text postgres=> SELECT * FROM pg_stat_replication_slots ; -[ RECORD 1 ]+---------- slot_name | sub1 spill_txns | 3 spill_count | 9 spill_bytes | 416939406 stream_txns | 0 stream_count | 0 stream_bytes | 0 total_txns | 0 total_bytes | 0 stats_reset | ``` ##### Pg_stat_wal {#pg14-p19-pg-stat-wal} Only one tuple of WAL output status is output to the pg_stat_wal view. The value of this view can be reset by pg_stat_reset_shared function with 'wal' specified. The values of the wal_write_time and wal_sync_time columns are output only when the track_wal_io_timing parameter to 'on'.PDF page 20
**Table 13.** Pg\_stat\_wal view | Column name | Data type | Description | | --- | --- | --- | | wal_records | bigint | Total number of WAL records. | | wal_fpi | bigint | Total number of WAL full page images. | | wal_bytes | numeric | Total amount of WAL generated. | | wal_buffers_full | bigint | Number of times WAL data was written to disk because WAL buffers became full | | wal_write | bigint | Number of times WAL buffers were written. | | wal_sync | bigint | Number of times WAL files were synced to disk. | | wal_write_time | double precision | Total amount of time spent writing WAL buffers | | wal_sync_time | double precision | Total amount of time spent syncing WAL files. | | stats_reset | timestamp with time zone | with Time at which these statistics were last reset. | **Example 12.** Query pg\_stat\_wal view ```text postgres=> SELECT * FROM pg_stat_wal ; -[ RECORD 1 ]----+------------------------------ wal_records | 52417581 wal_fpi | 98 wal_bytes | 4139801371 wal_buffers_full | 341903 wal_write | 342723 wal_sync | 876 wal_write_time | 0 wal_sync_time | 0 stats_reset | 2021-10-01 10:21:41.173493+09 ``` #### 3.1.2 Logical Replication {#pg14-3-1-2-logical-replication} The following features have been added to Logical Replication. ##### Logical Streaming Replication {#pg14-p20-logical-streaming-replication} Traditional logical replication transfers updates to the subscription instance on every transaction commit. PostgreSQL 14 offers a logical streaming replication feature that transfers data without waiting for the transaction to be committed. For logical streaming replication, set the streamingPDF page 21
attribute to 'on' when creating or modifying SUBSCRIPTION. The default value for the STREAMING attribute is 'off'. **Example 13.** Streaming replication setting ```text postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remhost1 dbname=postgres user=postgres password=<PDF page 22
##### Separation of initial synchronization and differential update {#pg14-p22-separation-of-initial-synchronization-and-differential-update} Data initial synchronization and update information transactions have been separated. This eliminates the need to start over from the initial data synchronization when an error occurs. An additional temporary logical replication slot is required when migrating initial data. The name of this replication slot is "pg_%u_sync_%u_%llu" (SUBSCRIPTION oid, table relid, System ID). ##### Wait Events {#pg14-p22-wait-events} The following wait events can now be confirmed. **Table 14.** Added Wait Events | Wait Event name | Description | | --- | --- | | LogicalChangesRead | Waiting for reading from a logical change file. | | LogicalChangesWrite | Waiting for writing to the logical change file. | | LogicalSubxactRead | Waiting for a read from a logical sub-transaction file. | | LogicalSubxactWrite | Waiting to write to a logical sub-transaction file. | ##### Add Messages {#pg14-p22-add-messages} The output plugin accepts a new parameter 'Messages' that controls if logical decoding messages are written into the replication stream. #### 3.1.3 Parallel Query {#pg14-3-1-3-parallel-query} SELECT part of the REFRESH MATERIALIZED VIEW statement can now be executed in parallel. The example below is a log of the auto_explain module when the REFRESH MATERIALIZED VIEW statement is executed. **Example 15.** Execution plan log ```text LOG: duration: 6649.485 ms plan: Query Text: REFRESH MATERIALIZED VIEW mview1; Gather (cost=0.00..95721.67 rows=10000000 width=12) Workers Planned: 2 -> Parallel Seq Scan on data1 (cost=0.00..95721.67 rows=4166667 width=12) ```PDF page 23
#### 3.1.4 LZ4 compression of TOAST columns {#pg14-3-1-4-lz4-compression-of-toast-columns} It is now possible to specify LZ4 as the compression method for TOAST column data. In order to perform LZ4 compression, PostgreSQL with --with-lz4 specified in the option of the 'configure' command is required. To specify the compression method for TOAST columns in the CREATE TABLE statement, specify pglz or lz4 in the COMPRESSION clause of the column attribute. The default value is determined by default_toast_compression. In the ALTER TABLE statement (or ALTER MATERIALIZED VIEW statement), the SET COMPRESSION clause can be specified in the column definition change syntax. Changing the compression method with the ALTER TABLE statement does not change the existing tuple. **Example 16.** LZ4 compression ```text postgres=> CREATE TABLE compress1(c1 INT, c2 TEXT COMPRESSION lz4) ; CREATE TABLE postgres=> \d+ compress1 Table "public.compress1" Column | Type | Collation | Nullable | Default | Storage | Compression | … --------+---------+-----------+----------+---------+----------+-------------+ … c1 | integer | | | | plain | | … c2 | text | | | | extended | lz4 | … Access method: heap postgres=> ALTER TABLE compress1 ALTER COLUMN c2 SET COMPRESSION pglz ; ALTER TABLE postgres=> \d+ compress1 Table "public.compress1" Column | Type | Collation | Nullable | Default | Storage | Compression | … --------+---------+-----------+----------+---------+----------+-------------+ … c1 | integer | | | | plain | | … c2 | text | | | | extended | pglz | … Access method: heap ``` ```sql In the CREATE TABLE (LIKE) statement, specify the "LIKE table name INCLUDING COMPRESSION" clause to copy the table definition up to the compressed definition. Compression attribute of each column can be found in the attcompression column of pg_attribute catalog. 'p' is pglz compression and 'l' is LZ4 compression. To check the compression method of the column, execute the pg_column_compression function with the column name. ```PDF page 24
**Example 17.** Pg\_column\_compression function ```text postgres=> SELECT c1, pg_column_compression(c2) FROM compress1 ; c1 | pg_column_compression -----+----------------------- 100 | pglz 200 | lz4 (2 rows) ``` #### 3.1.5 Execution Plan {#pg14-3-1-5-execution-plan} The following enhancements have been implemented to the SQL statement execution plan creation feature. ##### Asynchronous execution {#pg14-p24-asynchronous-execution} Asynchronous processing can now be executed in ForeignScan processing. Improve performance by running ForeignScan in parallel when accessing data on remote servers. To use this feature, set the async_capable option to 'on' in the postgres_fdw module (default value is 'off'). **Example 18.** Asynchronous execution setting ```text postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remhost1', port '5432', dbname 'postgres', async_capable 'on') ; CREATE SERVER ``` ```text It is displayed as Async Foreign Scan in the execution plan. In the example below, the partitions part1v1 and part1v2 contained in the partition table part1 are implemented as FOREIGN TABLE respectively. ```PDF page 25
**Example 19.** Execution plan for asynchronous execution ```text postgres=> EXPLAIN SELECT * FROM part1 ; QUERY PLAN --------------------------------------------------------------------------- Append (cost=100.00..306.87 rows=2482 width=42) -> Async Foreign Scan on part1v1 part1_1 (cost=100.00..147.23 rows=1241 width=42) -> Async Foreign Scan on part1v2 part1_2 (cost=100.00..147.23 rows=1241 width=42) (3 rows) ``` ##### Memoize {#pg14-p25-memoize} The new executor node type named Memoize has been added. The planner can include this node type in the plan to have the executor cache the results from inside the parameterized nested loop join. The use of this execution plan can be controlled by the parameter enable_memoize. The default value is 'on'. Currently, this execution plan is considered for regular joins and LATERAL type joins. **Example 20.** Memoize node ```text postgres=> EXPLAIN SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 INNER JOIN tenk1 t2 ON t1.unique1 = t2.twenty WHERE t2.unique1 < 1000 ; QUERY PLAN --------------------------------------------------------------------------- Aggregate (cost=450.10..450.11 rows=1 width=40) -> Nested Loop (cost=24.33..445.10 rows=1000 width=4) -> Bitmap Heap Scan on tenk1 t2 (cost=24.04..381.54 … Recheck Cond: (unique1 < 1000) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.79 … Index Cond: (unique1 < 1000) -> Memoize (cost=0.30..1.85 rows=1 width=4) Cache Key: t2.twenty -> Index Only Scan using tenk1_unique1 on tenk1 t1 (cost=… Index Cond: (unique1 = t2.twenty) (10 rows) ```PDF page 26
#### 3.1.6 Extended Statistics {#pg14-3-1-6-extended-statistics} It is now possible to define extended statistics on expressions. The pg_stats_ext_exprs system view has been added to reference extended statistics with expressions. **Example 21.** Extended statistics using expressions ```text postgres=> CREATE STATISTICS extstat1_data1 ON MOD(c1, 10), MOD(c2, 10) FROM data1 ; CREATE STATISTICS postgres=> ANALYZE data1 ; ANALYZE postgres=> SELECT * FROM pg_stats_ext_exprs ; -[ RECORD 1 ]----------+----------------------------------------- schemaname | public tablename | data1 statistics_schemaname | public statistics_name | extstat1_data1 statistics_owner | demo expr | mod(c1, 10) null_frac | 0 avg_width | 4 … ``` #### 3.1.7 Data types {#pg14-3-1-7-data-types} The following data types have been added / extended. ##### Maximum / minimum value of numeric data type {#pg14-p26-maximum-minimum-value-of-numeric-data-type} Infinity and -Infinity, which indicate the maximum and minimum values, can now be specified for numeric and float types. The value obtained by dividing a non-zero floating point number by the Infinity value, or the return value of zero when -Infinity is specified for the exp function and power function.PDF page 27
**Example 22.** Infinity\/\-Infinity value ```text postgres=> CREATE TABLE numeric1(c1 NUMERIC, c2 float) ; CREATE TABLE postgres=> INSERT INTO numeric1 VALUES ('Infinity', '-Infinity') ; INSERT 0 1 postgres=> SELECT * FROM numeric1 WHERE c1='Infinity' ; c1 | c2 ----------+----------- Infinity | -Infinity (1 row) postgres=> SELECT 0.1 / '-Infinity', exp('-Infinity'), power(2, '-Infinity') ; ?column? | exp | power ----------+-----+------- 0 | 0 | 0 (1 row) ``` ##### Multirange type {#pg14-p27-multirange-type} A data type has been provided that indicates multiple ranges that do not overlap. The specific data types are as follows. **Table 15.** Added data types | Data type name | Description | | --- | --- | | datemultirange | Date type range | | int4multirange | Int type range | | int8multirange | Bigint type range | | nummultirange | Numeric type range | | tsmultirange | Timestamp type range | | tstzmultirange | Timestamp with time zone type range | | anymultirange | Multi-range pseudo data type | | anycompatiblemutirange | Any pseudo-type multiple range type | The following is an example of range specification by bigint type.PDF page 28
**Example 23.** Using multirange type ```text postgres=> SELECT '{[1, 5), (10, 20]}'::int8multirange ; int8multirange ----------------- {[1,5),[11,21)} (1 row) postgres=> SELECT nummultirange( numrange(1, 10, '()'), numrange(15, 20, '[]')) ; nummultirange ------------------ {(1,10),[15,20]} (1 row) ``` ##### Jsonb type {#pg14-p28-jsonb-type} The access method by subscript for jsonb type has been enhanced. **Example 24.** Enhancement of jsonb type ```text postgres=> SELECT ('[1, "2", null]'::jsonb)[1] ; jsonb ------- "2" (1 row) postgres=> SELECT ('{"age": 25}'::jsonb)['age'] ; jsonb ------- 25 (1 row) postgres=> SELECT ('{"email":"pgsql-hackers@postgresql.org", "phone":"+3012345678"}'::jsonb) ['phone'] ; jsonb ---------------- "+3012345678" (1 row) ```PDF page 29
##### Pg_lsn type {#pg14-p29-pg-lsn-type} It is now possible to perform addition / subtraction calculations using the numeric type for values of the pg_lsn type. **Example 25.** Calculation of pg\_lsn type ```text postgres=> SELECT pg_current_wal_lsn(), pg_current_wal_lsn() + 256 ; pg_current_wal_lsn | ?column? --------------------+----------- 0/15D1F88 | 0/15D2088 (1 row) ``` ##### Point type {#pg14-p29-point-type} The operators (<<|, >>|) that get strictly below/above can now be used for point types. #### 3.1.8 BRIN indexes {#pg14-3-1-8-brin-indexes} The following operator classes are now available for BRIN indexes. ##### Bloom Filter {#pg14-p29-bloom-filter} The operator classes "{data type}_bloom_ops" that use Bloom filters have been added. The following attributes can be specified for this operator class. **Table 16.** Added attributes | Attribute name | Description | | --- | --- | | n_distinct_per_range | Specifies the estimated number of non-null values in the block range. The default value is -0.1. | | false_positive_rate | Specifies the desired false positive rate used by the index. The default value is 0.01. | **Example 26.** Using the Bloom filter in the BRIN index ```text postgres=> CREATE INDEX idx1_data1 ON data1 USING brin (c1 numeric_bloom_ops (false_positive_rate = 0.05, n_distinct_per_range = 100)) ; CREATE INDEX ```PDF page 30
##### Min/Max-Multi Index {#pg14-p30-min-max-multi-index} The operator classes "{data type}_minmax_multi_ops" have been added to store multiple minimum and maximum values. The following attributes can be specified for this operator class. **Table 17.** Added attribute | Attribute name | Description | | --- | --- | | values_per_range | Specifies the maximum number of values to be stored by the index. The default value is 32, and values from 8 to 32 can be specified. | #### 3.1.9 Wait Events {#pg14-3-1-9-wait-events} The following wait events have been added. **Table 18.** Added Wait Events | Wait Event name | Type | Description | | --- | --- | --- | | AppendReady | IPC | Waiting for subplan node preparation. | | BaseBackupRead | IO | Waiting for base backup file to be read. | | BufferIO | IPC | Waiting for buffer I/O to complete. | | BufFileTruncate | IO | Waiting for buffer file truncation. | | LogicalChangesRead | IO | Waiting to read the logical change file. | | LogicalChangesWrite | IO | Waiting to write a logical change file. | | LogicalSubxactRead | IO | Waiting to load a logical subtransaction. | | LogicalSubxactWrite | IO | Waiting for a logical subtransaction to write. | | WalReceiverExit | IPC | Waiting for the WAL receiver process to finish. | The following wait events have been retyped. **Table 19.** Wait event with changed type | Wait Event name | Before | After | Note | | --- | --- | --- | --- | | WalReceiverWaitStart | Client | IPC | | #### 3.1.10 Stored Procedure {#pg14-3-1-10-stored-procedure} The following functions have been added.PDF page 31
##### SPI_execute_extended {#pg14-p31-spi-execute-extended} This API replaces the deprecated SPI_execute_plan_with_paramlist. It is possible to specify the reowner used to fix the execution plan. Syntax ```text int SPI_execute_extended(const char *src, const SPIExecuteOptions *options) ``` ##### SPI_cursor_parse_open {#pg14-p31-spi-cursor-parse-open} Set up a cursor using a query string and parameters. Syntax ```text Portal SPI_cursor_parse_open(const char *name, const char *src, const SPIParseOpenOptions *options) ``` ##### SPI_scroll_cursor_fetch {#pg14-p31-spi-scroll-cursor-fetch} Fetch some rows from a cursor Syntax ```text void SPI_scroll_cursor_fetch(Portal portal, FetchDirection direction, long count) ``` #### 3.1.11 Pg_hba.conf file {#pg14-3-1-11-pg-hba-conf-file} By adding a backslash (\) at the end of a line, a single setting can now be described across multiple lines. The backslash must be the last character on the line. **Example 27.** Line breaks in the pg\_hba\.conf file ```bash # IPv4 local connections: host all all 127.0.0.1/32 trust host postgres postgres 192.168.1.219/32 \ md5 ```PDF page 32
#### 3.1.12 LIBPQ library {#pg14-3-1-12-libpq-library} The following extensions have been implemented in the libpq library. ##### Connection string target_session_attrs {#pg14-p32-connection-string-target-session-attrs} The following values have been added to the settings of the target_session_attrs parameter. **Table 20.** Added setting values | Value | Description | | --- | --- | | read-only | The destination database cannot be updated. The opposite of read-write. | | primary | Connect to the primary instance of streaming replication. | | standby | Connect to a standby instance of streaming replication. | | prefer-standby | Connect if a standby instance exists. If the standby instance does not exist, connect to the primary instance. | ##### Connection string sslcrldir {#pg14-p32-connection-string-sslcrldir} It is now possible to set the sslcrldir to specify the CRL storage directory for SSL connections. It can also be specified by the environment variable PGSSLCRLDIR. ##### Connection string sslsni {#pg14-p32-connection-string-sslsni} Configure Server Name Indication (SNI) for SSL connections. It can also be specified by the environment variable PGSSLSNI. ##### Pipeline mode {#pg14-p32-pipeline-mode} The libpq pipeline mode allows applications to avoid FE/BE protocol synchronization messages implicitly included in the old libpq API after each query. The following functions have been added. **Table 21.** Added functions for pipeline mode | Function name | Description | | --- | --- | | PQpipelineStatus | Returns the status of the current pipeline mode. | | PQenterPipelineMode | Enter pipeline mode. | | PQexitPipelineMode | Exit pipeline mode. | | PQpipelineSync | Request pipeline synchronization. | ##### Debug log {#pg14-p32-debug-log} The PQsetTraceFlags function has been added.PDF page 33
##### Sync message {#pg14-p33-sync-message} The PQsendFlushRequest function has been added. #### 3.1.13 ECPG {#pg14-3-1-13-ecpg} DECLARE STATEMENT statement can now be specified for ECPG. **Example 28.** DECLARE STATEMENT statement ```text EXEC SQL BEGIN DECLARE SECTION; char *selectStr = "SELECT c1 FROM data1"; long f1; EXEC SQL END DECLARE SECTION; int main() { EXEC SQL CONNECT TO postgres USER postgres; EXEC SQL DECLARE stmt1 STATEMENT; EXEC SQL PREPARE stmt1 FROM :selectStr; EXEC SQL DECLARE cur1 CURSOR FOR stmt1; EXEC SQL OPEN cur1; … } ``` #### 3.1.14 Roles {#pg14-3-1-14-roles} The following roles have been added. ##### Pg_database_owner {#pg14-p33-pg-database-owner} The pg_database_owner role has been added. Members of this role implicitly consist of the database owner. It is usually expected to be used in a template database. ##### Pg_read_all_data / pg_write_all_data {#pg14-p33-pg-read-all-data-pg-write-all-data} Added pg_read_all_data role, which allows reading all objects in the database, and pg_write_all_data role, which allows writing.PDF page 34
#### 3.1.15 Logfile {#pg14-3-1-15-logfile} Additional information is now output to the log file. ##### Log_autovacuum_min_duration {#pg14-p34-log-autovacuum-min-duration} Detailed information for each index is now output to the automatic VACUUM log (log_autovacuum_min_duration). **Example 29.** Index information for automatic VACUUM log ```sql LOG: automatic vacuum of table "postgres.public.data1": index scans: 1 pages: 0 removed, 5406 remain, 0 skipped due to pins, 0 skipped frozen tuples: 500000 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 566 buffer usage: 21775 hits, 0 misses, 1 dirtied index scan needed: 5406 pages from table (100.00% of total) had 500000 dead item identifiers removed index "idx1_data1": pages: 2745 in total, 0 newly deleted, 0 currently deleted, 0 reusable index "idx2_data1": pages: 2745 in total, 0 newly deleted, 0 currently deleted, 0 reusable avg read rate: 20.330 MB/s, avg write rate: 20.239 MB/s … ``` ##### Log_connection {#pg14-p34-log-connection} Additional information about authentication is output to the connection log (log_connection). **Example 30.** Authentication information log at the time of connection ```text LOG: connection received: host=192.168.1.219 port=51524 LOG: connection authenticated: identity="demo" method=md5 (/usr/local/pgsql/data/pg_hba.conf:91) LOG: connection authorized: user=demo database=postgres application_name=psql ```PDF page 35
#### 3.1.16 Process name {#pg14-3-1-16-process-name} The information is appended to some process names in the instance. ##### WAL Receiver {#pg14-p35-wal-receiver} The status is now output to the process name of the wal receiver process. **Example 31.** Process name of WAL receiver ```bash $ ps -ef|grep receiver | grep -v grep postgres 93255 93249 0 14:34 ? 00:00:01 postgres: walreceiver streaming 0/5D744F8 $ ``` ##### WAL sender {#pg14-p35-wal-sender} The replication command name is now output to the process name of the WAL sender process for logical replication. **Example 32.** Process name of WAL sender ```bash $ ps -ef|grep sender | grep -v grep postgres 92995 92944 0 14:28 ? 00:00:00 postgres: walsender postgres [local] START_REPLICATION $ ``` #### 3.1.17 Regular Expression {#pg14-3-1-17-regular-expression} The complement-class escapes \D, \S, \W are now allowed within bracket expressions. Also, 'word' has been added as a character class. This is the equivalent of \w. **Example 33.** \&\#x27\;Word\&\#x27\; character class ```text postgres=> SELECT 'abc def' ~ '[[:word:]] [[:word:]]' ; ?column? ---------- t (1 row) ```PDF page 36
#### 3.1.18 Text search {#pg14-3-1-18-text-search} The languages for text search have been increased. The following languages have been added to PostgreSQL 14. **Table 22.** Added languages | Language name | Description | | --- | --- | | armenian | Armenian language | | basque | Basque language | | catalan | Catalan language | | hindi | Hindi language | | serbian | Serbian language | | yiddish | Yiddish language | #### 3.1.19 Test module {#pg14-3-1-19-test-module} The PL/Sample module, which can be used as a template for procedure languages, has been added.PDF page 37
**Example 34.** PL\/Sample module ```bash $ cd src/test/modules/plsample $ ls -l total 24 drwxrwxr-x. 2 postgres postgres 26 Sep 28 06:00 expected -rw-r--r--. 1 postgres postgres 440 Sep 28 05:57 Makefile -rw-r--r--. 1 postgres postgres 469 Sep 28 05:57 plsample--1.0.sql -rw-r--r--. 1 postgres postgres 5046 Sep 28 05:57 plsample.c -rw-r--r--. 1 postgres postgres 177 Sep 28 05:57 plsample.control -rw-r--r--. 1 postgres postgres 228 Sep 28 05:57 README drwxrwxr-x. 2 postgres postgres 26 Sep 28 06:00 sql ``` #### 3.1.20 Maximum password length {#pg14-3-1-20-maximum-password-length} The maximum length limit for password strings used for client authentication has been removed. #### 3.1.21 LLVM {#pg14-3-1-21-llvm} Now supports LLVM 12.