NpgsqlRetryingExecutionStrategy with CommandTimeout induced TimeoutExceptions

[Hoffs]

Hi,

Was wondering if there is a way to use retrying execution strategy with command timeout without it retrying timed out queries? Basically I want to set some amount of time that queries should take maximum to execute and then don't even retry them. But in other cases (e.g. timeout because server restarted and has to connect to another host) it should still retry.

Passing cancellation token's with same timeout also probably doesn't really work, because then it will also have to take into account the time it might retry.

[roji]

How do you expect to distinguish between timeouts that occur because the query takes too long and other types of timeouts? In both cases the client receives no response from the server...

[Hoffs]

Yeah, thats what I was thinking, that it's probably impossible to distinguish between normal timeout and command one as it throws generic socket TimeoutException, although I was hoping that there would be a difference.

Actually I also did just notice that I probably want statement_timeout in this case and that CommandTimeout was changed some time to use purely socket timeout. I suppose that would allow the behavior I want?

[roji]

Yeah, statement_timeout may be what you're looking for, but what exactly the timeout means is trickier than it looks. For example, a query sending back lots of data may end up blocking PG for a while (if the cilent is reading fast enough), and that counts towards the statement_timeout limit. In other words, it may not actually express time spent processing the query on the PG side. I've made a note to make all that clearer in npgsql/doc#47.

[Hoffs]

Yeah, I've noticed the responses in pgsql-hackers email linked in the other issue, that result set return time is part of statement timeout, but I don't really get this part a query sending back lots of data may end up blocking PG for a while (if the cilent is reading fast enough). If client is consuming data fast enough (so query is processed and data is read quickly) it should not cause an issue (especially if result sets are small)? But if data is read in async iterator (or something similar), then it may end up timing out in a semi unexpected manner?

Also is that still true that command timeout forces connection to be closed (and reopened subsequently)? Is same true for statement timeout?

[roji]

This has nothing to do with sync or async - that distinction is simply a matter of whether a thread is blocking or not while waiting in I/O.

The point is more that your client application may read a row from Npgsql, and then go off to do some long processing - or even just wait for user input. When that happens, if PG continues sending lots of data, at some point it will block as the client application isn't reading. If you're only reading small resultsets, or if you're sure you're consuming results fast enough (e.g. by buffering them in memory), then you should be fine.

Also is that still true that command timeout forces connection to be closed (and reopened subsequently)? Is same true for statement timeout?

When Command Timeout is reached, Npgsql first attempts to contact PG and cancel the query; if the query is running for long but networking is fine, this should work, and the main thread reading the results willg et a timeout exception. If, on the other hand, networking is down, Npgsql waits for a while longer and then breaks the connection.

The PG statement_timeout setting simply raises a PostgresException, and doesn't break the connection. But if the network is down, you'd never receive that error, at which point the Command Timeout would kick in.

[roji]

@Hoffs I'll go ahead and close this now as there's nothing actionable on the Npgsql side (except updating the docs). But don't hesitate to post back here with any questions etc.

[Hoffs]

I also just wanted to also ask what would be the most performant way to then set statement_timeout and avoid additional roundtrip while still using efcore, but I see that there is already issue for this type of api npgsql/npgsql#4522 . I suppose then an extra roundtrip is inevitable at this point (besides some interceptor based "hacks" mentioned in related issues)?

Also in case of multiplexing, would it even be possible to use statement_timeout to somehow make sure that statement_timeout and efcore command are sent in-order (without acquiring exclusive connection with transaction)? I dont see npgsql/npgsql#4522 mentioning multiplexing at all.

[roji]

You can simply set statement_timeout in the connection string, via the Options: Host=localhost;Username=test;Password=test;Options=-c statement_timeout=30s. This would make all your connections have a statement_timeout of 30 seconds, without a need to prepend anything. If you need to vary statement_timeout for some reason, things would indeed become more complicated, but that's very rarely the case.

[Hoffs]

As a matter of fact I do want to have different timeout on different actions, like longer timeout when returning a collection of rows vs returning a single row, but setting an upper limit on connection string would be a start I suppose.