Subject: RFC: pg_stat_logmsg

From: Joe Conway <[email-masked]>
Date: Fri, 30 Jun 2023 19:57:09 -0400

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.

Thanks!

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

---

From: Pavel Stehule <[email-masked]>
Date: Sat, 1 Jul 2023 05:20:08 +0200

Hi

> `make installcheck` with the extension loaded:

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

Regards

Pavel

---

From: Joe Conway <[email-masked]>
Date: Sat, 1 Jul 2023 15:52:50 -0400

On 6/30/23 23:20, Pavel Stehule wrote:
> so 1. 7. 2023 v 1:57 odesílatel Joe Conway <[email-masked] 
> <mailto:[email-masked]>> napsal:

Thanks!

FWIW, I just modified it to provide the localized text of the elevel 
rather than the internal number. I also localized the message format string:

8<------------------------------
psql (16beta2)
Type "help" for help.

test=# \x
Expanded display is on.
test=# select * from pg_stat_logmsg where elevel = 'ERROR' and 
sqlerrcode = 'XX000' and count > 1;
-[ RECORD 1 ]---------------------------------------------
filename   | tablecmds.c
lineno     | 10908
elevel     | ERROR
funcname   | ATExecAlterConstraint
sqlerrcode | XX000
message    | cannot alter constraint "%s" on relation "%s"
count      | 2
-[ RECORD 2 ]---------------------------------------------
filename   | user.c
lineno     | 2130
elevel     | ERROR
funcname   | check_role_membership_authorization
sqlerrcode | XX000
message    | role "%s" cannot have explicit members
count      | 2

test=# set lc_messages ='sv_SE.UTF8';
SET
test=# select * from pg_stat_logmsg where elevel = 'FEL' and sqlerrcode 
= 'XX000' and count > 1;
-[ RECORD 1 ]---------------------------------------------
filename   | tablecmds.c
lineno     | 10908
elevel     | FEL
funcname   | ATExecAlterConstraint
sqlerrcode | XX000
message    | kan inte ändra villkoret "%s" i relation "%s"
count      | 2
-[ RECORD 2 ]---------------------------------------------
filename   | user.c
lineno     | 2130
elevel     | FEL
funcname   | check_role_membership_authorization
sqlerrcode | XX000
message    | rollen "%s" kan inte ha explicita medlemmar
count      | 2
8<------------------------------

New tarball attached.