Обсуждение: 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)

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.