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

On Wed, 2023-03-22 at 12:09 -0700, Bryn Llewellyn wrote:
> > laurenz(dot)albe(at)cybertec(dot)at wrote:
> > I recently used cursor scrollability, so I can show you a use case:
> >
> > github.com/cybertec-postgresql/db_migrator/blob/master/db_migrator--1.0.0.sql#L49
>
> However, source code famously reveals only what it does and not what the author's intention,
> and overall design philosophy, is. I looked at the README accounts for these two functions here:
>
> github.com/cybertec-postgresql/db_migrator#materialize_foreign_table
> github.com/cybertec-postgresql/db_migrator#db_migrate_refresh
>
> But these accounts say nothing about the design of their implementations.

The documentation describes the purpose of the function and how to use it,
not how I went about implementing it.

You are interpreting too much. I just wanted to show you a case where scrollable cursors
can be useful.

> The upshot, therefore, is that I'm afraid that I can only guess at why you use "open, fetch, close"
> on a refcursor rather than an ordinary cursor for loop. After all, you use the technique only
> to traverse metadata tables about partitions, subpartitions, and columns. I'd be astonished
> if such tables have humongous numbers of rows (like hundreds of millions).

No, I don't expect large result sets, and concurrency won't be a problem.

I explained why I used scrollable cursors: to save myself an extra query for
the total result set count.

Here is another account of how cursors can be useful:
https://www.cybertec-postgresql.com/en/pagination-problem-total-result-count/

Yours,
Laurenz Albe