Обсуждение: BUG #16853: Materialized view not behaving in fully MVCC-compliant way
BUG #16853: Materialized view not behaving in fully MVCC-compliant way
От
PG Bug reporting form
Дата:
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.