Re: Session state per transaction

On Wed, Sep 26, 2012 at 5:56 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:
> 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?

If the double roundtrip is the problem you can send the two queries
together, and take care in your code to send the first only once per
user request

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

This is one of these things that would stop work moving to
PQexecParams: that's why I want to make sure to leave open the
possibility to keep on using PQexec even if we move to the *Params
functions.

-- Daniele