Как стать автором
Поиск
Написать публикацию
Обновить

Обновление статистик таблиц PostgreSQL обычным пользователем

Уровень сложностиПростой
Время на прочтение2 мин
Количество просмотров2.7K

Обычно, обновлением статистик таблиц 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')]);

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

Теги:
Хабы:
Всего голосов 8: ↑8 и ↓0+10
Комментарии0

Публикации

Ближайшие события