Re: Regarding multiple result set in query tool

Hi Dave,

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

>
>
> 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.
>
How about allowing multi result sets only for desktop app?
The problem with memory limits is - it's an extra overhead to keep checking
how much memory is consumed. A row size will depend on the number of
columns and data. If we have a predefined algorithm which will decide the
limits in a performant way is desirable.

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