Re: using server side cursor

2011/10/14 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>:
> On Fri, Oct 14, 2011 at 2:22 PM, thomas veymont
> <thomas(dot)veymont(at)gmail(dot)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
>