{"id":359431,"date":"2024-05-21T00:32:28","date_gmt":"2024-05-21T00:32:28","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=359431"},"modified":"-0001-11-30T00:00:00","modified_gmt":"2024-05-21T00:32:28","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=359431","title":{"rendered":"PostgreSQL 17: Part 2 or Commitfest 2023-09<\/span>"},"content":{"rendered":"
<\/div>\n
\n
\n
\n
\n

<\/p>\n

<\/p>\n

We continue to follow the news of the PostgreSQL 17 development. Let’s find out what the September commitfest brings to the table.<\/p>\n

<\/p>\n

If you missed our July commitfest review, you can check it out here: 2023-07<\/a>.<\/p>\n

<\/p>\n

Removed the parameter old_snapshot_threshold<\/em><\/a>
New parameter event_triggers<\/em><\/a>
New functions to_bin and to_oct<\/a>
New system view pg_wait_events<\/a>
EXPLAIN: a JIT compilation time counter for tuple deforming<\/a>
Planner: better estimate of the initial cost of the WindowAgg node<\/a>
pg_constraint: NOT NULL constraints<\/a>
Normalization of CALL, DEALLOCATE and two-phase commit control commands<\/a>
unaccent: the target rule expressions now support values in quotation marks<\/a>
COPY FROM: FORCE_NOT_NULL * and FORCE_NULL *<\/a>
Audit of connections without authentication<\/a>
pg_stat_subscription: new column worker_type<\/a>
The behaviour of pg_promote in case of unsuccessful switchover to a replica<\/a>
Choosing the disk synchronization method in server utilities<\/a>
pg_restore: optimization of parallel recovery of a large number of tables<\/a>
pg_basebackup and pg_receivewal with the parameter dbname<\/a>
Parameter names for a number of built-in functions<\/a>
psql: \\watch min_rows<\/a><\/p>\n

<\/a>
<\/a> <\/p>\n

Removed the parameter old_snapshot_threshold<\/em><\/a><\/h2>\n

<\/p>\n

commit: f691f5b8<\/a><\/p>\n

<\/p>\n

The parameter old_snapshot_threshold<\/em> first appeared in patch 9.6. The parameter defines the time threshold for a snapshot during which old row versions will not be deleted. After the threshold expires, all visible row versions in the snapshot become vacuumable. When querying the vacuumed row versions, PostgreSQL returns an error that is all too familiar to Oracle users: «Snapshot too old».<\/p>\n

<\/p>\n

As it turns out, there are issues with the parameter’s implementation, including some performance-related ones. With no clear-cut solution in sight so far, the parameter had to be scrapped.<\/p>\n

<\/a> <\/p>\n

New parameter event_triggers<\/em><\/a><\/h2>\n

<\/p>\n

commit: 7750fefd<\/a><\/p>\n

<\/p>\n

The parameter event_triggers<\/em> enables and disables event triggers.<\/p>\n

<\/p>\n

\\dconfig+ event_triggers<\/code><\/pre>\n
  <\/p>\n
               List of configuration parameters    Parameter    | Value | Type |  Context  | Access privileges ----------------+-------+------+-----------+-------------------  event_triggers | on    | bool | superuser | (1 row)<\/code><\/pre>\n
  <\/p>\n
It is intended as a trigger debugging tool. Previously, the CREATE EVENT TRIGGER<\/a> command documentation suggested to run the server in single-user mode if a malfunctioning trigger could not be deleted.<\/p>\n
  <\/a>  <\/p>\n
New functions to_bin and to_oct<\/a><\/h2>\n
  <\/p>\n
commit: 260a1f18<\/a><\/p>\n
  <\/p>\n
The existing decimal to hexadecimal conversion function to_hex gets its binary and octal siblings:<\/p>\n
  <\/p>\n
SELECT to_bin(2), to_oct(8), to_hex(16);<\/code><\/pre>\n
  <\/p>\n
 to_bin | to_oct | to_hex --------+--------+--------  10     | 10     | 10 (1 row)<\/code><\/pre>\n
  <\/p>\n
See also:
  Waiting for PostgreSQL 17 \u2013 Add to_bin() and to_oct(). \u2013 select * from depesz;<\/a><\/p>\n
  <\/a>  <\/p>\n
New system view pg_wait_events<\/a><\/h2>\n
  <\/p>\n
commit: 1e68e43d<\/a><\/p>\n
  <\/p>\n
In the documentation for the pg_stat_activity<\/code> view, the wait_event_type<\/code> and wait_event<\/code> columns are described in a table explaining the types of wait events<\/a> and several tables describing each wait event type. You can now access these descriptions by querying the new pg_wait_events<\/a> system view.<\/p>\n
  <\/p>\n
This can be useful in monitoring systems to get a description of what current processes are waiting for. What is the checkpoint process doing now?<\/p>\n
  <\/p>\n
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, w.description FROM pg_stat_activity a JOIN pg_wait_events w      ON (a.wait_event_type = w.type AND a.wait_event = w.name) WHERE a.backend_type = 'checkpointer'\\gx<\/code><\/pre>\n
  <\/p>\n
-[ RECORD 1 ]---+--------------------------------------------- pid             | 21090 state           | wait_event_type | Activity wait_event      | CheckpointerMain description     | Waiting in main loop of checkpointer process<\/code><\/pre>\n
  <\/a>  <\/p>\n
EXPLAIN: a JIT compilation time counter for tuple deforming<\/a><\/h2>\n
  <\/p>\n
commit: 5a3423ad<\/a><\/p>\n
  <\/p>\n
The patch adds specific time spent on JIT compilation for tuple deforming to EXPLAIN ANALYZE<\/code> and pg_stat_statements<\/code> outputs.<\/p>\n
  <\/p>\n
In the following query, JIT compilation is used to calculate the ?:<\/p>\n
  <\/p>\n
EXPLAIN (analyze, summary off) WITH pi AS (   SELECT random() x, random() y   FROM generate_series(1,10000000) ) SELECT 4*sum(1-floor(x*x+y*y))\/count(*) val FROM pi;<\/code><\/pre>\n
  <\/p>\n
                                                                  QUERY PLAN                                                                    -----------------------------------------------------------------------------------------------------------------------------------------------  Aggregate  (cost=525000.00..525000.02 rows=1 width=8) (actual time=3934.330..3934.331 rows=1 loops=1)    CTE pi      ->  Function Scan on generate_series  (cost=0.00..150000.00 rows=10000000 width=16) (actual time=725.126..1654.054 rows=10000000 loops=1)    ->  CTE Scan on pi  (cost=0.00..200000.00 rows=10000000 width=16) (actual time=725.129..3429.925 rows=10000000 loops=1)  JIT:    Functions: 6    Options: Inlining true, Optimization true, Expressions true, Deforming true    Timing: Generation 0.954 ms (Deform 0.161 ms), Inlining 8.476 ms, Optimization 25.632 ms, Emission 22.098 ms, Total 57.160 ms (8 rows)<\/code><\/pre>\n
  <\/p>\n
The Generation value in the last line of the plan is the sum of the expression compilation time (toggled by the jit_expressions<\/em> parameter) and the deforming compilation time (jit_tuple_deforming<\/em>). The deforming compilation time specifically is listed in the parentheses (Deform<\/code>).<\/p>\n
  <\/p>\n
Since there are no tables in this example, tuple deforming compilation can be switched off to save time. Disable the parameter to see how much we save:<\/p>\n
  <\/p>\n
SET jit_tuple_deforming = off;<\/code><\/pre>\n
  <\/p>\n
The JIT section of the plan:   JIT:    Functions: 5    Options: Inlining true, Optimization true, Expressions true, Deforming false    Timing: Generation 0.665 ms (Deform 0.000 ms), Inlining 15.209 ms, Optimization 25.897 ms, Emission 16.664 ms, Total 58.435 ms<\/code><\/pre>\n
  <\/a>  <\/p>\n
Planner: better estimate of the initial cost of the WindowAgg node<\/a><\/h2>\n
  <\/p>\n
commit: 3900a02c<\/a><\/p>\n
  <\/p>\n
The planner greatly underestimated the cost of getting the first row from a window function in cases where the window function processes a large number of rows.<\/p>\n
  <\/p>\n
In the following (slightly contrived) example, the planner selects a nested loop to join two tables, because the query returns only one row, and the cost of getting the first row is very small:<\/p>\n
  <\/p>\n
16=> EXPLAIN (analyze, settings) SELECT t.ticket_no, SUM(tf.amount) OVER () FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no LIMIT 1;<\/code><\/pre>\n
  <\/p>\n
                                                                           QUERY PLAN                                                                            ----------------------------------------------------------------------------------------------------------------------------------------------------------------  Limit  (cost=0.56..0.86 rows=1 width=46) (actual time=30416.237..30416.239 rows=1 loops=1)    ->  WindowAgg  (cost=0.56..2525161.21 rows=8391708 width=46) (actual time=30416.236..30416.237 rows=1 loops=1)          ->  Nested Loop  (cost=0.56..2420264.86 rows=8391708 width=20) (actual time=0.049..27742.105 rows=8391852 loops=1)                ->  Seq Scan on tickets t  (cost=0.00..78913.45 rows=2949845 width=14) (actual time=0.015..287.636 rows=2949857 loops=1)                ->  Index Scan using ticket_flights_pkey on ticket_flights tf  (cost=0.56..0.76 rows=3 width=20) (actual time=0.007..0.009 rows=3 loops=2949857)                      Index Cond: (ticket_no = t.ticket_no)  Settings: jit = 'off', search_path = 'bookings, public'  Planning Time: 0.456 ms  Execution Time: 30454.862 ms (9 rows)<\/code><\/pre>\n
  <\/p>\n
Note the second line of the plan, the one where the WindowAgg<\/code> node appears. The initial cost is 0.56, but the actual cost of getting the first row was about 30 seconds.<\/p>\n
  <\/p>\n
The new patch amends the assessment algorithm, so the planner uses a more appropriate hash join instead, immediately increasing the speed of the query:<\/p>\n
  <\/p>\n
17=> EXPLAIN (analyze, settings) SELECT t.ticket_no, SUM(tf.amount) OVER () FROM tickets t JOIN ticket_flights tf ON t.ticket_no = tf.ticket_no LIMIT 1;<\/code><\/pre>\n
  <\/p>\n
                                                                   QUERY PLAN                                                                     -------------------------------------------------------------------------------------------------------------------------------------------------  Limit  (cost=523778.31..523778.31 rows=1 width=46) (actual time=7157.639..7157.642 rows=1 loops=1)    ->  WindowAgg  (cost=523778.31..523778.36 rows=8392150 width=46) (actual time=7157.638..7157.640 rows=1 loops=1)          ->  Hash Join  (cost=130215.56..418876.48 rows=8392150 width=20) (actual time=740.005..5075.390 rows=8391852 loops=1)                Hash Cond: (tf.ticket_no = t.ticket_no)                ->  Seq Scan on ticket_flights tf  (cost=0.00..153881.50 rows=8392150 width=20) (actual time=0.066..794.699 rows=8391852 loops=1)                ->  Hash  (cost=78938.47..78938.47 rows=2949847 width=14) (actual time=737.972..737.973 rows=2949857 loops=1)                      Buckets: 262144  Batches: 32  Memory Usage: 6190kB                      ->  Seq Scan on tickets t  (cost=0.00..78938.47 rows=2949847 width=14) (actual time=0.029..307.040 rows=2949857 loops=1)  Settings: search_path = 'bookings, public', jit = 'off'  Planning Time: 0.331 ms  Execution Time: 7202.957 ms (11 rows)<\/code><\/pre>\n
  <\/a>  <\/p>\n
pg_constraint: NOT NULL constraints<\/a><\/h2>\n
  <\/p>\n
commit: b0e96f31<\/a><\/p>\n
  <\/p>\n
A new, but well-known, type of integrity constraint, NOT NULL<\/code> is now officially introduced into the pg_constraint<\/code> system catalog. For various reasons, NOT NULL<\/code> constraints were not written to pg_constraint<\/code> before.<\/p>\n
  <\/p>\n
This change does not bring new application development opportunities, but the information in the system catalog becomes more complete, and working with constraints becomes more consistent.<\/p>\n
  <\/p>\n
The NOT NULL<\/code> constraints in pg_constraint<\/code> have the contype = 'n'<\/code> type:<\/p>\n
  <\/p>\n
CREATE TABLE t (     col1 int NOT NULL,     col2 int NOT NULL );  SELECT conname, contype, conkey FROM pg_constraint WHERE conrelid = 't'::regclass;<\/code><\/pre>\n
  <\/p>\n
     conname     | contype | conkey -----------------+---------+--------  t_col1_not_null | n       | {1}  t_col2_not_null | n       | {2} (2 rows)<\/code><\/pre>\n
  <\/p>\n
Just like any other constraint, the NOT NULL constraint can be removed by name:<\/p>\n
  <\/p>\n
ALTER TABLE t DROP CONSTRAINT t_col2_not_null;<\/code><\/pre>\n
  <\/p>\n
The constraints are listed in a separate section of the \\d+<\/code> output:<\/p>\n
  <\/p>\n
postgres@postgres(17.0)=# \\d+ t<\/code><\/pre>\n
  <\/p>\n
                                            Table \"public.t\"  Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------  col1   | integer |           | not null |         | plain   |             |              |  col2   | integer |           |          |         | plain   |             |              | Not-null constraints:     \"t_col1_not_null\" NOT NULL \"col1\" Access method: heap<\/code><\/pre>\n
  <\/p>\n
Currently, NOT NULL<\/code> constraints cannot be created without checking existing data (NOT VALID<\/code>) or declared deferred (DEFERRED<\/code>). In addition, NOT NULL<\/code> constraints for system catalog tables are not written in pg_constraint<\/code>.<\/p>\n
  <\/a>  <\/p>\n
Normalization of CALL, DEALLOCATE and two-phase commit control commands<\/a><\/h2>\n
  <\/p>\n
commit: 11c34b34<\/a>, 638d42a3<\/a>, bb45156f<\/a><\/p>\n
  <\/p>\n
The command normalization mechanism can now place constants in place of procedure parameters in CALL<\/code> statements. With this patch, a procedure call with different parameters will be recorded in pg_stat_statements<\/code> as a single line:<\/p>\n
  <\/p>\n
CALL p(1); CALL p(2); CALL p(3);  SELECT queryid, query, calls                                                                                FROM pg_stat_statements WHERE query ILIKE 'CALL%';<\/code><\/pre>\n
  <\/p>\n
       queryid       |   query    | calls ---------------------+------------+-------  7076031282516132618 | CALL p($1) |     3 (1 row)<\/code><\/pre>\n
  <\/p>\n
In addition, transaction identifiers in two-phase commit control commands (see the second commit) and names of prepared statements in DEALLOCATE<\/code> (the third commit) can now be replaced with constants as well.<\/p>\n
  <\/p>\n
See also:
  Normalization of DDL and service commands<\/a>
  Normalization of DDL and service commands, continued<\/a><\/p>\n
  <\/a>  <\/p>\n
unaccent: the target rule expressions now support values in quotation marks<\/a><\/h2>\n
  <\/p>\n
commit: 59f47fb9<\/a><\/p>\n
  <\/p>\n
To eliminate diacritics, the built-in full-text search suggests using the unaccent<\/code> dictionary supplied with the server as an extension under the same name.<\/p>\n
  <\/p>\n
When defining custom dictionary rules, target expressions can now be enclosed in quotation marks. This is necessary if the target expression must start with a whitespace or end with it. Previously, the leading and trailing spaces were cut off.<\/p>\n
  <\/a>  <\/p>\n
COPY FROM: FORCE_NOT_NULL  and FORCE_NULL <\/em><\/a><\/h2>\n
  <\/p>\n
commit: f6d4c9cf<\/a><\/p>\n
  <\/p>\n
The COPY<\/code> command parameters FORCE_NOT_NULL<\/code> and FORCE_NULL<\/code> appeared a long time ago, in PostgreSQL 9.0 and 9.4, respectively. However, they required an explicit enumeration of columns, even if they were to be applied to all columns in the table.<\/p>\n
  <\/p>\n
You can now use the asterisk character *<\/code> instead.<\/p>\n
  <\/p>\n
CREATE TABLE t (col1 text NOT NULL, col2 text NOT NULL);  COPY t FROM STDIN WITH (FORMAT csv, FORCE_NOT_NULL *);<\/code><\/pre>\n
  <\/p>\n
Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >> \"\",\"\" >> \\. COPY 1<\/code><\/pre>\n
  <\/p>\n
SELECT * FROM t WHERE col1 = '' and col2 = '';<\/code><\/pre>\n
  <\/p>\n
 col1 | col2 ------+------       | (1 row)<\/code><\/pre>\n
  <\/a>  <\/p>\n
Audit of connections without authentication<\/a><\/h2>\n
  <\/p>\n
commit: e48b19c5<\/a><\/p>\n
  <\/p>\n
If a user connects without authentication while the parameter log_connections<\/em> is enabled, an entry about the connection will be recorded in the server log, indicating the triggered line number (117) with the trust<\/code> method in pg_hba.conf:<\/p>\n
  <\/p>\n
2023-10-19 11:40:07.609 MSK [111233] LOG:  connection authenticated: user=\"postgres\" method=trust (\/home\/pal\/master\/data\/pg_hba.conf:117) 2023-10-19 11:40:07.609 MSK [111233] LOG:  connection authorized: user=postgres database=demo application_name=psql<\/code><\/pre>\n
  <\/p>\n
Previously, only the second line was recorded for the trust<\/code> method.<\/p>\n
  <\/p>\n
Note that the SYSTEM_USER<\/code> function still returns NULL<\/code> for such connections.<\/p>\n
  <\/p>\n
See also:
  Waiting for PostgreSQL 17 \u2013 Generate new LOG for \u201ctrust\u201d connections under log_connections \u2013 select * from depesz;<\/a><\/p>\n
  <\/a>  <\/p>\n
pg_stat_subscription: new column worker_type<\/a><\/h2>\n
  <\/p>\n
commit: 13aeaf07<\/a><\/p>\n
  <\/p>\n
The new pg_stat_subscription<\/a> view column worker_type<\/code> helps to quickly identify what task is being performed by the process serving the subscription. Possible values are self-explanatory: apply<\/code>, parallel apply<\/code> and table synchronization<\/code>.<\/p>\n
  <\/p>\n
Remember that from PostgreSQL 16 and onwards, the server can now use multiple processes to apply transactions.<\/p>\n
  <\/a>  <\/p>\n
The behaviour of pg_promote in case of unsuccessful switchover to a replica<\/a><\/h2>\n
  <\/p>\n
commit: f593c551<\/a><\/p>\n
  <\/p>\n
One of the means of switching to a replica is the pg_promote<\/code> function. If the switchover is unsuccessful, the function returns false<\/code>, no matter what caused it:<\/p>\n
  <\/p>\n