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

On 3/14/23 17:50, Bryn Llewellyn wrote:
> Section "43.7. Cursors” in the PL/pgSQL chapter of the doc
(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
readthe query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result
containsa large number of rows. (However, PL/pgSQL users do not normally need to worry about that, since FOR loops
automaticallyuse a cursor internally to avoid memory problems.) A more interesting usage is to return a reference to a
cursorthat a function has created, allowing the caller to read the rows. This provides an efficient way to return large
rowsets 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
itfrom a subprogram that's owned by a role that cannot select from the relevant table(s) to loop through the rows. But
Ican'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-sideapp checking out a connection from the pool, generating the entire response to the end-user's request,
releasingthe connection, and sending the response back to the browser. This paradigm isn't consistent with allowing the
enduser to navigate forwards and backwards in a scrollable cursor that is somehow held in its open state in in the
severby the the middle tier client on behalf of a browser session that comes back time and again to its dedicated
middletier 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:

https://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?
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com