From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: postgresql@mscott.org
Subject: BUG #16853: Materialized view not behaving in fully MVCC-compliant way
Date: Thu, 04 Feb 2021 00:58:07 +0000
Message-ID: <16853-edcd2caf18096366@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 16853
Logged by: Scott Marcy
Email address: postgresql@mscott.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, postgresql@mscott.org
Subject: Re: BUG #16853: Materialized view not behaving in fully MVCC-compliant way
In-Reply-To: <16853-edcd2caf18096366@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox