まず、いくつか質問をします。
- 範囲に値のギャップを含めることはできますか？
- jsonタイプにインデックス表記が必要なのはなぜですか？
- インデックスは、頻繁に更新されるテーブルよりも大きくなることはありますか？そして、まったく成長しないのですか？
- idle_in_transactionでセッションがアイドル状態になっている時間はどれくらいですか？
- システムカタログテーブルのER図を作成するにはどうすればよいですか？
クライアントアプリケーション
psql：\ dX-拡張統計の表示
commit： ad600bba
新しいコマンド\ dXは、拡張統計オブジェクトをリストとして表示します。
CREATE STATISTICS flights_from_to (dependencies, MCV)
ON departure_airport, arrival_airport
FROM flights;
\x
\dX
List of extended statistics -[ RECORD 1 ]+------------------------------------------------ Schema | bookings Name | flights_from_to Definition | departure_airport, arrival_airport FROM flights Ndistinct | Dependencies | defined MCV | defined
統計のタイプ（依存関係、Ndistinct、MCV）ごとに、収集の事実のみが表示されます。値自体はpg_statistic_ext_dataで確認する必要があります。デフォルトでは、スーパーユーザーのみがアクセスできます。
psql：\ dtSはTOASTテーブルの
コミットを示しています： 7d80441d
以前は\ dコマンドで別のTOASTテーブルを表示できました。ただし、\ dtまたは\ dtSコマンドでそのようなテーブルのリストを取得することはできませんでした。省略が修正され、\ dtSはサービステーブルであるTOASTテーブルを表示するようになりました。
ただし、注意点が1つあります。すべてのTOASTテーブルはpg_toastスキーマにあり、search_pathに含まれる可能性はほとんどありません 。したがって、リストを取得するには、適切なテンプレートを指定する必要があります。
\dtS pg_toast.*165*
List of relations Schema | Name | Type | Owner ----------+----------------+-------------+---------- pg_toast | pg_toast_16529 | TOAST table | postgres pg_toast | pg_toast_16539 | TOAST table | postgres pg_toast | pg_toast_16580 | TOAST table | postgres
CLOSE、FETCH、MOVE、およびDECLARE コマンド
commitのPsqlタブ補完が改善されました： 3f238b88
追加の説明は必要ありません。
ドキュメンテーション
ドキュメントの校正と編集の
コミット： 2a5862f0
多くの人がPostgreSQLには優れたドキュメントがあると述べています。しかし、それは通常ペンのマスターとは見なされない開発者自身によって書かれています。どのようにして高品質を維持していますか？それは簡単です。他の執筆と同様に、編集者と校正者が必要です。そのため、Justin Prizzbyは過去2年間、ドキュメントの校正という非常に重要な仕事をしてきました。その結果、18個のパッチの大きなリストが作成されます。そして、コミッターとしてのミカエル・パキエは彼を助けました。
そして、それはたった1つの大きなコミットメントです。また、ドキュメントを改善する小さなパッチの数は数えられません。
その他
Idle_session_timeoutパラメーター-アイドルセッションの強制終了
commit： 9877374b
新しいidle_session_timeoutパラメーター は、アイドルセッションのタイムアウトを指定します。設定した制限時間を超えると、セッションは終了します。このパラメーターは、9.6で登場したidle_in_transaction_session_timeoutと非常に似てい ますが、開始されたトランザクションがないセッションにのみ影響します。したがって、アイドルセッションを中断する場合は、トランザクションが開始されているかどうかに関係なく、両方のパラメーターを設定する必要があります。
このパラメーターは、接続プラーまたはpostgres_fdw接続を使用するシステムでは細心の注意を払って使用することをお勧めします。
このパラメータは、セッションのすべてのユーザーが設定できます。次の例では、パラメータを設定して1秒待つと、サーバーログにセッション終了エントリが表示されます。その後、リクエストの実行は失敗しますが、psqlは自動的に新しい接続を確立します。
SET idle_session_timeout = '1000ms';
--
\! tail -n 1 logfile
2021-02-01 12:25:06.716 MSK [5262] FATAL: terminating connection due to idle-session timeout
SHOW idle_session_timeout;
FATAL: terminating connection due to idle-session timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
SHOW idle_session_timeout;
idle_session_timeout ---------------------- 0
depeszによる説明。
サーバーログメッセージ
コミットのGSS情報： dc11f31a
この認証方法が使用された場合、サーバーログの新しいセッション接続メッセージがGSS情報で更新されました。
pageinspect：インデックスの関数GiST
commit： 756ab291
GiSTインデックスの編成と保存の調査に関心のある人のために、pageinspect拡張機能は新しい機能を提供し ます。
IF NOT EXISTS
コミットを使用したコマンドのEXPLAIN動作を修正： e665769e
IF NOT EXISTSオプションを使用して既存のテーブルを作成しようとすると、警告が表示されます。
CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping
ただし、そのようなコマンドの計画を立てると、予期しない結果が発生します。EXPLAINがSELECTの計画を出力するだけで、コマンドはチケットテーブルの存在を確認する前にそれを構築することができます。そして警告はありません！
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
QUERY PLAN ----------------------------------------------------------------- Seq Scan on bookings (cost=0.00..4301.88 rows=262788 width=21)
EXPLAIN ANALYZEは、警告ではなく失敗します。
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
ERROR: relation "tickets" already exists
バージョン14では、動作が予測可能になりました。
EXPLAIN CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN ANALYZE CREATE TABLE IF NOT EXISTS tickets AS SELECT * FROM bookings;
NOTICE: relation "tickets" already exists, skipping QUERY PLAN ------------ (0 rows)
EXPLAIN [ANALYZE] CREATE MATERIALIZED VIEW IF NOTEXISTSコマンドの同じ変更。
システム・カタログ表に追加しましたプライマリおよびユニークキーが
コミット： dfb75e47、 62f34097
システム・カタログ表に追加しました整合性制約：プライマリおよびユニークキーを。以前は独自のインデックスがありましたが、現在はそれらに基づいて制限が設けられています。
これはそれがどのように見えるかです：
\d pg_class
Table "pg_catalog.pg_class" Column | Type | Collation | Nullable | Default ---------------------+--------------+-----------+----------+--------- oid | oid | | not null | relname | name | | not null | relnamespace | oid | | not null | ... ... Indexes: "pg_class_oid_index" PRIMARY KEY, btree (oid) "pg_class_relname_nsp_index" UNIQUE CONSTRAINT, btree (relname, relnamespace) "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)
ただし、例外があります：pg_depend、pg_shdepend。これらの2つのテーブルには2つのインデックスがあり、どちらも一意ではありません。独自の列の組み合わせを思いつくことはできませんでした。依存関係テーブルがどのように機能するかを理解することは簡単な作業ではないことを認めなければなりません。そして、主キーを作成できないことは、これを明確に確認するものです。
しかし、外部キーは追加されませんでした。これには正当な理由があります。
- 多くのテーブルでは、参照する実際のOIDがない場合、OID列は0になる可能性があります。外部キーを作成するには、すべての場所で0をNULLに置き換える必要があります。これは、実行する準備ができていない大規模な書き換え作業です。
- 多くのテーブルには、タイプoidだけでなく、oid []の潜在的な外部キーを持つ列があります。配列から外部キーを作成することはできません。
パッチの元々のアイデアは、データベースからの情報に基づいて、システムカタログ内のテーブル間の関係の図を作成することでした。これは、外部ツールによって自動的に実行できます。結局のところ、外部キーに関する情報なしで図を作成することは、手動で、リリースごとに定期的に変更することによってのみ行うことができ、これは非常に不便です。
コミットの直後、パッチのタスクが完了していないことに気付いたトムレーンは、システムカタログ内のすべてのテーブルの外部キーのリストを返す関数の形で妥協案を提案する新しい議論を開始しました 。パッチは1月のcommitfestの終了後に採用されましたが、今それを説明する方が論理的です。
したがって、システムカタログテーブルには外部キーがありません。ただし、pg_get_catalog_foreign_keys関数を呼び出すことで、それらに関する情報を取得できます。次のクエリは、誰がpg_attributeを参照しているかを示しています。
SELECT fktable, fkcols, is_array, is_opt
FROM pg_get_catalog_foreign_keys()
WHERE pktable = 'pg_attribute'::regclass
AND pkcols = ARRAY['attrelid','attnum'];
fktable | fkcols | is_array | is_opt ----------------------+-----------------------+----------+-------- pg_attrdef | {adrelid,adnum} | f | f pg_constraint | {conrelid,conkey} | t | t pg_constraint | {confrelid,confkey} | t | f pg_index | {indrelid,indkey} | t | t pg_statistic_ext | {stxrelid,stxkeys} | t | f pg_statistic | {starelid,staattnum} | f | f pg_trigger | {tgrelid,tgattr} | t | f pg_partitioned_table | {partrelid,partattrs} | t | t (8 rows)
モニタリング
Log_recovery_conflict_waitsパラメーター-回復の競合を解決するための長い待機のログ記録
commit： 0650ff23
新しいlog_recovery_conflict_waitsパラメーターが 有効になっている場合、タイムアウトがdeadlock_timeoutを超えると、起動プロセスによる回復の競合解決の待機がサーバーログに記録されます。
状況をシミュレートしてみましょう。レプリカで、パラメーターを有効にしてから、トランザクションを開始して待機します。
ALTER SYSTEM SET log_recovery_conflict_waits = on;
SELECT pg_reload_conf();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM t;
そして今マスターに：
DELETE FROM t;
VACUUM t;
1秒後（ deadlock_timeout）、競合を説明する詳細メッセージがレプリカログに表示されます。競合するプロセスの番号、スタックしたLSNの位置、およびブロック番号を含むファイル名（テーブルの読み取り）は次のとおりです。
LOG: recovery still waiting after 1023.267 ms: recovery conflict on snapshot DETAIL: Conflicting process: 29119. CONTEXT: WAL redo at 0/1001BEB0 for Heap2/CLEAN: latestRemovedXid 717; blkref #0: rel 1663/16384/17198, blk 0
さらに30秒待機した後（ max_standby_streaming_delay）、レプリカのセッションは、そのような場合に終了するはずです。
これは、前回のcommitfestで開始および説明された作業の続きです 。
Pg_stat_databaseビュー-ユーザーセッションの
コミットに関する統計を追加しました： 960869da
モニタリングシステムの開発者は、やるべきことがもっとあります。便利でクールな変更が監視システムのユーザーを待っています！
pg_stat_databaseには、クラスター内の各データベースのユーザーセッションに関する追加情報を含む多くの列が表示されています。
- session_time-このデータベースで費やされたすべてのセッションの合計時間。
- active_time ― , ;
- idle_in_transaction_time ― ;
- sessions ― ;
- sessions_abandoned ― - ;
- sessions_fatal ― - FATAL;
- sessions_killed ― .
depeszによる説明。
ps：チェックポイントが実行されたときにプロセスのステータスを更新する
commit： df9274ad
pg_stat_activityを照会することにより、起動プロセスとチェックポインタープロセスを監視できます。ただし、pg_stat_activityビューが使用できず、チェックポインターが機能する状況は3つあります。これらは、クラッシュリカバリプロセスの終了時のチェックポイント、サーバー停止中のチェックポイント、およびレプリカ停止中の再起動ポイントです。
これらの3つの状況では、たとえばpsユーティリティを使用して、オペレーティングシステムの起動プロセスとチェックポインタプロセスのステータスを監視できます。
典型的な例は災害復旧です。最終的に、WALからの変更をロールオーバーした後、起動プロセスがチェックポイントを実行します。これには時間がかかる場合があります。ただし、起動プロセスのステータスは変更されず、「NNNの回復中」と表示されます。変更のロールフォワードが完了し、チェックポイントの完了を待つことは残っていることを知っておくと便利ですが。ステータスが更新され、緊急時にDBAのアラートレベルが下がります。
pg_stat_statements：
コミット統計がフラッシュされた とき： 2e0fedf0pg_stat_statements統計が
定期的にフラッシュされるべきであることは間違いありません。それ以外の場合、昨日、1週間前、1か月、1年に実行されたリクエストに関する情報を蓄積するポイントは何ですか...
しかし、統計が最後にリセットされたのはいつかをどうやって知るのでしょうか？とてもシンプルです。pg_stat_statements_infoを調べます。
SELECT now(), pg_stat_statements_reset();
now | pg_stat_statements_reset -------------------------------+-------------------------- 2021-02-03 13:25:44.738188+03 |
SELECT * FROM pg_stat_statements_info;
dealloc | stats_reset ---------+------------------------------- 0 | 2021-02-03 13:25:44.738468+03
pg_stat_statements_infoビューはバージョン14で導入されました。前回の記事でdealloc列について読むことができ ます。 depeszによる
説明。
さらに便利なのは、統計を定期的にダンプするだけでなく、各ダンプの前にpg_stat_statementsの内容を保存するというアイデアです。そして、定期的に実行されるスライスが多数存在する場合、過去の時間間隔の情報を取得することができます。このアプローチは、pgpro_pwrモニタリング拡張機能によって使用され ます。
コピー
コミットの進行状況： 8a4f618e
pg_stat_progress_ *ビューファミリーが更新されました！これで、COPYコマンドの進行状況を監視できます。
デモデータベースの論理コピーを作成しましょう。
\! pg_dump -d demo -Fc -f demo.dump
次に、postgresデータベースのコピーを2つのスレッドに展開し、プロセスの進行中に、pg_stat_progress_copyビューを確認します。
\! pg_restore tickets.dump -d postgres -j 2 &
SELECT pid, datname, relid::regclass, bytes_processed, bytes_total, lines_processed
FROM pg_stat_progress_copy\gx
-[ RECORD 1 ]---+------------------------- pid | 18771 datname | postgres relid | bookings.tickets bytes_processed | 19088527 bytes_total | 0 lines_processed | 189820 -[ RECORD 2 ]---+------------------------- pid | 18772 datname | postgres relid | bookings.boarding_passes bytes_processed | 14833287 bytes_total | 0 lines_processed | 567652
COPY…FROM'file 'コマンドが実行されたとき、bytes_total列はファイルサイズで埋められていました。ただし、上記の例では、ダウンロードはpg_dumpのコピーからのものであるため、サイズは不明です。
ステータスは、ダウンロード（COPY…FROM）だけでなく、データのダウンロード（COPY…TO）についても監視できます。 depeszによる
説明。
パフォーマンス
バッファキャッシュ
コミットのフラッシュの最適化： d6ad34f3、 bea449c6
多くの操作では、特定のテーブルに関連付けられているすべてのバッファをバッファキャッシュから削除する必要があります。これらの操作には、TRUNCATEおよびDROPテーブルコマンド、中断されたCREATE TABLE AS SELECTコマンド、およびテーブルの最後から空のブロックを削除する必要がある場合のVACUUMが含まれます。
削除するには、バッファキャッシュ全体をスキャンしますが、キャッシュサイズが大きいとコストがかかる可能性があります。これで、小さなテーブルの場合、占有されているバッファに関する情報を含む特別な構造がメモリに保持され、バッファキャッシュ全体のスキャンが回避されます。
テストでは、100GB以上のshared_buffersを使用すると、数千のテーブルの切り捨てが100倍以上高速になることが示されています。
これは、バージョン13で開始された作業の続きです 。
postgres_fdw：
コミットレコードを挿入するためのバッチモード： b663a413
外部テーブルからのデータの フェッチpostgres_fdwはバッチモードを使用します。レコードは、100のバッチ（fetch_sizeパラメーターのデフォルト値）で外部サーバーから転送されます 。これは、一度に1つずつ送信するよりも大幅に高速です。ただし、作業を1行ずつ挿入、変更、削除します。したがって、非常にゆっくりです。
FDW APIは、最適化のために改善されました。確かに、挿入操作のバッチモードの一部でのみ改善されました。更新と削除は将来の参照用です。もちろん、postgres_fdwは新しいAPIを利用した最初のラッパーでした。
何が起きたのか見てみましょう。デモデータベースの外部テーブルを操作するようにpostgres_fdwを構成します。
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER remote_server
OPTIONS (user 'postgres');
外部テーブルは、隣接するpostgresデータベースに配置されます。
postgres=# CREATE TABLE bookings(
book_ref char(6),book_date timestamptz, total_amount numeric(10,2)
);
ローカルテーブルへの挿入速度を基準速度としてみましょう。そして、測定のタイミングを有効にします。
CREATE TABLE bookings_local (LIKE bookings);
\timing
ローカルテーブルに挿入します。
INSERT INTO bookings_local SELECT * FROM bookings;
INSERT 0 262788 Time: 165,653 ms
そして、外部テーブルに挿入すると、バッチモードが無効になります。（実際には有効になっていますが、デフォルトではバッチサイズは1行です）。
CREATE FOREIGN TABLE bookings_remote_no_batch (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings'
);
INSERT INTO bookings_remote_no_batch SELECT * FROM bookings;
INSERT 0 262788 Time: 6729,867 ms (00:06,730)
ほぼ40倍遅い！そして、これは同じクラスターのベース間であり、ネットワーク遅延はありません。
実験を繰り返しますが、バッチサイズ（batch_size）を100に設定します。
CREATE FOREIGN TABLE bookings_remote_batch_100 (
book_ref char(6), book_date timestamptz, total_amount numeric(10,2)
) SERVER remote_server OPTIONS (
schema_name 'public', table_name 'bookings',
batch_size '100'
);
INSERT INTO bookings_remote_batch_100 SELECT * FROM bookings;
INSERT 0 262788 Time: 679,632 ms
まったく別の問題。もちろん、ローカル挿入の損失はまだ顕著で、約4回ですが、40回ではありません。
そして最後に。挿入するバッチのサイズ（batch_size）は、外部サーバーのパラメーターで設定できます。その場合、明示的に定義されていないすべてのテーブルで有効になります。
下から上へのインデックス行の削除
コミット： 9dc718bd、 d168b666
この最適化は、インデックス列が変更されていない状況で、UPDATE操作でインデックスページを最後まで2で分割することを回避しようとします。新しいバージョンの行をインデックスに追加する前に、このページで不要な行を削除できるかどうかを確認する必要があります。たとえば、同じテーブル行を参照する不要な重複インデックス行のチェーンを見つけた場合、それらの行を削除できます。パッチの作者であるPeterGeiganは、これを「ボトムアップ削除」と呼びました。
同様の問題（インデックスの増加を回避するため）は、HOT更新の最適化によって解決されます。UPDATEがインデックス付きの列を変更しない場合、インデックス内の行の新しいバージョンが作成されない可能性があります。また、テーブルに複数のインデックスがあり、そのうちの1つだけの列が変更された場合はどうなりますか？この場合、HOTアップデートはアシスタントではありません。
「ボトムアップで削除」で何ができるか確認してみましょう。実験のために、2つの別々にインデックスが付けられた列があり、自動クリーニングが無効になっているテーブルを見てみましょう。
CREATE TABLE t(col1 int, col2 int) WITH (autovacuum_enabled=off);
CREATE INDEX t_col1 ON t(col1);
CREATE INDEX t_col2 ON t(col2);
INSERT INTO t VALUES (1, 1);
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size --------+-------------+------------- 8192 | 16384 | 16384
一括更新の前に、テーブルに1つの行があります。テーブルサイズは1ページで、両方のインデックスが2ページ（サービスページ+データページ）を占めます。
ここで、1つの列col2のみを100,000回変更し、テーブルとインデックスのサイズを確認します。
SELECT 'UPDATE t SET col2 = col2+1' FROM generate_series(1,100000)\gexec
SELECT pg_relation_size('t') AS t_size,
pg_relation_size('t_col1') AS t_col1_size,
pg_relation_size('t_col2') AS t_col2_size;
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 2121728 | 2260992
これらの結果はPostgreSQL12で取得されました。ご覧のとおり、HOT更新は機能せず、両方のインデックスのサイズがほぼ同じになりました。
PostgreSQL13でのこれと同じ実験：
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 663552 | 2260992
変更がなかったt_col1インデックスの増加は、はるかに少なく、約3.5倍でした。これは、有名なバージョン13の最適化の結果です： インデックス重複排除。しかし、それにもかかわらず、彼は成長しました。
そして最後に、PostgreSQL14の内容を見てみましょう。
t_size | t_col1_size | t_col2_size ---------+-------------+------------- 2818048 | 16384 | 2260992
Blimey！t_col1インデックスには、データが残っているページが1つだけあります。カッコいい！
もちろん、自動真空がオンになっている場合は、実験中にいくつかの期限をクリアする時間があります。しかし、それが実験の目的です。さらに、実際の状況では、頻繁に更新されるため（キューテーブルが良い例です）、autovacuumにはすべてを時間内にクリーンアップする時間がありません。 ViktorEgorovからの
説明。
並列実行REINDEXは、同時に
コミット： f9900df5
11月commitfest記事では、私はすでに 書いたCREATE INDEX CONCURRENTLYの非ブロック並列実行について。同様の最適化がREINDEXCONCURRENTLYで利用できるようになりました。
手続き型言語
プロシージャは
コミットを実行するのが高速です 。ee895a65
プロシージャはトランザクションを完了できるように考案されました。 COMMITを実行するそのようなプロシージャ自体が、たとえばループ内で何度も呼び出された場合、ループの各反復で、プロシージャ内のすべてのステートメントが再解析されます。
コマンドを再解析する重大な理由はありませんでしたが、パッチで削除されました。ループ内でプロシージャを呼び出すために必要な作業とリソースが少なくなりました。そして、その結果、それはより速く実行されます。
PL / pgSQLの：代入演算子は、完全に再設計
コミット： 844fe9f1、 c9d52984、 1788828d、 1c1cbe27これ
以上の苦労なし：
DO $$
<<local>>
DECLARE
a bookings[];
x bookings;
BEGIN
/* */
local.a[1].book_ref := 'ABCDEF';
local.a[1].book_date := current_date;
local.a[1].total_amount := 0;
/* */
local.a[2:3] := (SELECT array_agg(t.*)
FROM (SELECT b.* FROM bookings b LIMIT 2) AS t
);
FOREACH x IN ARRAY a LOOP
RAISE NOTICE '%', x;
END LOOP;
END;
$$;
NOTICE: (ABCDEF,"2021-02-04 00:00:00+03",0.00) NOTICE: (00000F,"2017-07-05 03:12:00+03",265700.00) NOTICE: (000012,"2017-07-14 09:02:00+03",37900.00) DO
これで、PL / pgSQLブロック内で、複合型の配列要素と配列スライスに値を割り当てることができます。
この目的のために、PL / pgSQL代入演算子は完全に再設計されました。そして、サーバーパーサーはPL / pgSQL式を解析することを学びました。
式を評価するために、
«
SELECTexprの ようなコマンドを発行する必要がなくなりました
»
。次の例のエラーメッセージを見ると、これを簡単に確認できます。
DO $$ BEGIN RAISE NOTICE '%', 2 + 'a'; END; $$;
ERROR: invalid input syntax for type integer: "a" LINE 1: 2 + 'a' ^ QUERY: 2 + 'a' CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE
SELECTワードはQUERY行にありません。
レプリケーション
ウィザードの
コミットでの 構成パラメーターの変更のレプリカ処理： 15251c0a
サーバーの共有メモリサイズに影響を与える設定へのウィザードの変更は、物理レプリカで見過ごされません。これらのパラメータが変更されたことを示すWALレコードがレプリカに到着すると、レプリカは自動的に停止し、現在のすべてのセッションを終了します。パラメータのリストは、ドキュメントにあり ます。
あまり良くありません。したがって、これを行いました。パラメータの変更の記録を受信すると、レプリカは引き続き機能しますが、レプリケーションを一時停止し、ログにメッセージを発行します。管理者は、重要なセッションが終了するのを待ち、構成ファイルのパラメーターをマスターの値と一致するように更新し、レプリケーションの一時停止を解除できます。レプリカはその後すぐに停止しますが、構成の変更が既に行われているため、すぐに開始でき、ダウンタイムを最小限に抑えることができます。
サーバー
コミット を再起動せずにrestore_commandを変更します。942305a3 バージョン13で採用されたSergeiKornilov
の作業の継続 。その後、サーバーを再起動せずにパラメータを変更することが可能になりました primary_conninfo、 primary_slot_nameおよび wal_receiver_create_temp_slot。
これで、restore_commandがそれらに追加されました 。
サーバ
拡張統計
コミットの使用法の改善： 25a9e54d
クエリの条件のカーディナリティを評価するために、より多くのケースで拡張統計が使用されるようになりました。特に、拡張統計を個別に使用できるさまざまな条件をORで組み合わせる場合に、拡張統計が使用されるようになります。
この例では、出発空港と到着空港の拡張統計を収集します。次に、シェレメーチエヴォ国際空港とプルコヴォ間または反対方向のフライト数をカウントします。
CREATE STATISTICS s ON departure_airport, arrival_airport FROM flights;
ANALYZE flights;
正確なフライト数は610です。バージョン13および14のプランナーの見積もりと比較してください。
EXPLAIN SELECT *
FROM flights
WHERE (departure_airport = 'SVO' AND arrival_airport = 'LED')
OR (departure_airport = 'LED' AND arrival_airport = 'SVO');
PostgreSQL 13：
Seq Scan on flights (cost=0.00..1054.42 rows=885 width=63)
PostgreSQL 14：
Seq Scan on flights (cost=0.00..1054.42 rows=607 width=63)
ご覧のとおり、バージョン14の見積もりはほぼ正確です。
任意のデータ型のインデックス表記をサポートするための一般的なインフラが
コミット： c7aba7c1、 0ec5f7e7は、 676887a3
インデックス表記は、配列を操作するために使用されます。たとえば、インデックス3の要素を見つけましょう。
SELECT (ARRAY[10,20,30,40,50])[3];
array ------- 30
ただし、この構文を使用すると便利なデータ型は他にもあります。まず、jsonについて話します。このパッチに関するDmitryDolgovの長い作業の旅が始まったのは、jsonのインデックス表記をサポートするというアイデアでした。
そして今、数年後、そのようなサポートが現れました。最初のパッチは、任意のデータ型に必要なインデックス表記インフラストラクチャを作成します。第2のパッチは、hstoreのタイプに指数表記を追加し、JSONの第3のB型 。
これで、特別な関数や演算子の代わりに、json値から必要な部分を抽出できます。いずれかのチケットの連絡先の詳細で電話番号を見つけましょう。
SELECT contact_data, contact_data['phone'] AS phone
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]+----------------------------------------------------------------------------------------------------------------------------------------------------------- contact_data | {"email": "antonova.irina04121972@postgrespro.ru", "phone": "+70844502960"} phone | "+70844502960"
インデックス表記を使用して、jsonbに書き込むこともできます。以前に見つかったIrinaAntonovaの連絡先にアドレスを追加しましょう。
UPDATE tickets
SET contact_data['address'] =
'{"city": "",
"street": " ",
"building": "7"
}'::jsonb
WHERE ticket_no = '0005432000994';
アドレス自体は複合であり、インデックス表記を使用してその一部を参照することもできることに注意してください。
SELECT contact_data['address'] AS address,
contact_data['address']['city'] AS city,
contact_data['address']['street'] AS street,
contact_data['address']['building'] AS building,
contact_data['phone'] AS phone,
contact_data['email'] AS email
FROM tickets
WHERE ticket_no = '0005432000994'\gx
-[ RECORD 1 ]---------------------------------------------------------------- address | {"city": "", "street": " ", "building": "7"} city | "" street | " " building | "7" phone | "+70844502960" email | "antonova.irina04121972@postgrespro.ru"
とても便利です！
（明確化。デモデータベース内のすべての連絡先は架空のものであり、Postgres Proにはそのような従業員はいません。） depeszからのhstoreの
説明。
SQLコマンド
マルチレンジデータ型
commit： 6df7a969
各レンジデータ型に独自のマルチレンジデータ型が追加されました。このタイプは、基本的に個々の範囲の配列です。マルチバンドタイプ内の範囲はオーバーラップしてはなりませんが、範囲間にギャップがある場合があります。
通常の範囲は、対応するサブタイプの値の連続した範囲です：intサブタイプのin4range範囲、timestampサブタイプのtimestamptz範囲など。しかし、いくつかの場所にギャップのある範囲を保存する必要がある場合はどうなりますか？ここでマルチバンドが助けになります。
PostgreSQLの各バージョンのテーブルにcommitfest時間を保存するとします。1回のcommitfestは、1か月の範囲と考えることができます。しかし、1つのバージョンの5つのcommitfestすべてをどのように表現するのでしょうか。
サブタイプtimestamptzの範囲はtstzrangeと呼ばれ、マルチレンジはtstzmultirangeです。使用可能なタイプは、ドキュメントに記載されてい ます。テーブルを作成します。
CREATE TABLE pg_commitfest (
version text,
working_period tstzmultirange
);
値を形成するには、コンストラクターを使用します。
INSERT INTO pg_commitfest VALUES
('13', tstzmultirange(
tstzrange('2019-07-01', '2019-08-01', '[)'),
tstzrange('2019-09-01', '2019-10-01', '[)'),
tstzrange('2019-11-01', '2019-12-01', '[)'),
tstzrange('2020-01-01', '2020-02-01', '[)'),
tstzrange('2020-03-01', '2020-04-07', '[]')
)
),
('14', tstzmultirange(
tstzrange('2020-07-01', '2020-08-01', '[)'),
tstzrange('2020-09-01', '2020-10-01', '[)'),
tstzrange('2020-11-01', '2020-12-01', '[)'),
tstzrange('2021-01-01', '2021-02-01', '[)'),
tstzrange('2021-03-01', '2021-04-01', '[)')
)
);
マルチバンドタイプを操作するための関数と演算子のリストには、通常のバンドと同じものに加えて、マルチバンドのみを対象としたものが含まれています。
たとえば、開発者コミュニティが過去1年間に取り組んだPostgreSQLのバージョンを確認できます。
SELECT version
FROM pg_commitfest
WHERE working_period @> '2021-01-01'::timestamptz;
version --------- 14
または、バージョン13での作業の開始日と終了日：
SELECT lower(working_period), upper(working_period)
FROM pg_commitfest
WHERE version = '13';
lower | upper ------------------------+------------------------ 2019-07-01 00:00:00+03 | 2020-04-07 00:00:00+03
新しいカスタムマルチレンジタイプを作成できます。これは、組み込みバンドおよび対応するマルチバンドタイプがない場合に役立ちます。同じCREATETYPE…ASRANGEコマンドが使用され、自動生成されたマルチレンジタイプの名前を指定できます。
たとえば、時間の範囲とマルチレンジ、サブタイプ時間に関心があります。範囲を作成するには、time型の2つの値の差を計算する関数が必要です：
CREATE FUNCTION time_diff(a time, b time) RETURNS double precision
AS $$
SELECT extract(epoch FROM (a - b));
$$ LANGUAGE sql STRICT IMMUTABLE;
時間範囲のタイプを作成すると同時に、マルチ範囲のタイプを作成します。
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_diff,
multirange_type_name = timemultirange
);
これで、作業時間は次の式で形成できます。
SELECT timemultirange(
timerange('09:00', '13:00', '[)'),
timerange('14:00', '18:00', '[)')
) AS working_hours;
working_hours ------------------------------------------- {[09:00:00,13:00:00),[14:00:00,18:00:00)}
depeszによる説明。
バイナリ文字列のLtrim関数とrtrim関数
commit： a6cf3df4
btrim関数を使用して、bytea文字列の最初と最後のバイトを同時にトリミングすることもできます。バイナリ文字列の新しいltrim関数とrtrim関数を使用して、各エッジを個別にトリミングできるようになりました。
コマンドGRANTおよびREVOKE
のGRANTEDBY句コミット： 6aaaa76b
GRANTおよびREVOKEコマンドのSQL標準との互換性のために、オプションの句GRANTEDBYが追加されました。例えば：
GRANT ALL ON TABLE table_name TO role_specification
[GRANTED BY role_specification];
REVOKE ALL ON TABLE table_name FROM role_specification
[GRANTED BY role_specification];
GRANTED BYの役割名は、現在の役割と一致する必要があります。したがって、別の役割に代わって権利を発行/取得することはできません。このフレーズは、標準に準拠するために追加されました。
これは、9月のcommitfestの記事で説明されている作業の続きです 。
システム管理
initdbを--no-命令が
コミット： e09155bd
initdbのユーティリティは、クラスタを初期化するために使用されます。そして、その作業の最後に、クラスターを開始する方法の指示が表示されます。
Success. You can now start the database server using: pg_ctl -D /usr/local/pgsql/data -l logfile start
しかし、これは常に正しいとは限りません。たとえば、Debianパッケージ配布では、pg_ctlではなくpg_ctlclusterユーティリティを使用してクラスターを開始します。そして彼女のパラメータは異なります。
新しい--no-instructionsオプションを使用すると、initdbは、パッケージディストリビューションが利用できる起動アドバイスを提供しなくなります。
pg_dumpは：スタンドアロンテーブルとして、単一のパーティションを復元
コミット： 9a4c0e36を、 9eabfe30
パーティションテーブルがpg_dumpの論理コピーに含まれている場合、そのようなコピーから独立したテーブルとして別のパーティションを復元することはできません。CREATETABLEコマンドの直後にALTERTABLE…ATTACHPARTITIONコマンドがあります。これは、このような状況では不要であるだけでなく、エラーで終了します。親テーブルは復元しませんでした。
CREATE TABLE t (id int) PARTITION BY RANGE(id);
CREATE TABLE t_p1 PARTITION OF t FOR VALUES FROM (1) TO (1001);
CREATE TABLE t_p2 PARTITION OF t FOR VALUES FROM (1001) TO (2001);
\! pg_dump -Fc -f db.dump
\! pg_restore db.dump -t t_p1 -f -
... CREATE TABLE public.t_p1 ( id integer ); ALTER TABLE ONLY public.t ATTACH PARTITION public.t_p1 FOR VALUES FROM (1) TO (1001); ...
これで、すべてのパーティションのALTERTABLE…ATTACHPARTITIONコマンドが個別にアンロードされ、その後、CREATETABLEパーティションを作成するためのすべてのコマンドがアンロードされます。したがって、-tオプションで指定された単一のパーティションを復元する場合、CREATE TABLEコマンドのみが実行され、パーティションを独立したテーブルとして復元することができます。
それは今のところすべてです。3月14日の最後のcommitfestを待ってい ます。