05.06.2024 10:46
Vlad65536 0 1100 Источник

Обычно, обновлением статистик таблиц PostgreSQL занимается демон AUTOVACUUM. Однако, после кардинальных изменений в таблице, к которой сразу же требуется обращаться в последующих запросах к БД, рекомендуется явно обновить статистики этой таблицы явным запуском ANALYZE <имя таблицы>. Команда ANALYZE в PostgreSQL доступна только владельцу таблицы, владельцу базы данных и суперпользователю. При этом иногда возникает необходимость предоставить возможность явного обновления статистик обычному пользователю, не являющемуся владельцем таблицы.

Пусть у нас есть некоторые таблицы, в которые разные пользователи загружают достаточно большие массивы данных. Дать права на INSERT, UPDATE, DELETE, SELECT всем этим пользователям труда не составляет. Но владелец то у каждой таблицы должен быть только один. Причем права этого владельца намного шире, чем доступные пользователям. Если после загрузки данных пользователь сразу же использует эти таблицы в различных запросах под своими правами, то некорректные статистики могут привести к некорректному плану выполнения и длительным неэффективным выполнениям запросов.

Для решения этой проблемы была создана следующая хранимая процедура:

CREATE OR REPLACE PROCEDURE do_tables_analyze(tables_list regclass[])
SECURITY DEFINER AS $proc$
<<proc>>
DECLARE
  sql_cmd text;
BEGIN
  SELECT STRING_AGG('ANALYZE ' || S.nspname || '.' || C.relname || ';', '')
  FROM unnest(tables_list) L(t)
  JOIN pg_class C ON C.oid = L.t::oid
  JOIN pg_namespace S ON S.oid = C.relnamespace
  INTO proc.sql_cmd;
  EXECUTE proc.sql_cmd;
END; $proc$ LANGUAGE plpgsql;

Так как процедура имеет атрибут SECURITY DEFINER, то выполняется она под правами суперпользователя. Во избежании SQL-иньекций, процедуре передается массив не с именами таблиц, а с их regclass. И уже по regclass каждой таблицы определяется её имя в pg_class и название схемы в pg_namespace.

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

CALL do_tables_analyze(
  ARRAY[
    to_regclass('prod.some_table_1'),
    to_regclass('tst.some_table_2'),
    to_regclass('dev.some_table_3')]);

Спасибо за внимание!

Комментарии (0)