检查实例化视图的上次刷新时间

Question

我有一个名为price_changes的物化视图，用于某些报告。我还有一个 cron 工作，用 refresh materialized view price_changes 刷新物化视图。一切都很好。

我想给查看报告的用户一条消息"数据从 X 开始是最新的"。当 cron 运行时我可以将其存储在某个地方，但是 postgres 是否已经将此元数据存储在某个地方？

Answer 1

我认为从 9.3.4 开始，系统中没有内置任何内容可以提供此功能。当我需要提供上次刷新的日期时，我会在具体化视图中的选择查询中添加一个名为"last_refresh"的列，因为具体化视图中的数据在刷新之前不会更改。

出于安全原因，我也更喜欢这样做，因为如果信息存储在那里，您可能不希望授予 sql 用户对系统表的访问权限。

根据您是否需要时间，您可以使用：

1. CURRENT_DATE
2. now()

只是日期：

CREATE MATERIALIZED VIEW mv_address AS 
SELECT *, CURRENT_DATE AS last_refresh FROM address;

日期和时间：

CREATE MATERIALIZED VIEW mv_address AS 
SELECT *, now() AS last_refresh FROM address;

2017-02-17更新：

PostgreSQL 版本 9.4+ 现在包含CONCURRENTLY选项。如果您使用REFRESH MATERIALIZED VIEW CONCURRENTLY选项，请注意注释中@Smudge指示的内容。这实际上只是大型且经常更新的数据集的问题。如果您的数据集很小或不经常更新，那么您应该没问题。

Answer 2

WITH
        pgdata AS (
                SELECT
                        setting AS path
                FROM
                        pg_settings
                WHERE
                        name = 'data_directory'
        ),
        path AS (
                SELECT
                        CASE
                                WHEN pgdata.separator = '/' THEN '/'    -- UNIX
                                ELSE ''                                -- WINDOWS
                        END AS separator
                FROM 
                        (SELECT SUBSTR(path, 1, 1) AS separator FROM pgdata) AS pgdata
        )
SELECT
        ns.nspname||'.'||c.relname AS mview,
        (pg_stat_file(pgdata.path||path.separator||pg_relation_filepath(ns.nspname||'.'||c.relname))).modification AS refresh
FROM
        pgdata,
        path,
        pg_class c
JOIN
        pg_namespace ns ON c.relnamespace=ns.oid
WHERE
        c.relkind='m'
;

Answer 3

由于物化视图是存储在磁盘上的数据段，因此它们在文件系统中将具有相应的文件。 当您调用REFRESH MATERIALIZED VIEW 时，磁盘上的数据将以新的文件名重新创建。 因此，您可以通过与pg_class中的relfilenode交叉引用来查找视图的修改/创建时间戳：

[user@server /]# psql -c "create materialized view myview as select aid from pgbench_accounts where aid < 100"
SELECT 99
[user@server /]# psql -c "select relfilenode from pg_class where relname = 'myview'"
 relfilenode 
-------------
       16445
(1 row)
[user@server /]# ls -l /var/lib/edb/as12/data/base/15369/16445
-rw------- 1 enterprisedb enterprisedb 8192 Jun 14 23:28 /var/lib/edb/as12/data/base/15369/16445
[user@server /]# date
Mon Jun 14 23:29:16 UTC 2021
[user@server /]# psql -c "refresh materialized view myview"
REFRESH MATERIALIZED VIEW
[user@server /]# psql -c "select relfilenode from pg_class where relname = 'myview'"
 relfilenode 
-------------
       16449
(1 row)
[user@server /]# ls -l /var/lib/edb/as12/data/base/15369/16449
-rw------- 1 enterprisedb enterprisedb 8192 Jun 14 23:29 /var/lib/edb/as12/data/base/15369/16449
[user@server /]# 

Answer 4

谢谢@ajaest的帮助。我们有包含许多实例的生产环境。我们必须防止物化竞争，而不是刷新速度超过50秒。这是我们的解决方案。我们使用了 在物化视图上注释

-- dodanie funkcji do odświeżania widoku
CREATE OR REPLACE FUNCTION public.refresh_mv_users()
    RETURNS timestamp AS $$
DECLARE
    last_refreshed  TIMESTAMP;
    next_refresh    TIMESTAMP;
    comment_sql     text;
BEGIN
    SELECT ((pg_catalog.obj_description('public.mv_users'::regclass, 'pg_class')::json->>'last_refreshed')::timestamp) into last_refreshed;
    SELECT (last_refreshed + (50 * interval '1 seconds')) into next_refresh;
    IF next_refresh < now() THEN
        REFRESH MATERIALIZED VIEW CONCURRENTLY public.mv_users;
        comment_sql := 'COMMENT ON MATERIALIZED VIEW public.mv_users is '|| quote_literal('{"last_refreshed": "' || now() || '"}');
        execute comment_sql;
        SELECT now() into last_refreshed;
    END IF;
    RETURN last_refreshed;
END;
$$  LANGUAGE plpgsql;

Answer 5

我认为@richyen如果您可以访问服务器，解决方案是最好的。

如果不这样做，则必须以某种方式手动将上次刷新日期存储为元数据。一些选项是：

• 与@thames的响应中的列一样，主要缺点是列的额外使用存储空间（重复次数与行具有MV一样多）以及@smudge所述的大表中的刷新问题。

• 在自定义表中。主要缺点是您必须确保所有刷新的内容都包含表更新。

-- Create table
CREATE TABLE pg_matviews_last_refreshed (
  matviewowner NAME NOT NULL,
  matviewname NAME NOT NULL,
  schemaname NAME NOT NULL,
  last_refreshed TIMESTAMP NOT NULL,
  CONSTRAINT pk UNIQUE (matviewowner, matviewname, schemaname)
)
CREATE VIEW pg_matviews_extra AS
SELECT * 
FROM pg_matviews
NATURAL JOIN pg_matviews_last_refreshed
-- Set initial values
INSERT INTO pg_matviews_last_refreshed (
  matviewowner,
  matviewname,
  schemaname,
  last_refreshed
)
SELECT 
  matviewowner,
  matviewname,
  schemaname,
  CURRENT_TIMESTAMP
FROM pg_matviews;
-- Consult dates
SELECT * FROM pg_matviews_extra
-- Refresh 
BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY my_materialized_view;
-- Insert update date into last_refreshed table. Of course, if 
-- more complex permissions are into place, the query becomes more
-- complex to discover owner, schema, etc.
INSERT INTO pg_matviews_last_refreshed (matviewname, matviewowner, schemaname, last_refreshed)
SELECT matviewname, matviewowner, schemaname, CURRENT_TIMESTAMP
FROM pg_matviews
WHERE matviewname='my_materialized_view'
ON CONFLICT (matviewname, matviewowner, schemaname)
DO UPDATE SET last_refreshed=EXCLUDED.last_refreshed;
COMMIT;

• 与使用专用表来存储上次刷新日期类似，您可以将该信息隐式存储在 MV 注释中：

-- Refresh operation
BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY my_materialized_view;
-- This statement can be wrapped into a function to allow using CURRENT_TIMESTAMP
COMMENT ON MATERIALIZED VIEW my_materialized_view IS '{"last_refreshed": "2021-07-07T09:30:59Z"}'
COMMIT;

-- Retrieve refresh date
SELECT (pg_catalog.obj_description('my_materialized_view'::regclass, 'pg_class')::json->>'last_refreshed')::timestamp;

Answer 6

来自 CLI

让我们首先为我们要检查的物化视图及其所属的数据库声明一些变量。

materialized_view_name='my_materialized_view'
database_name='my_database'

接下来，我们需要找到物化视图的 id。我们可以通过查询pg_class表来做到这一点。

在下面的 w 查询中，将 <materialized_view_name> 替换为实例化视图的名称

id=$(psql -d $database_name -c "SELECT relfilenode FROM pg_class WHERE relname = '<materialized_view_name>';" -tA)

现在，我们可以找到物化视图的文件路径，因此可以找到上次更新的时间。

file_path=$(find / -name "${id}" 2>/dev/null)
ls -l $file_path

奖励：有时服务器可能与本地计算机具有不同的时区。可以通过运行以下命令来获取对服务器时区的引用。

date