Kill query after command timout

Question

I'm trying to kill query triggered by ADO.NET command on postgresql database, after command timeout:

using (var command = new PgSqlCommand("public.timeout_test", connection))
{
    command.CommandTimeout = 10; 
    command.CommandType = CommandType.StoredProcedure;

    connection.Open();
    command.ExecuteNonQuery();
}

In dotnet code timeout exception is being throwed correctly, but I wonder why query triggered by timout_test function is still in active state. If I run below query, then query executed by timeout_tets is listed as active:

SELECT * FROM pg_stat_activity where state = 'active';

Tried to test it with devart and npgsql connectors, but both of them behave in the same way, so I assume that it's intended behevior, but don't understand the reason. Also wanted to ask if there is a way to kill query after command timeout.

Answer 1

At least in Npgsql, CommandTimeout is implemented as a client-side socket timeout: after a certain amount of time Npgsql will simply close the network socket on its side. This doesn't cancel the query server-side, which will continue running.

You can set the PostgreSQL statement_timeout parameter to have it kill queries running more than a given amount of time; for best results, set statement_timeout to something lower than CommandTimeout - this will ensure that server timeout occurs before client timeout, preserving the connection and transmitting the server timeout to the client as a regular exception.

Another option is to manually trigger a cancellation from the client by calling NpgsqlCommand.Cancel(). You can do this whenever you want (e.g. when the user clicks a button), but contrary to statement_timeout it will obviously work only if the network is up.