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 1v1kmX-00CKmA-HE for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:09:05 +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 1v1kmV-002FzM-Vk for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 12:09:03 +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 1v1kmV-002FzE-Fx for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 12:09:03 +0000 Received: from mail-ua1-x92d.google.com ([2607:f8b0:4864:20::92d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1kmR-002MTp-31 for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 12:09:01 +0000 Received: by mail-ua1-x92d.google.com with SMTP id a1e0cc1a2514c-8e30a19da78so747288241.2 for ; Thu, 25 Sep 2025 05:08:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1758802138; x=1759406938; 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=4QlIvyiZymKqAq5mfc2v00fisY0CE3CIORjUxHMvC8I=; b=akVL7IoJtKzQHU9tTmNJBQ7qESTd2OlCocsPfHStuIX+rM2AthBllKXbMEU7vp6rpT 9b1b0mtTbe3j0DwmooS4G0pHOEo5Z9hzfyH4uM8QyjjmGQr3QSpCMn3zUKCm44A/iFDz 27znJ61F4AgDIwHbKpl5A36653+MfhpvJabWnLs15k/lVvz3QiOpY9/Pmlpn3ekdbBzE csegsnjKiW5FBtYFED5Beeg9u+9DvnMs2pckX2jh1KmjEs9uraanXPkO+aVuXwckM/No wqAmvbv1mxNA/FO8fMzzzsQqfyB2MWVN8m+2rIajEuoRYcRarG30pAPolJZMpj3Glu9o zUng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758802138; x=1759406938; 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=4QlIvyiZymKqAq5mfc2v00fisY0CE3CIORjUxHMvC8I=; b=XJVXaxdT0I58sG9ptTwBqwbWPEWEQi/1JCe4YkfQ+lt7TvG2p5Z51TzkTPlW9ce0y4 9VMGC4hFi0q0b43hCtHpS2B+pmkmCEeZah3qoRjCxgl0S9g9r2nOwfD8c3Gv6hlqCG6o H8tZsmRLWDhgimZXO7oWR0w5HfcXTujkZfQNEwJtGx1U5a5VxrABzq3jLnVdajXa5Frx AQWrcGeiV1d2MG4+Bw7c0rEwrSIytEF9TmP+Wrra3Md+gPtzWWXLoXk3CCe4+rOcr/FT 1NGqOOwtfzIj4FvzzEUfmXDqHkhhYLdcwiMMnDOLAAGM2b2kJy/cFqp0FigzgjxkjZ7/ JTCQ== X-Gm-Message-State: AOJu0YwZc8zfyquo6iXGlQlZd2cStSTPP1k+cZRYZQh9SlTPw+T1zdAD boCtbbSNDkPUwvEFtiy+R7k1lMC1A8V3CvfzRGYxkH04+zU6gSgfBGV1pAnhDJxCVUekT9zdHVF fZMjPW0RFKyXu/VS3fS9Hu1nkLidD5OUSbcPlDyvF X-Gm-Gg: ASbGncvUdU3PtZE8Pk3b7wmQap3uY8TifA0kDndlwj4d7P2+udRNQRiIc/60rYuMq4N yNMG13NzT8WUM7rwRd82wNP92U3Si4RdeTW1rnCueEIkXAdWYIKKYcs8ROlPc0BUcINOGHG3MDS UAKY4XeSlA5LDte/0rtR4xe9hHQ8Og+pzLmFkP/3B6fKZ0ell9UakjJ4bH4GlvzvTqduPTqmdE/ u1Izujh6g== X-Google-Smtp-Source: AGHT+IGcR2GkqKTniLpZw+Yj5mQYg/TCq/W3oMHjxJjOhmUpYib97mSNVSmhcJyi1B7creLcvtEVzXzyj/l1ZjbsLfs= X-Received: by 2002:a05:6122:17a9:b0:54b:bf4d:558b with SMTP id 71dfb90a1353d-54bea1d8178mr1416254e0c.10.1758802138384; Thu, 25 Sep 2025 05:08:58 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Thu, 25 Sep 2025 17:38:30 +0530 X-Gm-Features: AS18NWCDf4DIJBQKmBWRM1-Cj2a5lgGg1u2Qb4SdNsYJ57my-6AzXDpcNTlUGXw Message-ID: Subject: Re: Regarding multiple result set in query tool To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000b05bbb063f9f08b2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b05bbb063f9f08b2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Dave, On Thu, Sep 25, 2025 at 4:31=E2=80=AFPM Dave Page 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 wr= ote: >> >>> 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 do= es >>>> 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 t= o >>>> 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 m= oving >>>> to the next set. >>>> psycopg already stores the data on the client side, the only differenc= e >>>> 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, an= d >>> workarounds would likely lead to potentially confusing behaviour for en= d >>> 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, t= o >>> allow random access to the result sets. At a quick glance, it looks lik= e >>> BaseCursor._results is a simple list of PGresult objects, which could b= e >>> easily exposed of course. What I haven't checked is whether any witchcr= aft >>> 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 da= ta > has been truncated and that if they want to see it all they should run th= e > 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 understand what to do next. Users have the option of a server cursor if they have memory issues because of very large data sets. > > -- > 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" --000000000000b05bbb063f9f08b2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Dave,

On Th= u, 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@enterpri= sedb.com> wrote:
Hi Dave/Hackers,

I'm workin= g 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 resul= t sets (as libpq provides) but there is a catch. Let me explain how pgAdmin= currently works:
1. psy= copg provides a cursor object on query execution.
2. The cursor object has a function called nextse= t which can be used to move to the next result set of queries executed.
3. Once you move to the nex= tset, you cannot get data for the previous set. It will only point to the c= urrent set.
4. Right 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 in clie= nt memory (python process)

So if we need to= show the output of all the queries, we'll have to fetch=C2=A0the resul= t for each query and store it in python memory before moving to the next se= t.
psycopg already store= s the data on the client side, the only difference will be that we'll s= tore 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 be= haviour 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 abo= ve.

I don't have any = suggestions regarding memory management here, but I do wonder if this is so= mething 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 mak= e random access to those objects problematic.
No we cannot move to the next result set, until=C2=A0you close the previo= us one even with libpq.

Hmm, yes - true.
=C2=A0
Another wa= y around will be to parse and separate out the queries and run each one sep= arately.=C2=A0

I&#= 39;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 confu= sion and potential for errors.

I wonder if we shou= ld 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 i= n the result grid and highlight to the user that the data has been truncate= d 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 exper= ience. 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.
= User= s have the option of a server cursor if they have memory issues because of = very large data sets.


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