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

<\/p>\n

<\/p>\n

The November commitfest is ripe with new interesting features! Without further ado, let’s proceed with the review.<\/p>\n

<\/p>\n

If you missed our July and September commitfest reviews, you can check them out here: 2023-07<\/a>, 2023-09<\/a>.<\/p>\n

<\/p>\n

ON LOGIN trigger<\/a>
Event triggers for REINDEX<\/a>
ALTER OPERATOR: commutator, negator, hashes, merges<\/a>
pg_dump —filter=dump.txt<\/a>
psql: displaying default privileges<\/a>
pg_stat_statements: track statement entry timestamps and reset min\/max statistics<\/a>
pg_stat_checkpointer: checkpointer process statistics<\/a>
pg_stats: statistics for range type columns<\/a>
Planner: exclusion of unnecessary table self-joins<\/a>
Planner: materialized CTE statistics<\/a>
Planner: accessing a table with multiple clauses<\/a>
Index range scan optimization<\/a>
dblink, postgres_fdw: detailed wait events<\/a>
Logical replication: migration of replication slots during publisher upgrade<\/a>
Replication slot use log<\/a>
Unicode: new information functions<\/a>
New function: xmltext<\/a>
AT LOCAL support<\/a>
Infinite intervals<\/a>
ALTER SYSTEM with unrecognized custom parameters<\/a>
Building the server from source<\/a><\/p>\n

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

ON LOGIN trigger<\/strong><\/a><\/h2>\n

<\/p>\n

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

<\/p>\n

The ability to put event an trigger on a database login event is coming to PostgreSQL.<\/p>\n

<\/p>\n

As usual, the trigger is created in two steps. First, the trigger function:<\/p>\n

<\/p>\n

CREATE FUNCTION check_login() RETURNS event_trigger AS $$ BEGIN     IF session_user = 'postgres' THEN RETURN; END IF;      IF to_char(current_date, 'DY') IN ('SAT','SUN')     THEN         RAISE 'Have a nice weekend, see you on Monday!';     END IF; END; $$ LANGUAGE plpgsql;<\/code><\/pre>\n
  <\/p>\n
Then the trigger itself:<\/p>\n
  <\/p>\n
CREATE EVENT TRIGGER check_login     ON LOGIN     EXECUTE FUNCTION check_login();<\/code><\/pre>\n
  <\/p>\n
Now, to everyone’s delight, users will not bother the administrator on weekends. ?<\/p>\n
  <\/p>\n
$ psql -U alice -d postgres<\/code><\/pre>\n
  <\/p>\n
psql: error: connection to server on socket \"\/tmp\/.s.PGSQL.5401\" failed: FATAL:  Have a nice weekend, see you on Monday! CONTEXT:  PL\/pgSQL function check_login() line 6 at RAISE<\/code><\/pre>\n
  <\/p>\n
See also
  Waiting for PostgreSQL 17 \u2013 Add support event triggers on authenticated login \u2013 select * from depesz;<\/a><\/p>\n
  <\/a>  <\/p>\n
Event triggers for REINDEX<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: f21848de<\/a><\/p>\n
  <\/p>\n
Event triggers now work with the REINDEX command, allowing you to create triggers for ddl_command_start<\/code> and ddl_command_end<\/code> events.<\/p>\n
  <\/a>  <\/p>\n
ALTER OPERATOR: commutator, negator, hashes, merges<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: 2b5154be<\/a><\/p>\n
  <\/p>\n
In the ALTER OPERATOR<\/a> command, you can now specify a commutator and a negator for the operator being defined, if they were not specified in the CREATE OPERATOR already. You can also add support for hash join and merge join operations (HASHES and MERGES)<\/p>\n
  <\/a>  <\/p>\n
pg_dump —filter=dump.txt<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: a5cf808b<\/a><\/p>\n
  <\/p>\n
The new parameter --filter<\/code> allows the user to specify a file name with a list of objects to include or exclude from the dump:<\/p>\n
  <\/p>\n
