Re: Query tool data grid - Infinite scroll vs Pagination

On Thu, Aug 22, 2024 at 4:00 PM Aditya Toshniwal <
aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:

> Hi Khushboo,
>
> On Thu, Aug 22, 2024 at 3:56 PM Khushboo Vashi <
> khushboo(dot)vashi(at)enterprisedb(dot)com> wrote:
>
>>
>>
>> On Thu, Aug 22, 2024 at 3:42 PM Aditya Toshniwal <
>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>
>>> Hi All,
>>>
>>> I'm facing a problem here so request for suggestions.
>>> When I select rows, delete them and save the results - the rows are
>>> removed from the UI and from the DB. But, the cursor of the select query
>>> still has the result.
>>> When the page is changed, the rows come back again as they're still in
>>> the cursor memory. Same issue when a new row is added or any update is done.
>>> One way to tackle this problem is to execute the query again to refresh
>>> the data.
>>>
>>> What do you suggest?
>>>
>>>
>> For delete, you can use scroll
>> <https://www.psycopg.org/psycopg3/docs/api/cursors.html#psycopg.ServerCursor.scroll> method
>> of the cursor as it takes relative and absolute both the positions but for
>> add and update, executing a query is the option.
>>
> User may decide to delete in between rows. We'll have to keep track of
> those rows and skip them manually. It will make things odd and complex.
>
Yes, manual tracking will be required.

