From: | M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)com> |
---|---|
To: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: Getting following error in using cursor to fetch the records from a large table in c language(current transaction is aborted, commands ignored until end of transaction block) |
Date: | 2019-11-02 16:16:15 |
Message-ID: | VI1PR0701MB2718D0E07B8D3EC873FE50D5AE7D0@VI1PR0701MB2718.eurprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)peoplecall(dot)com>
Sent: Friday, November 1, 2019 10:38 PM
To: M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language(current transaction is aborted, commands ignored until end of transaction block)
On Fri, Nov 1, 2019 at 1:44 PM M Tarkeshwar Rao <m(dot)tarkeshwar(dot)rao(at)ericsson(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Konstantin Gredeskoul | 2019-11-03 03:51:48 | Re: Can you please suggest how to configure hot_standby_feedback? |
Previous Message | Tom Lane | 2019-11-02 14:22:27 | Re: QUERY: autovacuum: VACUUM ANALYZE table versus QUERY: autovacuum: VACUUM table |