public inbox for [email protected]
help / color / mirror / Atom feedRegarding 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]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[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-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 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
@ 2025-09-25 10:45 ` Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-25 11:01 ` Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
@ 2025-09-25 12:08 ` Aditya Toshniwal <[email protected]>
2025-09-25 12:31 ` Re: Regarding multiple result set in query tool Murtuza Zabuawala <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-25 12:31 ` Murtuza Zabuawala <[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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-25 12:36 ` Dave Page <[email protected]>
2025-09-25 13:43 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
@ 2025-09-25 13:43 ` Aditya Toshniwal <[email protected]>
2025-09-25 14:25 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 13:43 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-25 14:25 ` Dave Page <[email protected]>
2025-09-29 05:53 ` Re: Regarding multiple result set in query tool 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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 13:43 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 14:25 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
@ 2025-09-29 05:53 ` Aditya Toshniwal <[email protected]>
2025-09-29 09:01 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 13:43 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 14:25 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-29 05:53 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-29 09:01 ` Aditya Toshniwal <[email protected]>
2025-09-29 09:07 ` Re: Regarding multiple result set in query tool Dave Page <[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-24 12:43 Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 09:58 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 10:45 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 11:01 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 12:08 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 12:36 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-25 13:43 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-25 14:25 ` Re: Regarding multiple result set in query tool Dave Page <[email protected]>
2025-09-29 05:53 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
2025-09-29 09:01 ` Re: Regarding multiple result set in query tool Aditya Toshniwal <[email protected]>
@ 2025-09-29 09:07 ` Dave Page <[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