こんにちは、habracheloveki！ 少し前のことですが、
PostgreSQL 9.3のリリースがリリースされました。クライアントパーツに関する最も重要な技術革新について知りたいと思います。 この記事では、以下について説明します。
- 実体化された表現
- 更新されたビュー
- イベントトリガー
- 再帰表現
- 横方向の付着
- 可変外部テーブル
- JSONタイプを操作するための関数と演算子
マテリアライズドビュー
マテリアライズドビューは、クエリの結果を含む物理的なデータベースオブジェクトです。 間違いなく最も予想されるイノベーションの1つです。
PostgreSQLでの操作方法を見てみましょう。
著者のディレクトリと著者へのリンクを含む本のディレクトリを作成します。
CREATE TABLE author ( id serial NOT NULL, first_name text NOT NULL, last_name text NOT NULL, CONSTRAINT pk_author_id PRIMARY KEY ( id ), CONSTRAINT uk_author_name UNIQUE ( first_name, last_name ) ); CREATE TABLE book ( id serial NOT NULL, author_id integer NOT NULL, name text NOT NULL, CONSTRAINT pk_book_id PRIMARY KEY ( id ), CONSTRAINT fk_book_author_id FOREIGN KEY ( author_id ) REFERENCES author ( id ), CONSTRAINT uk_book_name UNIQUE ( author_id, name ) );
これらの表にデータを入力します-数人の著者を追加し、非常に多くの本を生成します。
INSERT INTO author ( first_name, last_name ) VALUES ( '', '' );
比較のために、通常のマテリアライズドビューを作成します（後者を作成するには、結果を選択して記録するのに少し時間がかかることに注意してください）。
CREATE VIEW vw_book AS SELECT book.id, author.first_name || ' ' || author.last_name AS author_name, book.name FROM book INNER JOIN author ON author.id = book.author_id; CREATE MATERIALIZED VIEW mvw_book AS SELECT book.id, author.first_name || ' ' || author.last_name AS author_name, book.name FROM book INNER JOIN author ON author.id = book.author_id;
次に、通常のマテリアライズドビューの条件を含むクエリプランを見てみましょう。
EXPLAIN ANALYZE SELECT * FROM vw_book WHERE author_name = ' ';
マテリアライズドビューのデータは積み上げられ、異なるテーブルから収集する必要はありません。 しかし、それだけではありません。インデックスを作成できるからです。 結果の改善：
CREATE INDEX idx_book_name ON mvw_book ( author_name ); EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = ' ';
悪くない、検索はインデックスによって行われ、検索時間が大幅に短縮されました。
ただし、マテリアライズドビューを使用する場合は微妙な違いがあります。ビューを構成するテーブルに対する
DML操作の後、ビューを更新する必要があります。
INSERT INTO book ( author_id, name ) VALUES ( 2, ' ' ); REFRESH MATERIALIZED VIEW mvw_book;
これはトリガーで自動化できます：
CREATE OR REPLACE FUNCTION mvw_book_refresh ( ) RETURNS trigger AS $BODY$ BEGIN REFRESH MATERIALIZED VIEW mvw_book; RETURN NULL; END $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER tr_book_refresh AFTER INSERT OR UPDATE OR DELETE ON book FOR EACH STATEMENT EXECUTE PROCEDURE mvw_book_refresh ( ); CREATE TRIGGER tr_author_refresh AFTER INSERT OR UPDATE OR DELETE ON author FOR EACH STATEMENT EXECUTE PROCEDURE mvw_book_refresh ( );
マテリア
ライズされた表現をシミュレートする機能は
PostgreSQL 9.2で実行できますが（テーブル、テーブルへのインデックス、トリッキーなクエリを実行するトリガーを作成することにより）、一般的にこれは便利な革新です。
更新されたビュー
DML操作は、更新されたビューに適用できます。 確かに、そのような表現の要件は高いです：
WITHリスト、
DISTINCT 、
GROUP BY 、
HAVING 、
LIMITおよび
OFFSET演算子なしで、セット（
UNION 、
INTERSECTおよび
EXCEPT ）およびフィールドの操作なしの
FROMリスト内の1つのエンティティ（テーブル、ビュー）のみ機能や操作を適用しないでください。
アクションの更新されたビュー：
CREATE TABLE employee ( id serial NOT NULL, fullname text NOT NULL, birthday date, salary numeric NOT NULL DEFAULT 0.0, CONSTRAINT pk_employee_id PRIMARY KEY ( id ), CONSTRAINT uk_employee_fullname UNIQUE ( fullname ), CONSTRAINT ch_employee_salary CHECK ( salary >= 0.0 ) ); INSERT INTO employee ( fullname, salary ) VALUES ( ' ', 800.0 ); INSERT INTO employee ( fullname, salary ) VALUES ( ' ', 2000.0 ); INSERT INTO employee ( fullname, salary ) VALUES ( '', 1500.0 ); CREATE VIEW vw_employee_top_salary AS SELECT employee.fullname AS name, employee.salary FROM employee WHERE employee.salary >= 1000.0;
INSERTはどのような場合でもビューで実行でき、
UPDATEおよび
DELETEは、ベーステーブルのセットがビューの条件に該当する場合にのみ実行できることに注意してください。
INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( '', 0.0 );
提出
ルールを使用して、より高度なことを行うことができ
ます 。
イベントトリガー
また、かなり期待される革新。 DBの
DDLコマンドをインターセプトできます。 これらは、特定のテーブルを参照せずにグローバルであるという点で通常のトリガーとは異なりますが、どのコマンドに応答するかを指定できます。
次のように作成されました。
CREATE OR REPLACE FUNCTION event_trigger_begin ( ) RETURNS event_trigger AS $BODY$ BEGIN RAISE NOTICE '(begin) tg_event = %, tg_tag = %', TG_EVENT, TG_TAG; END; $BODY$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION event_trigger_end ( ) RETURNS event_trigger AS $BODY$ BEGIN RAISE NOTICE '(end) tg_event = %, tg_tag = %', TG_EVENT, TG_TAG; END; $BODY$ LANGUAGE plpgsql; CREATE EVENT TRIGGER tr_event_begin ON ddl_command_start EXECUTE PROCEDURE event_trigger_begin ( ); CREATE EVENT TRIGGER tr_event_end ON ddl_command_end EXECUTE PROCEDURE event_trigger_end ( );
テーブルでさまざまな
DDL操作を実行します。
CREATE TABLE article ( id SERIAL NOT NULL, name text NOT NULL, CONSTRAINT pk_article_id PRIMARY KEY ( id ), CONSTRAINT uk_article_name UNIQUE ( name ) ); ALTER TABLE article ADD COLUMN misc numeric; ALTER TABLE article ALTER COLUMN misc TYPE text; ALTER TABLE article DROP COLUMN misc; DROP TABLE article;
出力は次のようになります。
tg_event = ddl_command_start, tg_tag = CREATE TABLE tg_event = ddl_command_end, tg_tag = CREATE TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = ALTER TABLE tg_event = ddl_command_end, tg_tag = ALTER TABLE tg_event = ddl_command_start, tg_tag = DROP TABLE tg_event = ddl_command_end, tg_tag = DROP TABLE
plpgsqlを通じて、イベントに関する情報（
TG_EVENT ）と、実際にはチームに関する情報（
TG_TAG ）のみが利用可能ですが、将来的には改善されることを願っています。
再帰的ビュー
ビューを構築する必要がある場合、
WITH RECURSIVE構文を単純化します。
テーブルを作成し、テストデータを入力します。
CREATE TABLE directory ( id serial NOT NULL, parent_id integer, name text NOT NULL, CONSTRAINT pk_directory_id PRIMARY KEY ( id ), CONSTRAINT fk_directory_parent_id FOREIGN KEY ( parent_id ) REFERENCES directory ( id ), CONSTRAINT uk_directory_name UNIQUE ( parent_id, name ) ); INSERT INTO directory ( parent_id, name ) VALUES ( NULL, 'usr' );
WITH RECURSIVEを使用したクエリ、および再帰表現を使用したクエリ：
WITH RECURSIVE vw_directory ( id, parent_id, name, path ) AS ( SELECT id, parent_id, name, '/' || name FROM directory WHERE parent_id IS NULL AND name = 'var' UNION ALL SELECT d.id, d.parent_id, d.name, t.path || '/' || d.name FROM directory d INNER JOIN vw_directory t ON d.parent_id = t.id ) SELECT * FROM vw_directory ORDER BY path; CREATE RECURSIVE VIEW vw_directory ( id, parent_id, name, path ) AS SELECT id, parent_id, name, '/' || name FROM directory WHERE parent_id IS NULL AND name = 'var' UNION ALL SELECT d.id, d.parent_id, d.name, t.path || '/' || d.name FROM directory d INNER JOIN vw_directory t ON d.parent_id = t.id; SELECT * FROM vw_directory ORDER BY path;
実際、再帰ビューは
WITH RECURSIVEのラッパーです。これは、フォーカスされたビューのテキストを見ると表示できます。
CREATE OR REPLACE VIEW vw_directory AS WITH RECURSIVE vw_directory(id, parent_id, name, path) AS ( SELECT directory.id, directory.parent_id, directory.name, '/'::text || directory.name FROM directory WHERE directory.parent_id IS NULL AND directory.name = 'var'::text UNION ALL SELECT d.id, d.parent_id, d.name, (t.path || '/'::text) || d.name FROM directory d JOIN vw_directory t ON d.parent_id = t.id ) SELECT vw_directory.id, vw_directory.parent_id, vw_directory.name, vw_directory.path FROM vw_directory;
横の順守
サブクエリからの外部クエリからエンティティにアクセスできます。 使用例（パブリックスキーマのエンティティのフィールド数のみをカウント）：
SELECT t.table_schema || '.' || t.table_name, q.columns_count FROM information_schema.tables t, LATERAL ( SELECT sum ( 1 ) AS columns_count FROM information_schema.columns c WHERE t.table_schema IN ( 'public' ) AND t.table_schema || '.' || t.table_name = c.table_schema || '.' || c.table_name ) q ORDER BY 1;
可変外部テーブル
新しい
postgres_fdwモジュール。別のデータベースにあるデータへの読み取り/書き込みアクセスを取得できます。 以前は、このような機能は
dblinkにありましたが、
postgres_fdwではすべてがより透過的で標準化された構文であり、より良いパフォーマンスを得ることができます。
postgres_fdwの使用方法を見てみましょう。
新しい
fdbデータベースとその中にテストテーブルを作成します（現在のデータベースの外部になります）。
CREATE TABLE city ( country text NOT NULL, name text NOT NULL, CONSTRAINT uk_city_name UNIQUE ( country, name ) );
現在のデータベースに戻り、外部データソースを設定しましょう：
これで、外部テーブルを操作できます。
データが実際に正しい場所に到達したことを確認するには、fdbデータベースに切り替えて、以下を確認します。
SELECT * FROM city;
JSONタイプを操作するための関数と演算子
JSONタイプは
PostgreSQL 9.2に登場しましたが、array_to_json（
JSONへの配列変換）とrow_to_json（
JSONへのレコード変換）の2つの関数しかありませんでした。 さらに多くの関数があり、このタイプで完全に作業できます。
CREATE TYPE t_link AS ( "from" text, "to" text ); CREATE TABLE param ( id serial NOT NULL, name text NOT NULL, value json NOT NULL, CONSTRAINT pk_param_id PRIMARY KEY ( id ), CONSTRAINT uk_param_name UNIQUE ( name ) ); INSERT INTO param ( name, value ) VALUES ( 'connection', '{ "username" : "Administrator", "login" : "root", "databases" : [ "db0", "db1" ], "enable" : { "day" : 0, "night" : 1 } }'::json ), ( 'link', '{ "from" : "db0", "to" : "db1" }'::json );
要約すると、私は
PostgreSQLの開発に満足していると言いたいと思い
ます 。プロジェクトは開発中ですが、まだ未加工のものがあります。
PS最後まで読んでくれてありがとう。
参照：