PostgreSQL. Пользовательские данные в рамках сессии

    Недавно у меня возникла интересная задача по хранению некоторых данных в рамках сессии работы с БД PostgreSQL (TTL = время жизни единичного коннекта к базе). Изначальный вопрос был таков…
    А можно ли вместо вот такой конструкции:
    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-е решение.

    Similar posts

    Ads
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More

    Comments 4

      +2
      Здорово, спасибо. Но это надо бы поместить в блог PostgreSQL.
        0
        Переместил, как-то сразу не подумал, голова не варила в 6 часов утра.
        0
        Спасибо! Как раз недавно коллеги решали такую задачу с помошью временных таблиц. Про Customized Options не знал, беру на заметку.
          +1
          В итоге 3 вариант был модифицирован до:
          CREATE OR REPLACE FUNCTION setvar(p_name character varying, p_val character varying, p_name2 character varying, p_val2 character varying)
          RETURNS void AS
          $BODY$BEGIN
          EXECUTE 'SET SESSION usrvar.'||$1||' = '''||$2||'''; SET SESSION usrvar.'||$3||' = '''||$4||''';';
          END;$BODY$
          LANGUAGE 'plpgsql' VOLATILE
          COST 100;

          Также присутствуют функции для 1 — 2 переменных.

          Only users with full accounts can post comments. Log in, please.