On 3/15/23 13:37, Bryn Llewellyn wrote:
laurenz.albe@xxxxxxxxxxx wrote:
Re « You seem to think that a client request corresponds to a single database request », I meant no more than what psql models when you hit "return"after terminating an ordinary SQL statement with semi-colon (i.e. not a meta-command). I used "client" to mean a program that connects directly to the PostgreSQL server (using TCP/IP)—and not the ultimate human client who's using, say, a browser. Perhaps I should have said "top-level SQL call". (To keep this simple, let's assume that I don't use "backslash-semicolon" to send two or more top-level SQL calls in one round trip.)
So in this example:
begin;
declare cur cursor for select k, v from s.t order by k;
fetch forward 10 in cur;
fetch absolute 90 in cur;
fetch forward 10 in cur;
commit;
where I hit "return" after each semicolon, I'd say that I made six top-level SQL calls—and that lines up with the fact that each of "begin". "declare", "fetch", and "commit" has its own page in the "SQL Commands" section of the PG doc.
It sounds like you prefer "database request" for this. Is that right?
I was trying to get at the distinction between issuing a particular SQL statement as a top-level SQL call and issuing it from a PL/pgSQL subprogram.
I've read wording like what you wrote following "Cursors shine wherever you need …" elsewhere. But I can't picture a concrete use case where, not withstanding the "where" restriction that my "select" used, I can't tell how much of the result set I'll need or where reading result #n1 informs me that I next need to scroll and read result #n2. So I was looking for a convincing example.
Huh?
You provided your own example earlier:
"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()"."
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
