PDF page 38
This section explains new features related to SQL statements. #### 3.2.1 ALTER CURRENT_ROLE {#pg14-3-2-1-alter-current-role} The statements that can specify CURRENT_USER clause, such as the ALTER AGGREGATE, ALTER CONVERSION, and ALTER DATABASE statements, can now also specify a CURRENT_ROLE clause. **Example 35.** ALTER DATABASE statement ```text postgres=# ALTER DATABASE demodb OWNER TO CURRENT_ROLE ; ALTER DATABASE ``` #### 3.2.2 ALTER SUBSCRIPTION {#pg14-3-2-2-alter-subscription} The syntax for adding/removing publications for existing subscriptions has been added. Specify ADD PUBLICATION clause or DROP PUBLICATION clause in the ALTER SUBSCRIPTION statement. Syntax ```sql ALTER SUBSCRIPTION subscription_name [ ADD | DROP ] PUBLICATION publication_name ``` #### 3.2.3 ALTER TABLE {#pg14-3-2-3-alter-table} CONCURRENTLY clause can now be specified in the ALTER TABLE DETACH PARTITION statement. If CONCURRENTLY is specified, it will be executed at a lower lock level to avoid blocking other sessions that may be accessing the partitioned table. In this mode, two transactions are used internally and cannot be executed within a transaction block. Also, it cannot be executed on a partitioned table that contains the default partition. If FINALIZE is specified, the previous DETACH CONCURRENTLY call that was canceled or interrupted is completed.PDF page 39
Syntax ```sql ALTER TABLE table_name DETACH PARTITION partition_name [ FINALIZE | CONCURRENTLY ] ``` #### 3.2.4 COPY FREEZE {#pg14-3-2-4-copy-freeze} Visibility Map is now updated with the execution of COPY FREEZE statement. **Example 36.** Check visibility map after executing COPY FREEZE statement ```text postgres=# BEGIN ; BEGIN postgres=*# TRUNCATE TABLE visible1 ; TRUNCATE TABLE postgres=*# COPY visible1 FROM '/home/postgres/visible1.csv' CSV FREEZE ; COPY 3 postgres=*# COMMIT ; COMMIT postgres=# SELECT * FROM pg_visibility_map('visible1', 0) ; all_visible | all_frozen -------------+------------ t | t (1 row) ``` #### 3.2.5 CREATE INDEX {#pg14-3-2-5-create-index} The INCLUDE clause can now be specified when creating SP-GiST indexes.PDF page 40
**Example 37.** INCLUDE clause with SP\-GiST index creation ```text postgres=> CREATE INDEX idx1_gist1 ON gist1 USING spgist (c1) INCLUDE (c2) ; CREATE INDEX postgres=> \d+ idx1_gist1 Index "public.idx1_gist1" Column | Type | Key? | Definition | Storage | Stats target --------+------+------+------------+----------+-------------- c1 | text | yes | c1 | extended | c2 | text | no | c2 | extended | spgist, for table "public.gist1" ``` #### 3.2.6 CREATE PROCEDURE/FUNCTION {#pg14-3-2-6-create-procedure-function} The following enhancements have been implemented for the CREATE PROCEDURE and CREATE FUNCTION statements. ##### OUT clause {#pg14-p40-out-clause} It is now possible to specify the OUT clause as a parameter of PROCEDURE.PDF page 41
**Example 38.** CREATE PROCEDURE statement ```text postgres=> CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y ; prod := x * y ; END $$ LANGUAGE plpgsql ; CREATE PROCEDURE postgres=> SELECT proargmodes FROM pg_proc WHERE proname='sum_n_product' ; proargmodes ------------- {i,i,o,o} (1 row) postgres=> CALL sum_n_product(2, 4, NULL, NULL) ; sum | prod -----+------ 6 | 8 (1 row) ``` ##### LANGUAGE SQL {#pg14-p41-language-sql} When the LANGUAGE SQL clause is specified in the CREATE FUNCTION statement and CREATE PROCEDURE statement, it is not necessary to enclose it in character literals, and SQL standard-compliant syntax is supported. The source for FUNCTION / PROCEDURE created with this syntax is stored in the prosqlbody column of the pg_proc catalog. **Example 39.** LANGUAGE SQL ```text postgres=> CREATE PROCEDURE insert_data1(id INTEGER, val TEXT) LANGUAGE SQL BEGIN ATOMIC INSERT INTO data1 VALUES (id, val) ; INSERT INTO data1 VALUES (id, val) ; END ; CREATE PROCEDURE ```PDF page 42
FUNCTION and PROCEDURE created in the standard format are aware of the dependencies of the objects they are used on. For this reason, when the DROP TABLE CASCADE statement is executed, FUNCTION with dependencies will be dropped at the same time. **Example 40.** DROP TABLE CASCADE statement ```text postgres=> CREATE PROCEDURE insert_data1(id NUMERIC, val TEXT) LANGUAGE SQL BEGIN ATOMIC INSERT INTO data1 VALUES (id, val); END ; CREATE PROCEDURE postgres=> DROP TABLE data1 ; ERROR: cannot drop table data1 because other objects depend on it DETAIL: function insert_data1(numeric,text) depends on table data1 HINT: Use DROP ... CASCADE to drop the dependent objects too. postgres=> DROP TABLE data1 CASCADE ; NOTICE: drop cascades to function insert_data1(numeric,text) DROP TABLE ``` #### 3.2.7 CREATE TABLE {#pg14-3-2-7-create-table} It is now possible to specify different values of COLLATION for partition boundaries. **Example 41.** CREATE TABLE statement ```text postgres=> CREATE TABLE part_coll (c1 TEXT) PARTITION BY RANGE (c1 COLLATE "POSIX") ; CREATE TABLE postgres=> CREATE TABLE part_coll_p1 PARTITION OF part_coll FOR VALUES FROM ('a' collate "C") to ('g') ; CREATE TABLE ``` #### 3.2.8 CREATE TRIGGER {#pg14-3-2-8-create-trigger} The OR REPLACE clause can now be specified in the CREATE TRIGGER statement.PDF page 43
**Example 42.** CREATE OR REPLACE TRIGGER statement ```text postgres=> CREATE OR REPLACE TRIGGER data1_trig1 AFTER INSERT ON data1 FOR EACH ROW EXECUTE FUNCTION data1_ins_func1 () ; CREATE TRIGGER ``` #### 3.2.9 CREATE TYPE {#pg14-3-2-9-create-type} SUBSCRIPT clause that specifies the MULTIRANGE_TYPE_NAME clause and generic subscript access for the function you specify more than one range type has been added to the CREATE TYPE statement. Syntax ```sql CREATE TYPE name AS RANGE ( SUBTYPE = subtype [ , MULTIRANGE_TYPE_NAME = multirange_type_name ] ) ``` Syntax ```sql CREATE TYPE name ( INPUT = input_function, OUTPUT = output_function [ , SUBSCRIPT = subscript_function ] ) ``` #### 3.2.10 GRANT / REVOKE {#pg14-3-2-10-grant-revoke} It is now possible to specify the GRANTED BY clause for granting roles with GRANT and REVOKE statements in accordance with the SQL standard. The GRANTED BY clause can only specify the current role that executes the GRANT statement, so it behaves the same as in previous versions. Syntax ```text GRANT role_name [, …] TO role_specification [ WITH ADMIN OPTION ] [ GRANTED BY role_specification ] REVOKE [ ADMIN OPTION FOR ] role_name [, …] FROM role_specification [ GRANTED BY role_specification ] [ CASCADE | RESTRICT ] ```PDF page 44
**Example 43.** GRANTED BY clause ```text postgres=> GRANT SELECT ON data1 TO user1 GRANTED BY CURRENT_USER ; GRANT postgres=> GRANT SELECT ON data1 TO user1 GRANTED BY postgres ; ERROR: grantor must be current user ``` #### 3.2.11 INSERT {#pg14-3-2-11-insert} The following enhancements have been implemented in the INSERT statement. ##### ON CONFLICT clause {#pg14-p44-on-conflict-clause} It is now possible to qualify a table name for a column specification in the WHERE clause in the ON CONFLICT clause. **Example 44.** Table qualified with ON CONFLICT clause ```text postgres=> INSERT INTO data1 AS d1 VALUES (10, 'data1') ON CONFLICT (c1) WHERE d1.c2 = 'data1' DO NOTHING ; INSERT 0 1 ``` ##### GENERATED ALWAYS clause {#pg14-p44-generated-always-clause} It is now possible to specify multi-tuple DEFAULT clause when executing an INSERT statement on a table that has a column with a GENERATED ALWAYS clause. **Example 45.** Multiple tuple DEFAULT clause ```text postgres=> CREATE TABLE gen1(c1 INT PRIMARY KEY, c2 INT GENERATED ALWAYS AS (c1+1) STORED) ; CREATE TABLE postgres=> INSERT INTO gen1 VALUES (100, DEFAULT),(200, DEFAULT) ; INSERT 0 2 postgres=> SELECT * FROM gen1 ; c1 | c2 -----+----- 100 | 101 200 | 201 (2 rows) ```PDF page 45
#### 3.2.12 REINDEX {#pg14-3-2-12-reindex} The following enhancements have been implemented to the REINDEX statement. ##### Partitioned table {#pg14-p45-partitioned-table} It is now possible to specify a partitioned table for the REINDEX statement. **Example 46.** Execute REINDEX statement on a partitioned table ```text postgres=> CREATE TABLE part1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ; CREATE TABLE postgres=> CREATE TABLE part1v1 PARTITION OF part1 FOR VALUES FROM (0) TO (100000) ; CREATE TABLE postgres=> CREATE TABLE part1v2 PARTITION OF part1 FOR VALUES FROM (100000) TO (200000) ; CREATE TABLE postgres=> CREATE INDEX idx1_part1 ON part1(c1) ; CREATE INDEX postgres=> REINDEX TABLE part1 ; REINDEX ``` ##### Specifying the tablespace {#pg14-p45-specifying-the-tablespace} Tablespaces for indexes can now be specified in the optional TABLESPACE clause.PDF page 46
**Example 47.** Specifying the tablespace ```text postgres=> REINDEX (TABLESPACE ts1, VERBOSE) INDEX idx1_data1 ; INFO: index "idx1_data1" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s REINDEX postgres=> REINDEX (TABLESPACE ts1, VERBOSE) TABLE data1 ; INFO: index "data1_pkey" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "idx1_data1" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "pg_toast_16385_index" was reindexed DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s REINDEX ``` #### 3.2.13 VACUUM {#pg14-3-2-13-vacuum} The following enhancements have been implemented in VACUUM. ##### TOAST table {#pg14-p46-toast-table} The PROCESS_TOAST option can now be specified in the VACUUM statement. This option performs VACUUM processing on the TOAST table. This parameter is enabled by default. To disable processing of the TOAST table, specify the PROCESS_TOAST FALSE option. **Example 48.** VACUUM \(PROCESS\_TOAST\) statement ```text postgres=> VACUUM (PROCESS_TOAST FALSE, VERBOSE) data1 ; INFO: vacuuming "public.data1" INFO: "data1": found 0 removable, 37 nonremovable row versions in 1 out of 5406 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 518 There were 38 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM ```PDF page 47
##### Failsafe Mechanism {#pg14-p47-failsafe-mechanism} If it determines that the table's relfrozenxid or relminmxid is dangerously past, VACUUM bypasses non-freeze processing. The parameters vacuum_failsafe_age and vacuum_multixact_failsafe_age have been added as these thresholds. ##### Bypass index vacuum {#pg14-p47-bypass-index-vacuum} Allow vacuum to skip index vacuuming when the number of removable index entries is insignificant. The vacuum parameter INDEX_CLEANUP has a new default of auto that enables this optimization. **Example 49.** Autovacuum log at bypass index vacuum ```sql LOG: automatic vacuum of table "postgres.public.data1": index scans: 0 pages: 0 removed, 5406 remain, 0 skipped due to pins, 0 skipped frozen tuples: 15000 removed, 985000 remain, 0 are dead but not yet removable, oldest xmin: 880 index scan bypassed: 82 pages from table (1.52% of total) have 15000 dead item identifiers avg read rate: 0.095 MB/s, avg write rate: 0.191 MB/s buffer usage: 10793 hits, 2 misses, 4 dirtied WAL usage: 5407 records, 1 full page images, 356929 bytes … ``` #### 3.2.14 SELECT {#pg14-3-2-14-select} The following enhancements have been implemented to the SELECT statement. ##### Support for reserved words {#pg14-p47-support-for-reserved-words} In PostgreSQL 14, AS clause is no longer required even when a reserved word is specified as an alias for a column in the SELECT statement. The number of reserved words that cannot be used for aliases has been significantly reduced. **Example 50.** No AS clause required ```text postgres=> SELECT loc analyze FROM dept ; analyze --------- (0 rows) ```PDF page 48
##### GROUP BY DISTINCT {#pg14-p48-group-by-distinct} It is now possible to specify DISTINCT to eliminate duplicates in the GROUP BY clause. It is also possible to specify ALL to output all (the default behavior is ALL). **Example 51.** GROUP BY DISTINCT ```text postgres=> SELECT a, b, c FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c) GROUP BY ALL ROLLUP(a, b), ROLLUP(a, c) ORDER BY a, b, c ; a | b | c ---+---+--- 1 | 2 | 3 1 | 2 | … | | (25 rows) postgres=> SELECT a, b, c FROM (VALUES (1, 2, 3), (4, NULL, 6), (7, 8, 9)) AS t (a, b, c) GROUP BY DISTINCT ROLLUP(a, b), ROLLUP(a, c) ORDER BY a, b, c ; a | b | c ---+---+--- 1 | 2 | 3 1 | 2 | … | | (13 rows) ``` ##### SEARCH clause / CYCLE clause {#pg14-p48-search-clause-cycle-clause} The SEARCH and CYCLE clauses defined in the SQL standard can now be specified in the WITH clause of recursive SQL statements. The CYCLE clause is used for closed circuit detection, and the SEARCH clause can be used to specify depth-first search (DEPTH FIRST) or breadth-first search (BREADTH FIRST).PDF page 49
**Example 52.** SEARCH clause \(DEPTH FIRST\) ```text postgres=> CREATE TABLE graph0(f INT, t INT, label TEXT) ; CREATE TABLE postgres=> INSERT INTO graph0 VALUES (1, 2, 'arc 1 -> 2'), (1, 3, 'arc 1 -> 3'), (2, 3, 'arc 2 -> 3'), (1, 4, 'arc 1 -> 4'), (4, 5, 'arc 4 -> 5') ; INSERT 0 5 postgres=> WITH RECURSIVE search_graph(f, t, label) AS ( SELECT * FROM graph0 g UNION ALL SELECT g.* FROM graph0 g, search_graph sg WHERE g.f = sg.t ) SEARCH DEPTH FIRST BY f, t SET seq SELECT * FROM search_graph ORDER BY seq ; f | t | label | seq ---+---+------------+------------------- 1 | 2 | arc 1 -> 2 | {"(1,2)"} 2 | 3 | arc 2 -> 3 | {"(1,2)","(2,3)"} 1 | 3 | arc 1 -> 3 | {"(1,3)"} 1 | 4 | arc 1 -> 4 | {"(1,4)"} 4 | 5 | arc 4 -> 5 | {"(1,4)","(4,5)"} 2 | 3 | arc 2 -> 3 | {"(2,3)"} 4 | 5 | arc 4 -> 5 | {"(4,5)"} (7 rows) ```PDF page 50
**Example 53.** SEARCH clause \(BREADTH FIRST\) ```text postgres=> WITH RECURSIVE search_graph(f, t, label) AS ( SELECT * FROM graph0 g UNION ALL SELECT g.* FROM graph0 g, search_graph sg WHERE g.f = sg.t ) SEARCH BREADTH FIRST BY f, t SET seq SELECT * FROM search_graph ORDER BY seq ; f | t | label | seq ---+---+------------+--------- 1 | 2 | arc 1 -> 2 | (0,1,2) 1 | 3 | arc 1 -> 3 | (0,1,3) 1 | 4 | arc 1 -> 4 | (0,1,4) 2 | 3 | arc 2 -> 3 | (0,2,3) 4 | 5 | arc 4 -> 5 | (0,4,5) 2 | 3 | arc 2 -> 3 | (1,2,3) 4 | 5 | arc 4 -> 5 | (1,4,5) (7 rows) ```PDF page 51
**Example 54.** CYCLE clause ```text postgres=> CREATE TABLE graph1(f INT, t INT, label TEXT) ; CREATE TABLE postgres=> INSERT INTO graph1 VALUES (1, 2, 'arc 1 -> 2'), (1, 3, 'arc 1 -> 3'), (2, 3, 'arc 2 -> 3'), (1, 4, 'arc 1 -> 4'), (4, 5, 'arc 4 -> 5'), (5, 1, 'arc 5 -> 1') ; INSERT 0 6 postgres=> WITH RECURSIVE search_graph(f, t, label) AS ( SELECT * FROM graph1 g UNION ALL SELECT g.* FROM graph1 g, search_graph sg WHERE g.f = sg.t ) CYCLE f, t SET is_cycle TO TRUE DEFAULT FALSE USING PATH SELECT * FROM search_graph ; f | t | label | is_cycle | path ---+---+------------+----------+--------------------------------- 1 | 2 | arc 1 -> 2 | f | {"(1,2)"} 1 | 3 | arc 1 -> 3 | f | {"(1,3)"} 2 | 3 | arc 2 -> 3 | f | {"(2,3)"} 1 | 4 | arc 1 -> 4 | f | {"(1,4)"} … 5 | 1 | arc 5 -> 1 | t | {"(5,1)","(1,4)","(4,5)","(5,1)"} 2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"} (25 rows) ``` ##### USING AS clause {#pg14-p51-using-as-clause} It is now possible to use aliases for columns specified in the JOIN USING clause. Aliases are also available in the SELECT clause for the columns specified in the JOIN. This is a feature defined in SQL:2016 feature F404 "Range variable for common column names".PDF page 52
**Example 55.** JOIN USING AS clause ```text postgres=> SELECT d1.c1, x.c1, x.c2 FROM data1 d1 JOIN data2 d2 USING (c1, c2) AS x ; ``` #### 3.2.15 TRUNCATE {#pg14-3-2-15-truncate} TRUNCATE statements can now be executed in FOREIGN TABLE if supported by FOREIGN DATA WRAPPER. The postgres_fdw module now supports the TRUNCATE statement. **Example 56.** TRUNCATE statement for FOREIGN TABLE ```text postgres=> CREATE FOREIGN TABLE remote1(c1 NUMERIC, c2 VARCHAR(10)) SERVER remsvr1 ; CREATE FOREIGN TABLE postgres=> TRUNCATE TABLE remote1 ; TRUNCATE TABLE ``` #### 3.2.16 Functions {#pg14-3-2-16-functions} The following functions have been added / extended. ##### Bit_count {#pg14-p52-bit-count} Outputs the number of bits that are turned on in the specified bit string or bytea type. Syntax ```text bigint BIT_COUNT(bit | bytea) ``` **Example 57.** Bit\_count function ```text postgres=> SELECT bit_count(B'0101011001'::bit(10)) ; bit_count ----------- 5 (1 row) ```PDF page 53
##### Bit_xor {#pg14-p53-bit-xor} An aggregate function bit_xor has been added to calculate the XOR of a bit string. The bit_or and bit_and functions are already provided. Syntax ```text integer | bit BIT_XOR(bigint | bit | integer | smallint) ``` **Example 58.** Bit\_xor function ```text postgres=> SELECT SUM(c1), BIT_XOR(c1) FROM data1 ; sum | bit_xor ------------+--------- 5000050000 | 100000 (1 row) ``` ##### Date_bin {#pg14-p53-date-bin} Truncates the entered timestamp at the specified interval. It is similar to the date_trunc function, but can be truncated at any interval. Syntax ```text timestamp with time zone DATE_BIN(stride interval, source timestamp with time zone, origin timestamp with time zone) ``` **Example 59.** Date\_bin function ```text postgres=> SELECT date_bin('15 minutes', TIMESTAMP'2020-02-11 15:44:17', TIMESTAMP'2001-01-01 00:02:30') ; date_bin --------------------- 2020-02-11 15:32:30 (1 row) ``` ##### Make_timestamp / make_timestamptz {#pg14-p53-make-timestamp-make-timestamptz} These functions can now specify a negative value indicating the BC to the specified year.PDF page 54
**Example 60.** Create a date BC ```text postgres=> SELECT make_timestamp(-2, 12, 25, 10, 20, 30) ; make_timestamp ------------------------ 0002-12-25 10:20:30 BC (1 row) ``` ##### Split_part {#pg14-p54-split-part} It is now possible to specify a negative value for the third parameter of the split_part function. If a negative value is specified, the part from the right side of the string specified in the first parameter can be cut off. **Example 61.** Split\_part function ```text postgres=> SELECT split_part('www.postgresql.org', '.', -1) ; split_part ------------ org (1 row) ``` ##### Substring {#pg14-p54-substring} It now conforms to the syntax formulated in the SQL standard, SQL:2003. Syntax ```text text SUBSTRING(search_text text SIMILAR pattern text ESCAPE escape text) ``` The execution result is the same as SUBSTRING(text FROM pattern FOR escape). **Example 62.** Substring function ```text postgres=> SELECT substring('Thomas' SIMILAR '%#"o_a#"_' ESCAPE '#') ; substring ----------- oma (1 row) ```PDF page 55
##### Trim / ltrim / rtrim {#pg14-p55-trim-ltrim-rtrim} The ltrim and rtrim functions can now be used for bytea types, and the trim function can now specify LEADING and TRAILING clauses for bytea types. **Example 63.** Rtrim function ```text postgres=> SELECT rtrim('abc'::bytea, 'c'::bytea) ; rtrim -------- \x6162 (1 row) postgres=> \df rtrim List of functions Schema | Name | Result data type | Argument data types | Type ------------+-------+------------------+---------------------+--- pg_catalog | rtrim | bytea | bytea, bytea | func pg_catalog | rtrim | text | text | func pg_catalog | rtrim | text | text, text | func (3 rows) ``` ##### Trim_array {#pg14-p55-trim-array} The function trim_array has been added to remove the end of an array. Syntax ```text anyarray TRIM_ARRAY(array anyarray, n integer) ``` **Example 64.** Trim\_array function ```text postgres=> SELECT trim_array(ARRAY[1, 2, 3, 4, 5], 2) ; trim_array ------------ {1,2,3} (1 row) ``` ##### Unistr {#pg14-p55-unistr} The unistr function evaluates the specified Unicode escaped string.PDF page 56
Syntax ```text text UNISTR(text) ``` **Example 65.** Unistr function ```text postgres=> SELECT unistr('\0441\+00043B\u043E\043D') ; unistr -------- слон (1 row) ``` ##### String_to_table {#pg14-p56-string-to-table} The string_to_table function separates the string with the specified separator and converts it to a table. Syntax ```text setoff STRING_TO_TABLE(string text, delimiter text [, null_string text]) ``` **Example 66.** String\_to\_table function ```text postgres=> SELECT string_to_table('ABC-+-DEF-+-GHI', '-+-', 'abc') ; string_to_table ----------------- ABC DEF GHI (3 rows) ``` ##### Jsonb type ISO 8601 format date {#pg14-p56-jsonb-type-iso-8601-format-date} Supports ISO 8601 date formats. This feature has been backported since PostgreSQL 13.1.PDF page 57
**Example 67.** Date format in ISO 8601 format ```sql postgres => SELECT jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()') ; jsonb_path_query ----------------------------- "2017-03-10T12:34:56+03:10" (1 row) ``` ##### Pg_get_wal_pause_state {#pg14-p57-pg-get-wal-pause-state} Returns the paused state of recovery. Returns "not paused" if no pause was requested, "pause requested" if pause was requested but recovery is not paused, and "paused" if recovery is paused. In PostgreSQL 14, recovery is now paused if the required parameter values are missing. Syntax ```text text PG_GET_WAL_REPLAY_PAUSE_STATE() ``` **Example 68.** Pg\_get\_wal\_pause\_state function ```text postgres=# SELECT pg_wal_replay_pause() ; pg_wal_replay_pause --------------------- (1 row) postgres=# SELECT pg_get_wal_replay_pause_state() ; pg_get_wal_replay_pause_state ------------------------------- paused (1 row) ``` ##### Pg_log_backend_memory_contexts {#pg14-p57-pg-log-backend-memory-contexts} The pg_log_backend_memory_contexts function outputs the memory status of the process with the specified ID as a LOG level message.PDF page 58
Syntax ```text bool PG_LOG_BACKEND_MEMORY_CONTEXTS(pid integer) ``` **Example 69.** Pg\_log\_backend\_memory\_contexts function ```text postgres=# SELECT pg_log_backend_memory_contexts(pg_backend_pid()) ; LOG: logging memory contexts of PID 67300 STATEMENT: SELECT pg_log_backend_memory_contexts(pg_backend_pid()); LOG: level: 0; TopMemoryContext: 68704 total in 5 blocks; 14416 free (10 chunks); 54288 used … ``` ##### Pg_terminate_backend {#pg14-p58-pg-terminate-backend} The pg_terminate_backend function now has a parameter to specify a timeout. The function will return true if the session exits within the timeout. Syntax ```text boolean PG_TERMINATE_BACKEND(pid integer, timeout bigint DEFAULT 0) ``` ##### Pg_xact_commit_timestamp_origin {#pg14-p58-pg-xact-commit-timestamp-origin} This function outputs the time stamp and replication origin for the transaction ID. There is a need to specify 'on' the track_commit_timestamp the execution of this function. Syntax ```text record PG_XACT_COMMITTED_TIMESTAMP_ORIGIN(xid xid, OUT timestamp timestamp with time zone, OUT roident oid) ```PDF page 59
**Example 70.** Pg\_xact\_commit\_timestamp\_origin function ```text postgres=# SHOW track_commit_timestamp ; track_commit_timestamp ------------------------ on (1 row) postgres=# SELECT * FROM pg_xact_commit_timestamp_origin(txid_current()::text::xid) ; timestamp | roident -----------+--------- | (1 row) ``` ##### Pg_last_committed_xact {#pg14-p59-pg-last-committed-xact} The function now outputs a roident column indicating the replication origin in the execution result. Syntax ```text record PG_LAST_COMMITTED_XACT() ``` **Example 71.** Pg\_last\_committed\_xact function ```text postgres=# SELECT * FROM pg_last_committed_xact() ; xid | timestamp | roident -----+-------------------------------+--------- 515 | 2021-10-01 11:08:58.549866+09 | 0 (1 row) ``` ##### Pg_get_catalog_foreign_keys {#pg14-p59-pg-get-catalog-foreign-keys} Added pg_get_catalog_foreign_keys function to retrieve information about foreign keys defined in a system catalog. This function can be run by general users. ##### Pg_create_logical_replication_slot {#pg14-p59-pg-create-logical-replication-slot} The parameter twophase has been added to support two-phase commit.PDF page 60
**Example 72.** Pg\_create\_logical\_replication\_slot function ```text postgres=# \dfS 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, OUT slot_name name, OUT lsn pg_lsn Type | func ```