---
# System prepended metadata

title: Untitled

---

Materialized View - Incremental refresh methods

1) With Triggers
2) Using pg_ivm
3) WAL decoding method

1) With Triggers 
    `/* Create a new table, populated some data into it */`
    CREATE TABLE test_t(id INT, name TEXT);
    INSERT INTO test_t SELECT s AS id,  s||'_'||substr(md5(random()::TEXT), 1, 6) AS name FROM generate_series(1,100) s;
    
    `/* Created one more table populate some data into it */`
    CREATE TABLE test_t_age(id INT, t_age INT);
    INSERT INTO test_t_age SELECT s AS id,  (random() * 100)::INT AS t_age FROM generate_series(1,100) s;
    
    `/* Crete MATERIALIZED VIEW by joining two tables using id */`
    CREATE MATERIALIZED VIEW mv_t_age AS SELECT * FROM test_t_age JOIN test_t USING(id);
    
    `/* Create a function for refreshing MATERIALIZED VIEW */` 
    CREATE OR REPLACE FUNCTION refresh_t_age()
    RETURNS TRIGGER AS
    $fun$
    BEGIN
      REFRESH MATERIALIZED VIEW CONCURRENTLY mv_t_age;
      ```
          /*
           * Here, I am going with CONCURRENTLY option, means we can refresh the
           * materialized view data withoud locking out concurent selects on
           * materialized view.
           */
      ```
      RETURN NULL;
    END;
    $fun$ LANGUAGE PLPGSQL;

    `/* We should have UNIQUE index on materialized view for using CONCURRENTLY refresh */`
    haritest=# CREATE UNIQUE INDEX idx_unique_id ON public.mv_t_age(id);
    
    `Trigger ON test_t`
    CREATE OR REPLACE TRIGGER refresh_t_age
    AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_t
    FOR EACH STATEMENT
    EXECUTE PROCEDURE  refresh_t_age();

    `Trigger ON test_t_age`
    CREATE OR REPLACE TRIGGER refresh_t_age
    AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE ON test_t_age
    FOR EACH STATEMENT
    EXECUTE PROCEDURE  refresh_t_age();
    
    haritest=# SELECT COUNT(1) FROM test_t;
     count
    ───────
       100
    (1 row)

    haritest=# SELECT COUNT(1) FROM test_t_age;
     count
    ───────
       100
    (1 row)

    haritest=# SELECT COUNT(1) FROM mv_t_age;
     count
    ───────
       100
    (1 row)

    i) Verify AFTER DELETE event 
        `Here, We are going to delete 90 records from test_t_age Table.`
        haritest=# delete from test_t_age where id > 10;
        DELETE 90
        haritest=# SELECT COUNT(1) FROM mv_t_age;
        count
        ───────
            10
        `Without manual refreshing, we can see the updated data.`
        
    ii) Verify AFTER UPDATE event
        ```
            I am going to Update name for one person, it should reflect withoud doing manual refresh
                    Before Updating just verify the data in the materialized view.
        ```
        haritest=# SELECT * FROM mv_t_age;
         id │ t_age │   name
        ────┼───────┼───────────
          1 │    68 │ 1_64000e
          2 │    39 │ 2_9a8557
          3 │    76 │ 3_80c0ad
          4 │    26 │ 4_1c32a5
          5 │    72 │ 5_250fd5
          6 │    89 │ 6_d80b8f
          7 │    18 │ 7_3cdbf8
          8 │    11 │ 8_e5ad04
          9 │    53 │ 9_c277c4
         10 │     1 │ 10_11da9c
        (10 rows) 
        haritest=# SELECT * FROM test_t where id = 1;
         id │   name
        ────┼──────────
          1 │ 1_64000e
        haritest=# UPDATE test_t SET name = 'John' WHERE id = 1;
        UPDATE 1
        haritest=# SELECT * FROM mv_t_age;
         id │ t_age │   name
        ────┼───────┼───────────
          2 │    39 │ 2_9a8557
          3 │    76 │ 3_80c0ad
          4 │    26 │ 4_1c32a5
          5 │    72 │ 5_250fd5
          6 │    89 │ 6_d80b8f
          7 │    18 │ 7_3cdbf8
          8 │    11 │ 8_e5ad04
          9 │    53 │ 9_c277c4
         10 │     1 │ 10_11da9c
          1 │    68 │ John `/* We can see the updated data here without manual refresh */`
    
    iii) Verify AFTER INSERT event
        `I am going to INSERT new record into test_t_age, it should reflect withoud doing manual refresh`
        haritest=# SELECT * FROM test_t_age;
         id │ t_age
        ────┼───────
          1 │    68
          2 │    39
          3 │    76
          4 │    26
          5 │    72
          6 │    89
          7 │    18
          8 │    11
          9 │    53
         10 │     1
        haritest=# INSERT INTO test_t_age VALUES (11, 25);
        INSERT 0 1
        haritest=# SELECT * FROM test_t_age;
         id │ t_age
        ────┼───────
          1 │    68
          2 │    39
          3 │    76
          4 │    26
          5 │    72
          6 │    89
          7 │    18
          8 │    11
          9 │    53
         10 │     1
         11 │    25
        (11 rows)
        haritest=# SELECT * FROM mv_t_age;
         id │ t_age │   name
        ────┼───────┼───────────
          2 │    39 │ 2_9a8557
          3 │    76 │ 3_80c0ad
          4 │    26 │ 4_1c32a5
          5 │    72 │ 5_250fd5
          6 │    89 │ 6_d80b8f
          7 │    18 │ 7_3cdbf8
          8 │    11 │ 8_e5ad04
          9 │    53 │ 9_c277c4
         10 │     1 │ 10_11da9c
          1 │    68 │ John
         11 │    25 │ 11_489b07 `/* We can see the new record populating here without doing manual refresh */`
        (11 rows)
    iv) Verify AFTER TRUNCATE event
        ```
            Check the materialized view data after Truncating any one of the Table.
            I am going to TRUNCATE test_t table data.
        ```
        haritest=# TRUNCATE test_t;
        TRUNCATE TABLE
        haritest=# SELECT COUNT(*) FROM test_t;
         count
        ───────
             0
        (1 row)
        haritest=# SELECT COUNT(*) FROM mv_t_age;
         count
        ───────
             0
        (1 row)
        `Here, we can see the refreshed data without doing manual refresh.`

