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

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

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