{"id":385906,"date":"2024-06-29T06:24:08","date_gmt":"2024-06-29T06:24:08","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=385906"},"modified":"-0001-11-30T00:00:00","modified_gmt":"2024-06-29T06:24:08","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=385906","title":{"rendered":"<span>PostgreSQL 16: Part 1 or CommitFest 2022-07<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>August was a special month in PostgreSQL release cycle as\u00a0<a href=\"https:\/\/commitfest.postgresql.org\/38\/\">the first CommitFest<\/a>\u00a0for the 16th PostgreSQL release was held.<\/p>\n<p>Let&#8217;s\u00a0<a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=summary\">compile the server<\/a>\u00a0and check out the cool new stuff!<\/p>\n<pre><code class=\"pgsql\">\\dconfig server_version<\/code><\/pre>\n<pre><code>List of configuration parameters    Parameter    |  Value ----------------+---------  server_version | 16devel<\/code><\/pre>\n<h3>Notable features<\/h3>\n<p>There are 14 patches I want to talk about:<\/p>\n<ul>\n<li>\n<p><a href=\"#commit_a45388d6\">psql: \\pset xheader_width<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7781f4e3\">vacuumdb &#8212;schema and &#8212;exclude-schema options<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_08951a7c\">New createuser features added<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_62c46eee\">Checkpoint and redo LSN added to LogCheckpointEnd log message<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_84ad713c\">pg_prepared_statements.result_types<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d4bfe412\">New parameter log_parameter_max_length for auto_explain<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_bcedd8f5\">Subquery alias in FROM clause became optional<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_2cbc3c17\">REINDEX: syntax improvements and more<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_624aa2a1\">CREATE STATISTICS: statistic name is now optional<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_784cedda\">CREATE TABLE: the STORAGE attribute<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_e530be2c\">The user created during cluster initialization now can&#8217;t be stripped of superuser privileges<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3b00a944\">TRUNCATE triggers on foreign tables are now supported<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_283129e3\">New argument variation for pg_read_file\/pg_read_binary_file<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5c279a6d\">Extensible WAL resource managers<\/a><\/p>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3295\/\"><strong>psql: \\pset xheader_width<\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a45388d6\">a45388d6<\/a><\/p>\n<p>Expanded output mode (<code>\\x<\/code>\u00a0or\u00a0<code>\\pset expanded<\/code>) is a convenient option whenever your column data won&#8217;t fit into the terminal screen. However, even the expanded mode may not be enough for some content.<\/p>\n<p>Let&#8217;s run a query in a 72-character-wide terminal:<\/p>\n<pre><code class=\"pgsql\">\\pset expanded on \\pset pager off SELECT version(), length(version()) version_length;<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]--+-------------------------------------------------------- version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit version_length | 104<\/code><\/pre>\n<p>The version value doesn&#8217;t fit and gets wrapped. That&#8217;s completely fine. But the output header is generated in the same size as the widest column and gets wrapped as well. The wider the widest column, the longer the header will be. This is especially annoying when displaying multiple wide records, because each header occupies multiple lines in the output. (Try running\u00a0<code>SELECT * FROM pg_proc;<\/code>\u00a0in the expanded mode.)<\/p>\n<p>PostgreSQL 16 introduces a new record formatting option to handle these long headers:<\/p>\n<pre><code class=\"pgsql\">\\pset xheader_width<\/code><\/pre>\n<pre><code>Expanded header width is 'full'.<\/code><\/pre>\n<p>The default option (<em>full<\/em>) is the familiar behavior from the past versions. Other options include:<\/p>\n<ul>\n<li>\n<p><em>column<\/em>\u00a0\u2015 the header is trimmed to the width of the first output column.<\/p>\n<\/li>\n<li>\n<p><em>page<\/em>\u00a0\u2015 the header is trimmed to the width of the terminal window.<\/p>\n<\/li>\n<li>\n<p><em>nnn<\/em>\u00a0\u2015 the header is trimmed to the nnn-th character.<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"pgsql\">\\pset xheader_width column<\/code><\/pre>\n<pre><code>Expanded header width is 'column'.<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT version(), length(version()) version_length;<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]--+ version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit version_length | 104<\/code><\/pre>\n<p>The header no longer wastes valuable terminal space.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3612\/\"><strong>vacuumdb &#8212;schema and &#8212;exclude-schema<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7781f4e3\">7781f4e3<\/a>  <\/p>\n<p><code>vacuumdb<\/code>\u00a0gets new parameters for vacuuming specified schemas (<code>--schema<\/code>) and tables outside of specified schemas (<code>--exclude-schema<\/code>).<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3618\/\"><strong>New createuser features added<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/08951a7c\">08951a7c<\/a>  <\/p>\n<p><code>createuser<\/code>\u00a0is a wrapper for the\u00a0<code>CREATE ROLE<\/code>\u00a0command, but with limited functionality. The patch adds new parameters to make use of previously unavailable functions, namely:<\/p>\n<ul>\n<li>\n<p><code>--valid-until<\/code>\u00a0for the\u00a0<code>VALID UNTIL<\/code>\u00a0clause<\/p>\n<\/li>\n<li>\n<p><code>--bypassrls<\/code>\/<code>--no-bypassrls<\/code>\u00a0for the\u00a0<code>BYPASSRLS<\/code>\/<code>NOBYPASSRLS<\/code>\u00a0attributes<\/p>\n<\/li>\n<li>\n<p><code>-m<\/code>\/<code>--member<\/code>\u00a0to grant membership to the created role<\/p>\n<\/li>\n<li>\n<p><code>-a<\/code>\/<code>--admin<\/code>\u00a0to grant membership to the created role using the\u00a0<code>WITH ADMIN OPTION<\/code>\u00a0clause<\/p>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3474\/\"><strong>Checkpoint and redo LSN added to LogCheckpointEnd log message<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/62c46eee\">62c46eee<\/a>  <\/p>\n<p>After a checkpoint is made, WAL takes a record with the LSN of the checkpoint and the LSN of the recovery point (redo LSN).<\/p>\n<p>The two LSNs for the latest checkpoint are stored in the control file:<\/p>\n<pre><code class=\"pgsql\">$ pg_controldata | egrep 'Latest.*location'<\/code><\/pre>\n<pre><code>Latest checkpoint location:         1\/1A3DEB20 Latest checkpoint's REDO location:  1\/1A3DEAE8<\/code><\/pre>\n<p>In PostgreSQL 16, these records are now mentioned in the checkpoint completion message in the server log:<\/p>\n<pre><code>2022-08-02 12:15:17.961 MSK [198868] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.072 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=418462 kB; lsn=1\/1A3DEB20, redo lsn=1\/1A3DEAE8<\/code><\/pre>\n<p>Note that in PostgreSQL 15 and higher, the\u00a0<em>log_checkpoints<\/em>\u00a0parameter is on by default.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3644\/\"><strong>pg_prepared_statements.result_types<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/84ad713c\">84ad713c<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6ffff0fd\">6ffff0fd<\/a><\/p>\n<p>The\u00a0<code>pg_prepared_statements<\/code>\u00a0view now has a column\u00a0<code>result_types<\/code>.<\/p>\n<pre><code class=\"pgsql\">PREPARE get_booking (text) AS SELECT * FROM bookings WHERE book_ref = $1; SELECT * FROM pg_prepared_statements WHERE name = 'get_booking'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]---+----------------------------------------------- name            | get_booking statement       | PREPARE get_booking (text) AS                 + | SELECT * FROM bookings WHERE book_ref = $1; prepare_time    | 2022-08-02 16:54:14.313221+03 parameter_types | {text} result_types    | {character,\"timestamp with time zone\",numeric} from_sql        | t generic_plans   | 0 custom_plans    | 0<\/code><\/pre>\n<p>For prepared statements that return nothing the column will remain empty.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3660\/\"><strong>New parameter log_parameter_max_length for auto_explain<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d4bfe412\">d4bfe412<\/a><\/p>\n<p>The new parameter\u00a0<em>auto_explain.log_parameter_max_length<\/em>\u00a0serves to log query parameters, just like\u00a0<em>log_parameter_max_length<\/em>.<\/p>\n<pre><code class=\"pgsql\">LOAD 'auto_explain'; SHOW auto_explain.log_parameter_max_length;<\/code><\/pre>\n<pre><code>auto_explain.log_parameter_max_length  1 (1 row)<\/code><\/pre>\n<p>The default value is -1. This means that it will display parameter values in full. If set to 0, no parameter values will be displayed. A positive value limits the output length in bytes.<\/p>\n<p>Let&#8217;s set up logging and run a parameterized query\u00a0<code>EXECUTE... USING<\/code>\u00a0within an anonymous PL\/pgSQL block.<\/p>\n<pre><code class=\"pgsql\">SET auto_explain.log_min_duration = 0; SET auto_explain.log_nested_statements = 'on'; SET auto_explain.log_level = 'NOTICE'; DO $$BEGIN EXECUTE 'SELECT $;<\/code><\/pre>\n<pre><code>NOTICE:  duration: 0.003 ms  plan: Query Text: SELECT $1 Query Parameters: $1 = '42' Result  (cost=0.00..0.01 rows=1 width=4) DO<\/code><\/pre>\n<p>Note the newly added\u00a0<em>Query Parameters<\/em>\u00a0string listing the parameters the query from <em>Query Text<\/em> was run wit.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3714\/\"><strong><u>Subquery alias in FROM clause became optional<\/u><\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/bcedd8f5\">bcedd8f5<\/a><\/p>\n<p>The SQL standard demands that any subquery within a\u00a0<code>FROM<\/code>\u00a0clause has an alias:<\/p>\n<pre><code class=\"pgsql\">15=# SELECT * FROM (SELECT 42 AS a);<\/code><\/pre>\n<pre><code>ERROR:  subquery in FROM must have an alias LINE 1: SELECT * FROM (SELECT 42 AS a); ^ HINT:  For example, FROM (SELECT ...) [AS] foo.<\/code><\/pre>\n<p>This complicates migration from other DBMS that don&#8217;t strongly demand an alias here.<\/p>\n<p>PostgreSQL 16 makes these aliases optional.<\/p>\n<pre><code class=\"pgsql\">16=# SELECT * FROM (SELECT 42 AS a);<\/code><\/pre>\n<pre><code>a   42 (1 row)<\/code><\/pre>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3636\/\"><strong>REINDEX: syntax improvements and more<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/2cbc3c17\">2cbc3c17<\/a><\/p>\n<p><code>REINDEX DATABASE<\/code>\u00a0and\u00a0<code>REINDEX SYSTEM<\/code>\u00a0variations demand that a database is specified. The only valid option, however, is the current database, which makes the option redundant. This patch makes specifying the database optional.<\/p>\n<p>Additionally, the\u00a0<code>REINDEX DATABASE<\/code>\u00a0command now reindexes only user-generated indexes and skips system catalog table indexes. You can still rebuild all database indexes by running both\u00a0<code>REINDEX DATABASE<\/code>\u00a0and\u00a0<code>REINDEX SYSTEM<\/code>.<\/p>\n<p>This patch addresses the demand for an ability to reindex only user-generated indexes across the whole database. Plus, rebuilding system indexes is straight up impossible in\u00a0<code>CONCURRENTLY<\/code>\u00a0mode and may result in deadlocks in high load environments.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3708\/\"><strong>CREATE STATISTICS: statistic name is now optional<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/624aa2a1\">624aa2a1<\/a><\/p>\n<p>Extended statistics are separate database objects and therefore need names. But why not let PostgreSQL handle the naming without the user&#8217;s involvement? We already do that with indexes and integrity constraints.<\/p>\n<p>Done and done:<\/p>\n<pre><code class=\"pgsql\">CREATE STATISTICS ON departure_airport, arrival_airport FROM flights; \\d flights<\/code><\/pre>\n<pre><code>... Statistics objects: \"bookings.flights_departure_airport_arrival_airport_stat\" ON departure_airport, arrival_airport FROM flights<\/code><\/pre>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3479\/\"><strong>CREATE TABLE: the STORAGE attribute<\/strong><\/a> <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/784cedda\">784cedda<\/a><\/p>\n<p>Suppose we want to store images in our database. Let&#8217;s create a table:<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE images (filename text, data bytea); ALTER TABLE images ALTER COLUMN data SET STORAGE external;<\/code><\/pre>\n<p>In the second command, we change the TOAST storage strategy to\u00a0<code>external<\/code>\u00a0to prohibit data compression. After all, the image files are already compressed.<\/p>\n<p>The question is, why do this as a separate command, and not immediately specify the desired strategy as a part of the\u00a0<code>CREATE TABLE<\/code>\u00a0command? The answer is simple. Before PostgreSQL 16,\u00a0<code>CREATE TABLE<\/code>\u00a0didn&#8217;t not allow specifying the\u00a0<code>STORAGE<\/code>\u00a0attribute for columns. Now it does:<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE images (filename text, data bytea STORAGE external);<\/code><\/pre>\n<p><a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/CA%2BTgmoZirCwArJms_fgvLBFrC6b%3DHdxmG7iAhv%2Bkt_%3DNBA7tEw%40mail.gmail.com\"><strong>The user created during cluster initialization now can&#8217;t be stripped of superuser privileges<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/e530be2c\">e530be2c<\/a><\/p>\n<p>Prior to PostgreSQL 16, the superuser created during cluster initialization (<em>pg_authid.oid=10<\/em>) could have taken the\u00a0<code>SUPERUSER<\/code>\u00a0attribute from himself:<\/p>\n<pre><code class=\"pgsql\">15=# ALTER ROLE postgres NOSUPERUSER;<\/code><\/pre>\n<p>(If there were no other superusers, you had to restart the server in the single-user mode to restore the attribute.)<\/p>\n<p>PostgreSQL 16 makes it impossible for the superuser to lose the attribute on accident:<\/p>\n<pre><code class=\"pgsql\">16=# ALTER ROLE postgres NOSUPERUSER;<\/code><\/pre>\n<pre><code>ERROR:  permission denied: bootstrap user must be superuser<\/code><\/pre>\n<p>By the way, it was impossible to delete the superuser created during cluster initialization even in previous versions:<\/p>\n<pre><code class=\"pgsql\">CREATE ROLE root LOGIN SUPERUSER; \\c - root DROP ROLE postgres;<\/code><\/pre>\n<pre><code>ERROR:  cannot drop role postgres because it is required by the database system<\/code><\/pre>\n<p>In a nutshell, you need the initial superuser for the server to function properly and you shouldn&#8217;t delete it or strip its superuser privileges. For extra security, you might\u00a0<a href=\"https:\/\/www.postgresql.org\/message-id\/f941fa4a-a294-165a-82c4-947d3c8425f6%40joeconway.com\">consider<\/a>\u00a0making it a group role and stripping the\u00a0<code>LOGIN<\/code>\u00a0attribute from it.<\/p>\n<p><a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/20220630193848.5b02e0d6076b86617a915682%40sraoss.co.jp\"><strong>TRUNCATE triggerd on foreign tables are now supported<\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3b00a944\">3b00a944<\/a><\/p>\n<p>Some foreign data wrappers support the\u00a0<code>TRUNCATE<\/code>\u00a0command. This patch adds the ability to create a trigger on\u00a0<code>TRUNCATE<\/code>\u00a0for foreign tables for such wrappers.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3730\/\"><strong>New argument variation for pg_read_file\/pg_read_binary_file<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/283129e3\">283129e3<\/a><\/p>\n<p>The specification of the\u00a0<code>pg_read_file<\/code>\u00a0and the similar\u00a0<code>pg_read_binary_file<\/code>\u00a0functions was as follows:<\/p>\n<pre><code>pg_read_file ( filename text [, offset bigint, length bigint [, missing_ok boolean ]] ) \u2192 text<\/code><\/pre>\n<p>It is assumed that to read the entire file, you do not need to specify the\u00a0<em>offset<\/em>\u00a0and\u00a0<em>length<\/em>\u00a0parameters. But then you can&#8217;t set\u00a0<em>missing_ok=true<\/em>\u00a0to ignore the file absence.<\/p>\n<p>PostgreSQL 16 adds an overloaded function variant, resulting in a new specification:<\/p>\n<pre><code>pg_read_file ( filename text [, offset bigint, length bigint ] [, missing_ok boolean ] ) \u2192 text<\/code><\/pre>\n<p>Now you can specify a file name and ignore the file absence.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3469\/\"><strong>Extensible WAL resource managers<\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/5c279a6d\">5c279a6d<\/a><\/p>\n<p><em>Please welcome to the stage my colleague Egor Rogov, who will explain the highlight of this patch.<\/em><\/p>\n<p>One of the problems facing developers of table and index access methods is the inability to generate log records of a specific type. There is the mechanism of\u00a0<a href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/14\/generic-wal\">generic WAL records<\/a>\u00a0that logs the \u201cdifference\u201d between the old and new page states to WAL (it is used, for example, by the\u00a0<a href=\"https:\/\/github.com\/postgrespro\/rum\">rum<\/a>\u00a0extension), but it is not very efficient and does not support logical replication.<\/p>\n<p>In PostgreSQL 16, extensions will be able to\u00a0<a href=\"https:\/\/www.postgresql.org\/docs\/15\/custom-rmgr.html\">create their own resource managers<\/a>\u00a0and, consequently, log entries in their own format. The flip side of the coin is that recovering an instance after a failure becomes dependent on a third-party extension.<\/p>\n<p>From the user&#8217;s point of view, the patch does not change anything, but it is another important step towards the emergence of new access methods.<\/p>\n<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/696816\/\"> https:\/\/habr.com\/ru\/articles\/696816\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<p>August was a special month in PostgreSQL release cycle as\u00a0<a href=\"https:\/\/commitfest.postgresql.org\/38\/\">the first CommitFest<\/a>\u00a0for the 16th PostgreSQL release was held.<\/p>\n<p>Let&#8217;s\u00a0<a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=summary\">compile the server<\/a>\u00a0and check out the cool new stuff!<\/p>\n<pre><code class=\"pgsql\">\\dconfig server_version<\/code><\/pre>\n<pre><code>List of configuration parameters    Parameter    |  Value ----------------+---------  server_version | 16devel<\/code><\/pre>\n<h3>Notable features<\/h3>\n<p>There are 14 patches I want to talk about:<\/p>\n<ul>\n<li>\n<p><a href=\"#commit_a45388d6\">psql: \\pset xheader_width<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_7781f4e3\">vacuumdb &#8212;schema and &#8212;exclude-schema options<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_08951a7c\">New createuser features added<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_62c46eee\">Checkpoint and redo LSN added to LogCheckpointEnd log message<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_84ad713c\">pg_prepared_statements.result_types<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_d4bfe412\">New parameter log_parameter_max_length for auto_explain<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_bcedd8f5\">Subquery alias in FROM clause became optional<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_2cbc3c17\">REINDEX: syntax improvements and more<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_624aa2a1\">CREATE STATISTICS: statistic name is now optional<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_784cedda\">CREATE TABLE: the STORAGE attribute<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_e530be2c\">The user created during cluster initialization now can&#8217;t be stripped of superuser privileges<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_3b00a944\">TRUNCATE triggers on foreign tables are now supported<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_283129e3\">New argument variation for pg_read_file\/pg_read_binary_file<\/a><\/p>\n<\/li>\n<li>\n<p><a href=\"#commit_5c279a6d\">Extensible WAL resource managers<\/a><\/p>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3295\/\"><strong>psql: \\pset xheader_width<\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/a45388d6\">a45388d6<\/a><\/p>\n<p>Expanded output mode (<code>\\x<\/code>\u00a0or\u00a0<code>\\pset expanded<\/code>) is a convenient option whenever your column data won&#8217;t fit into the terminal screen. However, even the expanded mode may not be enough for some content.<\/p>\n<p>Let&#8217;s run a query in a 72-character-wide terminal:<\/p>\n<pre><code class=\"pgsql\">\\pset expanded on \\pset pager off SELECT version(), length(version()) version_length;<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]--+-------------------------------------------------------- version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit version_length | 104<\/code><\/pre>\n<p>The version value doesn&#8217;t fit and gets wrapped. That&#8217;s completely fine. But the output header is generated in the same size as the widest column and gets wrapped as well. The wider the widest column, the longer the header will be. This is especially annoying when displaying multiple wide records, because each header occupies multiple lines in the output. (Try running\u00a0<code>SELECT * FROM pg_proc;<\/code>\u00a0in the expanded mode.)<\/p>\n<p>PostgreSQL 16 introduces a new record formatting option to handle these long headers:<\/p>\n<pre><code class=\"pgsql\">\\pset xheader_width<\/code><\/pre>\n<pre><code>Expanded header width is 'full'.<\/code><\/pre>\n<p>The default option (<em>full<\/em>) is the familiar behavior from the past versions. Other options include:<\/p>\n<ul>\n<li>\n<p><em>column<\/em>\u00a0\u2015 the header is trimmed to the width of the first output column.<\/p>\n<\/li>\n<li>\n<p><em>page<\/em>\u00a0\u2015 the header is trimmed to the width of the terminal window.<\/p>\n<\/li>\n<li>\n<p><em>nnn<\/em>\u00a0\u2015 the header is trimmed to the nnn-th character.<\/p>\n<\/li>\n<\/ul>\n<pre><code class=\"pgsql\">\\pset xheader_width column<\/code><\/pre>\n<pre><code>Expanded header width is 'column'.<\/code><\/pre>\n<pre><code class=\"pgsql\">SELECT version(), length(version()) version_length;<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]--+ version        | PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit version_length | 104<\/code><\/pre>\n<p>The header no longer wastes valuable terminal space.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3612\/\"><strong>vacuumdb &#8212;schema and &#8212;exclude-schema<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/7781f4e3\">7781f4e3<\/a>  <\/p>\n<p><code>vacuumdb<\/code>\u00a0gets new parameters for vacuuming specified schemas (<code>--schema<\/code>) and tables outside of specified schemas (<code>--exclude-schema<\/code>).<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3618\/\"><strong>New createuser features added<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/08951a7c\">08951a7c<\/a>  <\/p>\n<p><code>createuser<\/code>\u00a0is a wrapper for the\u00a0<code>CREATE ROLE<\/code>\u00a0command, but with limited functionality. The patch adds new parameters to make use of previously unavailable functions, namely:<\/p>\n<ul>\n<li>\n<p><code>--valid-until<\/code>\u00a0for the\u00a0<code>VALID UNTIL<\/code>\u00a0clause<\/p>\n<\/li>\n<li>\n<p><code>--bypassrls<\/code>\/<code>--no-bypassrls<\/code>\u00a0for the\u00a0<code>BYPASSRLS<\/code>\/<code>NOBYPASSRLS<\/code>\u00a0attributes<\/p>\n<\/li>\n<li>\n<p><code>-m<\/code>\/<code>--member<\/code>\u00a0to grant membership to the created role<\/p>\n<\/li>\n<li>\n<p><code>-a<\/code>\/<code>--admin<\/code>\u00a0to grant membership to the created role using the\u00a0<code>WITH ADMIN OPTION<\/code>\u00a0clause<\/p>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3474\/\"><strong>Checkpoint and redo LSN added to LogCheckpointEnd log message<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/62c46eee\">62c46eee<\/a>  <\/p>\n<p>After a checkpoint is made, WAL takes a record with the LSN of the checkpoint and the LSN of the recovery point (redo LSN).<\/p>\n<p>The two LSNs for the latest checkpoint are stored in the control file:<\/p>\n<pre><code class=\"pgsql\">$ pg_controldata | egrep 'Latest.*location'<\/code><\/pre>\n<pre><code>Latest checkpoint location:         1\/1A3DEB20 Latest checkpoint's REDO location:  1\/1A3DEAE8<\/code><\/pre>\n<p>In PostgreSQL 16, these records are now mentioned in the checkpoint completion message in the server log:<\/p>\n<pre><code>2022-08-02 12:15:17.961 MSK [198868] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.072 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=418462 kB; lsn=1\/1A3DEB20, redo lsn=1\/1A3DEAE8<\/code><\/pre>\n<p>Note that in PostgreSQL 15 and higher, the\u00a0<em>log_checkpoints<\/em>\u00a0parameter is on by default.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3644\/\"><strong>pg_prepared_statements.result_types<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/84ad713c\">84ad713c<\/a>,\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/6ffff0fd\">6ffff0fd<\/a><\/p>\n<p>The\u00a0<code>pg_prepared_statements<\/code>\u00a0view now has a column\u00a0<code>result_types<\/code>.<\/p>\n<pre><code class=\"pgsql\">PREPARE get_booking (text) AS SELECT * FROM bookings WHERE book_ref = $1; SELECT * FROM pg_prepared_statements WHERE name = 'get_booking'\\gx<\/code><\/pre>\n<pre><code>-[ RECORD 1 ]---+----------------------------------------------- name            | get_booking statement       | PREPARE get_booking (text) AS                 + | SELECT * FROM bookings WHERE book_ref = $1; prepare_time    | 2022-08-02 16:54:14.313221+03 parameter_types | {text} result_types    | {character,\"timestamp with time zone\",numeric} from_sql        | t generic_plans   | 0 custom_plans    | 0<\/code><\/pre>\n<p>For prepared statements that return nothing the column will remain empty.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3660\/\"><strong>New parameter log_parameter_max_length for auto_explain<\/strong><\/a><strong><br \/><\/strong>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/d4bfe412\">d4bfe412<\/a><\/p>\n<p>The new parameter\u00a0<em>auto_explain.log_parameter_max_length<\/em>\u00a0serves to log query parameters, just like\u00a0<em>log_parameter_max_length<\/em>.<\/p>\n<pre><code class=\"pgsql\">LOAD 'auto_explain'; SHOW auto_explain.log_parameter_max_length;<\/code><\/pre>\n<pre><code>auto_explain.log_parameter_max_length  1 (1 row)<\/code><\/pre>\n<p>The default value is -1. This means that it will display parameter values in full. If set to 0, no parameter values will be displayed. A positive value limits the output length in bytes.<\/p>\n<p>Let&#8217;s set up logging and run a parameterized query\u00a0<code>EXECUTE... USING<\/code>\u00a0within an anonymous PL\/pgSQL block.<\/p>\n<pre><code class=\"pgsql\">SET auto_explain.log_min_duration = 0; SET auto_explain.log_nested_statements = 'on'; SET auto_explain.log_level = 'NOTICE'; DO $$BEGIN EXECUTE 'SELECT $;<\/code><\/pre>\n<pre><code>NOTICE:  duration: 0.003 ms  plan: Query Text: SELECT $1 Query Parameters: $1 = '42' Result  (cost=0.00..0.01 rows=1 width=4) DO<\/code><\/pre>\n<p>Note the newly added\u00a0<em>Query Parameters<\/em>\u00a0string listing the parameters the query from <em>Query Text<\/em> was run wit.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3714\/\"><strong><u>Subquery alias in FROM clause became optional<\/u><\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/bcedd8f5\">bcedd8f5<\/a><\/p>\n<p>The SQL standard demands that any subquery within a\u00a0<code>FROM<\/code>\u00a0clause has an alias:<\/p>\n<pre><code class=\"pgsql\">15=# SELECT * FROM (SELECT 42 AS a);<\/code><\/pre>\n<pre><code>ERROR:  subquery in FROM must have an alias LINE 1: SELECT * FROM (SELECT 42 AS a); ^ HINT:  For example, FROM (SELECT ...) [AS] foo.<\/code><\/pre>\n<p>This complicates migration from other DBMS that don&#8217;t strongly demand an alias here.<\/p>\n<p>PostgreSQL 16 makes these aliases optional.<\/p>\n<pre><code class=\"pgsql\">16=# SELECT * FROM (SELECT 42 AS a);<\/code><\/pre>\n<pre><code>a   42 (1 row)<\/code><\/pre>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3636\/\"><strong>REINDEX: syntax improvements and more<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/2cbc3c17\">2cbc3c17<\/a><\/p>\n<p><code>REINDEX DATABASE<\/code>\u00a0and\u00a0<code>REINDEX SYSTEM<\/code>\u00a0variations demand that a database is specified. The only valid option, however, is the current database, which makes the option redundant. This patch makes specifying the database optional.<\/p>\n<p>Additionally, the\u00a0<code>REINDEX DATABASE<\/code>\u00a0command now reindexes only user-generated indexes and skips system catalog table indexes. You can still rebuild all database indexes by running both\u00a0<code>REINDEX DATABASE<\/code>\u00a0and\u00a0<code>REINDEX SYSTEM<\/code>.<\/p>\n<p>This patch addresses the demand for an ability to reindex only user-generated indexes across the whole database. Plus, rebuilding system indexes is straight up impossible in\u00a0<code>CONCURRENTLY<\/code>\u00a0mode and may result in deadlocks in high load environments.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3708\/\"><strong>CREATE STATISTICS: statistic name is now optional<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/624aa2a1\">624aa2a1<\/a><\/p>\n<p>Extended statistics are separate database objects and therefore need names. But why not let PostgreSQL handle the naming without the user&#8217;s involvement? We already do that with indexes and integrity constraints.<\/p>\n<p>Done and done:<\/p>\n<pre><code class=\"pgsql\">CREATE STATISTICS ON departure_airport, arrival_airport FROM flights; \\d flights<\/code><\/pre>\n<pre><code>... Statistics objects: \"bookings.flights_departure_airport_arrival_airport_stat\" ON departure_airport, arrival_airport FROM flights<\/code><\/pre>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3479\/\"><strong>CREATE TABLE: the STORAGE attribute<\/strong><\/a> <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/784cedda\">784cedda<\/a><\/p>\n<p>Suppose we want to store images in our database. Let&#8217;s create a table:<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE images (filename text, data bytea); ALTER TABLE images ALTER COLUMN data SET STORAGE external;<\/code><\/pre>\n<p>In the second command, we change the TOAST storage strategy to\u00a0<code>external<\/code>\u00a0to prohibit data compression. After all, the image files are already compressed.<\/p>\n<p>The question is, why do this as a separate command, and not immediately specify the desired strategy as a part of the\u00a0<code>CREATE TABLE<\/code>\u00a0command? The answer is simple. Before PostgreSQL 16,\u00a0<code>CREATE TABLE<\/code>\u00a0didn&#8217;t not allow specifying the\u00a0<code>STORAGE<\/code>\u00a0attribute for columns. Now it does:<\/p>\n<pre><code class=\"pgsql\">CREATE TABLE images (filename text, data bytea STORAGE external);<\/code><\/pre>\n<p><a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/CA%2BTgmoZirCwArJms_fgvLBFrC6b%3DHdxmG7iAhv%2Bkt_%3DNBA7tEw%40mail.gmail.com\"><strong>The user created during cluster initialization now can&#8217;t be stripped of superuser privileges<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/e530be2c\">e530be2c<\/a><\/p>\n<p>Prior to PostgreSQL 16, the superuser created during cluster initialization (<em>pg_authid.oid=10<\/em>) could have taken the\u00a0<code>SUPERUSER<\/code>\u00a0attribute from himself:<\/p>\n<pre><code class=\"pgsql\">15=# ALTER ROLE postgres NOSUPERUSER;<\/code><\/pre>\n<p>(If there were no other superusers, you had to restart the server in the single-user mode to restore the attribute.)<\/p>\n<p>PostgreSQL 16 makes it impossible for the superuser to lose the attribute on accident:<\/p>\n<pre><code class=\"pgsql\">16=# ALTER ROLE postgres NOSUPERUSER;<\/code><\/pre>\n<pre><code>ERROR:  permission denied: bootstrap user must be superuser<\/code><\/pre>\n<p>By the way, it was impossible to delete the superuser created during cluster initialization even in previous versions:<\/p>\n<pre><code class=\"pgsql\">CREATE ROLE root LOGIN SUPERUSER; \\c - root DROP ROLE postgres;<\/code><\/pre>\n<pre><code>ERROR:  cannot drop role postgres because it is required by the database system<\/code><\/pre>\n<p>In a nutshell, you need the initial superuser for the server to function properly and you shouldn&#8217;t delete it or strip its superuser privileges. For extra security, you might\u00a0<a href=\"https:\/\/www.postgresql.org\/message-id\/f941fa4a-a294-165a-82c4-947d3c8425f6%40joeconway.com\">consider<\/a>\u00a0making it a group role and stripping the\u00a0<code>LOGIN<\/code>\u00a0attribute from it.<\/p>\n<p><a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/20220630193848.5b02e0d6076b86617a915682%40sraoss.co.jp\"><strong>TRUNCATE triggerd on foreign tables are now supported<\/strong><\/a>  <br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/3b00a944\">3b00a944<\/a><\/p>\n<p>Some foreign data wrappers support the\u00a0<code>TRUNCATE<\/code>\u00a0command. This patch adds the ability to create a trigger on\u00a0<code>TRUNCATE<\/code>\u00a0for foreign tables for such wrappers.<\/p>\n<p><a href=\"https:\/\/commitfest.postgresql.org\/38\/3730\/\"><strong>New argument variation for pg_read_file\/pg_read_binary_file<\/strong><\/a><br \/>commit:\u00a0<a href=\"https:\/\/github.com\/postgres\/postgres\/commit\/283129e3\">283129e3<\/a><\/p>\n<p>The specification of the\u00a0<code>pg_read_file<\/code>\u00a0and the similar\u00a0<code>pg_read_binary_file<\/code>\u00a0functions was as follows:<\/p>\n<pre><code>p<\/code><\/pre>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-385906","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/385906","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=385906"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/385906\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=385906"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=385906"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=385906"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}