Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
In short - no
show stats; to get the
total_sent per database. Assuming you can check the stats before query and after, calculating the difference won't give you the query result size. Even in ideal env, when no other parallel sessions exist. Eg:
t=# copy (select id from t) to '/tmp/1'; COPY 1043482 t=# \! du -h /tmp/1 7.0M /tmp/1
t=# select 40493353-40493297; ?column? ---------- 56 (1 row)
hm, so the size of a query result is 7MB, but received bytes are 56. Ah! I saved result of query on server! So pgbouncer technically indeed did not receive large set of data, ok - do it to client:
t=# \copy (select objectid from pond_user) to '/tmp/1'; COPY 1043482 t=# select 40493442-40493353; ?column? ---------- 89 (1 row)
Same story... Maybe lets check bytes sent?..
t# select (355612622-343149820)/(1024*1024); ?column? ---------- 11 (1 row)
11MB... Not particularly precise match.
pgbouncer does not log the result set size, You can use
show stats assuming you can isolate sessions in time and your estimations are to get a very rough approximation for sizes.
People are using
iptraf or such for the same, Somebody logs long taking queries with
log_min_duration_statement and then repeat them with
CREATE TABLE AS to get the size, some even write patches for client.
Analytic approach here leads to even more complications: