public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Page <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: Khushboo Vashi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: pgAdmin Async Server Cursor
Date: Tue, 13 May 2025 06:51:04 -0400
Message-ID: <CA+OCxowz4+VYzgQBh0KfdEB+oZV7ZJg5x4RrZy6oeu6Kr2Pc1A@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDf9h2F1E4hyXE3Bn47Wm+DcmQNQC-JgHPKK41k92G3GuA@mail.gmail.com>
References: <CAFOhELc7cFnTkqzfUUihjqtfYYAPxD9rYGQ6aA+D=UaxWSEfMA@mail.gmail.com>
<CA+OCxoyOroo=v5ptoUoM-cEY_5cvQWnsLoKoTYvFEbF=-hTpcw@mail.gmail.com>
<CAFOhELdmQDhO8+R_W3uQ1iywWSES+1ur+Wq9T9fNg4tuMXDmUw@mail.gmail.com>
<CANxoLDf9h2F1E4hyXE3Bn47Wm+DcmQNQC-JgHPKK41k92G3GuA@mail.gmail.com>
Hi
On Mon, 12 May 2025 at 02:19, Akshay Joshi <[email protected]>
wrote:
> Hi Khushboo,
>
> I feel that having duplicate buttons for the same action, specifically for
> using the server cursor, can be a bit redundant. Instead, I suggest
> introducing a preference setting like *"Use server cursor for more than N
> rows"*, with a default value of, say, 100,000 rows. Users could then
> easily adjust this threshold based on their needs.
>
> OR, we could offer two preferences:
>
> 1.
>
> *"Use server cursor?"* (a simple toggle), and
> 2.
>
> *"Use server cursor for more than N rows?"* (to fine-tune behavior).
>
> This approach would keep the UI cleaner and still give users the
> flexibility they need.
>
I agree that we should not add duplicate buttons. Like others, I would
suggest a simple option in Preferences, that is also reflected on the menu
with the commit options, as per your screenshot.
I do not think we should have a "Use server cursor for more than N rows?"
option... how will we know how many rows there are going to be without
running a potentially expensive query that might have other undesirable or
even dangerous side effects if executed twice.
>
> On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi <
> [email protected]> wrote:
>
>> Hello Dave/hackers,
>>
>> I'd like to update you on this issue. As we discussed in this thread, I
>> have implemented the server cursor. The psycopg implementation is also
>> done; the only part left is how we are going to show it on the UI.
>>
>> I have identified 3 to 4 places where we need to make a UI decision.
>>
>> *Menu/Button options*
>>
>> *1. Query Tool*
>> There are two options for the query tool.
>>
>> 1. Show as a menu item along with the Execute buttons panel, as shown
>> below.
>>
>>
>> [image: Screenshot 2025-05-06 at 4.54.56 PM.png]
>>
>> 2. Give a separate button in the Execute buttons panel, as shown below.
>> Note: I copied and pasted the same icon; we can change this icon once we
>> decide to go with this approach.
>>
>> [image: Screenshot 2025-05-06 at 4.54.06 PM.png]
>>
>>
>>
>>
>> *2. View/Edit Data*
>>
>>
>> 1. Context menu
>>
>> [image: Screenshot 2025-05-06 at 4.58.28 PM.png]
>>
>>
>> 2. On the left side panel, top menu bar. (We can change the icon later.)
>>
>> [image: Screenshot 2025-05-12 at 10.45.48 AM.png]
>>
>>
>>
>>
>>
>> *3. Save the Result to the File*
>> We can choose a different button option or a dropdown menu item like the
>> above approaches.
>>
>>
>> *Other than Menu/button option*
>> Another option is to provide the* Preferences option* to use the server
>> cursor instead of the client cursor.
>> The drawback of this approach is that the user needs to select/deselect
>> that option from the Preferences.
>> And it would be inconvenient if those settings need to be changed
>> frequently.
>>
>>
>>
>> Thanks,
>> Khushboo
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Jan 17, 2025 at 6:01 PM Dave Page <[email protected]> wrote:
>>
>>> Hi
>>>
>>> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi <
>>> [email protected]> wrote:
>>>
>>>> Hackers,
>>>>
>>>> Regarding #5797 <https://github.com/pgadmin-org/pgadmin4/issues/5797;: Full
>>>> query result is being loaded into RAM despite ON_DEMAND_RECORD_COUNT=1000
>>>>
>>>> pgAdmin uses the *Async cursor* to fetch the data in the query tool,
>>>> which basically fetches and stores the entire result on the client side, in
>>>> our case, the pgAdmin server.
>>>> So, if the query result is very large, it uses more memory on the
>>>> pgAdmin server. We use the ON_DEMAND_RECORD_COUNT to fetch the partial
>>>> data from the cursor (which is already transferred to the pgAdmin server)
>>>> and show it on the UI.
>>>>
>>>> To overcome this, we can use the* Async Server Cursor*, which
>>>> transfers data from the Postgres Server to the client (pgAdmin server) on
>>>> demand. This will reduce memory consumption and improve the performance.
>>>>
>>>> There are some downsides, too,
>>>>
>>>> 1. The *Server Cursor* does not return the Total number of rows.
>>>> - In this case, we will have a problem with pagination. We can either
>>>> just show the *next page* button in pagination and hide the Last page,
>>>> as we will not know the exact pages, so on clicking on the next button, we
>>>> will show the result if it exists, OR we can use infinite scrolling for the
>>>> Server Cursor.
>>>>
>>>
>>> Yes, that is a pretty big problem - not just for pagination, but for
>>> simply seeing how many rows your query returned - something I and I suspect
>>> many others do regularly.
>>>
>>> Another issue for some might be that it will change query timings such
>>> that they may no longer reflect what might happen in an application. This
>>> was a huge topic of debate when we discussed making this same change in
>>> pgAdmin III, probably 20 or more years ago!
>>>
>>>
>>>>
>>>> 2. The *Server Cursor* is less efficient for the small query results
>>>> as it takes more commands to receive the results.
>>>> - We can add one option in the query tool to run the query with either
>>>> the *Server or Client cursor*.
>>>>
>>>
>>> That might be our solution in general - have a per-query-tool-instance
>>> option to specify client or server cursor. If you choose server, you lose
>>> the row count, but get the performance. If you choose client, you get the
>>> current behaviour.
>>>
>>> --
>>> Dave Page
>>> pgAdmin: https://www.pgadmin.org
>>> PostgreSQL: https://www.postgresql.org
>>> pgEdge: https://www.pgedge.com
>>>
>>>
--
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com
Attachments:
[image/png] Screenshot 2025-05-06 at 4.54.56 PM.png (60.9K, 3-Screenshot%202025-05-06%20at%204.54.56%E2%80%AFPM.png)
download | view image
[image/png] Screenshot 2025-05-06 at 4.54.06 PM.png (48.5K, 4-Screenshot%202025-05-06%20at%204.54.06%E2%80%AFPM.png)
download | view image
[image/png] Screenshot 2025-05-06 at 4.58.28 PM.png (198.0K, 5-Screenshot%202025-05-06%20at%204.58.28%E2%80%AFPM.png)
download | view image
[image/png] Screenshot 2025-05-12 at 10.45.48 AM.png (20.2K, 6-Screenshot%202025-05-12%20at%2010.45.48%E2%80%AFAM.png)
download | view image
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: pgAdmin Async Server Cursor
In-Reply-To: <CA+OCxowz4+VYzgQBh0KfdEB+oZV7ZJg5x4RrZy6oeu6Kr2Pc1A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox