Недавно у меня возникла интересная задача по хранению некоторых данных в рамках сессии работы с БД PostgreSQL (TTL = время жизни единичного коннекта к базе). Изначальный вопрос был таков…
А можно ли вместо вот такой конструкции:
использовать такую:
т.е. использовать некую глобальную переменную в рамках сессии для хранения значение идентификатора пользователя, которое будет доступно всем процедурам внутри базы.
Порывшись в гугле, поспрашивав на форуме, я нашел даже не одно решение, а целых 3! Чем с вами и делюсь...
Как гласит документация, цитирую:
для каждой сессии нам необходимо заново создавать временную таблицу. Отлично, то что нам и надо было! Попробуем…
Пример взят с форума sql.ru:
Достоинство данного метода состоит в том, что нет необходимости в дополнительных настройках или модулях, а язык PL/PgSQL есть практически всегда.
Недостатком данного метода является необходимость в приличном количестве дополнительных операций, которые выполняют приведенные процедуры, включая запросы к системным таблицам.
Как опять подсказывает нам документация, мы можем использовать в таких языках ка PL/Perl, PL/Tcl, PL/Python специальный массив данных, видимый в рамках сессии.
Пример взят из официальной документации:
Достоинством данного метода является минимальное количество операций и простота использования.
Недостатком является необходимость в дополнительном модуле языка для PostgreSQL (postgresql-plperl). Его необходимо доустанавливать самостоятельно, а на shared-хостингах зачастую это невозможно.
Несколько нестандартное использование дополнительных параметров в PostgreSQL. Конфигурационная переменная custom_variable_classes изначально предназначена для создания дополнительных классов конфигурационных переменных, которые могут использовать дополнительные модули PostgreSQL. Но в ходе исследований выяснилось, что если не определять переменные в классе из postgresql.conf, то они "живут" ровно текущую сессию.
Для использования данного метода необходимо прописать в postgresql.conf соответствующую настройку:
Пример:
Достоинство данного метода в использовании внутренних ресурсов PostgreSQL и отсутствии необходимости использования дополнительных языков или таблиц.
Недостатком является необходимость доступа к конфигурационному файлу сервера.
Ну вот и все пожалуй. Выбор одного из трех решений полностью зависит от возможностей Вашей установки БД. Для себя я выбрал 3-е решение.
А можно ли вместо вот такой конструкции:
some_procedure1(user_id, param1, ... , paramN); ... some_procedureX(user_id, param1, ... , paramN);
использовать такую:
set_user(id); some_procedure1(param1, ... , paramN); .... some_procedureX(param1, ... , paramN);
т.е. использовать некую глобальную переменную в рамках сессии для хранения значение идентификатора пользователя, которое будет доступно всем процедурам внутри базы.
Порывшись в гугле, поспрашивав на форуме, я нашел даже не одно решение, а целых 3! Чем с вами и делюсь...
Первый вариант. Использование временной таблицы.
Как гласит документация, цитирую:
PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used.
для каждой сессии нам необходимо заново создавать временную таблицу. Отлично, то что нам и надо было! Попробуем…
Пример взят с форума sql.ru:
CREATE OR REPLACE FUNCTION set_var(p_var_name varchar, p_var_value varchar) RETURNS void AS $$ DECLARE v_cnt integer; BEGIN SELECT Count(pc.relname) into v_cnt FROM pg_catalog.pg_class pc, pg_namespace pn WHERE pc.relname = 'session_var_tbl' AND pc.relnamespace = pn.oid AND pn.oid = pg_my_temp_schema(); IF v_cnt = 0 THEN EXECUTE 'CREATE GLOBAL TEMPORARY TABLE session_var_tbl (var_name varchar(100) not null, var_value varchar(100)) <br /> ON COMMIT preserve ROWS'; END IF; UPDATE session_var_tbl SET var_value = p_var_value WHERE var_name = p_var_name; IF NOT FOUND THEN INSERT INTO session_var_tbl(var_name, var_value) VALUES (p_var_name, p_var_value); END IF; END; $$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_var(p_var_name varchar) RETURNS varchar AS $$ DECLARE v_cnt integer; v_result varchar(100); BEGIN SELECT Count(pc.relname) INTO v_cnt FROM pg_catalog.pg_class pc, pg_namespace pn WHERE pc.relname='session_var_tbl' AND pc.relnamespace=pn.oid AND pn.oid=pg_my_temp_schema(); IF v_cnt = 0 THEN v_result := null; ELSE SELECT var_value INTO v_result FROM session_var_tbl WHERE var_name = p_var_name; IF NOT FOUND THEN v_result := null; END IF; END IF; RETURN v_result; END; $$ LANGUAGE 'plpgsql';
Достоинство данного метода состоит в том, что нет необходимости в дополнительных настройках или модулях, а язык PL/PgSQL есть практически всегда.
Недостатком данного метода является необходимость в приличном количестве дополнительных операций, которые выполняют приведенные процедуры, включая запросы к системным таблицам.
Второй вариант. Использование GlobalData массива.
Как опять подсказывает нам документация, мы можем использовать в таких языках ка PL/Perl, PL/Tcl, PL/Python специальный массив данных, видимый в рамках сессии.
Пример взят из официальной документации:
CREATE OR REPLACE FUNCTION set_var(name text, val text) RETURNS text AS $$ if ($_SHARED{$_[0]} = $_[1]) { return 'ok'; } else { return "cannot set shared variable $_[0] to $_[1]"; } $$ LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_var(name text) RETURNS text AS $$ return $_SHARED{$_[0]}; $$ LANGUAGE plperl;
Достоинством данного метода является минимальное количество операций и простота использования.
Недостатком является необходимость в дополнительном модуле языка для PostgreSQL (postgresql-plperl). Его необходимо доустанавливать самостоятельно, а на shared-хостингах зачастую это невозможно.
Третий вариант. Использование Customized Options.
Несколько нестандартное использование дополнительных параметров в PostgreSQL. Конфигурационная переменная custom_variable_classes изначально предназначена для создания дополнительных классов конфигурационных переменных, которые могут использовать дополнительные модули PostgreSQL. Но в ходе исследований выяснилось, что если не определять переменные в классе из postgresql.conf, то они "живут" ровно текущую сессию.
Для использования данного метода необходимо прописать в postgresql.conf соответствующую настройку:
custom_variable_classes = 'usrvar'
Пример:
CREATE OR REPLACE FUNCTION set_var(p_var_name varchar, p_var_value varchar) RETURNS void AS $body$ BEGIN PERFORM set_config('usrvar.'||p_var_name, p_var_value, false); END; $body$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_var(p_var_name varchar) RETURNS varchar AS $body$ DECLARE v_var_value varchar; BEGIN SELECT INTO v_var_value current_setting('usrvar.'||p_var_name); RETURN v_var_value; EXCEPTION WHEN syntax_error_or_access_rule_violation THEN v_var_value := null; RETURN v_var_value; END; $body$ LANGUAGE 'plpgsql';
Достоинство данного метода в использовании внутренних ресурсов PostgreSQL и отсутствии необходимости использования дополнительных языков или таблиц.
Недостатком является необходимость доступа к конфигурационному файлу сервера.
Ну вот и все пожалуй. Выбор одного из трех решений полностью зависит от возможностей Вашей установки БД. Для себя я выбрал 3-е решение.