Re: statement_timeout affects query results fetching?

Thanks for your responses.

I'm not using cursors or anything fancy. The expected behavior (as far as I can tell) for a .NET database driver is to read one row at a time from the database and make it available. There's even a standard API option for fetching data on a column-by-column basis: this allows the user to not hold the entire row in memory (imagine rows with megabyte-sized columns). This makes sense to me; Tom, doesn't the libpq behavior you describe of absorbing the result set as fast as possible mean that a lot of memory is wasted on the client side? I'd be interested in your take on this.

I can definitely appreciate the complexity of changing this behavior. I think that some usage cases (such as mine) would benefit from a timeout on the time until the first row is sent, this would allow to put an upper cap on stuff like query complexity, for example.

Shay

On Sat, Aug 8, 2015 at 5:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Shay Rojansky <roji@roji.org> writes:
> Hi everyone, I'm seeing some strange behavior and wanted to confirm it.
> When executing a query that selects a long result set, if the code
> processing the results takes its time (i.e.g more than statement_timeout),
> a timeout occurs. My expectation was that statement_timeout only affects
> query *processing*, and does not cover the frontend actually processing the
> result.

Are you using a cursor, or something like that?

libpq ordinarily absorbs the result set as fast as possible and then hands
it back to the application as one blob; the time subsequently spent by the
application looking at the blob doesn't count against statement_timeout.

As Robert says, statement_timeout *does* include time spent sending the
result set to the client, and we're not going to change that, because it
would be too hard to disentangle calculation from I/O.  So if the client
isn't prompt about absorbing all the data then you have to factor that
into your setting.  But it would be a slightly unusual usage pattern
AFAIK.

                        regards, tom lane