pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Bryn Llewellyn <bryn@yugabyte.com>
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?
Date: Tue, 14 Mar 2023 19:09:33 -0700
Message-ID: <ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com> (raw)
In-Reply-To: <6A73AAEA-7A9F-42C9-A842-88213FA41003@yugabyte.com>
References: <6A73AAEA-7A9F-42C9-A842-88213FA41003@yugabyte.com>

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@aklaver.com



view thread (26+ messages)  latest in thread

Message-ID: <ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com>
Permalink:  ../ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com/
Also on:    postgresql.org/message-id/ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, bryn@yugabyte.com
  Subject: Re: Is the PL/pgSQL refcursor useful in a modern three-tier app?
  In-Reply-To: <ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com>
  References: <6A73AAEA-7A9F-42C9-A842-88213FA41003@yugabyte.com> <ae0f4e5c-d89d-ec43-6a94-00d57a963ce6@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox