Horton

Re: Reducing the log spam

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Laurenz Albe <laurenz.albe@cybertec.at>, Justin Pryzby <pryzby@telsasoft.com>
Cc: Jelte Fennema-Nio <postgres@jeltef.nl>, Isaac Morland <isaac.morland@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-03-07T19:38:39Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. plpgsql: make WHEN OTHERS distinct from WHEN SQLSTATE '00000'.

Hi Laurenz

On 18.06.24 18:49, Laurenz Albe wrote:
> I have attached a new version that leaves the parameter empty by default.


I've tested this patch and for the most part it works as intended. For
convenience, I wrote a small function to simulate the exceptions using a
given errcode:

CREATE OR REPLACE FUNCTION raise_sqlstate(state text)
RETURNS VOID AS $$
BEGIN
  RAISE EXCEPTION 'exception sqlstate = %',
    state USING ERRCODE = state;
END;
$$ LANGUAGE plpgsql;

== specific errcodes ==

SET log_suppress_errcodes TO '23505,3D000,42601';
SHOW log_suppress_errcodes;

SELECT raise_sqlstate('23505'); -- must be suppressed
SELECT raise_sqlstate('3D000'); -- must be suppressed
SELECT raise_sqlstate('42601'); -- must be suppressed
SELECT raise_sqlstate('42P01');

log entries:
2025-03-07 14:23:30.688 CET [3266008] ERROR:  exception sqlstate = 42P01
2025-03-07 14:23:30.688 CET [3266008] CONTEXT:  PL/pgSQL function
raise_sqlstate(text) line 3 at RAISE
2025-03-07 14:23:30.688 CET [3266008] STATEMENT:  SELECT
raise_sqlstate('42P01');


== errcode classes ==

SET log_suppress_errcodes TO '23000,3D000';
SHOW log_suppress_errcodes;

SELECT raise_sqlstate('23505'); -- must be suppressed
SELECT raise_sqlstate('42P01');


log entries:
2025-03-07 14:24:40.023 CET [3268343] ERROR:  exception sqlstate = 42P01
2025-03-07 14:24:40.023 CET [3268343] CONTEXT:  PL/pgSQL function
raise_sqlstate(text) line 3 at RAISE
2025-03-07 14:24:40.023 CET [3268343] STATEMENT:  SELECT
raise_sqlstate('42P01');



There are a few issues though ...

1) Invalid codes aren't rejected. Is there any way to validate them?

postgres=# SET log_suppress_errcodes TO '0foo1'; SHOW log_suppress_errcodes;
SET
 log_suppress_errcodes
-----------------------
 0foo1
(1 row)


2) No duplication check (not critical IMO)

postgres=# SET log_suppress_errcodes TO '3F000,3F000'; SHOW
log_suppress_errcodes;
SET
 log_suppress_errcodes
-----------------------
 3F000,3F000
(1 row)


3) errcodes are not trimmed (also not critical..  just doesn't look nice)

postgres=# SET log_suppress_errcodes TO '               3F000, 42P01';
SHOW log_suppress_errcodes;
SET
    log_suppress_errcodes    
-----------------------------
                3F000, 42P01
(1 row)

4) SHOW log_suppress_errcodes displays an invalid value if we set it
twice to an empty string

$ /usr/local/postgres-dev/bin/psql postgres
psql (18devel)
Type "help" for help.

postgres=# SET log_suppress_errcodes TO ''; SHOW log_suppress_errcodes;
SET log_suppress_errcodes TO ''; SHOW log_suppress_errcodes;
SET
 log_suppress_errcodes
-----------------------
 
(1 row)

SET
 log_suppress_errcodes
-----------------------
 wV
(1 row)


5) The system crashes if we set log_suppress_errcodesto an empty string
and then set it back to comma separated values

$ /usr/local/postgres-dev/bin/psql postgres
psql (18devel)
Type "help" for help.

postgres=# SET log_suppress_errcodes TO '3F000,42883'; SHOW
log_suppress_errcodes;
SET log_suppress_errcodes TO ''; SHOW log_suppress_errcodes;
SET log_suppress_errcodes TO '42P01,23505'; SHOW log_suppress_errcodes;
SET log_suppress_errcodes TO '3D000,42704'; SHOW log_suppress_errcodes;
SET
 log_suppress_errcodes
-----------------------
 3F000,42883
(1 row)

SET
 log_suppress_errcodes
-----------------------
 
(1 row)

SET
 log_suppress_errcodes
-----------------------
 42P01,23505
(1 row)

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
The connection to the server was lost. Attempting reset: Failed.
You are currently not connected to a database.

!?>


Best regards, Jim