Can you please tell us how set this prefetch attribute in following lines.
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
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
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
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
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
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
On Fri, Oct 18, 2019 at 03:47:49AM +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?
Yes, PQexec reads everything at once into a buffer on the library.
https://www.postgresql.org/docs/current/libpq-exec.html
I think you want this:
https://www.postgresql.org/docs/current/libpq-async.html
|Another frequently-desired feature that can be obtained with PQsendQuery and PQgetResult is retrieving large query results a row at a time. This is discussed in Section 33.5.
https://www.postgresql.org/docs/current/libpq-single-row-mode.html
Note this does not naively send "get one row" requests to the server on each
call. Rather, I believe it behaves at a protocol layer exactly the same as
PQexec(), but each library call returns only a single row. When it runs out of
rows, it requests from the server another packet full of rows, which are saved
for future iterations.
The effect is constant memory use for arbitrarily large result set with same
number of network roundtrips as PQexec(). You'd do something like:
PQsendQuery(conn)
PQsetSingleRowMode(conn)
while(res = PQgetResult(conn)) {
...
PQclear(res)
}
Justin
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