Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1l8g-00CPC3-Sm for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:31:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v1l8e-002Hm0-4x for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:31:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1l8d-002Hls-R7 for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 12:31:55 +0000 Received: from mail-pl1-x642.google.com ([2607:f8b0:4864:20::642]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1l8Z-00018Q-2y for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 12:31:54 +0000 Received: by mail-pl1-x642.google.com with SMTP id d9443c01a7336-27c369f8986so9309095ad.3 for ; Thu, 25 Sep 2025 05:31:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1758803509; x=1759408309; darn=postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=EGtqncf7HRHmiog9wxiF1pqZgeF31xQjoiM4mVj5L0A=; b=RLok/C9tYRuYdMWtNKi21eYseSXpycHPp88K0WKj/JsrjJt4gjvmXWRRGTzVUl4DGe 5UY+tuMaTQutMg5sKgy2YbGP5vMU+ADWAW5jTDva5fsRfjGU2X41n4/+rQSJKmsmO8kC rPbV3z/k3pPXVx1HLBg1aQLDcXvHe7oRPIQF8x1bn+lknOcAnsCWzXml0//9kgILih/G CEgptL60pyUVrvWpUiiXXC+03l9D+FPSRRz/xkL83htW16w4ksG/wig//R5ddkXqW7A2 4aNX11pvNlxwFqSmOkLbpRM+OeArl3ghiGSst7QCaqDPaFbLMCSYI+iIf+xpc7QkkZSy N87Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758803509; x=1759408309; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=EGtqncf7HRHmiog9wxiF1pqZgeF31xQjoiM4mVj5L0A=; b=dXkM1E6sBskClrOhqCQOl4Pb9rsF118U1AjHHbEsGeGKd2NsY/Nk7051w+ixYFx+2f 90VtVGSFwP3yGarXKuDGBKJhqEW6l0OIF7OurN3JIgo2QSUQYwyARJhuXze7MCSeG/Ps p52bXXkiDQJwlSf3dHE/Trqq2U8VYPJ0oq46FPFgu9FnZ4RZKJsx94SR8fEJgAudLrDm y52gpmj3Ef2nvr3UY9npGVt2nhSUQx1cFVwIhA+NUmkVM/O33OdcgUVHfhStZ0jH08ne bdPsEHrkdiXyMTx57aLYNoJVtPQxbxN6owxVOJtnKfLiqi5INxjioqbL+ArP9fImt+ac Khmw== X-Forwarded-Encrypted: i=1; AJvYcCUR7GM/5dRkS0MGDwGHYWEB+v1KdjddwqaBY5pS8wHgWx/j7jgOM9exJNCh2BfmJ1SJ46xqUDOHnom8MkcQdyU=@postgresql.org X-Gm-Message-State: AOJu0Yx0BcO5doZPQp5pKY04Aga/yvhmMa77IDfeSOSBCHIzwjY0sRyV K9nAmafzZhaGbK4VbBsLlFyR8pHG/2SGgrQu/tSt7k8b/XBHTziNMkRKOL+InV2xYliea1IiWhC g1mS1OVD8 X-Gm-Gg: ASbGnctItNRb870kzN05/qdlh2KPIFUHUZW1RlwTUF9xhyOYc5I+rDndKTmEzjOm4kQ bfJF4bNwuaheVELiJV4SbM2J2MH0lebc9vzuFT6+Th1CrvRlvRscLkjhj4Z9mwwdgd3VtPeY5FU WXgJJq7Wj5edHhXeMInouK0gv1U7acO+EF1onDoOJE82YJj8NrAzqIgYFMqqdaNtFI8BWxVjBGI rrT6DKI/1+DQdZ/mZ8D83PM03vhr5TQX1zD2ldeAD2NwYgtkWYyp91k6IbsoiDDjfurUKNVrK7b xnxlfMYuPd5Ut0D/Vg4uCDsR/jWrNycMA4h4cbj5JtlvgtMuhedCebX3paAwuq3mPFG9KZi2UhO 2UATWGVTZ+IzKUXNYF+kYAGgAZTGgRknL7zsLnbhBTO/R/pcVdn+/ActYoRzf X-Google-Smtp-Source: AGHT+IErKAywxUGKbHwIDa4gPP7PBVnABbuFlOPr1N7PTfEDiOdrSd+ayL+MKvoImj8O8EfnrIMAJg== X-Received: by 2002:a17:902:e5c6:b0:27b:472e:3a22 with SMTP id d9443c01a7336-27ed4a5eb18mr36548975ad.56.1758803509494; Thu, 25 Sep 2025 05:31:49 -0700 (PDT) Received: from smtpclient.apple ([163.116.205.144]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-27ed6700ccdsm23797135ad.37.2025.09.25.05.31.46 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 25 Sep 2025 05:31:49 -0700 (PDT) From: Murtuza Zabuawala Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_E5E81869-9EE7-48AD-9410-99BFE5978802" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Regarding multiple result set in query tool Date: Thu, 25 Sep 2025 18:01:35 +0530 In-Reply-To: Cc: Dave Page , pgadmin-hackers To: Aditya Toshniwal References: X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_E5E81869-9EE7-48AD-9410-99BFE5978802 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On 25 Sep 2025, at 5:38=E2=80=AFPM, Aditya Toshniwal = wrote: >=20 > Hi Dave, >=20 > On Thu, Sep 25, 2025 at 4:31=E2=80=AFPM Dave Page > wrote: >> Hi >>=20 >> On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal = > wrote: >>> Hi Dave, >>>=20 >>> On Thu, Sep 25, 2025 at 3:29=E2=80=AFPM Dave Page > wrote: >>>> Hi >>>>=20 >>>> On Wed, 24 Sept 2025 at 13:43, Aditya Toshniwal = > wrote: >>>>> Hi Dave/Hackers, >>>>>=20 >>>>> I'm working on a feature where the query tool will show separate = data output for all the select statements run in a single batch. psycopg = does provide the result sets (as libpq provides) but there is a catch. = Let me explain how pgAdmin currently works: >>>>> 1. psycopg provides a cursor object on query execution. >>>>> 2. The cursor object has a function called nextset which can be = used to move to the next result set of queries executed. >>>>> 3. Once you move to the nextset, you cannot get data for the = previous set. It will only point to the current set. >>>>> 4. Right now, we keep on looping through nextset until it reaches = the last set and then fetch the data from the last set (using = pagination). >>>>> 5. The fetched result is stored in client memory (python process) >>>>>=20 >>>>> So if we need to show the output of all the queries, we'll have to = fetch the result for each query and store it in python memory before = moving to the next set. >>>>> psycopg already stores the data on the client side, the only = difference will be that we'll store all sets and not just the last one. >>>>=20 >>>> That seems like it's potentially problematic with large result = sets, and workarounds would likely lead to potentially confusing = behaviour for end users (which I really don't like the sound of). >>>> =20 >>>>>=20 >>>>> If any one has any suggestions on memory management then please = let me know. >>>>> Otherwise, I'm proceeding with what is discussed above. >>>>=20 >>>> I don't have any suggestions regarding memory management here, but = I do wonder if this is something which warrants an enhancement to = psycopg, to allow random access to the result sets. At a quick glance, = it looks like BaseCursor._results is a simple list of PGresult objects, = which could be easily exposed of course. What I haven't checked is = whether any witchcraft happens that would make random access to those = objects problematic. >>> No we cannot move to the next result set, until you close the = previous one even with libpq. >>=20 >> Hmm, yes - true. >> =20 >>> Another way around will be to parse and separate out the queries and = run each one separately.=20 >>=20 >> I'm not sure that would work well - you'd lose the ability to control = transactions as you might expect, which could lead to even worse user = confusion and potential for errors. >>=20 >> I wonder if we should simply limit the amount of memory we're willing = to use for any given resultset. If we reach the limit, we return the = data we have for display in the result grid and highlight to the user = that the data has been truncated and that if they want to see it all = they should run the query on it's own. +1 We can provide a configurable option to memory limit check; if the limit = is exceeded, the result is truncated and the data is returned. > That would not be a good user experience. I would rather leave it to = the python process to handle memory, but follow best practices. If = memory runs out - we'll show the error on the query tool and the user = will understand what to do next. > Users have the option of a server cursor if they have memory issues = because of very large data sets. >> =20 >> --=20 >> Dave Page >> pgAdmin: https://www.pgadmin.org >> PostgreSQL: https://www.postgresql.org >> pgEdge: https://www.pgedge.com >>=20 >=20 >=20 >=20 > --=20 > Thanks, > Aditya Toshniwal > pgAdmin Hacker | Sr. Staff SDE II | enterprisedb.com = > "Don't Complain about Heat, Plant a TREE" --Apple-Mail=_E5E81869-9EE7-48AD-9410-99BFE5978802 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On 25 Sep 2025, at 5:38=E2=80=AFPM, Aditya Toshniwal = <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Thu, Sep 25, 2025 at 4:31=E2=80=AFPM Dave Page = <dpage@pgadmin.org> = wrote:
Hi

On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal = <aditya.toshniwal@enterprisedb.com> = wrote:
Hi Dave,

On Thu, Sep 25, 2025 at 3:29=E2=80=AFPM = Dave Page <dpage@pgadmin.org> wrote:
Hi

On Wed, 24 Sept 2025 at 13:43, Aditya Toshniwal = <aditya.toshniwal@enterprisedb.com> = wrote:
Hi = Dave/Hackers,

I'm working on a feature where the query tool will show = separate data output for all the select statements run in a single = batch. psycopg does provide the result sets (as libpq provides) but = there is a catch. Let me explain how pgAdmin currently works:
1. psycopg provides a cursor = object on query execution.
2. The cursor object has a function called nextset which = can be used to move to the next result set of queries = executed.
3. Once = you move to the nextset, you cannot get data for the previous set. It = will only point to the current set.
4. Right now, we keep on looping through nextset = until it reaches the last set and then fetch the data from the last = set (using pagination).
5. The fetched result is stored in client memory (python = process)

So if we need to show the output of all the queries, we'll = have to fetch the result for each query and store it in python = memory before moving to the next set.
psycopg already stores the data on the client = side, the only difference will be that we'll store all sets and not just = the last one.

That = seems like it's potentially problematic with large result sets, and = workarounds would likely lead to potentially confusing behaviour for end = users (which I really don't like the sound = of).
 

