CREATE OR REPLACE MATERIALIZED VIEW - Mailing list pgsql-hackers
From | Erik Wienhold |
---|---|
Subject | CREATE OR REPLACE MATERIALIZED VIEW |
Date | |
Msg-id | 3c86a16f-4272-4df3-9959-70a9a7d88a71@ewie.name Whole thread Raw |
Responses |
Re: CREATE OR REPLACE MATERIALIZED VIEW
Re: CREATE OR REPLACE MATERIALIZED VIEW Re: CREATE OR REPLACE MATERIALIZED VIEW |
List | pgsql-hackers |
I like to add CREATE OR REPLACE MATERIALIZED VIEW with the attached patches. Patch 0001 adds CREATE OR REPLACE MATERIALIZED VIEW similar to CREATE OR REPLACE VIEW. It also includes regression tests and changes to docs. Patch 0002 deprecates CREATE MATERIALIZED VIEW IF NOT EXISTS because it no longer seems necessary with patch 0001. Tom Lane commented[1] about the general dislike of IF NOT EXISTS, to which I agree, but maybe this was meant only in response to adding new commands. Anyway, my idea is to deprecate that usage in PG18 and eventually remove it in PG19, if there's consensus for it. We can drop that clause without violating any standard because matviews are a Postgres extension. I'm not married to the idea, just want to put it on the table for discussion. Motivation ---------- At $JOB we use materialized views for caching a couple of expensive views. But every now and then those views have to be changed, e.g., new logic, new columns, etc. The matviews have to be dropped and re-created to include new columns. (Just changing the underlying view logic without adding new columns is trivial because the matviews are just thin wrappers that just have to be refreshed.) We also have several views that depend on those matviews. The views must also be dropped in order to re-create the matviews. We've already automated this with two procedures that stash and re-create dependent view definitions. Native support for replacing matviews would simplify our setup and it would make CREATE MATERIALIZED VIEW more complete when compared to CREATE VIEW. I searched the lists for previous discussions on this topic but couldn't find any. So, I don't know if this was ever tried, but rejected for some reason. I've found slides[2] from 2013 (when matviews landed in 9.3) which have OR REPLACE on the roadmap: > Materialised Views roadmap > > * CREATE **OR REPLACE** MATERIALIZED VIEW > * Just an oversight that it wasn't added > [...] 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. Example ------- postgres=# CREATE MATERIALIZED VIEW test AS SELECT 1 AS a; SELECT 1 postgres=# SELECT * FROM test; a --- 1 (1 row) postgres=# CREATE OR REPLACE MATERIALIZED VIEW test AS SELECT 2 AS a, 3 AS b; CREATE MATERIALIZED VIEW postgres=# SELECT * FROM test; a | b ---+--- 2 | 3 (1 row) Implementation Details ---------------------- Patch 0001 extends create_ctas_internal in order to adapt an existing matview to the new tuple descriptor, access method, tablespace, and storage parameters. This logic is mostly based on DefineViewRelation. This also reuses checkViewColumns, but adds argument is_matview in order to tell if we want error messages for a matview (true) or view (false). I'm not sure if that flag is the correct way to do that, or if I should just create a separate function just for matviews with the same logic. Do we even need to distinguish between view and matview in those error messages? The patch also adds tab completion in psql for CREATE OR REPLACE MATERIALIZED VIEW. [1] https://www.postgresql.org/message-id/226806.1693430777%40sss.pgh.pa.us [2] https://wiki.postgresql.org/images/a/ad/Materialised_views_now_and_the_future-pgconfeu_2013.pdf#page=23 -- Erik
Attachment
pgsql-hackers by date: