Re: Regarding multiple result set in query tool

Hi Dave,

On Thu, Sep 25, 2025 at 4:31 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal <
> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>
>> Hi Dave,
>>
>> On Thu, Sep 25, 2025 at 3:29 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Hi
>>>
>>> On Wed, 24 Sept 2025 at 13:43, Aditya Toshniwal <
>>> aditya(dot)toshniwal(at)enterprisedb(dot)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.
>>>
>> 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 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 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
>
>

--
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com*
<https://www.enterprisedb.com/>
"Don't Complain about Heat, Plant a TREE"