Home PostgreSQL PostgreSQL. User data within a session

PostgreSQL. User data within a session

by admin

Recently I had an interesting task to store some data within PostgreSQL database session (TTL = lifetime of a single connection to the database). The original question was as follows…
Is it possible to replace this construct with :

 some_procedure1(user_id, param1, ... , paramN);...some_procedureX(user_id, param1, ... , paramN); 

use one of these :

 set_user(id);some_procedure1(param1, ... , paramN);....some_procedureX(param1, ... , paramN); 

i.e. to use some global variable within a session to store the value of user ID, which will be available to all procedures within the database.
Having searched in google, having asked on a forum, I have found not even one decision, but even 3! And I’m sharing them with you…

First option. Using a time table.

As stated by documentation , citing :

PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used.

we have to recreate a temporary table for each session. Great, just what we needed! Let’s try…
Example taken from the forum sql.ru :

 CREATE OR REPLACE FUNCTION set_var(p_var_name varchar, p_var_value varchar) RETURNS void AS$$DECLAREv_cnt integer;BEGINSELECT Count(pc.relname) into v_cntFROM pg_catalog.pg_class pc, pg_namespace pnWHERE pc.relname = 'session_var_tbl'AND pc.relnamespace = pn.oidAND pn.oid = pg_my_temp_schema();IF v_cnt = 0 THENEXECUTE '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_tblSET var_value = p_var_valueWHERE var_name = p_var_name;IF NOT FOUND THENINSERT 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$$DECLAREv_cnt integer;v_result varchar(100);BEGINSELECT Count(pc.relname)INTO v_cntFROM pg_catalog.pg_class pc, pg_namespace pnWHERE pc.relname='session_var_tbl'AND pc.relnamespace=pn.oidAND pn.oid=pg_my_temp_schema();IF v_cnt = 0 THENv_result := null;ELSESELECT var_valueINTO v_resultFROM session_var_tblWHERE var_name = p_var_name;IF NOT FOUND THENv_result := null;END IF;END IF;RETURN v_result;END;$$LANGUAGE 'plpgsql'; 

Dignity of this method is that there is no need for additional settings or modules, and the PL/PgSQL language is almost always available.
The disadvantage of of this method is the need for a decent number of additional operations that perform the above procedures, including queries to system tables.

Second option. Using GlobalData array.

As the documentation tells us again, we can use in languages like PL/Perl, PL/Tcl, PL/Python a special data array visible within a session.
This example is taken from official documentation :

 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; 

Dignity of this method is its minimal number of operations and ease of use.
The disadvantage of is the need for an additional language module for PostgreSQL (postgresql-plperl). It must be installed by yourself, and on shared-hosting it is often impossible.

Third option. Using Customized Options.

Slightly non-standard usage additional parameters in PostgreSQL. The configuration variable custom_variable_classes was originally intended to create additional configuration variable classes that can be used by additional PostgreSQL modules. But during the research it turned out that if you do not define variables in the class from postgresql.conf, they "live" exactly the current session.
To use this method, you must prescribe the appropriate setting in postgresql.conf :

 custom_variable_classes = 'usrvar' 

Example :

 CREATE OR REPLACE FUNCTION set_var(p_var_name varchar, p_var_value varchar) RETURNS void AS$body$BEGINPERFORM 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$DECLAREv_var_value varchar;BEGINSELECT INTO v_var_value current_setting('usrvar.'||p_var_name);RETURN v_var_value;EXCEPTIONWHEN syntax_error_or_access_rule_violation THENv_var_value := null;RETURN v_var_value;END;$body$LANGUAGE 'plpgsql'; 

Dignity of this method is that it uses the internal resources of PostgreSQL and does not require additional languages or tables.
The disadvantage of is the need to access the configuration file of the server.
So that’s about it. The choice of one of the three solutions depends entirely on the capabilities of your database installation. For me I chose the 3rd solution.

You may also like