Re: libpq - lack of support to set the fetch size

On Mar 12, 2014, at 5:57 AM, matshyeq <matshyeq(at)gmail(dot)com> wrote:

> I don't see why? I can't think of any single SQL tool I've been working with that didn't have this functionality, really.
> The principle I find very simple and useful.
> There is defined "fetch row size" parameter (each tool calls give its own name),
> after submitting ANY query, client fetches result set rows but not more than that.
> Some programs even automatically define this value based on result grid size displayed on the screen.
> User then usually has two buttons, fetch another batch/screen or fetch all - he decides.
> If he decides way too late (break for coffee) then he simply resubmits the query (and potentially change the parameter first)...
>
> I don't find value in auto-fetching millions of rows for user to present on the screen.
> Also I don't think it's particularly useful when you need to know and apply database specific SQL syntax to limit the rows.
> If you join multiple tables that may be even more tricky (which table to apply limit? or use subquerying instead?).

Using the extend query protocol, Postgres has a built-in way to limit the number of rows returned from any select without any textual manipulation of the query.

I'm not sure if libpq exposes this capability in the API, but it should not be too difficult to implement.

See:

http://www.postgresql.org/docs/current/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

> Once a portal exists, it can be executed using an Execute message. The Execute message specifies the portal name (empty string denotes the unnamed portal) and a maximum result-row count (zero meaning "fetch all rows"). The result-row count is only meaningful for portals containing commands that return row sets; in other cases the command is always executed to completion, and the row count is ignored. The possible responses to Execute are the same as those described above for queries issued via simple query protocol, except that Execute doesn't cause ReadyForQuery or RowDescription to be issued.

John DeSoi, Ph.D.