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

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
>