刷新实例化视图不包括添加的列 [英] Refreshing a materialized view does not include added column

问题描述

从手册

CREATE MATERIALIZED VIEW与CREATE TABLE AS类似，不同之处在于它还记住用于初始化视图的查询，以便以后可以按需刷新。

据我了解，刷新实例化视图的效果应与重新创建视图的效果相同。

As I understand refreshing a materialized view should have the same effect as recreate view as. But it is not what happens here.

用单列创建表

drop table if exists t cascade;

create table t (a int);

insert into t (a) values (1);

创建实例化视图

create materialized view mat_view_t as
select * from t ;

select * from mat_view_t;
 a 
---
 1

现在添加一列在源表中

alter table t add column b int;

\d t
       Table "public.t"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 

然后刷新实例化视图

refresh materialized view mat_view_t;

select * from mat_view_t;
 a 
---
 1

\d mat_view_t 
Materialized view "public.mat_view_t"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 

新列在哪里？这是预期的行为吗？如果是，那么我认为该手册具有误导性。

Where is the new column? Is it the expected behaviour? If it is then I think the manual is misleading.

推荐答案

SELECT * 会在执行时进行扩展，就像使用所有类似的操作一样（ 创建视图 ， 创建表AS ）

SELECT * is expanded at execution time, just like with all similar operations (CREATE VIEW, CREATE TABLE AS)

关键字是早期绑定，而不是后期绑定 。 Postgres保存执行 SELECT * 时出现的列列表，以后自动添加不添加的列。 查询字符串本身不会保存，只有内部表示形式之后会扩展 SELECT * 以及其他诸如解析所有标识符的内容。

The key word is "early binding" as opposed to "late binding". Postgres saves the list of columns present at execution time of SELECT *, columns added later are not included automatically. The query string itself is not saved, only the internal representation after expanding SELECT * and other stuff like resolving all identifiers.

刷新材料视图 从不更改数据定义，仅更改数据：

REFRESH MATERIALIZED VIEW never changes the data definition, only data:

刷新材料视图完全替换了物化视图的内容。

REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view.

手册可能更明确，但比较 CREATE TABLE AS 的行为对我来说很清楚：

The manual could be more explicit about it, but the comparison to the behavior of CREATE TABLE AS made it clear for me:

创建材料视图与 CREATE TABLE AS 类似，除了
，它还记得所使用的查询初始化在视图中。

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view.

这篇关于刷新实例化视图不包括添加的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！