こんにちは、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 ( '', '' ); -- id = 1 INSERT INTO author ( first_name, last_name ) VALUES ( '', '' ); -- id = 2 INSERT INTO book ( author_id, name ) VALUES ( 1, ' ( ' || generate_series ( 1, 100000 ) || ')' ); INSERT INTO book ( author_id, name ) VALUES ( 2, ' ' ), ( 2, ' ' );
比較のために、通常のマテリアライズドビューを作成します（後者を作成するには、結果を選択して記録するのに少し時間がかかることに注意してください）。
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 = ' '; -- Hash Join (cost=24.58..2543.83 rows=482 width=119) (actual time=19.389..19.390 rows=2 loops=1) Hash Cond: (book.author_id = author.id) -> Seq Scan on book (cost=0.00..2137.02 rows=100002 width=59) (actual time=0.017..9.231 rows=100002 loops=1) -> Hash (cost=24.53..24.53 rows=4 width=68) (actual time=0.026..0.026 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on author (cost=0.00..24.53 rows=4 width=68) (actual time=0.019..0.020 rows=1 loops=1) Filter: (((first_name || ' '::text) || last_name) = ' '::text) Rows Removed by Filter: 1 Total runtime: 19.452 ms EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = ' '; -- Seq Scan on mvw_book (cost=0.00..2584.03 rows=7 width=77) (actual time=15.869..15.870 rows=2 loops=1) Filter: (author_name = ' '::text) Rows Removed by Filter: 100000 Total runtime: 15.905 ms
マテリアライズドビューのデータは積み上げられ、異なるテーブルから収集する必要はありません。 しかし、それだけではありません。インデックスを作成できるからです。 結果の改善：
CREATE INDEX idx_book_name ON mvw_book ( author_name ); EXPLAIN ANALYZE SELECT * FROM mvw_book WHERE author_name = ' '; -- Index Scan using idx_book_name on mvw_book (cost=0.42..8.54 rows=7 width=77) (actual time=0.051..0.055 rows=2 loops=1) Index Cond: (author_name = ' '::text) Total runtime: 0.099 ms
悪くない、検索はインデックスによって行われ、検索時間が大幅に短縮されました。
ただし、マテリアライズドビューを使用する場合は微妙な違いがあります。ビューを構成するテーブルに対する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 INTO vw_employee_top_salary ( name, salary ) VALUES ( ' ', 2500.0 ); UPDATE vw_employee_top_salary SET salary = 2200.0 WHERE name = ' '; DELETE FROM vw_employee_top_salary WHERE name = ''; -- SELECT * FROM vw_employee_top_salary;
INSERTはどのような場合でもビューで実行でき、 UPDATEおよびDELETEは、ベーステーブルのセットがビューの条件に該当する場合にのみ実行できることに注意してください。
INSERT INTO vw_employee_top_salary ( name, salary ) VALUES ( '', 0.0 ); -- UPDATE vw_employee_top_salary SET salary = 3000.0 WHERE name = ''; -- , salary 0.0 DELETE FROM vw_employee_top_salary WHERE name = ''; -- , salary 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' ); -- id = 1 INSERT INTO directory ( parent_id, name ) VALUES ( 1, 'lib' ); INSERT INTO directory ( parent_id, name ) VALUES ( 1, 'include' ); INSERT INTO directory ( parent_id, name ) VALUES ( NULL, 'var' ); -- id = 4 INSERT INTO directory ( parent_id, name ) VALUES ( 4, 'opt' ); -- id = 5 INSERT INTO directory ( parent_id, name ) VALUES ( 5, 'tmp' ); INSERT INTO directory ( parent_id, name ) VALUES ( 4, 'log' ); -- id = 7 INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'samba' ); INSERT INTO directory ( parent_id, name ) VALUES ( 7, 'news' );
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 ) );
現在のデータベースに戻り、外部データソースを設定しましょう：
-- CREATE EXTENSION postgres_fdw; -- CREATE SERVER fdb_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', dbname 'fdb' ); -- CREATE USER MAPPING FOR PUBLIC SERVER fdb_server OPTIONS ( password 'pwd' ); -- CREATE FOREIGN TABLE fdb_city ( country text, name text ) SERVER fdb_server OPTIONS ( table_name 'city' );
これで、外部テーブルを操作できます。
-- INSERT INTO fdb_city ( country, name ) VALUES ( 'USA', 'Las Vegas' ); -- UPDATE fdb_city SET name = 'New Vegas' WHERE name = 'New Vegas'; -- , SELECT * FROM fdb_city;
データが実際に正しい場所に到達したことを確認するには、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 ); -- () SELECT value ->> 'username' FROM param WHERE name = 'connection'; -- Administrator -- ( ) () SELECT value #>> '{databases,0}' FROM param WHERE name = 'connection'; -- db0 -- SETOF ( key, value ) text () SELECT json_each_text ( value ) FROM param; -- (username,Administrator) (login,root) (databases,"[ ""db0"", ""db1"" ]") (enable,"{ ""day"" : 0, ""night"" : 1 }") (from,db0) (to,db1) -- () SELECT json_object_keys ( value ) FROM param; -- username login databases enable from to -- () SELECT * FROM json_populate_record ( null::t_link, ( SELECT value FROM param WHERE name = 'link' ) ); -- db0;db1 -- () SELECT json_array_elements ( value -> 'databases' ) FROM param; -- "db0" "db1"
要約すると、私はPostgreSQLの開発に満足していると言いたいと思います 。プロジェクトは開発中ですが、まだ未加工のものがあります。
PS最後まで読んでくれてありがとう。
参照：