Getting session variables without touching postgresql.conf

For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂

While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.

So, let's do it:

First let's make some assumptions on what we want to achieve:

a way to store value for parameter in given session

a way to get value of parameter in given session

This looks really simple.

So, let's make a table, and store backend pid and parameter name in there – together with value. If some session asks for value of parameter X, we check if it's there for given backend_pid.

Brilliant.

But. Let's assume some session stored some parameters, then disconnected. Then some other session connected, and it just so happened that the backend got the same pid as previously. While this might sound far fetched – it's actually pretty common in case you're using connection pooling software.

So, we need a way to clean params – so pooling program can cleanup session, before reusing it.

But what about the case when we don't use connection pooling? Reusage of pid is less likely, but still possible. So, perhaps some kind of expiration of parameters? For example “this parameter is only for 5 minutes".

So, now our feature list is longer:

a way to store value for parameter in given session

a way to get value of parameter in given session

a way to clean all values for given session

a way to expire parameters

Expiration should be automatical – i.e. we shouldn't need any cronjob to do it. The simplest way to do it, is to add implicit expiration as first step to all other operations, and just making sure that it's as fast as possible.

So, let's start. First – to minimize impact on other code, let's make our own schema:

As several people pointed in comments one can use temporary tables which solve the problem of wraparound of backend pids. It does not solve problem of connection reusage by connection pooler, so I'll leave cleanup() function intact.

We’ve also used session variables in our CRM. We’ve did some performance tests and discovered that using pltcl functions for this is much faster than using temp tables. We have written two simple functions that store and read a session variable in four ways: plpgsql temp table, pltcl global, plpython global and plperl global. Here are results of the test: http://www.truesolutions.pl/blog/benchmark-procedur-w-postgresql (sorry, Polish only, but the numbers should be understood).
Currently we are using these functions:

@Taai:
no, i was wrong. i use 9.2 on daily basis, and in 9.2 custom_variable_classes is gone.

in pre 9.1 you can use the trick like i described in this post.

and going back to your original question – yes, backend start is ok. and you don’t need “limit 1” in your query, but the query should be:
select backend_start from pg_stat_activity where procpid = pg_backend_pid();