Re: log_line_prefix: make it possible to add the search_path

On Tuesday, July 26, 2022 3:08:01 AM CEST Lukas Fittl wrote:
> On Mon, Jul 25, 2022 at 12:38 AM Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info>
>
> wrote:
> > usecase by not showing the schema, one of them being log_line_prefix.
> > It is possible to work around this using the application_name, but a
> > mistake
> > on the application side would be fatal, while the search_path would still
> > indicate the real tables used in a query.
>
> I'm assuming this is mostly referring to STATEMENT log lines and other
> situations where the original query is output (e.g. auto_explain).
>
> +1 on the benefit of solving this (I've had this use case before), but I
> think we can keep this more specific than a general log_line_prefix option.
> The search_path isn't relevant to any log line that doesn't reference a
> query, since e.g. autovacuum log output fully qualifies its relation names,
> and many other common log lines have nothing to do with tables or queries.
>
> What if we instead had something like this, as an extra CONTEXT (or DETAIL)
> log line:
>
> LOG: duration: 4079.697 ms execute <unnamed>:
> SELECT * FROM x WHERE y = $1 LIMIT $2
> DETAIL: parameters: $1 = 'long string', $2 = '1'
> CONTEXT: settings: search_path = 'my_tenant_schema, "$user", public'
>
> That way you could determine that the slow query was affecting the "x"
> table in "my_tenant_schema".
>
> This log output would be controlled by a new GUC, e.g.
> "log_statement_search_path" with three settings: (1) never, (2)
> non_default, (3) always.
>
> The default would be "never" (same as today). "non_default" would output
> the search path when a SET has modified it in the current session (and so
> we couldn't infer it from the config or the role/database overrides).
> "always" would always output the search path for statement-related log
> lines.
>
> Thanks,
> Lukas

Hi

This is a good idea. I've hacked a first implementation of it (lacking
documentation, and several logs are still missing) attached to this email.
The biggest issue I had was with knowing where the setting come from since no
guc.h function expose that information. I worked around this a bit, but I'm
sure it would be preferable to do it otherwise.

Thanks for your feedbacks

Regards

Pierre