Re: pgAdmin Async Server Cursor

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Khushboo Vashi <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: pgAdmin Async Server Cursor
Date: Mon, 12 May 2025 11:05:02 +0530
Message-ID: <CAFOhELdmQDhO8+R_W3uQ1iywWSES+1ur+Wq9T9fNg4tuMXDmUw@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxoyOroo=v5ptoUoM-cEY_5cvQWnsLoKoTYvFEbF=-hTpcw@mail.gmail.com>
References: <CAFOhELc7cFnTkqzfUUihjqtfYYAPxD9rYGQ6aA+D=UaxWSEfMA@mail.gmail.com>
	<CA+OCxoyOroo=v5ptoUoM-cEY_5cvQWnsLoKoTYvFEbF=-hTpcw@mail.gmail.com>

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
>
>


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]
  Subject: Re: pgAdmin Async Server Cursor
  In-Reply-To: <CAFOhELdmQDhO8+R_W3uQ1iywWSES+1ur+Wq9T9fNg4tuMXDmUw@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