$ cat dump.txt<\/code><\/pre>\n
  <\/p>\n
include table      bookings exclude table_data bookings<\/code><\/pre>\n
  <\/p>\n
$ pg_dump -d demo --filter=dump.txt |grep -v -E '^SET|^SELECT|^--|^$'<\/code><\/pre>\n
  <\/p>\n
CREATE TABLE bookings.bookings (     book_ref character(6) NOT NULL,     book_date timestamp with time zone NOT NULL,     total_amount numeric(10,2) NOT NULL ); ALTER TABLE bookings.bookings OWNER TO postgres; COMMENT ON TABLE bookings.bookings IS 'Bookings'; COMMENT ON COLUMN bookings.bookings.book_ref IS 'Booking number'; COMMENT ON COLUMN bookings.bookings.book_date IS 'Booking date'; COMMENT ON COLUMN bookings.bookings.total_amount IS 'Total booking cost'; ALTER TABLE ONLY bookings.bookings     ADD CONSTRAINT bookings_pkey PRIMARY KEY (book_ref);<\/code><\/pre>\n
  <\/p>\n
The exact file syntax is described in the documentation<\/a>.<\/p>\n
  <\/p>\n
This parameter may come in handy when the list of database objects is so long that it does not fit into the pg_dump<\/code> command’s character limit.<\/p>\n
  <\/p>\n
--filter<\/code> has also been added to pg_dumpall<\/code> and pg_restore<\/code>.<\/p>\n
  <\/a>  <\/p>\n
psql: displaying default privileges<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: d1379ebf<\/a><\/p>\n
  <\/p>\n
psql commands were never a good way to view default privileges. We will use schemas in this example, although the same logic applies to any type of database object.<\/p>\n
  <\/p>\n
CREATE SCHEMA s;<\/code><\/pre>\n
  <\/p>\n
When a schema is created, its owner gets both its privileges, USAGE and CREATE, by default. However, if you try to check the privileges using the \\dn+<\/code> command, it will return NULL, since the privileges aren’t recorded into pg_namespace.nspacl<\/code>.<\/p>\n
  <\/p>\n
16=# SELECT nspacl IS NULL FROM pg_namespace WHERE oid = 's'::regnamespace;<\/code><\/pre>\n
  <\/p>\n
 ?column? ----------  t<\/code><\/pre>\n
  <\/p>\n
16=# \\pset null '(null)' 16=# \\dn+ s<\/code><\/pre>\n
  <\/p>\n
                  List of schemas  Name |  Owner   | Access privileges | Description ------+----------+-------------------+-------------  s    | postgres |                   |<\/code><\/pre>\n
  <\/p>\n
Note how even if you set \\pset null<\/code>, it does not affect the NULL values being displayed. When viewing system catalog objects, psql commands used to ignore this setting.<\/p>\n
  <\/p>\n
Running any GRANT or REVOKE command will have the privileges recorded into the system catalog explicitly. The following commands grant and revoke privileges for the schema. Nothing changes as a result, except that the default privileges now appear in the table:<\/p>\n
  <\/p>\n
16=# GRANT USAGE ON SCHEMA s TO public; 16=# REVOKE USAGE ON SCHEMA s FROM public;  16=# \\dn+ s<\/code><\/pre>\n
  <\/p>\n
                   List of schemas  Name |  Owner   |  Access privileges   | Description ------+----------+----------------------+-------------  s    | postgres | postgres=UC\/postgres |<\/code><\/pre>\n
  <\/p>\n
The owner may revoke their own privileges (and grant them back again later):<\/p>\n
  <\/p>\n
16=# REVOKE ALL ON SCHEMA s FROM postgres;<\/code><\/pre>\n
  <\/p>\n
At the moment, pg_namespace.nspacl<\/code> is an empty aclitem[]<\/code> array. An empty array does not equal NULL, but how to distinguish between the two in the command output?<\/p>\n
  <\/p>\n
