BUG #16853: Materialized view not behaving in fully MVCC-compliant way

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: postgresql(at)mscott(dot)org
Subject: BUG #16853: Materialized view not behaving in fully MVCC-compliant way
Date: 2021-02-04 00:58:07
Message-ID: 16853-edcd2caf18096366@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16853
Logged by: Scott Marcy
Email address: postgresql(at)mscott(dot)org
PostgreSQL version: 12.4
Operating system: macOS
Description:

I have a materialized view that is very expensive to generate but also
infrequently used, so whenever something happens that invalidates the data
in the MV I use `REFRESH MATERIALIZED VIEW WITH NO DATA;` to de-populate it.
In the places where the MV will be used, I first check
pg_class.relispopulated to see if the MV has data or not, and if not, I
refresh the MV and then proceed.

However, if one connection removes the data from the MV while another
connection is already in a transaction that will access the MV's data, the
attempt to access the data in the MV should cause a transaction rollback
error, not a "materialized view has not been populated" error.

Here is a simple set of psql commands (run in two different psql
sessions--the indented lines are for the 2nd session) that shows the
problem.

create materialized view matview as select * from pg_catalog.pg_class;

begin isolation level serializable;
select relispopulated from pg_catalog.pg_class where relname = 'matview';
-- t

begin isolation level serializable;

select relispopulated from pg_catalog.pg_class where relname =
'matview';

-- t
refresh materialized view matview with no data;
select relispopulated from pg_catalog.pg_class where relname = 'matview';
-- f
commit;

select relispopulated from pg_catalog.pg_class where relname =
'matview';

-- t

-- Arguably correct under MVCC, although maybe a transaction rollback
error should happen here


select count(*) from matview;

ERROR: materialized view "matview" has not been populated

-- This should be a transaction rollback error of some sort.

While I can special-case this error to be handled as a transaction rollback
error, that really isn't correct here, and making such a change in my
application's logic runs the risk of real bugs where the MV hasn't been
populated are repeated numerous times to no good purpose.

I have verified this behavior in Postgres 9.4 and 12.4.

Browse pgsql-bugs by date

  From Date Subject
Next Message Neil Chen 2021-02-04 03:42:06 Re: BUG #16846: "retrieved too many tuples in a bounded sort"
Previous Message Tom Lane 2021-02-03 20:07:20 Re: BUG #16811: Severe reproducible server backend crash