References : 
    https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
    https://dba.stackexchange.com/questions/190552/trigger-to-update-a-materialized-view-once-after-relevant-transaction
    https://www.netguru.com/blog/materialized-view-refresh-problem-and-how-to-solve-using-database-triggers-in-ror-based-application

2) Using pg_ivm:
    pre requisites : Postgres version 14 or above, pg_ivm extension
    `/* Create a new table, populated some data into it */`
    CREATE TABLE test_t(id INT, name TEXT);
    INSERT INTO test_t SELECT s AS id,  s||'_'||substr(md5(random()::TEXT), 1, 6) AS name FROM generate_series(1,11) s;
    
    `/* Created one more table populate some data into it */`
    CREATE TABLE test_t_age(id INT, t_age INT);
    INSERT INTO test_t_age SELECT s AS id,  (random() * 100)::INT AS t_age FROM generate_series(1,11) s;
    
     haritest=# select * from test_t;
     id │   name
    ────┼───────────
      1 │ 1_ef768e
      2 │ 2_d4efd2
      3 │ 3_cf7de2
      4 │ 4_e6d89e
      5 │ 5_1096af
      6 │ 6_f99d14
      7 │ 7_8c640d
      8 │ 8_77f9da
      9 │ 9_05f802
     10 │ 10_e0f72c
     11 │ 11_1a85a2
    (11 rows)

    haritest=# select * from test_t_age;
     id │ t_age
    ────┼───────
      1 │    68
      2 │    39
      3 │    76
      4 │    26
      5 │    72
      6 │    89
      7 │    18
      8 │    11
      9 │    53
     10 │     1
     11 │    25
    (11 rows)
    
    haritest=# SELECT create_immv('mv_ivm', 'SELECT * FROM test_t_age JOIN test_t USING(id)');
    Note : Returns Interger value that is no of rows returned by the query. 
        In our case return value is 11 .
    
    i) Verify DELETE scenario: 
        haritest=# delete from test_t where id=11;
        DELETE 1 
        haritest=# select * from mv_ivm;
         id │ t_age │   name
        ────┼───────┼───────────
          1 │    68 │ 1_ef768e
          2 │    39 │ 2_d4efd2
          3 │    76 │ 3_cf7de2
          4 │    26 │ 4_e6d89e
          5 │    72 │ 5_1096af
          6 │    89 │ 6_f99d14
          7 │    18 │ 7_8c640d
          8 │    11 │ 8_77f9da
          9 │    53 │ 9_05f802
         10 │     1 │ 10_e0f72c
        (10 rows)
        Note : mv_ivm automaticly refreshed once data deleted from test_t. 
        
    ii) Verify UPDATE scenario : 
        haritest=# update test_t set name='test_name' where id = 10;
        UPDATE 1
        haritest=# select * from mv_ivm ;
         id │ t_age │   name
        ────┼───────┼───────────
          1 │    68 │ 1_ef768e
          2 │    39 │ 2_d4efd2
          3 │    76 │ 3_cf7de2
          4 │    26 │ 4_e6d89e
          5 │    72 │ 5_1096af
          6 │    89 │ 6_f99d14
          7 │    18 │ 7_8c640d
          8 │    11 │ 8_77f9da
          9 │    53 │ 9_05f802
         10 │    25 │ test_name `/* Uodated data reflected here */ `
        (10 rows)
        
    iii) Verify INSERT scenario : 
        haritest=# insert into test_t values (11, 'XYZ');
        INSERT 0 1
        haritest=# select * from mv_ivm ;
         id │ t_age │   name
        ────┼───────┼───────────
          1 │    68 │ 1_ef768e
          2 │    39 │ 2_d4efd2
          3 │    76 │ 3_cf7de2
          4 │    26 │ 4_e6d89e
          5 │    72 │ 5_1096af
          6 │    89 │ 6_f99d14
          7 │    18 │ 7_8c640d
          8 │    11 │ 8_77f9da
          9 │    53 │ 9_05f802
         10 │    25 │ test_name
         11 │    25 │ XYZ  `/* New record reflected without refreshing the data */`
        (11 rows)
        
    iv) Verify TRUNCATE scenario : 
        haritest=# truncate test_t_age;
        TRUNCATE TABLE
        Time: 238.093 ms
        haritest=# select * from mv_ivm ;
         id │ t_age │ name
        ────┼───────┼──────
        (0 rows)
        `/* mv_ivm data got truncated without refreshing */`
    
    `We can  see the created view definition with get_immv_def `
    haritest=# select get_immv_def('mv_ivm');
         get_immv_def
    ──────────────────────────────
      SELECT test_t_age.id,      ↵
         test_t_age.t_age,       ↵
         test_t.name             ↵
        FROM (test_t_age         ↵
          JOIN test_t USING (id))
    
    haritest=# DROP TABLE mv_ivm;
    `pg_ivm supports only inner join, won't support outer joins`
    haritest=# SELECT create_immv('mv_ivm', 'SELECT * FROM test_t_age LEFT JOIN test_t USING(id)');
    ERROR:  OUTER JOIN is not supported on incrementally maintainable materialized view
    
    `We can disable the incremental view maintainance by using refresh_immv command.`
    haritest=# select refresh_immv('mv_ivm', false);
    `mv_ivm disabled `
    
    `Lets populate some data into test_t_age`
    haritest=# INSERT INTO test_t_age SELECT s AS id,  (random() * 100)::INT AS t_age FROM generate_series(1,11) s;
    INSERT 0 11
    
    `Here we are not seeing updated data, because mv_ivm got dissabled`
    haritest=# select * from mv_ivm;
     id │ t_age │ name 
    ────┼───────┼──────
    (0 rows)
    
    `Let us enable the mv_ivm by with following command`
    haritest=# select refresh_immv('mv_ivm', true);
     refresh_immv 
    ──────────────
               11
    (1 row)
    
    `Data got reflected in mv_ivm`
    haritest=# select * from mv_ivm;
     id │ t_age │   name
    ────┼───────┼───────────
      1 │    34 │ 1_ef768e
      2 │    72 │ 2_d4efd2
      3 │     2 │ 3_cf7de2
      4 │    17 │ 4_e6d89e
      5 │    31 │ 5_1096af
      6 │    59 │ 6_f99d14
      7 │    29 │ 7_8c640d
      8 │     1 │ 8_77f9da
      9 │    60 │ 9_05f802
     10 │    34 │ test_name
     11 │    26 │ XYZ
    (11 rows)
    
    References :
      https://github.com/sraoss/pg_ivm 
      
      
