Re: CREATE OR REPLACE MATERIALIZED VIEW
That is expected because AccessExclusiveLock is acquired on the existing matview. This is also the case for CREATE OR REPLACE VIEW. Right, had this case many times. My initial idea, while writing the patch, was that one could replace the matview without populating it and then run the concurrent refresh, like this: CREATE OR REPLACE MATERIALIZED VIEW foo AS ... WITH NO DATA; REFRESH MATERIALIZED VIEW CONCURRENTLY foo; But that won't work because concurrent refresh requires an already populated matview. Right now the patch either populates the replaced matview or leaves it in an unscannable state. Technically, it's also possible to skip the refresh and leave the old data in place, perhaps by specifying WITH *OLD* DATA. New columns would just be null. Of course you can't tell if you got stale data without knowing how the matview was replaced. Thoughts? I believe the expectation is to get materialized views updated whenever it gets replaced so likely to confuse users ?
Re: CREATE OR REPLACE MATERIALIZED VIEW
Hi, +1 for this feature. Replacing Matviews -- With patch 0001, a matview can be replaced without having to drop it and its dependent objects. In our use case it is no longer necessary to define the actual query in a separate view. Replacing a matview works analogous to CREATE OR REPLACE VIEW: * the new query may change SELECT list expressions of existing columns * new columns can be added to the end of the SELECT list * existing columns cannot be renamed * the data type of existing columns cannot be changed In addition to that, CREATE OR REPLACE MATERIALIZED VIEW also replaces access method, tablespace, and storage parameters if specified. The clause WITH [NO] DATA works as expected: it either populates the matview or leaves it in an unscannable state. It is an error to specify both OR REPLACE and IF NOT EXISTS. I noticed replacing the materialized view is blocking all reads. Is that expected ? Even if there is a unique index ? Best, Sa_ïd_
Update platform notes to build Postgres on macos
Hi, I just built postgresql on macos sonoma (v14) and I had to install the following packages: * * icu - https://ports.macports.org/port/icu/ * * pkg - https://ports.macports.org/port/pkgconfig/ I don't see anything related to this on https://www.postgresql.org/docs/devel/installation-platform-notes.html Did I miss something ? Should we add a note? best, Saïd
Re: Add statistics refresh materialized view
Hi, However, as you said, for most use cases, pg_stat_statements and log_statement may be sufficient. I would like to withdraw this proposal. Well, they either require extensions or parameters to be set properly. One advantage I see to store those kind of information is that it can be queried by application developers (users are reporting old data for example). We currently have to rely on other ways to figure out if materialized views were properly refreshed.