>
>> On Thu, Aug 8, 2024 at 6:39 PM Aditya Toshniwal <
>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi Dave,
>>>>
>>>> On Thu, Aug 8, 2024 at 6:31 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>
>>>>>
>>>>>
>>>>> On Thu, 8 Aug 2024 at 13:53, Akshay Joshi <
>>>>> akshay(dot)joshi(at)enterprisedb(dot)com> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Thu, Aug 8, 2024 at 6:17 PM Aditya Toshniwal <
>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>
>>>>>>> Hello Everyone,
>>>>>>>
>>>>>>> Just want to share the progress on this. Attached is a quick demo.
>>>>>>> It is not complete yet, but I just want to make sure I'm in the right
>>>>>>> direction.
>>>>>>>
>>>>>>> https://drive.google.com/file/d/1B7DuD2Wx2SABGPEEjc8o4RwYcNgu1t_s/view?usp=sharing
>>>>>>>
>>>>>>> Excellent, I like the new design.
>>>>>>
>>>>>
>>>>> As do I. I do wonder if the ability to enter numbers for the page and
>>>>> the from/to rows is necessary. I guess it doesn't hurt.
>>>>>
>>>> There was a suggestion from @Dave Caughey <caugheyd(at)gmail(dot)com> to
>>>> consider if a user wants to view rows from different pages together. So
>>>> I thought of giving some flexibility. Since it will not be used by most of
>>>> the users, it is collapsed under an edit button to save space.
>>>>
>>>>>
>>>>>
>>>>>>
>>>>>>
>>>>>>> On Sat, Aug 3, 2024 at 7:39 PM Gus Spier <gus(dot)spier(at)gmail(dot)com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> I like the idea of gmail behavior.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>>
>>>>>>>> Gus Spier
>>>>>>>>
>>>>>>>> On Sat, Aug 3, 2024 at 5:45 AM Edson Richter <
>>>>>>>> edsonrichter(at)hotmail(dot)com> wrote:
>>>>>>>>
>>>>>>>>> Love it!
>>>>>>>>> I believe the Gmail behavior is a good approach.
>>>>>>>>>
>>>>>>>>> Regards,
>>>>>>>>>
>>>>>>>>> ER
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Obter o Outlook para Android <https://aka.ms/AAb9ysg>
>>>>>>>>> ------------------------------
>>>>>>>>> *From:* Aditya Toshniwal <aditya(dot)toshniwal(at)enterprisedb(dot)com>
>>>>>>>>> *Sent:* Friday, August 2, 2024 3:53:33 AM
>>>>>>>>> *To:* pgadmin-hackers <pgadmin-hackers(at)postgresql(dot)org>
>>>>>>>>> *Cc:* Usman Khan <umk555(at)gmail(dot)com>; pgAdmin Support <
>>>>>>>>> pgadmin-support(at)postgresql(dot)org>; Dave Page <dpage(at)pgadmin(dot)org>;
>>>>>>>>> Dave Caughey <caugheyd(at)gmail(dot)com>
>>>>>>>>> *Subject:* Re: Query tool data grid - Infinite scroll vs
>>>>>>>>> Pagination
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Hi Hackers,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I have come up with the following design, which allows:
>>>>>>>>>
>>>>>>>>> 1. Adjust the range of rows visible, aka Window.
>>>>>>>>>
>>>>>>>>> 2. Jump to a page number directly based on rows window size.
>>>>>>>>>
>>>>>>>>> 3. Pagination buttons to move forward and backward.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> My question is, how should "select all" behave now? Previously we
>>>>>>>>> used to fetch all the rows when select all was clicked which actually
>>>>>>>>> slowed down as it took time for large data.
>>>>>>>>>
>>>>>>>>> Maybe add a new button to virtually "Select All Rows".
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> [image: image (15).png]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Tue, Jul 2, 2024 at 6:16 PM Aditya Toshniwal <
>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>> Thank you Dave and others for your valuable feedback.
>>>>>>>>> We'll try to achieve what is best for the users.
>>>>>>>>>
>>>>>>>>> On Tue, Jul 2, 2024 at 5:46 PM Dave Caughey <caugheyd(at)gmail(dot)com>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> I think there's a nice blend between pagination and infinite
>>>>>>>>> scrolling.
>>>>>>>>>
>>>>>>>>> The problem with the *current *infinite scrolling implementation
>>>>>>>>> is that the scroll baris scaled to the number of *rendered* rows, so as you
>>>>>>>>> scroll down (which renders another bunch of records), it keeps
>>>>>>>>> rescaling the scrollbar, so to get the next "page", you have to continually
>>>>>>>>> move to the scrollbar (as Aditya says, "*Many users who want
>>>>>>>>> access in between rows or last row struggle to do it as the user has to
>>>>>>>>> scroll and scroll.*")
>>>>>>>>>
>>>>>>>>> If instead the scrollbar were scaled to the total number of rows,
>>>>>>>>> (e.g., 1000 rather than the initial 25 rows that were rendered), then
>>>>>>>>> clicking (say) in the lower third of the scrollbar would would do enough
>>>>>>>>> fetching/rendering to display rows 601-625 (or such). Problem solved.
>>>>>>>>>
>>>>>>>>> Alternatively (or additionally), provide a "jump to row..." button
>>>>>>>>> (similar to what Usman is suggesting re pagination) that gives the user
>>>>>>>>> control to see a specific bunch of records quickly.
>>>>>>>>>
>>>>>>>>> But if the issue is that people don't like infinite scrolling
>>>>>>>>> because "the user has to scroll and scroll", then fix that specific UE
>>>>>>>>> issue, and people will be happy.
>>>>>>>>>
>>>>>>>>> The concern I have with a paginated solution is if the page
>>>>>>>>> represents the maximum number of rows rendered in the results pane, at any
>>>>>>>>> one time. Assume I can see 25 rows in the result pane. I.e., you show
>>>>>>>>> rows 1-25 for the first page, then you *only *show rows 26-50 for
>>>>>>>>> the second page, then *only *show rows 27-50, etc. But when there
>>>>>>>>> is a cluster of records of interest between rows 640 and 655, then those
>>>>>>>>> records of interest are going to be spread between pages 25 and 26, and
>>>>>>>>> will constantly require flipping back and forth between pages. This would
>>>>>>>>> be possibly worse UE than the current "user has to scroll and scroll"
>>>>>>>>> issue. On the other hand, if your pagination solution includes letting
>>>>>>>>> someone nudge the rendered rows up or down a bit so that I can see rows
>>>>>>>>> 640-655 all at once (e.g., there's a field where I can type in that the
>>>>>>>>> current page should start at row 635, so I can see rows 640-655 all
>>>>>>>>> together), then I'm fine with that.
>>>>>>>>>
>>>>>>>>> However, if ultimately you decide to toss out infinite scrolling
>>>>>>>>> for pagination, then please make the (default?) page size be the number of
>>>>>>>>> rows you can actually see in the result pane, rather than some arbitrary
>>>>>>>>> number (e.g., 50). Having the page size equal to the number of rendered
>>>>>>>>> rows means I can quickly step through the pages and hopefully notice a
>>>>>>>>> record of interest.... If the page size is larger than the number of
>>>>>>>>> rendered rows, then as I step to the next page I *still *have to
>>>>>>>>> scroll down to see the last few rows, then step to the next page, then
>>>>>>>>> scroll down again, I.e., that would be hideous UE!
>>>>>>>>>
>>>>>>>>> So my vote preferences are:
>>>>>>>>>
>>>>>>>>> First choice: keep infinite scrolling, but simply fix the
>>>>>>>>> scrollbar scaling and/or give the user the means to quickly jump down by a
>>>>>>>>> page or to a specific page
>>>>>>>>> Second choice: use pagination, but *only *if there's the ability
>>>>>>>>> to see a specific chunk of records on a single page (rather than spread
>>>>>>>>> across two), and make the pagination size default to the number of records
>>>>>>>>> visible given the height of the result pane
>>>>>>>>>
>>>>>>>>> Cheers,
>>>>>>>>> Dave
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Mon, 1 Jul 2024 at 04:35, Usman Khan <umk555(at)gmail(dot)com> wrote:
>>>>>>>>>
>>>>>>>>> Hi Aditya
>>>>>>>>> I vote for pagination, it would really be helpful for end users.
>>>>>>>>> In addition to giving the user the ability to set page size, if he
>>>>>>>>> can also select or enter what page he can jump to say 501, 990 etc it would
>>>>>>>>> be helpful.
>>>>>>>>>
>>>>>>>>> On Fri, Jun 28, 2024 at 3:44 PM Aditya Toshniwal <
>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>> Hi,
>>>>>>>>>
>>>>>>>>> Unfortunately, there were only 3 responses to this.
>>>>>>>>> In that case, we will discuss internally and decide what to do.
>>>>>>>>>
>>>>>>>>> On Thu, Jun 20, 2024 at 2:46 PM Aditya Toshniwal <
>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>> Hi Everyone,
>>>>>>>>>
>>>>>>>>> Request you to share your opinion on this and respond on:
>>>>>>>>>
>>>>>>>>> https://docs.google.com/forms/d/e/1FAIpQLSdfJhNK8qXSe9mKcubZa8jjjYl0hiZVxhv6GGJo8WJcYc27ug/viewform?usp=sharing
>>>>>>>>>
>>>>>>>>> On Thu, Jun 20, 2024 at 1:36 PM Dave Page <dpage(at)pgadmin(dot)org>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> Hi
>>>>>>>>>
>>>>>>>>> On Wed, 19 Jun 2024 at 13:42, Aditya Toshniwal <
>>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>>>>
>>>>>>>>> Hi Hackers,
>>>>>>>>>
>>>>>>>>> Query tool data grid currently pulls the data on load basis in
>>>>>>>>> batches. For example, it will initially load only 1000 rows and once a user
>>>>>>>>> scrolls to the 1000th row, it will fetch the next batch of 1000.
>>>>>>>>> Many users who want access in between rows or last row struggle to
>>>>>>>>> do it as the user has to scroll and scroll. If someone grabs the scroller
>>>>>>>>> and pulls it down still it will be a good UX and the scrollbar may jump.
>>>>>>>>> One reported here -
>>>>>>>>> https://github.com/pgadmin-org/pgadmin4/issues/1780
>>>>>>>>> One more aspect to this is the in memory data of the query tool
>>>>>>>>> which keep on increasing on each scroll, it affects the performance.
>>>>>>>>>
>>>>>>>>> I propose we should use pagination instead of infinite scroll with
>>>>>>>>> the following advantages:
>>>>>>>>> 1. Users can jump to any page they want.
>>>>>>>>> 2. Users can change the page size on the grid directly.
>>>>>>>>> 3. Memory will be used only for visible rows so performance
>>>>>>>>> improvement.
>>>>>>>>> 4. Predictable UI, no jumping scrollbars.
>>>>>>>>>
>>>>>>>>> Let me know what you think.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I think there are definite benefits, but there is the downside of
>>>>>>>>> having to scroll and click to browse results. Personally I'm fine with
>>>>>>>>> that, but I think you should probably poll pgadmin-support for opinions
>>>>>>>>> from more users.
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Dave Page
>>>>>>>>> pgAdmin: https://www.pgadmin.org
>>>>>>>>> PostgreSQL: https://www.postgresql.org
>>>>>>>>> EDB: https://www.enterprisedb.com
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks,
>>>>>>>>> Aditya Toshniwal
>>>>>>>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>>>>>>>> <https://www.enterprisedb.com/>
>>>>>>>>> "Don't Complain about Heat, Plant a TREE"
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks,
>>>>>>>>> Aditya Toshniwal
>>>>>>>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>>>>>>>> <https://www.enterprisedb.com/>
>>>>>>>>> "Don't Complain about Heat, Plant a TREE"
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks,
>>>>>>>>> Aditya Toshniwal
>>>>>>>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>>>>>>>> <https://www.enterprisedb.com/>
>>>>>>>>> "Don't Complain about Heat, Plant a TREE"
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Thanks,
>>>>>>>>> Aditya Toshniwal
>>>>>>>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>>>>>>>> <https://www.enterprisedb.com/>
>>>>>>>>> "Don't Complain about Heat, Plant a TREE"
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Thanks,
>>>>>>> Aditya Toshniwal
>>>>>>> pgAdmin Hacker | Sr. Software Architect | *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
>>>>> EDB: https://www.enterprisedb.com
>>>>>
>>>>> PGDay UK 2024, 11th September, London: https://2024.pgday.uk/
>>>>>
>>>>>
>>>>
>>>> --
>>>> Thanks,
>>>> Aditya Toshniwal
>>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>>> <https://www.enterprisedb.com/>
>>>> "Don't Complain about Heat, Plant a TREE"
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>> Aditya Toshniwal
>>> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
>>> <https://www.enterprisedb.com/>
>>> "Don't Complain about Heat, Plant a TREE"
>>>
>>
>
> --
> Thanks,
> Aditya Toshniwal
> pgAdmin Hacker | Sr. Software Architect | *enterprisedb.com*
> <https://www.enterprisedb.com/>
> "Don't Complain about Heat, Plant a TREE"
>