Postgres connection state stays active when an exception occurs

0

The state of the connection stays active in pool, when there is an exception while executing a query or stored procedure through c#.

The Npgsql version that I am using is 4.1.7.

Here is the code that I am trying to execute.

NpgsqlCommand cmd = null;
NpgsqlDataAdapter sda = null;
NpgsqlConnection conn = null;
try {
    string sql = "a_test";
    conn = new NpgsqlConnection("Server=localhost;Port=5432;Username=admin;Password=test;Database=majordb;SearchPath=dbs;CommandTimeout=300;MaxPoolSize=500;Connection Idle Lifetime=180;Connection Pruning Interval=5;");
    cmd = new NpgsqlCommand(sql);
    cmd.CommandType = CommandType.StoredProcedure;
    sda = new NpgsqlDataAdapter(cmd);
    cmd.Connection = conn;
    sda.Fill(dataTable);
} 
catch (Exception e) {
   //log
}
finally {
    if(null != sda)
    {
        try
        {
            sda.Dispose();                        
        }
        catch (Exception)
        {
        }
    }
    try
    {
        cmd.Connection.Close();
        cmd.Connection.Dispose();
    }
    catch (Exception)
    {
    }
    try
    {
        cmd.Dispose();
    }
    catch (Exception)
    {
    }
}

If the above code executes properly without any exception, the connection state in pool goes to idle, which is correct. But if an exception occurs while executing, like below:

 "Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> 
 System.IO.IOException: Unable to read data from the transport connection: A connection attempt 
 failed because the connected party did not properly respond after a period of time, or established 
 connection failed because connected host has failed to respond."

The connection state in pool shows as active for about 5 mins or so, even though the close/dispose methods are called in finally block. This means the close/dispose did not properly executed by Npgsql. If the program keeps the connection state in pool active for every connection ran within 5 mins, then there can be an issue with MaxPoolSize error.

I wanted to see the connection state to idle, even when there is an exception. How do I do this.

Please note: I am not looking for a solution to the exception that I listed above. I am looking for a solution where the connection state is changed to idle from active when there is an exception while executing the above code.

To know if the connection state is active or not I used the following query:

SELECT
    pid,
    usename,
    application_name,
    datname,
    client_addr,
    rank() over (partition by client_addr order by backend_start ASC) as rank,
    state,
    state_change,
    current_timestamp,
    query,
    query_start,
    backend_start,
    FROM 
    pg_stat_activity
WHERE
    pid <> pg_backend_pid( )
AND
    application_name !~ '(?:psql)|(?:pgAdmin.+)'
AND
    datname = current_database() 
AND
    usename = current_user 

Any help is really appreciated.

c#
postgresql
npgsql
postgresql-11
asked on Stack Overflow Jan 8, 2021 by User12111111 • edited Jan 11, 2021 by User12111111

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0