Как я могу гарантировать, что материализованное представление всегда актуально?

Ответ 1

Мне нужно будет вызывать REFRESH MATERIALIZED VIEW при каждом изменении соответствующих таблиц, правильно?

Да, PostgreSQL сам по себе никогда не вызовет его автоматически, вам нужно сделать это каким-то образом.

Как мне это сделать?

Много способов добиться этого. Прежде чем приводить некоторые примеры, имейте в виду, что REFRESH MATERIALIZED VIEW команда блокирует просмотр в режиме AccessExclusive, поэтому пока он работает, вы не можете даже сделайте SELECT в таблице.

Хотя, если вы в версии 9.4 или новее, вы можете указать ему опцию CONCURRENTLY:

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

Это получит ExclusiveLock и не будет блокировать запросы SELECT, но может иметь большие накладные расходы (в зависимости от количества измененных данных, если несколько строк изменились, то это может быть быстрее). Хотя вы по-прежнему не можете одновременно запускать две команды REFRESH.

Обновить вручную

Это вариант рассмотрения. Специально в случаях загрузки данных или обновлений пакетов (например, системы, которая загружает только тонны информации/данных через длительные периоды времени), обычно приходится иметь операции по изменению или обработке данных, поэтому вы можете просто включить REFRESH в конце.

Планирование операции REFRESH

Первой и широко используемой опцией является использование некоторой системы планирования для вызова обновления, например, вы можете настроить подобное в задании cron:

*/30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"

Затем ваше материализованное представление будет обновляться каждые 30 минут.

Вопросы

Этот параметр действительно хорош, особенно с параметром CONCURRENTLY, но только если вы можете принять данные, которые не обновляются на 100%. Имейте в виду, что даже с помощью CONCURRENTLY или без CONCURRENTLY команда REFRESH должна запускать весь запрос, поэтому вам нужно потратить время, необходимое для выполнения внутреннего запроса, прежде чем учитывать время для планирования REFRESH.

Обновление с помощью триггера

Другой вариант - вызвать REFRESH MATERIALIZED VIEW в триггерной функции, например:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;
    RETURN NULL;
END;
$$;

Затем в любой таблице, которая включает изменения в представлении, вы делаете:

CREATE TRIGGER tg_refresh_my_mv AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH STATEMENT EXECUTE PROCEDURE tg_refresh_my_mv();

Вопросы

У него есть некоторые критические ошибки для производительности и concurrency:

• Любая операция INSERT/UPDATE/DELETE должна выполнить запрос (что может быть медленным, если вы рассматриваете MV);
• Даже с CONCURRENTLY один REFRESH по-прежнему блокирует другой, поэтому любой INSERT/UPDATE/DELETE на вовлеченных таблицах будет сериализован.

Единственная ситуация, я могу думать, что в качестве хорошей идеи, если изменения действительно редки.

Обновить с помощью LISTEN/NOTIFY

Проблема с предыдущей опцией заключается в том, что она синхронна и накладывает большие накладные расходы при каждой операции. Чтобы улучшить это, вы можете использовать триггер, как раньше, но это вызывает только NOTIFY операция:

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, 'my_mv';
    RETURN NULL;
END;
$$;

Итак, вы можете создать приложение, которое поддерживает связь, и использует LISTEN операцию, чтобы идентифицировать необходимость вызова REFRESH. Один хороший проект, который вы можете использовать для тестирования, это pgsidekick, с помощью этого проекта вы можете использовать shell script для выполнения LISTEN, поэтому вы можете запланировать REFRESH как:

pglisten --listen=refresh_mv --print0 | xargs -0 -n1 -I? psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ?;"

Или используйте pglater (также внутри pgsidekick), чтобы убедиться, что вы не вызываете REFRESH очень часто. Например, вы можете использовать следующий триггер, чтобы сделать его REFRESH, но в течение 1 минуты (60 секунд):

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, '60 REFRESH MATERIALIZED VIEW CONCURRENLTY my_mv';
    RETURN NULL;
END;
$$;

Поэтому он не будет называть REFRESH менее чем за 60 секунд, а также если вы NOTIFY много раз меньше, чем за 60 секунд, REFRESH будет запускаться только один раз.

Вопросы

В качестве опции cron этот тоже хорош только в том случае, если вы можете обнажить несколько устаревших данных, но это имеет то преимущество, что REFRESH вызывается только тогда, когда это действительно необходимо, поэтому у вас меньше накладных расходов, а также данные обновляются ближе по мере необходимости.

OBS: Я еще не пробовал коды и примеры, поэтому, если кто-то обнаруживает ошибку, опечатывает или пытается ее использовать (или нет), пожалуйста, дайте мне знать.

Ответ 2

Позвольте мне указать три момента на предыдущий ответ MatheusOl - технологию pglater.

• В качестве последнего элемента массива long_options он должен включать элемент "{0, 0, 0, 0}", как указано на https://linux.die.net/man/3/getopt_long по фразе "Последний элемент массива должен быть заполнен нулями". Поэтому он должен читать -

  static struct option long_options[] =     {
        //......
        {"help", no_argument, NULL, '?'},
        {0, 0, 0, 0} 
  };
  

• В malloc/free - один бесплатный (для char listen = malloc (...);) отсутствует. Во всяком случае, malloc вызвал pglater-процесс сбой на CentOS (но не на Ubuntu - я не знаю почему). Поэтому я рекомендую использовать массив char и назначить имя массива указателю char (как для char, так и для char **). Вам нужно принудительно преобразовать тип, когда вы это делаете (назначение указателя).

  char block4[100];
  ...
  password_prompt = block4;
  ...
  char block1[500];
  const char **keywords = (const char **)&block1;
  ...
  char block3[300];
  char *listen = block3;
  sprintf(listen, "listen %s", id);
  PQfreemem(id);
  res = PQexec(db, listen);
  

• Используйте таблицу ниже, чтобы рассчитать таймаут, где md зрелый_duration, который представляет собой разницу во времени между последней точкой обновления (lr) и текущим временем.

  когда md >= callback_delay (cd) == > timeout: 0

  когда md + PING_INTERVAL >= cd == > timeout: cd-md [= cd- (now-lr)]

  когда md + PING_INTERVAL < cd == > таймаут: PI

Чтобы реализовать этот алгоритм (3-я точка), вы должны инициализировать 'lr' следующим образом:

res = PQexec(db, command);
latest_refresh = time(0);
if (PQresultStatus(res) == PGRES_COMMAND_OK) {