PostgreSQL – Refreshing Materialized View in Transaction for Test Data

postgresql

I have created a postgresql database used in integration tests for my code.
Its schema is consisted with some materialized views and the code I want to test uses them, therefore I need once I place the test data to refresh them.

But because refreshing them makes the tests slow I need to run the tests in parallel with the materialized view to contain data required only for the test.

Hence, I thought the following approach:

START TRANSACTION

INSERT INTO students (name,class) values ('MIZUKI MINOHARA','A1'),('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

REFRESH MATERIALIZED VIEW sempai_list;
-- RUN MY TESTS

ROLLBACK

But would this approach make my tests look on a separate "version" of the materialized views. What I want to achieve is to have the tests look on different test-only data from the materialized view sempai_list.

Do I achieve that by using and rollback transactions?

Best Answer

Not sure what you are after here the questions has some contradictions in it

Materialized views work like tables so all the clients will being looking at the same data defined by the Query that created it.
You want the clients to look at different data sets for the test.

The problem is wanting the clients to look at different data sets when there looking at same data as Materialized views are not private to the connection

Rollback is not going to help as it going to cause locking issues and conflicts from multiple clients trying to create the same Materialized View.

If the desire is to have the clients not share the data for the test, use temporary tables instead...

Select * into temporary table students from students

this creates a identical temporary table of the students table in the private schema for this connection and will populate data into table based on the where clause

also can use

Create temporary table students like students.

Nice thing about private_name/schema_space is this is search first prior to searching public or any other schemas defined in search path.

So all the commands sent by the client will use this table.

INSERT INTO students (name,class, test5) values ('MIZUKI MINOHARA','A1'),
 ('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

-- RUN TESTS

There is a gotcha here and that is if queries specifies the schema this will not work

select * from public.student

will always match to public.student and not match the temporary table that was created.

Another big benefit using temporary tables is postgresql cleans up and deletes all the temp tables once the connection is closed or transaction ends depends on the command that created the temp table.

Related Solutions

PostgreSQL – Refresh Materialized View Incrementally

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3.

You can create a plain VIEW:

CREATE VIEW graph_avg_view AS 
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

And materialize the result once or whenever you need to start over:

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

(Or use the SELECT statement directly, without creating a VIEW.)
Then, depending on undisclosed details of your use case, you can DELETE / UPDATE / INSERT changes manually.

A basic DML statement with data-modifying CTEs for your table as is:

Assuming nobody else tries to write to graph_avg concurrently (reading is no problem):

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

Basic recipe

Add a timestamp column with default now() to your base table. Let's call it ts.
- If you have updates, add a trigger to set the current timestamp with every update that changes either xaxis or value.

Create a tiny table to remember the timestamp of your latest snapshot. Let's call it mv:

CREATE TABLE mv (
   tbl text PRIMARY KEY
 , ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details

Create this partial, multicolumn index:

CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE  ts >= '-infinity';

Use the timestamp of the last snapshot as predicate in your queries to refresh the snapshot with perfect index usage.
At the end of the transaction, drop the index and recreate it with the transaction timestamp replacing the timestamp in the index predicate (initially '-infinity'), which you also save to your table. Everything in one transaction.
Note that the partial index is great to cover INSERT and UPDATE operations, but not DELETE. To cover that, you need to consider the entire table. It all depends on exact requirements.

Postgresql – Best practice for working with materialized views in Postgres

While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns).

One such idea is to put them all in the same schema, for instance foo_matviews or foo_cache, and then to create a script that drops the schema entirely (with CASCADE), and then reinitializes the schema and all the internal mat-views back.

To be honest, it's always worked so well I've been kind of confused at why this is an issue for anyone. I always have a few SQL scripts in my code repo that do something similar to this. I've never even needed anything more complex like schema migration tools like flyway.

That all said, after you have them all in your schema you can create an extension if you wish.

Best Answer

Related Solutions

PostgreSQL – Refresh Materialized View Incrementally

Basic recipe

Postgresql – Best practice for working with materialized views in Postgres

Related Question