MATERIALIZED VIEWS -> ORACLE VS POSTGRESQL : 
Materialized View :
  Materialized view  is a database object, it contains the Query result. 


Comparison :

  ORACLE                                              POSTGRESQL
BUILD IMMEDIATE                                       WITH DATA
BUILD DEFERRED                                        WITH NO DATA

REFRESH COMPLETE                                      REFRESH
/* Discard existing data and rewrites */
REFRESH FAST                                          Incremental Refresh (pg_ivm)
/* Oracle REFRESH FAST working only when Materilized  /*We can achive with the pg_ivm, but pg_ivm has some limitations*/
/* view logs on enabled on base tables */

Refresh Strategy :
ON COMMIT                                              ON COMMIT -- We can achive this by creating triggers on underlaying tables in the MV
ON DEMAND                                              ON DEMAND -- Supports only manual refresh

We can perform DML on Materialized Views               We cannot perform DML on Materialized Views

EX1 :                                                    CREATE MATERIALIZED VIEW mv_emp AS SELECT * FROM hr.employees;
  CREATE MATERIALIZED VIEW mv_emp AS
  SELECT * FROM hr.employees;

  DBMS_MVIEW.REFRESH('mv_emp', 'cf');                    REFRESH MATERIALIZED VIEW mv_emp;
  /* cf -> Refresh configuration method */
  /* c -> complete, f-> fast */

EX2:
  CREATE MATERIALIZED VIEW                              /* Create a trigger that will initiate a refresh after */
  mv_emp REFRESH FAST ON COMMIT                         /* every DML command on the underlying tables */
  AS SELECT * FROM employees;                           CREATE OR REPLACE FUNCTION refresh_mv_emp()
                                                        RETURNS TRIGGER LANGUAGE PLPGSQL AS
                                                        $$ BEGIN
                                                        REFRESH MATERIALIZED VIEW mv_emp;
                                                        RETURN NULL;
                                                        END $$;

                                                        CREATE TRIGGER refresh_mv_emp AFTER INSERT OR UPDATE
                                                        OR DELETE OR TRUNCATE ON employees FOR EACH STATEMENT
                                                        EXECUTE PROCEDURE refresh_mv_emp();


EX3:
  CREATE MATERIALIZED VIEW LOG                          Not Supported
  ON employees…
  INCLUDING NEW VALUES;

  CREATE MATERIALIZED VIEW
  mv_emp REFRESH FAST AS
  SELECT  * FROM employees;
  
 
References :
  https://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/chap-oracle-aurora-pg.special.matviews.html