Re: Regarding multiple result set in query tool - Mailing list pgadmin-hackers

Hi Dave,

On Thu, Sep 25, 2025 at 4:31 PM Dave Page <dpage@pgadmin.org> wrote:

Hi

On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:

Hi Dave,

On Thu, Sep 25, 2025 at 3:29 PM Dave Page <dpage@pgadmin.org> 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 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.