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

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(dot)Thompson(at)omnicell(dot)com>
Sent: Thursday, October 17, 2019 9:49 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org
Cc: Reid Thompson <Reid(dot)Thompson(at)omnicell(dot)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