pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



Message-ID: <16853-edcd2caf18096366@postgresql.org>
Permalink:  ../16853-edcd2caf18096366@postgresql.org/
Also on:    postgresql.org/message-id/16853-edcd2caf18096366@postgresql.org

 · 

reply

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