Re: RFC: pg_stat_logmsg

Hi

so 1. 7. 2023 v 1:57 odesílatel Joe Conway <mail(at)joeconway(dot)com> napsal:

> Greetings,
>
> Attached please find a tarball (rather than a patch) for a proposed new
> contrib extension, pg_stat_logmsg.
>
> The basic idea is to mirror how pg_stat_statements works, except the
> logged messages keyed by filename, lineno, and elevel are saved with a
> aggregate count. The format string is displayed (similar to a query
> jumble) for context, along with function name and sqlerrcode.
>
> I threw this together rather quickly over the past couple of days
> between meetings, so not claiming that it is committable (and lacks
> documentation and regression tests as well), but I would love to get
> feedback on:
>
> 1/ the general concept
> 2/ the pg_stat_statement-like implementation
> 3/ contrib vs core vs external project
>
> Some samples and data:
>
> `make installcheck` with the extension loaded:
> 8<------------------
> # All 215 tests passed.
>
>
> real 2m24.854s
> user 0m0.086s
> sys 0m0.283s
> 8<------------------
>
> `make installcheck` without the extension loaded:
> 8<------------------
>
> # All 215 tests passed.
>
> real 2m26.765s
> user 0m0.076s
> sys 0m0.293s
> 8<------------------
>
> Sample output after running make installcheck a couple times (plus a few
> manually generated ERRORs):
>
> 8<------------------
> test=# select sum(count) from pg_stat_logmsg where elevel > 20;
> sum
> -------
> 10554
> (1 row)
>
> test=# \x
> Expanded display is on.
> test=# select * from pg_stat_logmsg where elevel > 20 order by count desc;
> -[ RECORD 1 ]-------------------------------
> filename | aclchk.c
> lineno | 2811
> elevel | 21
> funcname | aclcheck_error
> sqlerrcode | 42501
> message | permission denied for schema %s
> count | 578
> -[ RECORD 2 ]-------------------------------
> filename | scan.l
> lineno | 1241
> elevel | 21
> funcname | scanner_yyerror
> sqlerrcode | 42601
> message | %s at or near "%s"
> count | 265
> ...
>
> test=# select * from pg_stat_logmsg where elevel > 20 and sqlerrcode =
> 'XX000';
> -[ RECORD 1 ]---------------------------------------
> filename | tid.c
> lineno | 352
> elevel | 21
> funcname | currtid_for_view
> sqlerrcode | XX000
> message | ctid isn't of type TID
> count | 2
> -[ RECORD 2 ]---------------------------------------
> filename | pg_locale.c
> lineno | 2493
> elevel | 21
> funcname | pg_ucol_open
> sqlerrcode | XX000
> message | could not open collator for locale "%s": %s
> count | 2
> ...
>
> 8<------------------
>
> Part of the thinking is that people with fleets of postgres instances
> can use this to scan for various errors that they care about.
> Additionally it would be useful to look for "should not happen" errors.
>
> I will register this in the July CF and will appreciate feedback.
>

This can be a very interesting feature. I like it.

Regards

Pavel

> Thanks!
>
> --
> Joe Conway
> PostgreSQL Contributors Team
> RDS Open Source Databases
> Amazon Web Services: https://aws.amazon.com