PolarDB:Real-time materialized views

Real-time materialized view with aggregate functions

1. Install the polar_ivm extension.

   CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

2. Create a base table and insert initial data.

   CREATE TABLE t (a INT, b VARCHAR);
   INSERT INTO t VALUES
     (1, 'a'),
     (2, 'b'),
     (3, 'c'),
     (4, 'd'),
     (5, 'e');

3. Create a real-time materialized view using MAX, MIN, and GROUP BY.

   CREATE MATERIALIZED VIEW mv
   REFRESH FAST
   ON COMMIT
   AS
   SELECT max(a), min(a), b FROM t GROUP BY b;

4. Query the view and verify it reflects the base table.

   SELECT * FROM mv ORDER BY b;

   Result:

    max | min | b
   -----+-----+---
      1 |   1 | a
      2 |   2 | b
      3 |   3 | c
      4 |   4 | d
      5 |   5 | e
   (5 rows)

5. Insert a row and verify the view updates automatically.

   INSERT INTO t VALUES (6, 'f');
   SELECT * FROM mv ORDER BY b;

   Result:

    max | min | b
   -----+-----+---
      1 |   1 | a
      2 |   2 | b
      3 |   3 | c
      4 |   4 | d
      5 |   5 | e
      6 |   6 | f
   (6 rows)

6. Delete a row and verify the view updates automatically.

   DELETE FROM t WHERE a = 2;
   SELECT * FROM mv ORDER BY b;

   Result:

    max | min | b
   -----+-----+---
      1 |   1 | a
      3 |   3 | c
      4 |   4 | d
      5 |   5 | e
      6 |   6 | f
   (5 rows)

7. Update rows and verify the view updates automatically.

   UPDATE t SET a = a + 1;
   SELECT * FROM mv ORDER BY b;

   Result:

    max | min | b
   -----+-----+---
      2 |   2 | a
      4 |   4 | c
      5 |   5 | d
      6 |   6 | e
      7 |   7 | f
   (5 rows)

8. Drop the view when done.

   DROP MATERIALIZED VIEW mv;

Real-time materialized view with an OUTER JOIN

OUTER JOIN support requires minor engine version 2.0.14.18.37.0 or later.

1. Install the polar_ivm extension.

   CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

2. Create two base tables and insert initial data.

   CREATE TABLE t1 (a INT, b VARCHAR);
   INSERT INTO t1 VALUES
     (1, 'a'),
     (2, 'b'),
     (3, 'c'),
     (4, 'd'),
     (5, 'e');
   
   CREATE TABLE t2 (a INT, c VARCHAR);
   INSERT INTO t2 VALUES
     (1, 'e'),
     (2, 'f');

3. Create a real-time materialized view using a LEFT JOIN. All join-condition columns (t1.a, t2.a) are included in the projection, as required.

   CREATE MATERIALIZED VIEW mv
   REFRESH FAST
   ON COMMIT AS
   SELECT t1.a AS t1a, t1.b,
          t2.a AS t2a, t2.c
   FROM t1 LEFT JOIN t2
   ON t1.a = t2.a;

4. Query the view and verify it reflects the base tables.

   SELECT * FROM mv ORDER BY b;

   Result:

    t1a | b | t2a | c
   -----+---+-----+---
      1 | a |   1 | e
      2 | b |   2 | f
      3 | c |     |
      4 | d |     |
      5 | e |     |

5. Insert a row into t2 and verify the view updates automatically.

   INSERT INTO t2 VALUES (3, 'g');
   SELECT * FROM mv ORDER BY b;

   Result:

    t1a | b | t2a | c
   -----+---+-----+---
      1 | a |   1 | e
      2 | b |   2 | f
      3 | c |   3 | g
      4 | d |     |
      5 | e |     |

6. Delete rows from t1 and verify the view updates automatically.

   DELETE FROM t1 WHERE a IN (1, 4);
   SELECT * FROM mv ORDER BY b;

   Result:

    t1a | b | t2a | c
   -----+---+-----+---
      2 | b |   2 | f
      3 | c |   3 | g
      5 | e |     |

7. Update rows in t2 and verify the view updates automatically.

   UPDATE t2 SET a = a + 1;
   SELECT * FROM mv ORDER BY b;

   Result:

    t1a | b | t2a | c
   -----+---+-----+---
      2 | b |   2 | e
      3 | c |   3 | f
      5 | e |     |

8. Drop the view when done.

   DROP MATERIALIZED VIEW mv;