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