Materialized view update for PostgreSQL version 9.4

PostgreSQL version 9.4 has been out for some time, but also updated a lot of content, one of which is more interested in the materialized view of the update, compared to the original materialized view syntax, a new concurrently parameter.

first, the new syntax:

--Create syntax, no updates

CREATE MATERIALIZED VIEW table_name

    [(column_name [, ...])]

    [WITH (storage_parameter [= value] [, ...])]

    [TABLESPACE tablespace_name]

    AS query

    [WITH [NO] DATA]

--Refresh syntax

REFRESH MATERIALIZED VIEW [CONCURRENTLY] name

    [WITH [NO] DATA]

2. Data preparation:

[[email protected] ~] $ psql

psql (9.4.1)

Type "help" for help.

postgres = # create table tbl_kenyon (id int, remark text);

CREATE TABLE

postgres = # insert into tbl_kenyon select generate_series (1,1000000), md5 (random () :: text);

INSERT 0 1000000

postgres = # select * from tbl_kenyon limit 10;

 id | remark

---- + ----------------------------------

  1 | d4fc1c7440a4d1672028586c2bb76514

  2 | 5c1590519fa47f02db2895146a5f62a4

  3 | 1710ac4199746e9bfa188f1655d1f857

  4 | 6cae64191c2bc309a4884301e77b26ad

  5 | 813987a5c3af2d75bd0de6e288083b10

  6 | c52baa42cda22c89719bfb59dde1f78b

  7 | 491003337ea4e887c5ac24d174c691c6

  8 | 455cdf32b170fcf2b450c0b974fbf310

  9 | 43adb30aeb0a21ab35fdf97064ad1d21

 10 | 97dc1adc5484244a077e87ef36ecfe09

(10 rows)

--Create a simple materialized view

postgres = # 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 | mv_tbl_kenyon | materialized view | postgres | 65 MB |

 public | tbl_kenyon | table | postgres | 65 MB |

(2 rows)

Three, test cases:

--Test without concurrently

postgres = # insert into tbl_kenyon values (1000001, md5 (random () :: text));

INSERT 0 1

postgres = # select max (id) from mv_tbl_kenyon;

   max

---------

 1000000

(1 row)

postgres = # \ timing

Timing is on.

postgres = # refresh materialized view mv_tbl_kenyon;

REFRESH MATERIALIZED VIEW

Time: 2056.460 ms

--The test strip is concurrently, a unique index needs to be built

postgres = # insert into tbl_kenyon values (1000002, md5 (random () :: text));

INSERT 0 1

Time: 9.434 ms

postgres = # refresh materialized view concurrently mv_tbl_kenyon;

ERROR: cannot refresh materialized view "public.mv_tbl_kenyon" concurrently

HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.

Time: 22109.877 ms

postgres = # create unique index idx_ken on mv_tbl_kenyon (id);

CREATE INDEX

Time: 707.721 ms

postgres = # select max (id) from mv_tbl_kenyon;

   max

---------

 1000001

(1 row)

Time: 1.110 ms

postgres = # begin;

BEGIN

postgres = # refresh materialized view concurrently mv_tbl_kenyon;

REFRESH MATERIALIZED VIEW

Time: 24674.739 ms

--If it is refresh, add a begin in front;

-It can also be found that in another session opened, it will not block the query, otherwise it will block without concurrently

postgres = # select * from mv_tbl_kenyon limit 10;

 id | remark

---- + ----------------------------------

  1 | d4fc1c7440a4d1672028586c2bb76514

  2 | 5c1590519fa47f02db2895146a5f62a4

  3 | 1710ac4199746e9bfa188f1655d1f857

  4 | 6cae64191c2bc309a4884301e77b26ad

  5 | 813987a5c3af2d75bd0de6e288083b10

  6 | c52baa42cda22c89719bfb59dde1f78b

  7 | 491003337ea4e887c5ac24d174c691c6

  8 | 455cdf32b170fcf2b450c0b974fbf310

  9 | 43adb30aeb0a21ab35fdf97064ad1d21

 10 | 97dc1adc5484244a077e87ef36ecfe09

(10 rows)

Fourth, the source code

 The source code of the relevant unique index can be viewed in matview.c:

-First initialize the unique index is false

foundUniqueIndex = false;

--If the unique index is found to be set to true

if (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, TYPECACHE_EQ_OPR)-> eq_opr;

     mv_GenerateOper (& querybuf, op);

     appendStringInfo (& querybuf, "mv.% s", colname);

      foundUniqueIndex = true;

--If the unique index cannot be found

if (! 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 new version of the materialized view adds a concurrently parameter, so that the query of the materialized view will not be 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 time to obtain the query lock, the refresh speed will become very slow

3. The parameters for incremental refresh are not available yet, which is regrettable

6. Reference:

1.http: //www.postgresql.org/docs/9.4/static/sql-creatematerializedview.html 2.http: //francs3.blog.163.com/blog/static/405767272014421104127225/

3.src / backend / commands / matview.c

PostgreSQL version 9.4 materialized view update