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 1v1jjB-00C6qc-A3 for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 11:01:33 +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 1v1jj9-0022As-Kz for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 11:01:31 +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 1v1jj9-0022Ak-9r for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 11:01:31 +0000 Received: from mail-lf1-x12c.google.com ([2a00:1450:4864:20::12c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1jj6-002LtO-05 for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 11:01:29 +0000 Received: by mail-lf1-x12c.google.com with SMTP id 2adb3069b0e04-57b35e176dbso974757e87.1 for ; Thu, 25 Sep 2025 04:01:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1758798087; x=1759402887; 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=PkjrNaoaWtPMpEHSOTkuF4Sg6Yx2zypuWmZ65V19LLc=; b=LX0rzZRjk0KEFWiqzoGRDJuxUzR9Q5fMJcZhjbF1AQIMaQTdzzt+iYiXMxf9DUIs9+ zEPdft9wXeLcNi+Bhs6ynXnveWVnHNEM728bUYG9kE7xM69+O30VSn3mgEFPYJKbJ7Pi Bee/b5IsAqtPGv2c9j75xfzUhRQXtFhmwrQOw2xNppZqFR9eFWST5qwDEFyDsflAoNir ee+Rybq+wl6pI7/Ty+v6qmBkTULF4WT5sb9q27YvtuCUuk3gCKvD3DI2ritMhsU5AApv PNAw8FGAHKpQBEIEX/V/yrKrib0z5HZOFcKVkVISVhhnqBUz76IIUm8FLeHGndSZfRRN Ht6Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758798087; x=1759402887; 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=PkjrNaoaWtPMpEHSOTkuF4Sg6Yx2zypuWmZ65V19LLc=; b=COAMZfS1DUpYp1Tto4ArilMOSyJ+Vug6mXCpwNEtrc2zbsWGY6to6yzxqRB6XDqFG/ icSJd+7KHRsPYyrkUs9bBlqzCsdHlfO7BerrMujNIkzRX6tptF1TgHRrnUzeyTveQBAK DHEsKyW60lxQ7tFAXpqmM2sZWrdk3tb6AuWlDdJGiY2Lntg4dUnLhnZdxg1BveQqfuDh BueFVwe76ZfFTCxVoqnsv58Js9L/6X53yqgRIkASaQLiravyhVbX7yJhwTJncuEvskkM s39OUIN4jLnondGQpD3oYzSwoeE80jVYP1KP3VUQfNmlV1uFQ95aPtARuyqvImfwaJXV Fs1w== X-Gm-Message-State: AOJu0YwiKvcdRcrQS3VccjaQGCCzHGO2/NKzDniG2w18e8he5V1Uln/x vy7Xf/DgCum7pYb/3X0U5+NU4bPWiCxx/nz1HbcE6pMD1yaNYfO/deYF6vHyF49LdawqZzvrxUs kAI0zouTnnU95p/ds0WzAnqUc2FRhSt2j0ehVHM0G X-Gm-Gg: ASbGncvixbwF6Qe4z/4vfHd2hAcKEpRl98XJSo5wUJrM1jD2K+oyb5CBzuj4zbZPjZW qF/vl6BpHOjnPA8BAjDmBWVmToK5Fl6/YkTe5wNFIFEu4kHVK5sCKg9evUVrg4I3CUpuP13qYaX /btSna7fKtFrxhyc7akJFmHlwy6Ri3Azu107k19jgVDzIHMYWyHSsUKTmm+RhFQltqUzOhB+CNQ 1LRlMHObQ== X-Google-Smtp-Source: AGHT+IFfeBmAkRpPKBuGjPQ5Sjpj69NpvlazKiIY6HxvIIUQsbQy3X1lMtk1X3Rdj7XH5hGfEVcHe1vL1jT1HAJTdEM= X-Received: by 2002:a05:6512:ea9:b0:57d:d62e:b212 with SMTP id 2adb3069b0e04-582d3e825bfmr828286e87.38.1758798086515; Thu, 25 Sep 2025 04:01:26 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Thu, 25 Sep 2025 12:01:14 +0100 X-Gm-Features: AS18NWBJQIaOsuTCFzTU_mzOZUOBQqYKcNeFKOLd1re6U32YN8YUrUzv2mL7ofI Message-ID: Subject: Re: Regarding multiple result set in query tool To: Aditya Toshniwal Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000002dc305063f9e17a9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002dc305063f9e17a9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wro= te: > >> 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 doe= s >>> provide the result sets (as libpq provides) but there is a catch. Let m= e >>> 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 mo= ving >>> 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 witchcra= ft >> happens that would make random access to those objects problematic. >> > No we cannot move to the next result set, until you close the previous on= e > 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. --=20 Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com --0000000000002dc305063f9e17a9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 <<= a href=3D"mailto:dpage@pgadmin.org" target=3D"_blank">dpage@pgadmin.org= > wrote:
Hi

On Wed, 24 Se= pt 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 se= parate data output for all the select statements run in a single batch. psy= copg 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 h= as a function called nextset which can be used to move to the next result s= et 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=C2=A0= it reaches the last set and then fetch the data from the last set (using pa= gination).
5. The fetche= d result is stored in client memory (python process)

So if we need to show the output of all the queries, we'll ha= ve to fetch=C2=A0the result for each query and store it in python memory be= fore moving 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 potentia= lly problematic with large result sets, and workarounds would likely lead t= o potentially confusing behaviour for end users (which I really don't l= ike the sound of).
=C2=A0
=

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

I don't have any suggestio= ns regarding memory management here, but I do wonder if this is something w= hich warrants an enhancement to psycopg, to allow random access to the resu= lt sets. At a quick glance, it looks like BaseCursor._results is a simple l= ist of PGresult objects, which could be easily exposed of course. What I ha= ven't checked is whether any witchcraft happens that would make random = access to those objects problematic.
No we c= annot move to the next result set, until=C2=A0you close the previous one ev= en with libpq.

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

I'm not sure that would work well - you&#= 39;d lose the ability to control transactions as you might expect, which co= uld lead to even worse user confusion and potential for errors.
<= br>
I wonder if we should simply limit the amount of memory we= 9;re willing to use for any given resultset. If we reach the limit, we retu= rn the data we have for display in the result grid and highlight to the use= r that the data has been truncated and that if they want to see it all they= should run the query on it's own.
=C2=A0
--
--0000000000002dc305063f9e17a9--