session_variable

Overview

Version

Build

You can build the RPM / DEB packages for session_variable using pig build:

pig build pkg session_variable         # build RPM / DEB packages

Install

You can install session_variable directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install session_variable;          # Install for current active PG version

pig ext install -y session_variable -v 18  # PG 18
pig ext install -y session_variable -v 17  # PG 17
pig ext install -y session_variable -v 16  # PG 16
pig ext install -y session_variable -v 15  # PG 15
pig ext install -y session_variable -v 14  # PG 14

dnf install -y session_variable_18       # PG 18
dnf install -y session_variable_17       # PG 17
dnf install -y session_variable_16       # PG 16
dnf install -y session_variable_15       # PG 15
dnf install -y session_variable_14       # PG 14

apt install -y postgresql-18-session-variable   # PG 18
apt install -y postgresql-17-session-variable   # PG 17
apt install -y postgresql-16-session-variable   # PG 16
apt install -y postgresql-15-session-variable   # PG 15
apt install -y postgresql-14-session-variable   # PG 14

Create Extension:

CREATE EXTENSION session_variable;

Usage

Sources:

• 3.6 README
• 3.6 SQL API
• 3.6 control file
• 3.6 initialization code

session_variable defines database-level variables and constants with a separate value in each session. Changes to session-local values do not affect other connections.

Creating Variables and Constants

CREATE EXTENSION session_variable;

-- Create a variable with initial value
SELECT session_variable.create_variable('my_var', 'text'::regtype, 'initial text'::text);

-- Create a variable with NULL initial value
SELECT session_variable.create_variable('my_date_var', 'date'::regtype);

-- Create a constant (cannot be changed via set())
SELECT session_variable.create_constant('my_env', 'text'::regtype, 'Production'::text);

Getting and Setting Values

-- Get variable value (second arg is type hint)
SELECT session_variable.get('my_var', null::text);

-- Set variable value (returns true on success)
SELECT session_variable.set('my_var', 'new text'::text);

Using in PL/pgSQL

DO $$
DECLARE
    my_field text;
BEGIN
    my_field := session_variable.get('my_var', my_field);
    RAISE NOTICE 'Value: %', my_field;
END
$$ LANGUAGE plpgsql;

Administration Functions

-- Alter the initial/constant value (affects new sessions)
SELECT session_variable.alter_value('my_env', 'Development'::text);

-- Reload all variables from database definitions
SELECT session_variable.init();

-- Drop a variable or constant
SELECT session_variable.drop('my_var');

-- Check if a variable exists
SELECT session_variable.exists('my_var');

-- Get the type of a variable
SELECT session_variable.type_of('my_var');

Key Behaviors

• Variables are defined at the database level; each session gets a local copy
• set() only changes the session-local copy; other sessions are unaffected
• alter_value() changes the stored value; new sessions see it, existing sessions need init() to refresh
• Constants cannot be changed via set(), only via alter_value()
• Variable and constant names must be unique across both types

Privileges, Reads and Version Boundaries

A superuser installs this non-relocatable extension in schema session_variable; no shared preload or restart is required for the SQL workflow. Grant session_variable_administrator_role to manage definitions and session_variable_user_role for ordinary access. The administrator role includes the user role.

session_variable.set and session_variable.alter_value return boolean true on success, not the previous value. A successful stored-definition change becomes visible in the caller immediately and in future sessions after commit; existing sessions keep their local state until session_variable.init() resets all values. Definitions are stored in session_variable.variables and included in logical backups.

session_variable.get_stable may cache a value during a statement; use the regular getter when a trigger changes that value within the same statement. session_variable.get_constant is IMMUTABLE and can return cached results after administrative constant changes; use the regular getter during such changes.

Release 3.6 removes obsolete version-1 initialization support. The preceding 3.5 rejects non-null initial values for collection and icollection because text initialization can crash a backend. For those types, create a null-initialized variable and follow the source’s initialization hook session_variable.variable_initialisation(); the README’s alternate hook name is inconsistent with the implementation. Upstream lists PostgreSQL 14-18 and provisional PostgreSQL 19 support.