Thread: using server side cursor
hello, I'm using pgsql cursors for making queries or large datasets. To do this, my database already implements a plpgsql function that return a cursor on some query. (see http://www.postgresql.org/docs/9.0/static/plpgsql-cursors.html ) As shown in this doc, my database contains a function that looks like this : CREATE FUNCTION myfunction ( refcursor ) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT col FROM test; RETURN $1; END; ' LANGUAGE plpgsql; Then, from the db prompt, I am able to call the function (thus creating a cursor), then fetch one row at a time : BEGIN; SELECT myfunction('mycursor'); myfunction ----------------------- mycursor (1 row) FETCH mycursor; (...) FETCH mycursor; COMMIT; Now I want to do the same into the Python code - naively I do something like: mycursor.callproc ( "myfunction", [ "mycursor" ] ) # setup a cursor in the db engine mycursor.execute ( "fetch", [ "mycursor" ] ) # fetch row from this cursor mycursor.fetchone() (...) but this returns an empty result. I guess I'm doing it the wrong way. There's a doc in psycopg about named cursors but I don't understand exactly how I should follow it to tie to my problem. (that is : using my existing pgpsql function that returns a cursor, then iterate on that cursor). any suggestion ? thanks Tom
On 14/10/11 12:32, thomas veymont wrote:
[snip]
> I guess I'm doing it the wrong way. There's a doc in psycopg
> about named cursors but I don't understand exactly how I should
> follow it to tie to my problem. (that is : using my existing pgpsql function
> that returns a cursor, then iterate on that cursor).
>
> any suggestion ?
Yes, use the native support for server-side cursors in psycopg. First,
don't use a procedure to setup the cursor but just pass to execute()
your SQL:
curs = conn.cursor("mycursor")
curs.execute("SELECT col FROM test")
curs.fetchmany(10) # fetch 10 rows from the cursor
hipe this helps,
federico
--
Federico Di Gregorio fog@initd.org
When people say things are a lot more complicated than that, they
means they're getting worried that they won't like the truth.
-- Granny Weatherwax
On Fri, Oct 14, 2011 at 11:32 AM, thomas veymont <thomas.veymont@gmail.com> wrote: > mycursor.execute ( "fetch", [ "mycursor" ] ) # fetch row from this cursor > mycursor.fetchone() This is the only error you have made: there is no placeholder in the query. And even if there was, it would be an error as it would result in something like "fetch 'mycursor' ", and postgres doesn't want the quotes. mycursor.execute ( "fetch mycursor" ) would do what you were expecting. If you have to compose the command, you have no protection though, so you should be hypercareful about where "mycursor" comes from. Federico is right though that using the plain query with a psycopg named cursor makes things easier. -- Daniele
On Fri, Oct 14, 2011 at 12:41 PM, Federico Di Gregorio <fog@dndg.it> wrote:
> On 14/10/11 12:32, thomas veymont wrote:
> [snip]
>> I guess I'm doing it the wrong way. There's a doc in psycopg
>> about named cursors but I don't understand exactly how I should
>> follow it to tie to my problem. (that is : using my existing pgpsql function
>> that returns a cursor, then iterate on that cursor).
>>
>> any suggestion ?
>
> Yes, use the native support for server-side cursors in psycopg. First,
> don't use a procedure to setup the cursor but just pass to execute()
> your SQL:
He has a point though: what if somebody has a database function call
to be used as interface?
Oh, there's a nice hack that can be done :P because all cursors from
the same connections live in the same transaction, one can use a
regular cursor to create the postgres refcursor and a named cursor to
iterate it:
>>> cur1 = cnn.cursor()
>>> cur1.callproc('myfunction', ['mycursor'])
['mycursor']
>>> cur2 = cnn.cursor('mycursor')
>>> cur2.fetchone()
(1,)
>>> cur2.fetchmany(2)
[(2,), (3,)]
Nasty :D
-- Daniele
thanks for your helpful answers.
>> mycursor.execute ( "fetch mycursor" )
is working okay.
you are right : using the psycopg native support for cursors makes
clearer code. But, yes indeed, the pgsql function is somewhat a
mandatory API to the database, so the Python code doesn't have to know
the inner query structure.
Daniele, beside the hack you are providing, you say : "all cursors
from the same connections live in the same transaction"
=> is it something specific that is true today but may change in the
future ? I mean, may I rely on this for a long-living code ?
thanks again
Tom
2011/10/14 Daniele Varrazzo <daniele.varrazzo@gmail.com>:
> On Fri, Oct 14, 2011 at 12:41 PM, Federico Di Gregorio <fog@dndg.it> wrote:
>> On 14/10/11 12:32, thomas veymont wrote:
>> [snip]
>>> I guess I'm doing it the wrong way. There's a doc in psycopg
>>> about named cursors but I don't understand exactly how I should
>>> follow it to tie to my problem. (that is : using my existing pgpsql function
>>> that returns a cursor, then iterate on that cursor).
>>>
>>> any suggestion ?
>>
>> Yes, use the native support for server-side cursors in psycopg. First,
>> don't use a procedure to setup the cursor but just pass to execute()
>> your SQL:
>
> He has a point though: what if somebody has a database function call
> to be used as interface?
>
> Oh, there's a nice hack that can be done :P because all cursors from
> the same connections live in the same transaction, one can use a
> regular cursor to create the postgres refcursor and a named cursor to
> iterate it:
>
>>>> cur1 = cnn.cursor()
>>>> cur1.callproc('myfunction', ['mycursor'])
> ['mycursor']
>
>>>> cur2 = cnn.cursor('mycursor')
>>>> cur2.fetchone()
> (1,)
>>>> cur2.fetchmany(2)
> [(2,), (3,)]
>
> Nasty :D
>
>
> -- Daniele
>
On 14/10/11 15:22, thomas veymont wrote:
> thanks for your helpful answers.
>
>>> >> mycursor.execute ( "fetch mycursor" )
> is working okay.
>
> you are right : using the psycopg native support for cursors makes
> clearer code. But, yes indeed, the pgsql function is somewhat a
> mandatory API to the database, so the Python code doesn't have to know
> the inner query structure.
>
> Daniele, beside the hack you are providing, you say : "all cursors
> from the same connections live in the same transaction"
> => is it something specific that is true today but may change in the
> future ? I mean, may I rely on this for a long-living code ?
Yes. Cursors are (and always be, we care about forward compatibility)
WITHOUT HOLD, so they live inside the current transaction only (unless
you pass the withhold parameter, that's it.)
federico
--
Federico Di Gregorio fog@initd.org
Ahr ahr ahr! E mo' me'e magno tutte! -- Er bieco
On Fri, Oct 14, 2011 at 2:22 PM, thomas veymont <thomas.veymont@gmail.com> wrote: > thanks for your helpful answers. > >>> mycursor.execute ( "fetch mycursor" ) > is working okay. > > you are right : using the psycopg native support for cursors makes > clearer code. But, yes indeed, the pgsql function is somewhat a > mandatory API to the database, so the Python code doesn't have to know > the inner query structure. > > Daniele, beside the hack you are providing, you say : "all cursors > from the same connections live in the same transaction" > => is it something specific that is true today but may change in the > future ? I mean, may I rely on this for a long-living code ? It's a fundamental psycopg design choice, It's not going to change. The thing that is relatively brittle and makes of my idea an hack is that the named cursor is used "uninitialized": if you try calling fetch*() on a regular (not named) cursor without calling execute() before, it will laugh at you. But I acknowledge the need of using a DB API provided via a cursor (honestly not the most common scenario, in many years I can't remember it being requested). We could either design a feature just for that, or add proper tests to the test suite and guarantee for the future that you will be able to use a named cursor regardless of where the refcursor is coming from (still limited to cursors on the same connection of course). At this point we will document the hack which will officially become a supported feature :) Thoughts? -- Daniele
On 14/10/11 15:44, Daniele Varrazzo wrote: > On Fri, Oct 14, 2011 at 2:22 PM, thomas veymont > <thomas.veymont@gmail.com> wrote: >> > thanks for your helpful answers. >> > >>>> >>> mycursor.execute ( "fetch mycursor" ) >> > is working okay. >> > >> > you are right : using the psycopg native support for cursors makes >> > clearer code. But, yes indeed, the pgsql function is somewhat a >> > mandatory API to the database, so the Python code doesn't have to know >> > the inner query structure. >> > >> > Daniele, beside the hack you are providing, you say : "all cursors >> > from the same connections live in the same transaction" >> > => is it something specific that is true today but may change in the >> > future ? I mean, may I rely on this for a long-living code ? > It's a fundamental psycopg design choice, It's not going to change. > > The thing that is relatively brittle and makes of my idea an hack is > that the named cursor is used "uninitialized": if you try calling > fetch*() on a regular (not named) cursor without calling execute() > before, it will laugh at you. But I acknowledge the need of using a DB > API provided via a cursor (honestly not the most common scenario, in > many years I can't remember it being requested). We could either > design a feature just for that, or add proper tests to the test suite > and guarantee for the future that you will be able to use a named > cursor regardless of where the refcursor is coming from (still limited > to cursors on the same connection of course). At this point we will > document the hack which will officially become a supported feature :) We can add a new parameter (existing=True?) to cursor(); to be used only when the cursor already exists with the following behaviour: 1) execute() calls now result in an exception; and 2) you're guaranteed that fetchXXX() will wok as expected. If you *don't* pass existing=True trying to fetch from a named cursor without first calling execute() can result in an exception too. But maybe this is too much... federico -- Federico Di Gregorio fog@initd.org But not all bugs are an interesting challenge. Some are just a total waste of my time, which usually is much more valuable than the time of the submitter. -- Md
On 14/10/11 16:24, Federico Di Gregorio wrote:
> We can add a new parameter (existing=True?) to cursor(); to be used only
> when the cursor already exists with the following behaviour:
>
> 1) execute() calls now result in an exception; and
> 2) you're guaranteed that fetchXXX() will wok as expected.
>
> If you *don't* pass existing=True trying to fetch from a named cursor
> without first calling execute() can result in an exception too. But
> maybe this is too much...
Mm.. ignore this, it's stupid. :/
federico
--
Federico Di Gregorio fog@initd.org
Quis custodiet ipsos custodes? -- Juvenal, Satires, VI, 347
2011/10/14 Daniele Varrazzo <daniele.varrazzo@gmail.com>: > On Fri, Oct 14, 2011 at 2:22 PM, thomas veymont > <thomas.veymont@gmail.com> wrote: >> thanks for your helpful answers. >> >>>> mycursor.execute ( "fetch mycursor" ) >> is working okay. >> >> you are right : using the psycopg native support for cursors makes >> clearer code. But, yes indeed, the pgsql function is somewhat a >> mandatory API to the database, so the Python code doesn't have to know >> the inner query structure. >> >> Daniele, beside the hack you are providing, you say : "all cursors >> from the same connections live in the same transaction" >> => is it something specific that is true today but may change in the >> future ? I mean, may I rely on this for a long-living code ? > > It's a fundamental psycopg design choice, It's not going to change. > yep, ok. as just seen in the manual ;-) "Named cursors are usually created WITHOUT OLD, meaning they live only as long as the current transaction." > > The thing that is relatively brittle and makes of my idea an hack is > that the named cursor is used "uninitialized": if you try calling > fetch*() on a regular (not named) cursor without calling execute() > before, it will laugh at you. But I acknowledge the need of using a DB > API provided via a cursor (honestly not the most common scenario, in > many years I can't remember it being requested). > well, that's interesting to me : do you think there was a better way to design this ? (I mean : the best way to hide the SQL query from the Python code while handling the big query result). Maybe that's a little bit out of our initial topic anyway. > > We could either > design a feature just for that, or add proper tests to the test suite > and guarantee for the future that you will be able to use a named > cursor regardless of where the refcursor is coming from (still limited > to cursors on the same connection of course). At this point we will > document the hack which will officially become a supported feature :) > > Thoughts? > Well something that I had not understood first is that creating a server-side cursor from psycopg doing this : mycursor = myconnection.cursor( 'myname') or cursor.execute ( "DECLARE myname CURSOR FOR ....") or cursor.callproc ( "create_named_cursor_function", [ 'myname', ... ] ) would result in the same cursor located in the same DB "namespace" (for the current transaction of course). So, what you mean is that, for now, psycopg does not check if my named-cursor was initialized before I try to fetch from it. If I understand well, "guaranteeing that I will be able to use a named cursor regardless of where the refcursor comes from" means that psycopg2 should never check the named-cursor I'm trying to fetch() , and let the DB engine complain if I make a mistake ? I think that's fair. cheers, Tom (next visit on monday) > -- Daniele >
On Fri, Oct 14, 2011 at 3:26 PM, Federico Di Gregorio <fog@dndg.it> wrote: > On 14/10/11 16:24, Federico Di Gregorio wrote: >> We can add a new parameter (existing=True?) to cursor(); to be used only ... > Mm.. ignore this... I would rejoice for having found a feature without the need of writing code and devise an interface for it :) I've added a note about it in <http://initd.org/psycopg/docs/usage.html#server-side-cursors> and a test to ensure to keep it: I see no reason for which we would require to break the "name stealing" in the future: it's just a matter of _not_ performing a check that execute() has run on the cursor. I've also fixed the problem of the Decimal adaptation in wsgi as discussed in the tracker (ticket #52). With this I think we can really release this 2.4.3: we have gathered another good collection of bug fixes. -- Daniele
2011/10/15 Daniele Varrazzo <daniele.varrazzo@gmail.com>: > On Fri, Oct 14, 2011 at 3:26 PM, Federico Di Gregorio <fog@dndg.it> wrote: >> On 14/10/11 16:24, Federico Di Gregorio wrote: >>> We can add a new parameter (existing=True?) to cursor(); to be used only > ... >> Mm.. ignore this... > > I would rejoice for having found a feature without the need of writing > code and devise an interface for it :) > > I've added a note about it in > <http://initd.org/psycopg/docs/usage.html#server-side-cursors> and a > test to ensure to keep it: I see no reason for which we would require > to break the "name stealing" in the future: it's just a matter of > _not_ performing a check that execute() has run on the cursor. > great. thanks Tom > I've also fixed the problem of the Decimal adaptation in wsgi as > discussed in the tracker (ticket #52). With this I think we can really > release this 2.4.3: we have gathered another good collection of bug > fixes. > > -- Daniele > > -- > Sent via psycopg mailing list (psycopg@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/psycopg >