Обсуждение: 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()

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

> 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, ...])
>

Great! That's sufficient. I wasn't aware concatenating statements works
like that.

> 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.

Guess you refer to (which I here reproduce for others who might wonder):

"The primary advantage of PQexecParams over PQexec is that parameter
values can be separated from the command string, thus avoiding the need
for tedious and error-prone quoting and escaping.

Unlike PQexec, PQexecParams allows at most one SQL command in the given
string. (There can be semicolons in it, but not more than one nonempty
command.) This is a limitation of the underlying protocol, but has some
usefulness as an extra defense against SQL-injection attacks."

http://www.postgresql.org/docs/9.2/static/libpq-exec.html

===

Btw: does Psycopg currently use PQprepare/PQexecPrepared ?

Thanks for your help,
Tobias

>
> -- Daniele
>

On Wed, Sep 26, 2012 at 8:53 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:

>> 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.
>
> Guess you refer to (which I here reproduce for others who might wonder):
>
> Unlike PQexec, PQexecParams allows at most one SQL command in the given
> string.
>
> http://www.postgresql.org/docs/9.2/static/libpq-exec.html

Correct.

> Btw: does Psycopg currently use PQprepare/PQexecPrepared ?

Not yet: note that these function are very similar in signature to
PQexecParams: supporting the latter would bring us closer to use
prepare statement.

Note 2: you can still prepare a statement in sql using the PREPARE
statement, and this can currently be used by psycopg.

But for both the methods, one question remains: how to use statement
preparation? prepare() at each execute() seems overkilling if you have
to execute a statement only once. An obvious place that would benefit
of preparation is executemany(): we could prepare the query once on
the query and execPrepared once for each item.

Still there is an important use case: in a connection, using one or
several cursors, the same query could be repeated over and over. So
what?

- should we have a prepare() method to be called to manually prepare a
query? How to refer to the prepared query? Should prepare return a
name/opaque object? Should it just intern the string and detect that
the same query is used by execute()?
- should we have a connection subclass preparing all the queries you
throw at it (until a certain limit after which start discarding the
older ones)?

So, I'd say once we know how we would use a prepare/execute feature we
can implement it. In the meanwhile the feature can be somewhat
prototyped by subclassing connection and cursor and mangling the
queries with a PREPARE statement, with which we can use the current
psycopg parameters adaptation.

-- Daniele

On 27/09/2012 00:58, Daniele Varrazzo wrote:
[snip]
> - should we have a prepare() method to be called to manually prepare a
> query? How to refer to the prepared query? Should prepare return a
> name/opaque object? Should it just intern the string and detect that
> the same query is used by execute()?
> - should we have a connection subclass preparing all the queries you
> throw at it (until a certain limit after which start discarding the
> older ones)?

Preparing statements is useful only in a limited set of scenarios and we
should give the user as much control over them as possible. I'd use an
opaque object that keeps a reference to the connection and to the
prepared statement (in a sense it is just a smart cursor/connection
proxy, all the logic will continue to be there). The opaque object
should expose at least execute() and executemany() variations taking
only the parameters and expose the .connection and .cursor for
everything else (or we can proxy more than that...) You'll do:

prep = conn.prepare("INSERT INTO bla VALUES (%s, %s)")
prep.execute(("foo", "bar"))
prep.execute(("baz", "gaz"))
prep.connection.commit()

> So, I'd say once we know how we would use a prepare/execute feature we
> can implement it. In the meanwhile the feature can be somewhat
> prototyped by subclassing connection and cursor and mangling the
> queries with a PREPARE statement, with which we can use the current
> psycopg parameters adaptation.

Yes, we can probably have prepared statements in Python only.

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
 La macchina virtuale elabora quindi dati adempiendo le sue funzioni
  specifiche senza esistere nella realtà degli oggetti.  -- uno studente

Hi Daniele,

