Handling Global State in Procedures

In this lesson, you will learn how to maintain persistent, session-level memory within your PostgreSQL procedures to track information across multiple function calls.

The Concept of Persistent State

By default, PL/pgSQL functions are stateless: once a function completes, all local variables are discarded. However, when working with complex procedural logic, you often need to preserve data across separate calls without writing to permanent tables (which would incur unnecessary I/O overhead).

PostgreSQL provides a mechanism to handle this "global" state within the context of a single database session using session_replication_role or, more commonly for custom procedural state, the SET_CONFIG and CURRENT_SETTING functions. While these functions are often used for session parameters, they serve as our primary interface for a key-value store that persists for the lifetime of the connection.

Implementing Global State

To store a value, use set_config. To retrieve it, use current_setting. Since these are session-scoped, they behave as global variables for the duration of the current user's connection.

Consider this example where we track a "call counter" that persists across multiple procedure invocations:

CREATE OR REPLACE PROCEDURE increment_counter()
LANGUAGE plpgsql AS $$
DECLARE
    current_val INT;
BEGIN
    -- Retrieve the current value, default to 0 if not set
    current_val := COALESCE(current_setting('my_app.counter', true), '0')::INT;
    
    -- Increment and store back to the session context
    PERFORM set_config('my_app.counter', (current_val + 1)::TEXT, false);
    
    RAISE NOTICE 'Counter is now: %', current_setting('my_app.counter');
END;
$$;

Limitations and Best Practices

1. Scope: This state is strictly tied to the current session (connection). If the user disconnects or the connection is returned to a connection pool, the state is cleared.
2. Type Casting: Everything stored in this "global" space is treated as text. You must explicitly cast values back to their original types (e.g., ::INT or ::BOOLEAN) when retrieving them.
3. Namespace: Always prefix your configuration keys (e.g., my_app.variable_name) to avoid collisions with internal PostgreSQL settings.
4. Performance: While faster than disk-backed tables, this method is intended for lightweight metadata. For large data structures, consider shared memory or temporary tables.

Key Takeaway: Use set_config and current_setting to create a lightweight, session-scoped memory space for persisting variables across procedure calls.