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 1v1jTn-00C3cR-N5 for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 10:45:39 +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 1v1jTm-001vyY-EE for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 10:45:38 +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 1v1jTm-001vqI-25 for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 10:45:38 +0000 Received: from mail-vs1-xe2e.google.com ([2607:f8b0:4864:20::e2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1jTj-002LlG-1V for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 10:45:37 +0000 Received: by mail-vs1-xe2e.google.com with SMTP id ada2fe7eead31-5a7b2a6b13bso750304137.3 for ; Thu, 25 Sep 2025 03:45:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1758797135; x=1759401935; 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=fjbTdAseViyNdzF+igBrtzvi+ZU69RDWg3lkgWJu3+w=; b=mDE63hJUKsIy9xeK7+27blHzul50ZH/WAVC5hf6wIfxSUyClI+Cz1K6jC62B1tp1fx qmjRqz3mWA58IbmpUPQs0wmvcpR4EcYmxZB5E7eXNi66X9s9tHl2wiTs/T5pr9FZuet9 qz+PDDEEXdRM1MgNqIq0yLED0QMEWEozdkPeTp0bkDP0po52k8sen9dhwQKh52tyI4Tz Eiu/IyV7NWveWbALhKly6R0VuP8Vp/wgwCN7cw6hQ38Qh5ib7e6IntqF9eXoQThR63fI YbOsNznLM5c+8vrk0QZbJddt4TMVq0d03YFZQZoT+HQ73iRTTU+dzjsGIKo6tEjbb1dR lLsA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758797135; x=1759401935; 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=fjbTdAseViyNdzF+igBrtzvi+ZU69RDWg3lkgWJu3+w=; b=nPwl7EYDrTP9EjrBIHgHE362MM4VGO3MAvk3UkAe+R0QIn2tP5rlV1tNf5JpW6hj1i j+zWIt4m6O7iswYJVGbuWGFfcRIyYniM163jagvd22tj6MPsgABu+Vz8c1iOfW+UAuOe jzM5EfmSZVm9xyKIt+36VSlSPvxxjNpej5/rQt1VwcqMBnFO62JUesNtM4jrnmgb5RaZ ZIkO/JBd6Iddo+3MNNJnRosm/DAfGZM6Ok97QwZt36jWUIGuGprH9jghUvNSidxuv5zu UqVFnuyiBbt1OUtfgGjzC7lZs42JfXXff46uY+jl+qt65OnOWgV08y966d8stl/3ta1a aF3Q== X-Gm-Message-State: AOJu0Yy4/boEiT2q6oSEOJTyPDncu9jzq9YD6nEQc6BQ+5dxyElLZwFm jAE9wU0GKrPp0JXXO8SR9OlJVdgOv7S07nLXWtUc034u1epGhLpZG6tH7BnwvmbStkCz762qUHl JhXzpOIXm0zxkkKwf7xFwPmZ8sUkefj4IfmKmVsG9 X-Gm-Gg: ASbGncuzhh2IjA9g55ivvVXbrLSqg8Bs3vbj5x/1U0fs4X4u5HshCvHCjZ+Ljhp1Sxv cOVJd1L1OXza98jhDeWzOCngIVVVbqBYjpvglzEG/Yf4WB3H4htc42n7wTIBYM5UgBnQcxvv4Qn lyPn5NBWWY31y2zg+HNaWQISx15dKLjMbBuHCfzB5w+/138ecj9RF8MjhNhMqR1ISjfCEV2oGcf TtpeipiPg== X-Google-Smtp-Source: AGHT+IEPMe6ugyYEqLM2IllJ7I2wyyaXQZWflTRGg0mCHSoigtro2XtJwA8x64gMHHW04OPjkP9sD6UMVfqiuPkw4Cw= X-Received: by 2002:a05:6102:390d:b0:523:e010:df0f with SMTP id ada2fe7eead31-5acb88f5cbamr1415257137.0.1758797134516; Thu, 25 Sep 2025 03:45:34 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Thu, 25 Sep 2025 16:15:06 +0530 X-Gm-Features: AS18NWBbshyB9CQglzOUEjaw4r2tCzTtReV7wbbu6d1E-cqXca73AhHwQcHGmzs Message-ID: Subject: Re: Regarding multiple result set in query tool To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000006f7e57063f9ddeab" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006f7e57063f9ddeab Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 mov= ing >> 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 witchcraf= t > 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. Another way around will be to parse and separate out the queries and run each one separately. > > -- > 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" --0000000000006f7e57063f9ddeab Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave,

On Thu, Se= p 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.
Another way around will be to parse and separate out the queries and ru= n each one separately.=C2=A0

=
--


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