> On 25 Sep 2025, at 5:38 PM, Aditya Toshniwal > <[email protected]> wrote: > > Hi Dave, > > On Thu, Sep 25, 2025 at 4:31 PM Dave Page <[email protected] > <mailto:[email protected]>> wrote: >> Hi >> >> On Thu, 25 Sept 2025 at 11:45, Aditya Toshniwal >> <[email protected] >> <mailto:[email protected]>> wrote: >>> Hi Dave, >>> >>> On Thu, Sep 25, 2025 at 3:29 PM Dave Page <[email protected] >>> <mailto:[email protected]>> wrote: >>>> Hi >>>> >>>> On Wed, 24 Sept 2025 at 13:43, Aditya Toshniwal >>>> <[email protected] >>>> <mailto:[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. +1
We can provide a configurable option to memory limit check; if the limit is exceeded, the result is truncated and the data is returned. > 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 <https://www.pgadmin.org/> >> PostgreSQL: https://www.postgresql.org <https://www.postgresql.org/> >> pgEdge: https://www.pgedge.com <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"
