public inbox for [email protected]help / color / mirror / Atom feed
Regarding multiple result set in query tool 12+ messages / 3 participants [nested] [flat]
* Regarding multiple result set in query tool @ 2025-09-24 12:43 Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-24 12:43 UTC (permalink / raw) To: pgadmin-hackers 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. If any one has any suggestions on memory management then please let me know. Otherwise, I'm proceeding with what is discussed above. Note: This will not be applicable for server cursors as it will against the whole point of server cursor. -- Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* <https://www.enterprisedb.com/; "Don't Complain about Heat, Plant a TREE" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 09:58 Dave Page <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2025-09-25 09:58 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers 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. -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 10:45 Aditya Toshniwal <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-25 10:45 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers 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. Another way around will be to parse and separate out the queries and run each one separately. > > -- > 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" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 11:01 Dave Page <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2025-09-25 11:01 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers 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. -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 12:08 Aditya Toshniwal <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 2 replies; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-25 12:08 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers 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" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 12:31 Murtuza Zabuawala <[email protected]> parent: Aditya Toshniwal <[email protected]> 1 sibling, 0 replies; 12+ messages in thread From: Murtuza Zabuawala @ 2025-09-25 12:31 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: Dave Page <[email protected]>; pgadmin-hackers > 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" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 12:36 Dave Page <[email protected]> parent: Aditya Toshniwal <[email protected]> 1 sibling, 1 reply; 12+ messages in thread From: Dave Page @ 2025-09-25 12:36 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers 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 ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 13:43 Aditya Toshniwal <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-25 13:43 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers 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" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-25 14:25 Dave Page <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Dave Page @ 2025-09-25 14:25 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers On Thu, 25 Sept 2025 at 14:44, Aditya Toshniwal < [email protected]> wrote: > Hi Dave, > >> >> 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? > I *really* dislike that. We should support all features in both modes, except where it clearly doesn't make sense. > 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. > Well we can take the extra cycles to compute actual memory usage, or we can just pick an arbitrary number of rows (which as you note, will depend on the schema and data). The former is clearly easier to tune - it could be an arbitrary limit in the config, or it could be computed based on machine resources and utilisation, whilst the latter is always going to be a guess. I'm not sure I see another way. Anyone else? -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-29 05:53 Aditya Toshniwal <[email protected]> parent: Dave Page <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-29 05:53 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi Dave, On Thu, Sep 25, 2025 at 7:55 PM Dave Page <[email protected]> wrote: > > > On Thu, 25 Sept 2025 at 14:44, Aditya Toshniwal < > [email protected]> wrote: > >> Hi Dave, >> >>> >>> 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? >> > > I *really* dislike that. We should support all features in both modes, > except where it clearly doesn't make sense. > > >> 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. >> > > Well we can take the extra cycles to compute actual memory usage, or we > can just pick an arbitrary number of rows (which as you note, will depend > on the schema and data). The former is clearly easier to tune - it could be > an arbitrary limit in the config, or it could be computed based on machine > resources and utilisation, whilst the latter is always going to be a guess. > I'm not sure I see another way. Anyone else? > For the first approach - python provides a function which we can use. But deciding the limit is something tricky. A user may still get out of memory error with a limit set. It will all depend on what is running on his system. Setting the limit itself is like playing a video game. For the row count based limit, we already have a per page limit - and adding more such configs will only add more confusion. import sys my_tuple = (1, 2, 'hello', 4.5) memory_size = sys.getsizeof(my_tuple) print(f"The memory size of the tuple is: {memory_size} bytes") > -- > 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" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-29 09:01 Aditya Toshniwal <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 1 reply; 12+ messages in thread From: Aditya Toshniwal @ 2025-09-29 09:01 UTC (permalink / raw) To: Dave Page <[email protected]>; +Cc: pgadmin-hackers Hi, I raised this to the psycopg team and we may possibly not need to take care of anything on the pgAdmin side. Check the discussion here - https://github.com/psycopg/psycopg/discussions/1170 But, I will have to raise a PR to get some extra functions for our use-case. On Mon, Sep 29, 2025 at 11:23 AM Aditya Toshniwal < [email protected]> wrote: > Hi Dave, > > On Thu, Sep 25, 2025 at 7:55 PM Dave Page <[email protected]> wrote: > >> >> >> On Thu, 25 Sept 2025 at 14:44, Aditya Toshniwal < >> [email protected]> wrote: >> >>> Hi Dave, >>> >>>> >>>> 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? >>> >> >> I *really* dislike that. We should support all features in both modes, >> except where it clearly doesn't make sense. >> >> >>> 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. >>> >> >> Well we can take the extra cycles to compute actual memory usage, or we >> can just pick an arbitrary number of rows (which as you note, will depend >> on the schema and data). The former is clearly easier to tune - it could be >> an arbitrary limit in the config, or it could be computed based on machine >> resources and utilisation, whilst the latter is always going to be a guess. >> I'm not sure I see another way. Anyone else? >> > For the first approach - python provides a function which we can use. But > deciding the limit is something tricky. A user may still get out of memory > error with a limit set. It will all depend on what is running on his > system. Setting the limit itself is like playing a video game. For the row > count based limit, we already have a per page limit - and adding more such > configs will only add more confusion. > > import sys > > my_tuple = (1, 2, 'hello', 4.5) > memory_size = sys.getsizeof(my_tuple) > print(f"The memory size of the tuple is: {memory_size} bytes") > > >> -- >> 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" > -- Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* <https://www.enterprisedb.com/; "Don't Complain about Heat, Plant a TREE" ^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Regarding multiple result set in query tool @ 2025-09-29 09:07 Dave Page <[email protected]> parent: Aditya Toshniwal <[email protected]> 0 siblings, 0 replies; 12+ messages in thread From: Dave Page @ 2025-09-29 09:07 UTC (permalink / raw) To: Aditya Toshniwal <[email protected]>; +Cc: pgadmin-hackers On Mon, 29 Sept 2025 at 10:01, Aditya Toshniwal < [email protected]> wrote: > Hi, > > I raised this to the psycopg team and we may possibly not need to take > care of anything on the pgAdmin side. Check the discussion here - > https://github.com/psycopg/psycopg/discussions/1170 > Well that seems ideal - and Daniele seems quite open to the idea which is good news :-) > > But, I will have to raise a PR to get some extra functions for our > use-case. > > On Mon, Sep 29, 2025 at 11:23 AM Aditya Toshniwal < > [email protected]> wrote: > >> Hi Dave, >> >> On Thu, Sep 25, 2025 at 7:55 PM Dave Page <[email protected]> wrote: >> >>> >>> >>> On Thu, 25 Sept 2025 at 14:44, Aditya Toshniwal < >>> [email protected]> wrote: >>> >>>> Hi Dave, >>>> >>>>> >>>>> 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? >>>> >>> >>> I *really* dislike that. We should support all features in both modes, >>> except where it clearly doesn't make sense. >>> >>> >>>> 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. >>>> >>> >>> Well we can take the extra cycles to compute actual memory usage, or we >>> can just pick an arbitrary number of rows (which as you note, will depend >>> on the schema and data). The former is clearly easier to tune - it could be >>> an arbitrary limit in the config, or it could be computed based on machine >>> resources and utilisation, whilst the latter is always going to be a guess. >>> I'm not sure I see another way. Anyone else? >>> >> For the first approach - python provides a function which we can use. But >> deciding the limit is something tricky. A user may still get out of memory >> error with a limit set. It will all depend on what is running on his >> system. Setting the limit itself is like playing a video game. For the row >> count based limit, we already have a per page limit - and adding more such >> configs will only add more confusion. >> >> import sys >> >> my_tuple = (1, 2, 'hello', 4.5) >> memory_size = sys.getsizeof(my_tuple) >> print(f"The memory size of the tuple is: {memory_size} bytes") >> >> >>> -- >>> 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" >> > > > -- > Thanks, > Aditya Toshniwal > pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* > <https://www.enterprisedb.com/; > "Don't Complain about Heat, Plant a TREE" > -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com ^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-09-29 09:07 UTC | newest] Thread overview: 12+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-09-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]> 2025-09-25 09:58 ` Dave Page <[email protected]> 2025-09-25 10:45 ` Aditya Toshniwal <[email protected]> 2025-09-25 11:01 ` Dave Page <[email protected]> 2025-09-25 12:08 ` Aditya Toshniwal <[email protected]> 2025-09-25 12:31 ` Murtuza Zabuawala <[email protected]> 2025-09-25 12:36 ` Dave Page <[email protected]> 2025-09-25 13:43 ` Aditya Toshniwal <[email protected]> 2025-09-25 14:25 ` Dave Page <[email protected]> 2025-09-29 05:53 ` Aditya Toshniwal <[email protected]> 2025-09-29 09:01 ` Aditya Toshniwal <[email protected]> 2025-09-29 09:07 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox