Agfdhyk

I have a materialised view that basically keeps track of all vouchers used at checkout so I can keep track of how many have been used, how many are left etc. I have only just noticed that the materialized view wont update, It currently shows the following:

Where left obviously decreases as vouchers are used (voucher type and vouchers used along with the user id of who used it are in different tables). It has however detected that one voucher has been used but as for the other 2 I used on testing it won't pick them up from the table where used vouchers are stored and update the MV.

Here is part of the migration I currently have for making the MV:

exports.up = function(knex, Promise) {

  return knex.schema

  .raw(`

  CREATE MATERIALIZED VIEW mv_vouchers as

  SELECT v.voucher_id, v.quantity, COUNT(ov.voucher_id) AS "used", v.quantity - COUNT(ov.voucher_id) AS "left"

  FROM public.vouchers v LEFT OUTER JOIN

       public.order_vouchers ov

       ON v.voucher_id = ov.voucher_id

  GROUP BY v.voucher_id, v.quantity;

  `);

};

I have the feeling that I have used the materialized view incorrectly and that it only ran the query in the migration once (when I initially implemented the mv).

EDIT

For extra information here are the other 2 tables I have:

vouchers:

order_vouchers:

The MV is basically just a table, except it also remembers which query it should execute to update (actually replace) its contents.

I think Postgres doesn't have any parameter for automatically refreshing the MV, either live or periodically.

The data is queried and stored when you create the view, except when you specify WITH NO DATA, in which case it's create empty.

You can then (re)populate the view on demand by calling REFRESH MATERIALIZED VIEW.

Just thought I would add a quick solution to this:

exports.up = function(knex, Promise) {

  return knex.schema

  .raw(`

  CREATE MATERIALIZED VIEW mv_vouchers as

  SELECT v.voucher_id, v.quantity, COUNT(ov.voucher_id) AS "used", v.quantity - COUNT(ov.voucher_id) AS "left"

  FROM public.vouchers v LEFT OUTER JOIN

       public.order_vouchers ov

       ON v.voucher_id = ov.voucher_id

  GROUP BY v.voucher_id, v.quantity;

  `)

  .raw(`

      CREATE FUNCTION refresh_mv_vouchers() RETURNS trigger LANGUAGE plpgsql AS $$

        BEGIN

          REFRESH MATERIALIZED VIEW CONCURRENTLY mv_vouchers;

          RETURN null;

        END $$;

    `)

    .raw(`

      CREATE TRIGGER refresh_mv_vouchers

        AFTER insert OR update OR delete OR truncate

        ON order_vouchers

        EXECUTE PROCEDURE refresh_mv_vouchers();

    `);

};



exports.down = function(knex, Promise) {

  return knex.schema

    .raw('DROP MATERIALIZED VIEW mv_vouchers')

    .raw('DROP TRIGGER refresh_mv_vouchers ON order_vouchers')

    .raw('DROP FUNCTION refresh_mv_vouchers()');

};

Was a lot more straight forward than I initially thought.