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.thomp...@omnicell.com> Sent: Thursday, October 17, 2019 9:49 PM To: pgsql-general@lists.postgresql.org Cc: Reid Thompson <reid.thomp...@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