16=# \\dn+ s<\/code><\/pre>\n
  <\/p>\n
                  List of schemas  Name |  Owner   | Access privileges | Description ------+----------+-------------------+-------------  s    | postgres |                   |<\/code><\/pre>\n
  <\/p>\n
The only solution used to be to query pg_namespace<\/em>.<\/p>\n
  <\/p>\n
In PostgreSQL 17 this is no longer the case. The \\pset null<\/code> setting now properly affects NULL values:<\/p>\n
  <\/p>\n
17=# CREATE SCHEMA s;  17=# \\pset null '(null)' 17=# \\dn+ s<\/code><\/pre>\n
  <\/p>\n
                  List of schemas  Name |  Owner   | Access privileges | Description ------+----------+-------------------+-------------  s    | postgres | (null)            | (null)<\/code><\/pre>\n
  <\/p>\n
And the absence of privileges (an empty array) is displayed with a special value (none)<\/code>:<\/p>\n
  <\/p>\n
17=# REVOKE ALL ON SCHEMA s FROM postgres; 17=# \\dn+ s<\/code><\/pre>\n
  <\/p>\n
                  List of schemas  Name |  Owner   | Access privileges | Description ------+----------+-------------------+-------------  s    | postgres | (none)            | (null)<\/code><\/pre>\n
  <\/a>  <\/p>\n
pg_stat_statements: track statement entry timestamps and reset min\/max statistics<\/strong> <\/a><\/h2>\n
  <\/p>\n
commit: dc9f8a79<\/a><\/p>\n
  <\/p>\n
pg_stat_statements<\/code> now has a new column stats_since<\/code>, which records the time when collecting statistics for each operator was initiated. In addition to that, you can now reset the statistics in the min\/max*<\/code> columns for specific operators by calling pg_stat_statements<\/code> with the parameter minmax_only<\/code>. The reset time for the statistics is recorded in the minmax_stats_since<\/code> column.<\/p>\n
  <\/p>\n
These new features will come in handy for monitoring systems that rely on sampling information from pg_stat_statements<\/code>. In particular, they may skip collecting all the statistics before starting a snapshot.<\/p>\n
  <\/a>  <\/p>\n
pg_stat_checkpointer: checkpointer process statistics<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: 96f05261<\/a>, 74604a37<\/a><\/p>\n
  <\/p>\n
Buffer cache changes can be written to disk by three types of processes: background writer, checkpointer, and user backend processes. For the longest time, all the relevant statistics have been tracked in a single pg_stat_bgwriter<\/code> view<\/a>.<\/p>\n
  <\/p>\n
The new patch trims the number of columns in pg_stat_bgwriter<\/code> down considerably. All checkpointer statistics have been moved into a new view pg_stat_checkpointer<\/code><\/a> (first commit).<\/p>\n
  <\/p>\n
At the same time, the buffers_backend<\/code> and buffers_backend_fsync<\/code> columns were removed from pg_stat_bgwriter<\/code>, since more accurate and detailed information about the backend processes can now be found in the pg_stat_io<\/code> view that was introduced in PostgreSQL 16<\/a> (second commit).<\/p>\n
  <\/a>  <\/p>\n
pg_stats: statistics for range type columns<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: bc3c8db8<\/a><\/p>\n
  <\/p>\n
Statistics for range type columns have traditionally been collected and stored in the pg_statistic<\/code> table. However, this information was not displayed in the pg_stats<\/code> view.<\/p>\n
  <\/p>\n
This patch fixes that by adding three new columns to pg_stats<\/code><\/a>: range_length_histogram<\/code>, range_empty_frac<\/code>, and range_bounds_histogram<\/code>.<\/p>\n
  <\/a>  <\/p>\n
Planner: exclusion of unnecessary table self-joins<\/strong><\/a><\/h2>\n
  <\/p>\n
commit: d3d55ce5<\/a><\/p>\n
  <\/p>\n
In a poorly composed query, a table may be joined with itself for any reason, as the SQL syntax allows it. Such queries often come from all sorts of ORMs, and the burden of optimisation often falls onto those who are powerless to change the way the queries are structured in the first place.<\/p>\n
  <\/p>\n
