On Thu, 25 Sept 2025 at 13:08, Aditya Toshniwal < [email protected]> wrote:
> 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. > 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
