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 1v1lGK-00CQCA-Cm for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:39:52 +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 1v1lGJ-002LHn-2Q for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:39:51 +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 1v1lDF-002Iv9-DB for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 12:36:41 +0000 Received: from mail-lj1-x230.google.com ([2a00:1450:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1lDA-0001AU-2a for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 12:36:40 +0000 Received: by mail-lj1-x230.google.com with SMTP id 38308e7fff4ca-367874aeeacso8307621fa.1 for ; Thu, 25 Sep 2025 05:36:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1758803796; x=1759408596; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Vjdb185sqiau6pHdERScmGPjGf8kOqURxK+j9FY6E78=; b=Wqr1eIP1/0vDvzU/qvlpkmbtzwoH1lwtqf2zXh8t3PqEdo6SGmgFbpd+ZHQmQMq1nw SroXxxhvSDwzK6FsICza5quBAZMPY4620hh4Kw7dRdiDNYyaqsCd9/+mWhxTmzVqweMH SKf37gz+mE1JQIT3jmtC27bHt2d+ZNiOWLeDM/dFTs9eqd2akt5bGAiIgcmHzVlpSKg2 9NwtPnM8tgN0DXT+SSne7CKrA9MFpWxdEL6QcAbiNubDULmZf/GIU26/xeONnVRwuv1N KXFlNTbf8uf+AmS1xEwJreYZQWzsmmYmplZSKoAFqH9ydMKG6bP1OAz+fneYr7cU8VZ8 k68Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758803796; x=1759408596; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=Vjdb185sqiau6pHdERScmGPjGf8kOqURxK+j9FY6E78=; b=fVZjE3zs4SMydlr9ihREtSY0e/L2VC3BNTOZloXct+rvGK974SOtsNHVXnasD1oH/R TadW3yVwDOCD2OT07eJrQgSHiQB3zn1uIwTf40ccLnB0vwTX5fjXjrSXx1JeL2zvoupa 0jEuKY85IduO3LCPmRS2Mehg5RUPYCQpbV3cVVelEhx1n2EFVsi6on9I618f6okV0GX0 0Rx/ut3ODhvy6D3HQjQbz02qV4Y/xN34CINqNJF7wEzdDL6FcJejBW5aIuXYJ6pLKts3 aiZzRXta1ISePWEM6Tp8B1dODgj+W1x4lpqTXdYsF7qYV685eyf9hyHZCWMZvpkWjVbm IvPw== X-Gm-Message-State: AOJu0YxqnNzlFVzNsjAfjdR2DaagIIbd0BQ3rGmgF6vQ99RcaVSVo3QA +DT0dnVDzquP5BhEiewhh5SrtKbC1v8aA+UnwbYWh+OWJmYE6Vq4sC/fTlqOJBGPwrfiy4McFZl PC1GmtVSCJB7iAVG+8wlxnS9rUAeSSbOI5rmVahWt X-Gm-Gg: ASbGncu01RZfcBzF7GvS2VdI4QedYzQVOr6eY/XpFVcKA64QU+yhRXiEulJhYt5H4An 8/FlcY3R1sPa3zJWIEhwuI+dim19TMZvg/0GD/4kCviIpOfi4sqTjpRZ5vV6tPvS4eK34YPjdjt RHUaAxkMBhr65YSr2mTL0hbShrRnjIzFT8/a3GvOJho1ImI2XklfQp3SnIzTu4kAj1XHw23obiI XSIWWxN3A== X-Google-Smtp-Source: AGHT+IEwig+nKFgHfzvXJDSBl4ZkiMe0b9WneHui782qmO9YBtOSAAds5MPluAtQJTghgUgz3y51bW6PB56xqNMqUPg= X-Received: by 2002:a05:651c:981:b0:36b:f63:2785 with SMTP id 38308e7fff4ca-36f7f2485d7mr11939231fa.27.1758803795627; Thu, 25 Sep 2025 05:36:35 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Thu, 25 Sep 2025 13:36:24 +0100 X-Gm-Features: AS18NWADrbG1AYVI6aDY64HAUxSk0QNFLdlI8oFdY32RCOBgTJ-i547ezzQpLCw Message-ID: Subject: Re: Regarding multiple result set in query tool To: Aditya Toshniwal Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="00000000000077e35f063f9f6b85" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000077e35f063f9f6b85 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, 25 Sept 2025 at 13:08, Aditya Toshniwal < aditya.toshniwal@enterprisedb.com> wrote: > Hi Dave, > > On Thu, Sep 25, 2025 at 4:31=E2=80=AFPM Dave Page wro= te: > >> 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 w= rote: >>> >>>> 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 d= oes >>>>> 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 on= e. >>>>> >>>> >>>> That seems like it's potentially problematic with large result sets, >>>> and workarounds would likely lead to potentially confusing behaviour f= or >>>> end users (which I really don't like the sound of). >>>> >>>> >>>>> >>>>> If any one has any suggestions on memory management then please let m= e >>>>> know. >>>>> Otherwise, I'm proceeding with what is discussed above. >>>>> >>>> >>>> I don't have any suggestions regarding memory management here, but I d= o >>>> 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 li= ke >>>> BaseCursor._results is a simple list of PGresult objects, which could = be >>>> easily exposed of course. What I haven't checked is whether any witchc= raft >>>> 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 ru= n >>> 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 w= e >> have for display in the result grid and highlight to the user that the d= ata >> has been truncated and that if they want to see it all they should run t= he >> query on it's own. >> > 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 run= s > 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. > If pgAdmin were a single-user application, I'd agree - however it is not when running in server mode. Other users will not know what is going on if one user exhausts memory. --=20 Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com --00000000000077e35f063f9f6b85 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, 25 Sept= 2025 at 13:08, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi=C2=A0Dave,

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@en= terprisedb.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, Ad= itya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi Dave/Hackers,

I'm= working on a feature where the query tool will show separate data output f= or all the select statements run in a single batch. psycopg does provide th= e 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 execut= ed.
3. Once you move to = the nextset, you cannot get data for the previous set. It will only point t= o the current set.
4. Ri= ght now, we keep on looping through nextset until=C2=A0it reaches the last = set and then fetch the data from the last set (using pagination).
5. The fetched result is stored i= n client memory (python process)

So if we n= eed to show the output of all the queries, we'll have to fetch=C2=A0the= result for each query and store it in python memory before moving to the n= ext set.
psycopg already= stores the data on the client side, the only difference will be that we= 9;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 confus= ing behaviour for end users (which I really don't like the sound of).
=C2=A0

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 enh= ancement 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 obje= cts, which could be easily exposed of course. What I haven't checked is= whether any witchcraft happens that would make random access to those obje= cts problematic.
No we cannot move to the ne= xt result set, until=C2=A0you close the previous one even with libpq.

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

<= /div>
I'm not sure that would work well - you'd lose the abilit= y to control transactions as you might expect, which could lead to even wor= se user confusion and potential for errors.

I wond= er 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 quer= y on it's own.
That would not be a go= od 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 issue= s because of very large data sets.

If pgAdmin were a single-user application, I'd agree -= however it is not when running in server mode. Other users will not know w= hat is going on if one user exhausts memory.
=C2=A0
--
pgEdge: https://www.pgedge.com

<= /div>
--00000000000077e35f063f9f6b85--