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 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:
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@xxxxxxxxxxx
