Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?

On 3/14/23 20:29, Bryn Llewellyn wrote:
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>
>>> bryn(at)yugabyte(dot)com <mailto:bryn(at)yugabyte(dot)com> wrote:
>>>
>>> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc
>>> (www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-
>>> <http://www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR->DECLARATIONS) starts with this:
>>> «
>>> Rather than executing a whole query at once, it is possible to set up
>>> a cursor that encapsulates the query, and then read the query result
>>> a few rows at a time. One reason for doing this is to avoid memory
>>> overrun when the result contains a large number of rows. (However,
>>> PL/pgSQL users do not normally need to worry about that, since FOR
>>> loops automatically use a cursor internally to avoid memory
>>> problems.) A more interesting usage is to return a reference to a
>>> cursor that a function has created, allowing the caller to read the
>>> rows. This provides an efficient way to return large row sets from
>>> functions.
>>> »
>>> On its face, it seems to make sense. And I’ve written a few
>>> proof-of-concept tests. For example, I wrote a “security definer”
>>> function that's owned by a role that can select from the relevant
>>> table(s) that returns refcursor. And I called it from a subprogram
>>> that's owned by a role that cannot select from the relevant table(s)
>>> to loop through the rows. But I can't convince myself that this
>>> division of labor is useful. And especially I can't convince myself
>>> that the "pipeling" capability is relevant in a three-tier app with a
>>> stateless browser UI. Here, the paradigm has the client-side app
>>> checking out a connection from the pool, generating the entire
>>> response to the end-user's request, releasing the connection, and
>>> sending the response back to the browser. This paradigm isn't
>>> consistent with allowing the end user to navigate forwards and
>>> backwards in a scrollable cursor that is somehow held in its open
>>> state in in the sever by the the middle tier client on behalf of a
>>> browser session that comes back time and again to its dedicated
>>> middle tier client and thence yo its dedicated database server
>>> session. (Anyway, without anything like Oracle PL/SQL's packages, you
>>> have no mechanism to hold the opened cursor variable between
>>> successive server calls.)
>>
>> I guess that would depend on how you define a server call:
>>
>> www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-USING <http://www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-USING>
>>
>> "Once a cursor has been opened, it can be manipulated with the
>> statements described here.
>>
>> These manipulations need not occur in the same function that opened
>> the cursor to begin with. You can return a refcursor value out of a
>> function and let the caller operate on the cursor. (Internally, a
>> refcursor value is simply the string name of a so-called portal
>> containing the active query for the cursor. This name can be passed
>> around, assigned to other refcursor variables, and so on, without
>> disturbing the portal.)
>>
>> All portals are implicitly closed at transaction end. Therefore a
>> refcursor value is usable to reference an open cursor only until the
>> end of the transaction."
>>
>>> Is it fair to say that the PL/pgSQL refcursor is useful, at best,
>>> only in very special use-cases?
>
> Ah… I see. I had read this wrongly:
>
>> « Internally, a refcursor value is simply the string name of a
>> so-called portal containing the active query for the cursor. This name
>> can be passed around, assigned to other refcursor variables, and so
>> on, without disturbing the portal. »
>
> I thought that it was an "under the hood" explanation and that the only
> thing that you could see after entry into the PL/pgSQL subprogram that
> will be the top of the stack would be an opaque value (sometimes called
> a handle in other contexts) that was accessible only from PL/pgSQL. >

I am not sure how this:

"These manipulations need not occur in the same function that opened the
cursor to begin with. You can return a refcursor value out of a function
and let the caller operate on the cursor. ..."

could be any clearer.

> I just did. And the result of "select s.g(:cur)" was the expected "cat".
>
> It requires a leap of imagination, or help from the pgsql-general list,
> to get to this. So thanks!
>
> Might the doc add an example like this?

43.7.3.5. Returning Cursors

"PL/pgSQL functions can return cursors to the caller. This is useful to
return multiple rows or columns, especially with very large result sets.
To do this, the function opens the cursor and returns the cursor name to
the caller (or simply opens the cursor using a portal name specified by
or otherwise known to the caller). The caller can then fetch rows from
the cursor. The cursor can be closed by the caller, or it will be closed
automatically when the transaction closes."

And then a series of examples on how to do that.

I have a hard time fathoming why someone who writes documentation does
not actually read documentation.

>
> Of course, it all falls into place now. I can see how I could write a
> client app in, say, Python to write a humongous report to a file by
> fetching manageably-sized chunks, time and again until done with a
> function like my "g()" here, from a cursor that I'd opened using a
> function like my "f()".
>
> B.t.w., when I said "top-level call", I meant the SQL statement that a
> client issues—in this case most likely "select my_plpgsql_function()" or
> "call my_plpgsql_procedure()". That top-of-stack subprogram can invoke
> other subprograms and so on ad infinitum. But eventually the whole stack
> empties and control passes back to the client. But all that falls away
> now with the exampe I showed in place.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com