Session state per transaction

Hi,

I am looking for a mechanism (usable from Psycopg2) that allows me
to call stored procedures while having a per-transaction state set.

I have a connection pool serving web sessions, and I want to set
the web session ID as per stored procedure call.

There is no 1:1 relation between Web sessions and database sessions,
so this needs to be on a per-call basis.

I came over the current_setting and set_config functions

http://www.postgresql.org/docs/9.2/static/functions-admin.html

and did some tests (see below).

However, I am not sure how to use that from Psycopg2.

Normally I call SPs simply via

cur.execute("SELECT mysp1(%s, %s)", [...])

on a connection set to autocommit = True;

Now I want to do the same but have

cur.execute("SELECT set_config('webmq.sessionid', %s, false)", [session_id])

"implicitly called before".

That is I want to avoid the double roundtrip ..

Any ideas?

Thanks,
Tobias




CREATE OR REPLACE FUNCTION hello (msg VARCHAR) RETURNS VARCHAR
AS
$$
BEGIN
   RETURN msg || ' [session ID = ' || current_setting('webmq.sessionid')
|| ']';
END;
$$
LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION tabfun1 () RETURNS SETOF VARCHAR
AS
$$
BEGIN
   RETURN NEXT 'Hello';
   RETURN NEXT ', world';
   RETURN NEXT '!';
   RETURN NEXT current_setting('webmq.sessionid');
   RETURN;
END;
$$
LANGUAGE 'plpgsql';


DO
$$
BEGIN
   PERFORM set_config('webmq.sessionid', 'Xnjkas23', false);
   RAISE NOTICE '%', hello('Hello, world!');
   PERFORM set_config('webmq.sessionid', 'abda6ads', false);
   RAISE NOTICE '%', hello('Hello, world!');
END
$$
LANGUAGE 'plpgsql';


SELECT set_config('webmq.sessionid', 'kjzgbad7', false), hello('Hello,
world!');

SELECT set_config('webmq.sessionid', 'mhzAD456', false), tabfun1()