Re: Can you please tell us how set this prefetch attribute in following lines.

On Thu, 2019-10-17 at 11:16 +0000, M Tarkeshwar Rao wrote:
> How to fetch certain number of tuples from a postgres table.
>
> Same I am doing in oracle using following lines by setting prefetch attribute.
>
> For oracle
> // Prepare query
> if( OCIStmtPrepare( myOciStatement, myOciError, (text *)aSqlStatement,
> // Get statement type
> OCIAttrGet( (void *)myOciStatement, OCI_HTYPE_STMT, &statement_type, 0, OCI_ATTR_STMT_TYPE, myOciError );
> // Set prefetch count
> OCIAttrSet( myOciStatement, OCI_HTYPE_STMT, &prefetch, 0, OCI_ATTR_PREFETCH_ROWS, myOciError );
> // Execute query
> status = OCIStmtExecute( myOciServerCtx, myOciStatement, myOciError, iters, 0, NULL, NULL, OCI_DEFAULT );
>
> For Postgres
>
> Can you please tell us how set this prefetch attribute in following lines. Is PQexec returns all the rows from the table?
>
> mySqlResultsPG = PQexec(connection, aSqlStatement);
>
> if((PQresultStatus(mySqlResultsPG) == PGRES_FATAL_ERROR ) || (PQstatus(connection) != CONNECTION_OK)){}
> if ((PQresultStatus(mySqlResultsPG) == PGRES_COMMAND_OK) || (PQresultStatus(mySqlResultsPG) == PGRES_TUPLES_OK))
> {
> myNumColumns = PQnfields(mySqlResultsPG);
> myTotalNumberOfRowsInQueryResult = PQntuples(mySqlResultsPG);
> myCurrentRowNum = 0 ;
> }

The C API doesn't offer anything like Oracle prefetch to force prefetching of a certain
number of result rows.

In the PostgreSQL code you show above, the whole result set will be fetched in one go
and cached in client RAM, so in a way you have "prefetch all".

The alternative thet the C API gives you is PQsetSingleRowMode(), which, when called,
will return the result rows one by one, as they arrive from the server.
That disables prefetching.

If you want to prefetch only a certain number of rows, you can use the DECLARE and
FETCH SQL statements to create a cursor in SQL and fetch it in batches.

This workaround has the down side that the current query shown in "pg_stat_activity"
or "pg_stat_statements" is always something like "FETCH 32", and you are left to guess
which statement actually caused the problem.

If you are willing to bypass the C API and directly speak the network protocol with
the server, you can do better. This is documented in
https://www.postgresql.org/docs/current/protocol.html

The "Execute" ('E') message allows you to send an integer with the maximum number of
rows to return (0 means everything), so that does exactly what you want.

The backend will send a "PortalSuspended" ('s') to indicate that there is more to come,
and you keep sending "Execute" until you get a "CommandComplete" ('C').

I you feel hacky you could write C API support for that...

If you use that or a cursor, PostgreSQL will know that you are executing a cursor
and will plan its queries differently: it will assume that only "cursor_tuple_fraction"
(default 0.1) of your result set is actually fetched and prefer fast startup plans.
If you don't want that, because you are fetching batches as fast as you can without
lengthy intermediate client processing, you might want to set the parameter to 1.0.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com