5

Is it possible to refresh a materialized view in a database transaction?

I'm writing test cases for complex queries in laravel which uses transactions to roll back after the test has been run.

When i add data and refresh the view- no records appear when i do a select statement

Timur Shtatland
  • 12,024
  • 2
  • 30
  • 47
Chris Mccabe
  • 1,902
  • 6
  • 30
  • 61

1 Answers1

7

If you refresh a materialized view in a transaction and rollback the transaction, the view remains in the state before the transaction (i.e. not refreshed). You have to commit the transaction to get all its commands to be completed.

Example setup:

create table my_table(id int);
create materialized view mat_view as 
select * from my_table;

Transaction: 

begin;
insert into my_table
select i from generate_series(1, 3) i;
refresh materialized view mat_view;
select * from mat_view;

-- inside the transaction the view is refreshed
 id 
----
  1
  2
  3
(3 rows)

rollback;

Outside the transaction: 

select * from mat_view;

-- but after rollback the view is empty again
 id 
----
(0 rows)
klin
  • 112,967
  • 15
  • 204
  • 232