Updated materialized views of PostgreSQL 9.4

Updated materialized views of PostgreSQL 9.4

PostgreSQL version 9.4 has been released for a while and has updated a lot of content. One of them is the update of materialized views that are of interest. Compared with the original materialized view syntax, a concurrently parameter is added.

I. New Syntax:

-- CREATE syntax. create materialized view table_name [(column_name [,...])] is not updated. [WITH (storage_parameter [= value] [,...])] [TABLESPACE tablespace_name] AS query [WITH [NO] DATA] -- REFRESH the syntax refresh materialized view [CONCURRENTLY] name [WITH [NO] DATA]

Ii. Data preparation:

[S @ ~] $ Psqlpsql (9.4.1) Type "help" for help. S = # create table tbl_kenyon (id int, remark text); CREATE TABLEpostgres = # insert into tbl_kenyon select generate_series (MB), md5 (random (): text ); INSERT 0 1000000 postgres = # select * from tbl_kenyon limit 10; id | remark ---- + ------------------------------ 1 | California 2 | California 3 | California 4 | California 5 | California 6 | California 7 | California 8 | California 9 | California 10 | Connecticut (10 rows) -- create a simple materialized view S = # create materialized view mv_tbl_kenyon as select * from tbl_kenyon; SELECT 1000000 postgres = # \ d + List of relations Schema | Name | Type | Owner | Size | Description -------- + --------------- + --------------------- + ---------- + ------- + ------------- public | contents | materialized view | postgres | 65 MB | public | tbl_kenyon | table | postgres | 65 MB | (2 rows)

Iii. Test cases:

-- Test without concurrentlypostgres = # insert into tbl_kenyon values (1000001, md5 (random (): text); INSERT 0 1 S = # select max (id) from mv_tbl_kenyon; max --------- 1000000 (1 row) postgres = # \ timing Timing is on. postgres = # refresh materialized view duration; refresh materialized VIEWTime: 2056.460 ms -- test with concurrently, you need to create a unique index postgres = # insert into tbl_kenyon values (1000002, md5 (random ():: text); INSERT 0 1 Time: 9.434 mspostgres = # refresh materialized view concurrently mv_tbl_kenyon; ERROR: cannot refresh materialized view "public. mv_tbl_kenyon "concurrentlyHINT: Create a unique index with no WHERE clause on one or more columns of the materialized view. time: 22109.877 mspostgres = # create unique index idx_ken on mv_tbl_kenyon (id); CREATE INDEXTime: 707.721 mspostgres = # select max (id) from mv_tbl_kenyon; max --------- 1000001 (1 row) Time: 1.110 mspostgres = # begin; BEGINpostgres = # refresh materialized view concurrently mv_tbl_kenyon; refresh materialized VIEWTime: 24674.739 ms -- add a begin before refresh; -- the query will not be blocked when another session is enabled. Otherwise, the query will be blocked when concurrently is not added. ipvs = # select * from mv_tbl_kenyon limit 10; id | remark ---- + ------------------------------ 1 | California 2 | California 3 | California 4 | California 5 | California 6 | California 7 | California 8 | California 9 | California 10 | Connecticut (10 rows)

Iv. Source Code
The source code of the unique index can be viewed in matview. c:

-- First initialize the unique index: falsefoundUniqueIndex = false; -- if the unique index is found, the value is trueif (foundUniqueIndex) appendStringInfoString (& querybuf, "AND "); colname = quote_identifier (NameStr (tupdesc-> attrs [attnum-1])-> attname); appendStringInfo (& querybuf, "newdata. % s ", colname); type = attnumTypeId (matviewRel, attnum); op = lookup_type_cache (type, marker)-> eq_opr; mv_generateiterator (& querybuf, op); appendStringInfo (& q Uerybuf, "mv. % s", colname); foundUniqueIndex = true; -- if the unique index cannot be found, errorif (! FoundUniqueIndex) ereport (ERROR, (errcode (ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg ("cannot refresh materialized view \" % s \ "concurrently", matviewname ), errhint ("Create a unique index with no WHERE clause on one or more columns of the materialized view. "); appendStringInfoString (& querybuf," AND newdata OPERATOR (pg_catalog. * =) mv) "" WHERE newdata is null or mv is null "" order by tid ");

V. Summary:
1. The concurrently parameter is added to the new materialized view, so that the query of the materialized view is not locked when the view is refreshed.
2. The principle, advantages and disadvantages of this parameter are similar to the concurrently of the index. In exchange for the query lock by time, the refresh speed will become very slow.
3. There are no incremental refresh parameters. Sorry.

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: