On Mon, Aug 10, 2015 at 5:25 AM, Shay Rojansky <r...@roji.org> wrote: > Thanks for the explanation Robert, that makes total sense. However, it seems > like the utility of PG's statement_timeout is much more limited than I > thought. > > In case you're interested, I dug a little further and it seems that > Microsoft's client for SQL Server implements the following timeout (source): > > cumulative time-out (for all network packets that are read during the > invocation of a method) for all network reads during command execution or > processing of the results. A time-out can still occur after the first row is > returned, and does not include user processing time, only network read time. > > Since it doesn't seem possible to have a clean query-processing-only timeout > at the backend, we may be better off doing something similar to the above > and enforce timeouts on the client only. Any further thoughts on this would > be appreciated.
An alternative you may want to consider is using the Execute message with a non-zero row count and reading all of the returned rows as they come back, buffering them in memory. When those have all been consumed, issue another Execute message and get some more rows. AFAICS, the biggest problem with this is that there's no good way to bound the number of rows returned by size rather than by number, which has been complained about before by somebody else in a situation similar to yours. Another problem is that I believe it will cause cursor_tuple_fraction to kick in, which may change query plans. But it does have the advantage that the query will be suspended from the server's point of view, which I *think* will toll statement_timeout. You might also consider exposing some knobs to the user, so that they can set the number of rows fetched in one go, and let that be all the rows or only some of them. We really need a better way of doing this, but I think this is the way other drivers are handling it now. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
