Re: compute_query_id and pg_stat_statements

On Sat, Apr 24, 2021 at 5:22 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
> On Sat, Apr 24, 2021 at 11:54:25PM +0900, Fujii Masao wrote:
> > When compute_query_id is not enabled (this is the default setting),
> > pg_stat_statements doesn't track any statements. This means that
> > we will see no entries in pg_stat_statements by default. I'm afraid that
> > users may easily forget to enable compute_query_id
> > when using pg_stat_statements (because this setting was not necessary
> > in v13 or before), and finally may have noticed the mis-configuration
> > and failure of statements tracking after many queries were executed.
> > For example, we already have one report about this issue, in [1].
> >
> > Shouldn't we do something so that users can avoid such mis-configuration?
> >
> > One idea is to change the default value of compute_query_id from false to true.
> > If enabling compute_query_id doesn't incur any performance penalty,
> > IMO this idea is very simple and enough.
>
> I think the query overhead was too high (2%) to enable it by default:
>
> https://www.postgresql.org/message-id/20201016160355.GA31474@alvherre.pgsql

Personally I'd say 2% is not too high to turn it on by default, as it
goes down when you move past trivial queries, which is what most
people do. And since you can easily turn it off.

> > Another idea is to change pg_stat_statements so that it emits an error
> > at the server startup (i.e., prevents the server from starting up)
> > if compute_query_id is not enabled. In this case, users can easily notice
> > the mis-configuration from the error message in the server log,
> > enable compute_query_id, and then restart the server.
>
> I think it throws an error in the server logs, but preventing server
> start seems extreme. Also, compute_query_id is PGC_SUSET, meaning it
> can be changed by the super-user, so you could enable compute_query_id
> without a server restart, which makes failing on start kind of odd.

How about turning it into an enum instead of a boolean, that can be:

off = always off
auto = pg_stat_statments turns it on when it's loaded in
shared_preload_libraries. Other extensions using it can do that to.
But it remains off if you haven't installed any *extension* that needs
it
on = always on (if you want it in pg_stat_activity regardless of extensions)

The default would be "auto", which means that pg_stat_statements would
work as expected, but those who haven't installed it (or another
extension that changes it) would not have to pay the overhead.

--
Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/