Обсуждение: Getting following error in using cursor to fetch the records from alarge table in c language(current transaction is aborted, commands ignoreduntil end of transaction block)
Hi all,
Getting following error in using cursor to fetch the records from a large table in c language.
Can you please suggest why it is coming and what is the remedy for this.
Error Details
-----------------
Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-1] < 2019-11-01 13:21:54.212 CET > ERROR: current transaction is aborted, commands ignored until end of transaction block
Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-2] < 2019-11-01 13:21:54.212 CET > STATEMENT: BEGIN
Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-1] < 2019-11-01 13:21:54.324 CET > ERROR: current transaction is aborted, commands ignored until end of transaction block
Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-2] < 2019-11-01 13:21:54.324 CET > STATEMENT: BEGIN
Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-1] < 2019-11-01 13:21:54.356 CET > ERROR: current transaction is aborted, commands ignored until end of transaction block
Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-2] < 2019-11-01 13:21:54.356 CET > STATEMENT: BEGIN
Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-1] < 2019-11-01 13:21:54.360 CET > ERROR: current transaction is aborted, commands ignored until end of transaction block
Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-2] < 2019-11-01 13:21:54.360 CET > STATEMENT
Sample Code snippet used
theCursorDec = (RWCString)"DECLARE " + mySqlCursor + " CURSOR FOR " + theSql;
myFetchSql = "FETCH " + fetchStr + " IN " + mySqlCursor;
// Begin the cursor
PQexec(connection, ,"BEGIN"))
PQexec(connection, ,"myFetchSql”)
// Fetch records from the cursor. Getting First N tuples
mySqlResultsPG = PQexec(connection,myFetchSql);
if(PQresultStatus(mySqlResultsPG) == PGRES_TUPLES_OK)
{
myNumColumns = PQnfields(mySqlResultsPG);
ntuples = PQntuples(mySqlResultsPG);
myTotalNumberOfRowsInQueryResult = ntuples;
myCurrentRowNum = 0 ;
}
Regards
Tarkeshwar
On Fri, Nov 1, 2019 at 1:44 PM M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote: > Getting following error in using cursor to fetch the records from a large table in c language. Regarding this, "c language", I'll comment later.... > Can you please suggest why it is coming and what is the remedy for this. > Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-1] < 2019-11-01 13:21:54.212 CET > ERROR: current transaction is aborted,commands ignored until end of transaction block > Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-2] < 2019-11-01 13:21:54.212 CET > STATEMENT: BEGIN > Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-1] < 2019-11-01 13:21:54.324 CET > ERROR: current transaction is aborted,commands ignored until end of transaction block > Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-2] < 2019-11-01 13:21:54.324 CET > STATEMENT: BEGIN > Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-1] < 2019-11-01 13:21:54.356 CET > ERROR: current transaction is aborted,commands ignored until end of transaction block > Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-2] < 2019-11-01 13:21:54.356 CET > STATEMENT: BEGIN > Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-1] < 2019-11-01 13:21:54.360 CET > ERROR: current transaction is aborted,commands ignored until end of transaction block > Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-2] < 2019-11-01 13:21:54.360 CET > STATEMENT This seems incomplete, but I's telling you the cause. You had an error, you need to terminate the transaction before issuing a new one, i.e., do a commit ( which, IIRC, will rollback if the transaction is in error ) or rollback. > Sample Code snippet used As you stated C I cannot comment too much, but notice: > theCursorDec = (RWCString)"DECLARE " + mySqlCursor + " CURSOR FOR " + theSql; > myFetchSql = "FETCH " + fetchStr + " IN " + mySqlCursor; Neither of these are C, they COULD be C++ > // Begin the cursor Same as this comment. > PQexec(connection, ,"BEGIN")) > PQexec(connection, ,"myFetchSql”) And these are definitely not C ( no ; ) and, if you generated them by editing, myfetchsql is quoted which smells fishy. I won't comment more until you confirm that is the real code, but anyway it seems to me you issue transaction start queries without properly terminating them with a transaction end one. Francisco Olarte
Thanks Yes it is in c++. Actually we just written this code. Due to vaccum full cursor query failing on a connection and all the subsequent queries are failing and we found shared errors in /var/logs of the postgres installed machine. We also last query sent by the client application is: replicateDB=# select pid, state, backend_start, query_start, query from pg_stat_activity; pid | state | backend_start | query_start | query -------+-------------------------------+-------------------------------+-------------------------------+----------------------------------------------------- ------------------------ 18604 | idle in transaction (aborted) | 2019-11-01 13:18:07.919162+01 | 2019-11-01 13:23:19.92045+01 | BEGIN -----Original Message----- From: Francisco Olarte <folarte@peoplecall.com> Sent: Friday, November 1, 2019 10:38 PM To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> Cc: pgsql-general@postgresql.org Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language(current transactionis aborted, commands ignored until end of transaction block) On Fri, Nov 1, 2019 at 1:44 PM M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote: > Getting following error in using cursor to fetch the records from a large table in c language. Regarding this, "c language", I'll comment later.... > Can you please suggest why it is coming and what is the remedy for this. > Nov 1 13:21:54 sprintstd2 postgres[18604]: [10-1] < 2019-11-01 > 13:21:54.212 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [10-2] < 2019-11-01 13:21:54.212 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [11-1] < 2019-11-01 > 13:21:54.324 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [11-2] < 2019-11-01 13:21:54.324 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [12-1] < 2019-11-01 > 13:21:54.356 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [12-2] < 2019-11-01 13:21:54.356 CET > STATEMENT: > BEGIN Nov 1 13:21:54 sprintstd2 postgres[18604]: [13-1] < 2019-11-01 > 13:21:54.360 CET > ERROR: current transaction is aborted, commands > ignored until end of transaction block Nov 1 13:21:54 sprintstd2 > postgres[18604]: [13-2] < 2019-11-01 13:21:54.360 CET > STATEMENT This seems incomplete, but I's telling you the cause. You had an error, you need to terminate the transaction before issuinga new one, i.e., do a commit ( which, IIRC, will rollback if the transaction is in error ) or rollback. > Sample Code snippet used As you stated C I cannot comment too much, but notice: > theCursorDec = (RWCString)"DECLARE " + mySqlCursor + " CURSOR FOR " + theSql; > myFetchSql = "FETCH " + fetchStr + " IN " + mySqlCursor; Neither of these are C, they COULD be C++ > // Begin the cursor Same as this comment. > PQexec(connection, ,"BEGIN")) > PQexec(connection, ,"myFetchSql”) And these are definitely not C ( no ; ) and, if you generated them by editing, myfetchsql is quoted which smells fishy. I won't comment more until you confirm that is the real code, but anyway it seems to me you issue transaction start querieswithout properly terminating them with a transaction end one. Francisco Olarte
M Tarkeshwar Rao: On Sat, Nov 2, 2019 at 5:16 PM M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote: > Thanks Yes it is in c++. Actually we just written this code. > Due to vaccum full cursor query failing on a connection and all the subsequent queries are failing and we > found shared errors in /var/logs of the postgres installed machine. ... Some more things followed by a huge bottom quote-to-the-sig..... Your writing style makes it really hard to decipher what you are trying to say. I hope your problem has been solved, because I am not going to try to decipher it or comment more. Francisco Olarte.