We run user submitted queries which can potentially return a large result set.
In order to avoid memory issues, we would like to detect these cases and cancel the query. The user then is expected to modify the query.
We already use PreparedStatement#setFetchSize() to scroll the result set and process a large result set incrementally.
However, when the result set is too large, we would like to avoid bringing even the first results over the network or any other unnecessary work as much as possible on the client side and on the database side.
Doing a SELECT COUNT(*)... beforehand just degrades the performance of the expected case where the queries behave nicely in general.
Is there a way for postgres to tell the expected result set size?