Would refreshing a materialized view in a transaction allow me to have test-only specific data on it?

Question

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?

Answer 1

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

1. Materialized views work like tables so all the clients will being looking at the same data defined by the Query that created it.
2. 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.