Re: pgAdmin Async Server Cursor

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.

On Mon, May 12, 2025 at 11:05 AM Khushboo Vashi <
khushboo(dot)vashi(at)enterprisedb(dot)com> 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 <dpage(at)pgadmin(dot)org> wrote:
>
>> Hi
>>
>> On Mon, 13 Jan 2025 at 09:25, Khushboo Vashi <
>> khushboo(dot)vashi(at)enterprisedb(dot)com> 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
>>
>>