Hi Dave, On Thu, Sep 25, 2025 at 6:06 PM Dave Page <[email protected]> wrote:
> > > 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. > 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"
