Re: Server side cursor limitations for on demand loading of data in query tool [RM2137] [pgAdmin4]

Hi Dave,

--
*Harshal Dhumal*
*Sr. Software Engineer*

EnterpriseDB India: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, May 8, 2017 at 4:49 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> This is fantastic! The speedup in the query tool is phenomenal with large
> result sets. For 96K rows, I'm now seeing 1 second until I can browse the
> data, vs. 13 in v1.4 (and 22 secs in pgAdmin 3)!! Awesome work Harshal :-)
>
> Questions/comments:
>
> - Can we put the row number in the left-hand column, to the right of the
> checkbox? This patch highlights just how much value that had in pgAdmin 3
> (I hadn't realised we had missed it until now)
>
> - If the user clicks the checkbox to select all rows, we need to retrieve
> them all at that time, otherwise they may be inadvertently working with a
> truncated result set.
>
> Sure I'll do above both the changes.

> - Are any changes needed to ensure the Download button works? I'm seeing
> missing rows in my test here, but that could be because of the known issues
> there with Unicode.
>
> This is fixed and checked in.

> Thanks!
>
>
> On Sun, May 7, 2017 at 6:10 PM, Harshal Dhumal <
> harshal(dot)dhumal(at)enterprisedb(dot)com> wrote:
>
>> Hi,
>>
>> Here is patch for initial implementation of on demand loading of result
>> set for query tool and datagrid.
>>
>> --
>> *Harshal Dhumal*
>> *Sr. Software Engineer*
>>
>> EnterpriseDB India: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>> On Tue, Apr 25, 2017 at 5:21 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>>> Hi
>>>
>>> On Tue, Apr 25, 2017 at 8:41 AM, Harshal Dhumal <
>>> harshal(dot)dhumal(at)enterprisedb(dot)com> wrote:
>>>
>>>> Hi Dave,
>>>>
>>>> To implement feature #2137 <https://redmine.postgresql.org/issues/2137> we'll
>>>> need to use server cursor. However server cursor has some
>>>> limitation.
>>>> For eg.
>>>> 1. It cannot execute BEGIN; query (basically user cannot start new
>>>> database transaction)
>>>> 2. In case if Auto commit is true then we try to execute user queries
>>>> inside BEGIN and END when ever it's possible even though user has not put
>>>> BEGIN and END in his query.
>>>>
>>>> Also not all queries executed using Query tool produces records as
>>>> result. So can we assume only
>>>> queries started with SELECT should be executed using server cursor to
>>>> support on demand loading.
>>>> Or should we give user an option to use on demand loading like we have
>>>> options for Auto commit? and Auto rollback?
>>>> In case of on demand loading option user will be responsible to execute
>>>> correct queries (queries which can be executed using server cursor)
>>>>
>>>> Let me know your opinion on this.
>>>>
>>>
>>> Hmm, those are good points.
>>>
>>> So, as a first step, there's no absolute requirement to use a server
>>> side cursor here. The results can be materialised in libpq/psycopg2
>>> (perhaps using an async query), then transferred to the client in batches
>>> as described in the ticket.
>>>
>>> I think this would be a significant improvemet - we can re-visit the
>>> possibility of using server side cursors in the future when we have more
>>> ability to parse the query string before executing it (something we will
>>> want to do when we merge query tool/edit grid functionality).
>>>
>>> --
>>> Dave Page
>>> Blog: http://pgsnake.blogspot.com
>>> Twitter: @pgsnake
>>>
>>> EnterpriseDB UK: http://www.enterprisedb.com
>>> The Enterprise PostgreSQL Company
>>>
>>
>>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>