Re: Regarding multiple result set in query tool

On Thu, 25 Sept 2025 at 13:08, Aditya Toshniwal <
aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:

> 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.
>

If pgAdmin were a single-user application, I'd agree - however it is not
when running in server mode. Other users will not know what is going on if
one user exhausts memory.

--
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com