Hi Dave, On Thu, Sep 25, 2025 at 4:31 PM Dave Page <[email protected]> wrote:
> Hi > > On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal < > [email protected]> wrote: > >> Hi Dave, >> >> On Thu, Sep 25, 2025 at 3:29 PM Dave Page <[email protected]> wrote: >> >>> Hi >>> >>> On Wed, 24 Sept 2025 at 13:43, Aditya Toshniwal < >>> [email protected]> 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"
