Re: Resdhift's lack of cursors and PQsetSingleRowMode

On Tue, Dec 24, 2013 at 07:23:58PM +0000, Daniele Varrazzo wrote:
> On Tue, Dec 24, 2013 at 4:45 PM, Ryan Kelly <rpkelly22@gmail.com> wrote:
> > We're using psycopg2 to connect to Amazon's Redshift, and one problem
> > Redshift has is that it lacks server-side cursors. What I'd like is to
> > be able to use PQsetSingleRowMode to avoid consuming all the data at
> > once (in specific circumstances, of course). I'm not entirely sure what
> > in psycopg2 would need to be modified, but it seems like the changes
> > would mostly be restricted to the `async == 0` portion of `pq_execute`,
> > but I'm not familiar enough with the source to be certain.
>
> Hello Ryan,
>
> supporting SingleRowMode doesn't look like a trivial change. I have
> personally no plan to work on it but of course I'm not against
> contributions. Here's a bunch of random thoughts.
>
> General implementation of psycopg with SingleRowMode seems relatively
> hard: for instance implementing fetchmany(10) would require 10 calls
> and handling the result from 10 PQresult structures into a temporary
> storage we currently don't need and don't have: currently we have a
> single result both with client-side and server-side cursor. It would
> be easier to implement a subset of the interface, e.g. forward only
> iteration with no bookkeeping (no scroll(), no rownumber...).

The single row mode is designed for following high-level API:

    curs = db.single-row-mode-cursor()
    curs.execute(sql)
    for row in curs.fetchall():
    process(row)

IOW, it is meant to implement efficient iterators.

In case of psycopg, it might be best to implement as alternative
cursor object?  Integraring with existing cursor implementation
might create too much complexity perhaps?

> ISTM that in single row mode you can only get a single row per network
> operation. If you have a large dataset (likely if you want this
> feature) you may end up in a large overhead, the sort of problem we
> have solved with cursor.itersize but whose solution doesn't seem to
> apply for SingleRowMode.

No, libpq will still works with 8KB buffers over network.  Single
row mode will return rows one-by-one from that buffer.  So it's only
slightly inefficient as it creates PGresult object for each row,
instead one for full resultset.  This inefficiency is offset by
much better cache usage for larger resultsets.

In any case, there is no difference on how it operates over TCP
compared to plain PQexec().

--
marko