In PostgreSQL 17, the planner now can catch these unnecessary joins and exclude them from the query plan. The following example demonstrates an unnecessary semijoin of the table bookings with itself.<\/p>\n
  <\/p>\n
17=# EXPLAIN (costs off) WITH b AS (     SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);<\/code><\/pre>\n
  <\/p>\n
            QUERY PLAN             ----------------------------------  Seq Scan on bookings    Filter: (book_ref IS NOT NULL)<\/code><\/pre>\n
  <\/p>\n
The planner is justified in deciding that the table should only be scanned once.<\/p>\n
  <\/p>\n
This optimization is toggled by the parameter enable_self_join_removal<\/em>. Switching it off leads to the behavior observed in the older versions:<\/p>\n
  <\/p>\n
17=# SET enable_self_join_removal = off;  17=# EXPLAIN (costs off) WITH b AS (     SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);<\/code><\/pre>\n
  <\/p>\n
                       QUERY PLAN                        --------------------------------------------------------  Hash Join    Hash Cond: (bookings.book_ref = bookings_1.book_ref)    ->  Seq Scan on bookings    ->  Hash          ->  Seq Scan on bookings bookings_1<\/code><\/pre>\n
  <\/a>  <\/p>\n
Planner: materialized CTE statistics<\/a><\/strong><\/h2>\n
  <\/p>\n
commit: f7816aec<\/a><\/p>\n
  <\/p>\n
Let’s try the same example, but with CTE materialization. This is how it plays out in PostgreSQL 16 (jit off):<\/p>\n
  <\/p>\n
16=# EXPLAIN (analyze,timing off) WITH b AS MATERIALIZED (     SELECT book_ref FROM bookings ) SELECT * FROM bookings WHERE book_ref IN (SELECT book_ref FROM b);<\/code><\/pre>\n
  <\/p>\n
                                                    QUERY PLAN                                                      -------------------------------------------------------------------------------------------------------------------  Nested Loop  (cost=82058.51..83469.66 rows=1055555 width=21) (actual rows=2111110 loops=1)    CTE b      ->  Seq Scan on bookings bookings_1  (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1)    ->  HashAggregate  (cost=47499.98..47501.98 rows=200 width=28) (actual rows=2111110 loops=1)          Group Key: b.book_ref          Batches: 141  Memory Usage: 11113kB  Disk Usage: 57936kB          ->  CTE Scan on b  (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)    ->  Index Scan using bookings_pkey on bookings  (cost=0.43..8.37 rows=1 width=21) (actual rows=1 loops=2111110)          Index Cond: (book_ref = b.book_ref)  Planning Time: 0.237 ms  Execution Time: 10847.689 ms                                                    <\/code><\/pre>\n
  <\/p>\n
The planner has decided to group the CTE by book_ref<\/code> before joining with the outer query (the HashAggregate node). But book_ref<\/code> is the primary key of the bookings table, so the number of rows after grouping will remain at ~2 million, not at the estimated 200 rows. As a result, the planner selects a wrong join type (nested loop) for the CTE and the outer query.<\/p>\n
  <\/p>\n
In PostgreSQL 17, the planner has access to some statistics about columns from materialized CTE and can use those in the outer parts of the plan. This leads to more precise cardinality estimates. This is how the same query runs now:<\/p>\n
  <\/p>\n
                                                    QUERY PLAN                                                      -------------------------------------------------------------------------------------------------------------------  Hash Semi Join  (cost=117601.18..222997.93 rows=2111110 width=21) (actual rows=2111110 loops=1)    Hash Cond: (bookings.book_ref = b.book_ref)    CTE b      ->  Seq Scan on bookings bookings_1  (cost=0.00..34558.10 rows=2111110 width=7) (actual rows=2111110 loops=1)    ->  Seq Scan on bookings  (cost=0.00..34558.10 rows=2111110 width=21) (actual rows=2111110 loops=1)    ->  Hash  (cost=42222.20..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)          Buckets: 131072  Batches: 32  Memory Usage: 3529kB          ->  CTE Scan on b  (cost=0.00..42222.20 rows=2111110 width=28) (actual rows=2111110 loops=1)  Planning Time: 0.127 ms  Execution Time: 1556.576 ms<\/code><\/pre>\n
  <\/p>\n