>> Btw: does Psycopg currently use PQprepare/PQexecPrepared ?
>
> Not yet: note that these function are very similar in signature to
> PQexecParams: supporting the latter would bring us closer to use
> prepare statement.
>
> Note 2: you can still prepare a statement in sql using the PREPARE
> statement, and this can currently be used by psycopg.
>
> But for both the methods, one question remains: how to use statement
> preparation? prepare() at each execute() seems overkilling if you have
> to execute a statement only once. An obvious place that would benefit
> of preparation is executemany(): we could prepare the query once on

Agreed. executemany is definitely a use case.

> the query and execPrepared once for each item.

My use case is this: all DB access is via Stored Procedures (SP) on a
pool of long lived DB connections. The set of eligible SPs is determined
in advance. Hence, something that does not lead to reparsing/replanning
of the SP call SELECT statements on every execution is desirable.

I notice there is

http://www.postgresql.org/docs/9.2/static/libpq-fastpath.html

Does Psycopg support this?

Is cursor.callproc using that under the hood?

(This won't allow me to do the "multiple statements" trick for setting
the per-transaction state .. but I may find another way to achieve the
latter).

The pqlib docs mention:

"This interface is somewhat obsolete, as one can achieve similar
performance and greater functionality by setting up a prepared statement
to define the function call. Then, executing the statement with binary
transmission of parameters and results substitutes for a fast-path
function call."

So it can be emulated, but one needs to do multiple things (not only
prepared statement)

>
> Still there is an important use case: in a connection, using one or
> several cursors, the same query could be repeated over and over. So
> what?
>
> - should we have a prepare() method to be called to manually prepare a
> query? How to refer to the prepared query? Should prepare return a
> name/opaque object? Should it just intern the string and detect that
> the same query is used by execute()?

+1 for returning an opaque object

I can build query string (plain SQL with placeholders before adaption)
to prepared statement object mapping in 3 lines of I need it on top.

> - should we have a connection subclass preparing all the queries you
> throw at it (until a certain limit after which start discarding the
> older ones)?

Mmh. As a convenience class, why not? Seems neat.

>
> So, I'd say once we know how we would use a prepare/execute feature we
> can implement it. In the meanwhile the feature can be somewhat
> prototyped by subclassing connection and cursor and mangling the
> queries with a PREPARE statement, with which we can use the current
> psycopg parameters adaptation.
>
> -- Daniele
>

Cheers,
Tobias

On 27/09/2012 09:35, Tobias Oberstein wrote:
> My use case is this: all DB access is via Stored Procedures (SP) on a
> pool of long lived DB connections. The set of eligible SPs is determined
> in advance. Hence, something that does not lead to reparsing/replanning
> of the SP call SELECT statements on every execution is desirable.
>
> I notice there is
>
> http://www.postgresql.org/docs/9.2/static/libpq-fastpath.html
>
> Does Psycopg support this?

Nope.

federico

--
Federico Di Gregorio                         federico.digregorio@dndg.it
Studio Associato Di Nunzio e Di Gregorio                  http://dndg.it
            Bhoe, bhe, bhe. Sono brutto e cattivo. Brutto lama! -- Cuzco

Am 27.09.2012 09:47, schrieb Federico Di Gregorio:
> On 27/09/2012 09:35, Tobias Oberstein wrote:
>> My use case is this: all DB access is via Stored Procedures (SP) on a
>> pool of long lived DB connections. The set of eligible SPs is determined
>> in advance. Hence, something that does not lead to reparsing/replanning
>> of the SP call SELECT statements on every execution is desirable.
>>
>> I notice there is
>>
>> http://www.postgresql.org/docs/9.2/static/libpq-fastpath.html
>>
>> Does Psycopg support this?
>
> Nope.

;(

Then I am definitely interested in helping bringing support for prepared
stuff and also fastpath-SP-calls to Pscyopg (since the API for both seem
to be somewhat similar).

Tobias

>
> federico
>