From: | Tobias Oberstein <tobias(dot)oberstein(at)gmail(dot)com> |
---|---|
To: | psycopg(at)postgresql(dot)org |
Subject: | Session state per transaction |
Date: | 2012-09-26 16:56:33 |
Message-ID: | 506333C1.701@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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()
From | Date | Subject | |
---|---|---|---|
Next Message | Daniele Varrazzo | 2012-09-26 18:34:29 | Re: Session state per transaction |
Previous Message | Daniele Varrazzo | 2012-09-24 17:54:18 | Re: Range type adaptation implemented |