With the unnecessary grouping gone and using hash join for the CTE and the outer query, the execution is about 7 times faster.<\/p>\n
  <\/a>  <\/p>\n
Planner: accessing a table with multiple clauses<\/a><\/strong><\/h2>\n
  <\/p>\n
commit: 5d8aa8bc<\/a><\/p>\n
  <\/p>\n
When a table is queried with multiple restriction clauses, should it always be queried in the first place? This patch introduces a small optimisation along this line of thinking.<\/p>\n
  <\/p>\n
Consider this query in PostgreSQL 16.<\/p>\n
  <\/p>\n
16=# EXPLAIN (costs off, analyze, timing off, summary off) SELECT * FROM tickets WHERE ticket_no = '0005432' AND ticket_no = '0005000';<\/code><\/pre>\n
  <\/p>\n
                           QUERY PLAN                             -----------------------------------------------------------------  Result (actual rows=0 loops=1)    One-Time Filter: false    ->  Index Scan using tickets_pkey on tickets (never executed)          Index Cond: (ticket_no = '0005432'::bpchar)<\/code><\/pre>\n
  <\/p>\n
Two clauses for the tickets table contradict each other, but the planner does not immediately figure this out and proceeds to select a table access method. It concludes at a later stage that there is no need to access the table after all, but the resources spent on planning end up wasted.<\/p>\n
  <\/p>\n
Now, here is the plan for the same query in PostgreSQL 17:<\/p>\n
  <\/p>\n
           QUERY PLAN            --------------------------------  Result (actual rows=0 loops=1)    One-Time Filter: false<\/code><\/pre>\n
  <\/p>\n
Both the clauses are analyzed in advance. The plan not only becomes cheaper to build, but looks much neater as well.<\/p>\n
  <\/a>  <\/p>\n
Index range scan optimization<\/a><\/strong><\/h2>\n
  <\/p>\n
commit: e0b1ee17<\/a><\/p>\n
  <\/p>\n
When a range scan is performed using a B-tree index, each scanned index page is searched for values within the given range.<\/p>\n
  <\/p>\n
Previously, PostgreSQL used to check if every value in an index page is within the range. With the new patch, it first checks the last value in a page: if the last value is within the range, so must be all other values. The more index pages are there to scan and the more expensive the comparison operator, the greater the optimization effect.<\/p>\n
  <\/p>\n
In the following example, more than 40 thousand index pages are scanned. This is the query in PostgreSQL 16:<\/p>\n
  <\/p>\n
16=# EXPLAIN (analyze, buffers, costs off, timing off) SELECT * FROM tickets WHERE ticket_no > '0005432' AND ticket_no < '0005434';<\/code><\/pre>\n
  <\/p>\n
                                     QUERY PLAN                                       -------------------------------------------------------------------------------------  Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1)    Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar))    Buffers: shared hit=10675 read=30185  Planning:    Buffers: shared read=4  Planning Time: 0.214 ms  Execution Time: 683.801 ms<\/code><\/pre>\n
  <\/p>\n
The same query in PostgreSQL 17 executes much faster:<\/p>\n
  <\/p>\n
                                     QUERY PLAN                                       -------------------------------------------------------------------------------------  Index Scan using tickets_pkey on tickets (actual rows=1469571 loops=1)    Index Cond: ((ticket_no > '0005432'::bpchar) AND (ticket_no < '0005434'::bpchar))    Buffers: shared hit=10690 read=30170  Planning:    Buffers: shared read=4  Planning Time: 0.268 ms  Execution Time: 237.177 ms<\/code><\/pre>\n
  <\/a>  <\/p>\n