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 1v1ikv-00BpDS-IF for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 09:59:18 +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 1v1iku-001hIQ-AG for pgadmin-hackers@arkaria.postgresql.org; Thu, 25 Sep 2025 09:59:16 +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 1v1ikt-001hII-P3 for pgadmin-hackers@lists.postgresql.org; Thu, 25 Sep 2025 09:59:16 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v1ikq-002LPg-1h for pgadmin-hackers@postgresql.org; Thu, 25 Sep 2025 09:59:14 +0000 Received: by mail-lf1-x131.google.com with SMTP id 2adb3069b0e04-57dfd0b6cd7so854224e87.0 for ; Thu, 25 Sep 2025 02:59:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1758794346; x=1759399146; 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=TZ/y9OItYSyomTlEYFrO9X7oFN/DSIrqj1AT5mgtE/4=; b=VId0vNzIR7fPKCKr9SlR4KBDu2O5hLXJXkcuJBunUcXGfeFg5C9bDI+cQZTXfrEGI7 3rn7jrvNH0rTIgivORbUOz5jgqFGnKIAanPQqPfw1h+Y21Wk9GMR3iMVsKC0ZAaEeq/7 TRZ8aMP5nSNMO6hLrJL6cENmIEvA8zXTTdUB+wkZZM/zA8qTu5UGQbPLPLVX5ltmaD5/ aY4E9AWd7fxHNv9N2pZcg2SN5q80n8/XTYuaUEaGTWo6+RWCb/0umBpI1BRKVxy+cn18 EKOGHfe6hf96dehw00j+ZyyJ5DqSkd01ltTo3BPknHf4U3BvOOAyf4LCQ0ImXz2pgWig e0kw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758794346; x=1759399146; 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=TZ/y9OItYSyomTlEYFrO9X7oFN/DSIrqj1AT5mgtE/4=; b=csJCM+1BSzxi5seeNxcTdEq75XfeKD+gMgHl6LoC3q2qbGBQJKg42LV7oc1zsKrzea HEtIs/cw1qbxQiSHSoNWEyuZM734dCjELinTYRXuMl8s8x8QO9EG21fLuNtvXJwJStWi 4vINEs3JBwreznG7NwBgcgm+LA88h9aZW4FJD7dp5/GtjhI6MslU4OzXR4DanZ4Cc1QG 0o9M7fIrikj940fb/rTr3OeGRd9f4SjKlgPCWTYgmEauCnfFlZdBzI1pfB7rswR6yqzO GptJ6DP5vYVnxVe8qzNHblPcDHS2h2I9QmkhDJeEWPuihwSIpGGFpZFq4kY49KV5KF4n Akww== X-Gm-Message-State: AOJu0YxLL4m2sasnVAELsH+Kvpm4ErS85cc+STqwULY4c+mRADcRFvPM ThuFO8RBHsbYhqI8Hx2ToGtKglddiPRFsxOvrnHz2c/Bypax9bZ0xw3jZFjjAtA9AEewvMwgrKE af0g5brnywBzcyR1vY4/q7nlXn27Bkz5hM5DtyrVm X-Gm-Gg: ASbGncus3sEerwo3JzD8QCGKsnUBEocKXSNnksKuFGzKLaNyg8KBjV+j3vZvF9Girm5 wC2o/0vhXAc+VUHEYwfbTnZl2496hhyTaLeLmJ/6+vL/2CCvRJ9xj48aNG1+4Xqb9jX/LBVAf0S llfZIi+ZK+RxNrST93nCoSLsupysbPzLv0pJrF/BPUN+cx6qyJHU8smHIOathMmDi4ej3RlwofY uevQvXjOQ== X-Google-Smtp-Source: AGHT+IFVRlHUd13TYaV/Q15d1glqpZDV/PMdVh78BnKnZT++2kchNN8Eiy131PdZTp0RSNEhqxAvpHqCfD7e4aBhYEo= X-Received: by 2002:a05:6512:ad6:b0:57d:6a7c:a0b1 with SMTP id 2adb3069b0e04-582d4443599mr733203e87.51.1758794345698; Thu, 25 Sep 2025 02:59:05 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Thu, 25 Sep 2025 10:58:54 +0100 X-Gm-Features: AS18NWBeKa-o2rbIh7m2CWaAyQG8cWsN2JZCdhXNQI61INoytTiE3fQVuaYB5-k Message-ID: Subject: Re: Regarding multiple result set in query tool To: Aditya Toshniwal Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="00000000000035976c063f9d386e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000035976c063f9d386e Content-Type: text/plain; charset="UTF-8" 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 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 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 witchcraft happens that would make random access to those objects problematic. -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com --00000000000035976c063f9d386e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Wed, 24 Sept 2025 at 13:43= , Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi Dav= e/Hackers,
I'm working on a = feature where the query tool will show separate data output for all the sel= ect 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 curren= tly works:
1. psycopg pr= ovides 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, y= ou cannot get data for the previous set. It will only point to the current = set.
4. Right now, we ke= ep on looping through nextset until=C2=A0it reaches the last set and then f= etch the data from the last set (using pagination).
5. The fetched result is stored in client memor= y (python process)

<= /div>
So if we need to show th= e output of all the queries, we'll have to fetch=C2=A0the result for ea= ch query and store it in python memory before moving to the next set.
=
psycopg already stores the da= ta 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).
=C2= =A0

= If any one has any suggestions on memory management then please let me know= .
Otherwise, I'm pro= ceeding with what is discussed above.
I don't have any suggestions regarding memory management h= ere, 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 l= ooks like BaseCursor._results is a simple list of PGresult objects, which c= ould be easily exposed of course. What I haven't checked is whether any= witchcraft happens that would make random access to those objects problema= tic.

--
--00000000000035976c063f9d386e--