Обсуждение: Getting following error in using cursor to fetch the records from alarge table in c language
Getting following error in using cursor to fetch the records from alarge table in c language
От
M Tarkeshwar Rao
Дата:
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
-----------------
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.
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 Wed, 2019-10-30 at 16:59 +0000, M Tarkeshwar Rao wrote: > 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 > ----------------- > 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. This is not a proble with your program. Your query is running against a standby server with "hot_standby" on, and there are conflicts between replication and your query. If you increase "max_standby_streaming_delay" in "postgresql.conf" on the standby, your query will be given more time to complete. This will, however, cause replay of the replicated changes to be delayed. Yours, Laurenz Albe
RE: Getting following error in using cursor to fetch the records froma large table in c language
От
M Tarkeshwar Rao
Дата:
Hi Laurenz, You are absolutely right. This is the issue with us. If we retry the query again. Will it be successful? Can you please suggest how to configure hot_standby_feedback? Regards Tarkeshwar -----Original Message----- From: Laurenz Albe <laurenz.albe@cybertec.at> Sent: Wednesday, October 30, 2019 11:20 PM To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org> Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language On Wed, 2019-10-30 at 16:59 +0000, M Tarkeshwar Rao wrote: > 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 > ----------------- > 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. This is not a proble with your program. Your query is running against a standby server with "hot_standby" on, and there are conflicts between replication and yourquery. If you increase "max_standby_streaming_delay" in "postgresql.conf" on the standby, your query will be given more time tocomplete. This will, however, cause replay of the replicated changes to be delayed. Yours, Laurenz Albe
On Thu, 2019-10-31 at 05:18 +0000, M Tarkeshwar Rao wrote: [queries get canceled on the standby] > You are absolutely right. This is the issue with us. > If we retry the query again. Will it be successful? Sometimes :^/ > Can you please suggest how to configure hot_standby_feedback? You set it to "on", then you get no query cancellation because of VACUUM (at the price of potential bloat on the primary server). Your query can still get canceled by conflichts with ACCESS EXCLUSIVE locks that are taken by TRUNCATE, ALTER/DROP TABLE and similar as well as autovacuum truncation. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
RE: Getting following error in using cursor to fetch the records froma large table in c language
От
M Tarkeshwar Rao
Дата:
When we running with vacuum full we are getting following error message. Error: ------- user was holding a relation lock for too long Any idea about this error. -----Original Message----- From: Laurenz Albe <laurenz.albe@cybertec.at> Sent: Thursday, October 31, 2019 12:28 PM To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org> Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language On Thu, 2019-10-31 at 05:18 +0000, M Tarkeshwar Rao wrote: [queries get canceled on the standby] > You are absolutely right. This is the issue with us. > If we retry the query again. Will it be successful? Sometimes :^/ > Can you please suggest how to configure hot_standby_feedback? You set it to "on", then you get no query cancellation because of VACUUM (at the price of potential bloat on the primaryserver). Your query can still get canceled by conflichts with ACCESS EXCLUSIVE locks that are taken by TRUNCATE, ALTER/DROP TABLEand similar as well as autovacuum truncation. Yours, Laurenz Albe -- Cybertec | https://protect2.fireeye.com/v1/url?k=a20e6965-fe84a291-a20e29fe-86cd58c48020-fb007d5e5585f41b&q=1&e=c64a1818-0510-4ceb-bd0f-50fdd335f83c&u=https%3A%2F%2Fwww.cybertec-postgresql.com%2F
M Tarkeshwar Rao wrote: > When we running with vacuum full we are getting following error message. > > Error: > ------- > user was holding a relation lock for too long > > Any idea about this error. That means that you got a replication conflict, which is to be expected, because VACUUM (FULL) is also one of the commands that require an ACCESS EXCLUSIVE lock. First suggestion: don't use VACUUM (FULL). Second suggestion: A standby server can *either* be used for high availability, in which case queries on the standby should be forbidden or canceled in case of conflicts, *or* it can be used to run resource intensive reading queries, in which case application of the transaction log should be delayed. Don't try to use a standby for both - use two standby servers. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
RE: Getting following error in using cursor to fetch the records froma large table in c language
От
M Tarkeshwar Rao
Дата:
Hi, If VACUUM full is going on and any conflicting query come then what will happen to that query. Regards Tarkeshwar -----Original Message----- From: M Tarkeshwar Rao Sent: Thursday, October 31, 2019 12:45 PM To: Laurenz Albe <laurenz.albe@cybertec.at>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org> Subject: RE: Getting following error in using cursor to fetch the records from a large table in c language When we running with vacuum full we are getting following error message. Error: ------- user was holding a relation lock for too long Any idea about this error. -----Original Message----- From: Laurenz Albe <laurenz.albe@cybertec.at> Sent: Thursday, October 31, 2019 12:28 PM To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; 'pgsql-general@postgresql.org' <pgsql-general@postgresql.org> Subject: Re: Getting following error in using cursor to fetch the records from a large table in c language On Thu, 2019-10-31 at 05:18 +0000, M Tarkeshwar Rao wrote: [queries get canceled on the standby] > You are absolutely right. This is the issue with us. > If we retry the query again. Will it be successful? Sometimes :^/ > Can you please suggest how to configure hot_standby_feedback? You set it to "on", then you get no query cancellation because of VACUUM (at the price of potential bloat on the primaryserver). Your query can still get canceled by conflichts with ACCESS EXCLUSIVE locks that are taken by TRUNCATE, ALTER/DROP TABLEand similar as well as autovacuum truncation. Yours, Laurenz Albe -- Cybertec | https://protect2.fireeye.com/v1/url?k=a20e6965-fe84a291-a20e29fe-86cd58c48020-fb007d5e5585f41b&q=1&e=c64a1818-0510-4ceb-bd0f-50fdd335f83c&u=https%3A%2F%2Fwww.cybertec-postgresql.com%2F