Обсуждение: Can you please tell us how set this prefetch attribute in followinglines.

On Thu, 2019-10-17 at 11:16 +0000, M Tarkeshwar Rao wrote:
> [EXTERNAL SOURCE]
> 
>  
> 
> Hi all,
>  
> 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 ;
>     }
> 
>  
> Regards
> Tarkeshwar
>  

declare a cursor and fetch


https://books.google.com/books?id=Nc5ZT2X5mOcC&pg=PA405&lpg=PA405&dq=pqexec+fetch&source=bl&ots=8P8w5JemcL&sig=ACfU3U0POGGSP0tYTrs5oxykJdOeffaspA&hl=en&sa=X&ved=2ahUKEwjevbmA2KPlAhXukOAKHaBIBcoQ6AEwCnoECDEQAQ#v=onepage&q=pqexec%20fetch&f=false

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

Thanks Thompson. Your inputs are very valuable and we successfully implemented it and results are very good. 

But I am getting following error message. Can you please suggest why this is coming and what is the remedy for this.

Error Details
-----------------
Failed to execute the sql command close: 
mycursor_4047439616_1571970686004430275FATAL:  terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Regards
Tarkeshwar

-----Original Message-----
From: Reid Thompson <Reid.Thompson@omnicell.com> 
Sent: Thursday, October 17, 2019 9:49 PM
To: pgsql-general@lists.postgresql.org
Cc: Reid Thompson <Reid.Thompson@omnicell.com>
Subject: 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:
> [EXTERNAL SOURCE]
> 
>  
> 
> Hi all,
>  
> 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 ;
>     }
> 
>  
> Regards
> Tarkeshwar
>  

declare a cursor and fetch


https://protect2.fireeye.com/v1/url?k=d75a6ab6-8b8e60bf-d75a2a2d-86740465fc08-fa8f74c15b35a3fd&q=1&e=7b7df498-f187-408a-a07c-07b1c5f6f868&u=https%3A%2F%2Fbooks.google.com%2Fbooks%3Fid%3DNc5ZT2X5mOcC%26pg%3DPA405%26lpg%3DPA405%26dq%3Dpqexec%2Bfetch%26source%3Dbl%26ots%3D8P8w5JemcL%26sig%3DACfU3U0POGGSP0tYTrs5oxykJdOeffaspA%26hl%3Den%26sa%3DX%26ved%3D2ahUKEwjevbmA2KPlAhXukOAKHaBIBcoQ6AEwCnoECDEQAQ%23v%3Donepage%26q%3Dpqexec%2520fetch%26f%3Dfalse