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.
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 |