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 1v1mGt-00CZJz-UJ for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 13:44:32 +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 1v1mGs-002v8w-M9 for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 13:44:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v1mGs-002v8o-6V for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 13:44:30 +0000 Received: from mail-vk1-xa2b.google.com ([2607:f8b0:4864:20::a2b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1mGp-002NE4-0P for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 13:44:28 +0000 Received: by mail-vk1-xa2b.google.com with SMTP id 71dfb90a1353d-54bc08ef45dso557467e0c.1 for ; Thu, 25 Sep 2025 06:44:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1758807866; x=1759412666; 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=GOAcAWb3ta8lFWhU3GF36tKfRct/NLd3mj3pCw6RT0M=; b=EwyZNZXhUT4WuLKrZJ9XqV+w5z2VKiEjwBiHU3JpKPzcFmhfp24Iq4tCwMtyrVyCaN Z1b87FSXPFBS8/rnkMXDzZRlnLO9j3H9rDD5B4kp/yixd/rnpGTvKJiODXhRaB+eIMsA 2Yw9RjwXq0ZyFu8wO/GJhcDQugQJMK2F5p5YJBjhJ69IF62ZtjVuYmhVoLqiuuL1+9yJ UEEIOddNXrJgKBemjV27dAps6iZf3d8id6JPD91wEvG9lV1ZEpUPqM0mbd9Em59AcJxt DYDNsaL8MnDPMnQwuhVZNCPnt8xHwthdgA2ZXTsbLFxyT7lk02YImdV85INxI8y9Tw+e 5+VQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758807866; x=1759412666; 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=GOAcAWb3ta8lFWhU3GF36tKfRct/NLd3mj3pCw6RT0M=; b=ZwtuSwSijsJJNLiqfldM2cLMy4ulO/nepAr5c3RBvQRBk8kLVNQXaCM3LuTNyTz01K GixHc9cGgOMnZIDNCx2Gh/Z3o0aMyqtlKZH48NL+koQC22yeukleFA9JY3CHff6y9hVU YKo7QgkSQqLu42XXEyikRN6xuUA71H9JYBYgoW1ZGoKZOXMIpleWhXBlMaUoVXo2meT8 d+uFvJwk09BO7HnOQcAj7VshOUFFEDGrU8V/aPp/yXjaG9xf/LpEyySCAkGdfDySnoEG YFBa4hzGaOxvyXf2oaO3abdqDrT/V7WZP8AWWKtIovF6zWaGmHjDIobvPqPrEALJPxp5 uWFA== X-Gm-Message-State: AOJu0Yz4nXkQDflIHdEKZcdx3AvFMAa2vdDyW4WqTDqz3hNzo3Rr1+h/ 1kmKjHOA79TAErCYqvQrnnaxGbAQCRRvQjj2cBipVOAjbzjyXx782XWsihHQfdMeVhvMPf40kmI NSIUotVcVD/YvFXs6hEBnbrJvvaa8yXOumcfi74xaTJcradNMrvE= X-Gm-Gg: ASbGnct35Pj7N7+gIwVzVaCviZQaKIOmDPZFA3CoWUSPIbEuLvF6BpG1zPyILZPNas8 dMZ6m5318Ep5n0SyXwtE12xjYqnq+eB98GJgzUTpeH2oFgAvyL6c/FiaJJsK+0s7m2DYaiGbYC8 RUnU0NOKDTCxWwmgyELeIIEDOqM1WFJv3JdAdLkVaOAn9QkrEMN/+ceJtAEcklD+zKifl8pPV4Q 4ps5lU8FOyEvybmrCO5 X-Google-Smtp-Source: AGHT+IGeHuurGEjk8pa5evbOhc9ogu4FWd66EcUoJx9+R/wnReVmdASseodzD2Zt7z36w+w2NVCy3cQTasuOtyFxcL8= X-Received: by 2002:a05:6122:180e:b0:544:71fb:f49b with SMTP id 71dfb90a1353d-54bea30ed99mr1445113e0c.10.1758807866376; Thu, 25 Sep 2025 06:44:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Thu, 25 Sep 2025 19:13:58 +0530 X-Gm-Features: AS18NWCRP0WvSaMLrh1Ss7naT2IdLANgDXMs0ksvxolsBO7af4CwR9Ti9QSslds Message-ID: Subject: Re: Regarding multiple result set in query tool To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000001a9812063fa05e20" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001a9812063fa05e20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Dave, On Thu, Sep 25, 2025 at 6:06=E2=80=AFPM Dave Page wrote= : > > > 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 wr= ote: >> >>> 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 = 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 dat= a >>>>>> 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. Le= t 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 previou= s >>>>>> set. It will only point to the current set. >>>>>> 4. Right now, we keep on looping through nextset until it reaches th= e >>>>>> 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 o= ne. >>>>>> >>>>> >>>>> 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 psyco= pg, to >>>>> allow random access to the result sets. At a quick glance, it looks l= ike >>>>> BaseCursor._results is a simple list of PGresult objects, which could= be >>>>> easily exposed of course. What I haven't checked is whether any witch= craft >>>>> 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 t= o >>> 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. >>> >> 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 ru= ns >> out - we'll show the error on the query tool and the user will understan= d >> 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 i= f > one user exhausts memory. > How about allowing multi result sets only for desktop app? The problem with memory limits is - it's an extra overhead to keep checking how much memory is consumed. A row size will depend on the number of columns and data. If we have a predefined algorithm which will decide the limits in a performant way is desirable. > > -- > Dave Page > pgAdmin: https://www.pgadmin.org > PostgreSQL: https://www.postgresql.org > pgEdge: https://www.pgedge.com > > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --0000000000001a9812063fa05e20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Dave,

On Th= u, Sep 25, 2025 at 6:06=E2=80=AFPM Dave Page <dpage@pgadmin.org> wrote:

On Thu, 2= 5 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@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:4= 3, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
=
Hi Dave/Hackers,
<= div>

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. T= he 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 n= extset until=C2=A0it 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 que= ries, we'll have to fetch=C2=A0the result for each query and store it i= n 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 w= ould likely lead to potentially confusing 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&#= 39;m proceeding with what is discussed above.

I don't have any suggestions regarding memory mana= gement here, but I do wonder if this is something which warrants an enhance= ment to psycopg, to allow random access to the result sets. At a quick glan= ce, 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 whe= ther any witchcraft happens that would make random access to those objects = problematic.
No we cannot move to the next r= esult set, until=C2=A0you close the previous one even with libpq.

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

I'm not sure that would work wel= l - 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 mem= ory 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 t= o 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.
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 und= erstand what to do next.
Users have the option of a server cur= sor 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.
How about allowing multi result sets only for= desktop app?
The problem with memory limits is - it's an = extra overhead to keep checking how much memory is consumed. A row size wil= l depend on the number of columns and data. If we have a predefined algorit= hm which will=C2=A0decide the limits in a performant way is desirable.


--
Thanks,
Aditya Toshniw= al
pgAdmin Hacker=C2=A0| Sr. Staff SDE II=C2= =A0| enterprisedb.com
"Don't Complain about Heat, Plant a TREE"
--0000000000001a9812063fa05e20--