MIME-Version: 1.0
References: <89742024-d51a-c66b-90b9-67f837072cd2@joeconway.com>
In-Reply-To: <89742024-d51a-c66b-90b9-67f837072cd2@joeconway.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 1 Jul 2023 05:20:08 +0200
Message-ID: <CAFj8pRDOrB9sP1s4+wz3vuCSM82bVzw8wses0BJ=n5E8fHMGLA@mail.gmail.com>
Subject: Re: RFC: pg_stat_logmsg
To: Joe Conway <mail@joeconway.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000418ec305ff646b00"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRDOrB9sP1s4%2Bwz3vuCSM82bVzw8wses0BJ%3Dn5E8fHMGLA%40mail.gmail.com>
Precedence: bulk

--000000000000418ec305ff646b00
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

so 1. 7. 2023 v 1:57 odes=C3=ADlatel Joe Conway <mail@joeconway.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=3D# select sum(count) from pg_stat_logmsg where elevel > 20;
>    sum
> -------
>   10554
> (1 row)
>
> test=3D# \x
> Expanded display is on.
> test=3D# select * from pg_stat_logmsg where elevel > 20 order by count de=
sc;
> -[ 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=3D# select * from pg_stat_logmsg where elevel > 20 and sqlerrcode =
=3D
> '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

--000000000000418ec305ff646b00
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">so 1. 7. 2023 v=C2=A01:57 odes=C3=ADlatel Joe=
 Conway &lt;<a href=3D"mailto:mail@joeconway.com">mail@joeconway.com</a>&gt=
; napsal:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Greeti=
ngs,<br>
<br>
Attached please find a tarball (rather than a patch) for a proposed new <br=
>
contrib extension, pg_stat_logmsg.<br>
<br>
The basic idea is to mirror how pg_stat_statements works, except the <br>
logged messages keyed by filename, lineno, and elevel are saved with a <br>
aggregate count. The format string is displayed (similar to a query <br>
jumble) for context, along with function name and sqlerrcode.<br>
<br>
I threw this together rather quickly over the past couple of days <br>
between meetings, so not claiming that it is committable (and lacks <br>
documentation and regression tests as well), but I would love to get <br>
feedback on:<br>
<br>
1/ the general concept<br>
2/ the pg_stat_statement-like implementation<br>
3/ contrib vs core vs external project<br>
<br>
Some samples and data:<br>
<br>
`make installcheck` with the extension loaded:<br>
8&lt;------------------<br>
# All 215 tests passed.<br>
<br>
<br>
real=C2=A0 =C2=A0 2m24.854s<br>
user=C2=A0 =C2=A0 0m0.086s<br>
sys=C2=A0 =C2=A0 =C2=A00m0.283s<br>
8&lt;------------------<br>
<br>
`make installcheck` without the extension loaded:<br>
8&lt;------------------<br>
<br>
# All 215 tests passed.<br>
<br>
real=C2=A0 =C2=A0 2m26.765s<br>
user=C2=A0 =C2=A0 0m0.076s<br>
sys=C2=A0 =C2=A0 =C2=A00m0.293s<br>
8&lt;------------------<br>
<br>
Sample output after running make installcheck a couple times (plus a few <b=
r>
manually generated ERRORs):<br>
<br>
8&lt;------------------<br>
test=3D# select sum(count) from pg_stat_logmsg where elevel &gt; 20;<br>
=C2=A0 =C2=A0sum<br>
-------<br>
=C2=A0 10554<br>
(1 row)<br>
<br>
test=3D# \x<br>
Expanded display is on.<br>
test=3D# select * from pg_stat_logmsg where elevel &gt; 20 order by count d=
esc;<br>
-[ RECORD 1 ]-------------------------------<br>
filename=C2=A0 =C2=A0| aclchk.c<br>
lineno=C2=A0 =C2=A0 =C2=A0| 2811<br>
elevel=C2=A0 =C2=A0 =C2=A0| 21<br>
funcname=C2=A0 =C2=A0| aclcheck_error<br>
sqlerrcode | 42501<br>
message=C2=A0 =C2=A0 | permission denied for schema %s<br>
count=C2=A0 =C2=A0 =C2=A0 | 578<br>
-[ RECORD 2 ]-------------------------------<br>
filename=C2=A0 =C2=A0| scan.l<br>
lineno=C2=A0 =C2=A0 =C2=A0| 1241<br>
elevel=C2=A0 =C2=A0 =C2=A0| 21<br>
funcname=C2=A0 =C2=A0| scanner_yyerror<br>
sqlerrcode | 42601<br>
message=C2=A0 =C2=A0 | %s at or near &quot;%s&quot;<br>
count=C2=A0 =C2=A0 =C2=A0 | 265<br>
...<br>
<br>
test=3D# select * from pg_stat_logmsg where elevel &gt; 20 and sqlerrcode =
=3D <br>
&#39;XX000&#39;;<br>
-[ RECORD 1 ]---------------------------------------<br>
filename=C2=A0 =C2=A0| tid.c<br>
lineno=C2=A0 =C2=A0 =C2=A0| 352<br>
elevel=C2=A0 =C2=A0 =C2=A0| 21<br>
funcname=C2=A0 =C2=A0| currtid_for_view<br>
sqlerrcode | XX000<br>
message=C2=A0 =C2=A0 | ctid isn&#39;t of type TID<br>
count=C2=A0 =C2=A0 =C2=A0 | 2<br>
-[ RECORD 2 ]---------------------------------------<br>
filename=C2=A0 =C2=A0| pg_locale.c<br>
lineno=C2=A0 =C2=A0 =C2=A0| 2493<br>
elevel=C2=A0 =C2=A0 =C2=A0| 21<br>
funcname=C2=A0 =C2=A0| pg_ucol_open<br>
sqlerrcode | XX000<br>
message=C2=A0 =C2=A0 | could not open collator for locale &quot;%s&quot;: %=
s<br>
count=C2=A0 =C2=A0 =C2=A0 | 2<br>
...<br>
<br>
8&lt;------------------<br>
<br>
Part of the thinking is that people with fleets of postgres instances <br>
can use this to scan for various errors that they care about. <br>
Additionally it would be useful to look for &quot;should not happen&quot; e=
rrors.<br>
<br>
I will register this in the July CF and will appreciate feedback.<br></bloc=
kquote><div><br></div><div>This can be a very interesting feature. I like i=
t.</div><div><br></div><div>Regards</div><div><br></div><div>Pavel</div><di=
v><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
Thanks!<br>
<br>
-- <br>
Joe Conway<br>
PostgreSQL Contributors Team<br>
RDS Open Source Databases<br>
Amazon Web Services: <a href=3D"https://aws.amazon.com" rel=3D"noreferrer" =
target=3D"_blank">https://aws.amazon.com</a></blockquote></div></div>

--000000000000418ec305ff646b00--