If any one has any suggestions on memory management then = please let me know.
Otherwise, I'm proceeding with what is discussed = above.

I don't have = any suggestions regarding memory management here, but I do wonder if = this is something which warrants an enhancement to psycopg, to allow = random access to the result sets. At a quick glance, it looks like = BaseCursor._results is a simple list of PGresult objects, which could be = easily exposed of course. What I haven't checked is whether any = witchcraft happens that would make random access to those objects = problematic.
No = we cannot move to the next result set, until you close the previous = one even with = libpq.

Hmm, yes = - true.
 
Another way around will be to parse and separate out the = queries and run each one = separately. 

I'm not sure that would work well - you'd lose the ability to control = transactions as you might expect, which could lead to even worse user = confusion and potential for errors.

I wonder if = we should simply limit the amount of memory we're willing to use for any = given resultset. If we reach the limit, we return the data we have for = display in the result grid and highlight to the user that the data has = been truncated and that if they want to see it all they should run the = query on it's = own.
+1=

We can provide a configurable option to memory limit = check; if the limit is exceeded, the result is truncated and the data is = returned.

That = would not be a good user experience. I would rather leave it to the = python process to handle memory, but follow best practices. If memory = runs out - we'll show the error on the query tool and the user will = understand what to do next.
Users have the option of a = server cursor if they have memory issues because of very large data = sets.
 
-- 


-- 
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Sr. Staff SDE II | enterprisedb.com
"Don't = Complain about Heat, Plant a = TREE"
<= /div>

= --Apple-Mail=_E5E81869-9EE7-48AD-9410-99BFE5978802--