Thread: Re: Truncate logs by max_log_size
On Thu, Sep 26, 2024, at 3:30 PM, diPhantxm wrote:
I would like to suggest a patch to truncate some long queries. I believe sometimes there is no need to log a query containing some gigabytes of blob, for example. In patch a new parameter, named max_log_size, is introduced. It defines the maximum size of logged query, in bytes. Everything beyond that size is truncated.
I don't know how useful is this proposal. IMO the whole query is usually
crucial for an analysis. Let's say you arbitrarily provide max_log_size = 100
but it means you cannot see a WHERE clause and you have a performance issue in
that query. It won't be possible to obtain the whole query for an EXPLAIN. It
would break audit systems that requires the whole query. I don't know if there
are some log-based replication systems but it would break such tools too.
There are other ways to avoid logging such long queries. The GRANT ... ON
PARAMETER and SET LOCAL commands are your friends. Hence, you can disable
specific long queries even if you are not a superuser.
If your main problem is disk space, you can adjust the rotation settings or have
an external tool to manage your log files (or even use syslog).
> On 27 Sep 2024, at 03:30, Euler Taveira <euler@eulerto.com> wrote: > > Let's say you arbitrarily provide max_log_size = 100 Consider max_log_size = 10Mb. The perspective might look very different. It’s not about WHERE anymore. It's a guard againstheavy abuse. The feature looks very important for me. Best regards, Andrey Borodin.
On 27.09.24 12:36, Andrey M. Borodin wrote:
> Consider max_log_size = 10Mb. The perspective might look very different.
> It’s not about WHERE anymore. It's a guard against heavy abuse.
>
> The feature looks very important for me.
I have the same opinion. As a fail safe it sounds very useful to me.
Unfortunately, the patch does not apply:
$ git apply
~/patches/max_log_query/0001-parameter-max_log_size-to-truncate-logs.patch
-v
Checking patch src/backend/utils/error/elog.c...
error: while searching for:
char *Log_destination_string = NULL;
bool syslog_sequence_numbers = true;
bool syslog_split_messages = true;
/* Processed form of backtrace_symbols GUC */
static char *backtrace_symbol_list;
error: patch failed: src/backend/utils/error/elog.c:114
error: src/backend/utils/error/elog.c: patch does not apply
Checking patch src/backend/utils/misc/guc_tables.c...
Hunk #1 succeeded at 3714 (offset 247 lines).
Checking patch src/backend/utils/misc/postgresql.conf.sample...
Hunk #1 succeeded at 615 (offset 23 lines).
Checking patch src/bin/pg_ctl/t/004_logrotate.pl...
error: while searching for:
check_log_pattern('csvlog', $new_current_logfiles, 'syntax error', $node);
check_log_pattern('jsonlog', $new_current_logfiles, 'syntax error', $node);
$node->stop();
done_testing();
error: patch failed: src/bin/pg_ctl/t/004_logrotate.pl:135
error: src/bin/pg_ctl/t/004_logrotate.pl: patch does not apply
Checking patch src/include/utils/elog.h...
Hunk #1 succeeded at 502 (offset 1 line).
Does it truncate only single queries or also a transaction with many
statements?
Thanks!
Best, Jim
On Fri, Sep 27, 2024 at 6:37 AM Andrey M. Borodin <x4mmm@yandex-team.ru> wrote:
Consider max_log_size = 10Mb. The perspective might look very different. It’s not about WHERE anymore. It's a guard against heavy abuse.
Can you elaborate on this? Do you mean someone purposefully writing large entries to your log file?
On 01.10.24 17:46, Kirill Gavrilov wrote: > My apologies, attached patch should work on master branch. Nice. I tested the feature and it does what it is intended to. postgres=# SHOW max_log_size; max_log_size -------------- 10 (1 row) postgres=# INSERT INTO t VALUES (1,2,3,4,5,6,7,8,9,0); ERROR: relation "t" does not exist LINE 1: INSERT INTO t VALUES (1,2,3,4,5,6,7,8,9,0); Log file: 2024-10-02 00:57:13.618 CEST [1975926] ERROR: relation "t" does not exist at character 13 2024-10-02 00:57:13.618 CEST [1975926] STATEMENT: INSERT INT A few observations: 1) You missed a const qualifier in elog.c: elog.c: In function ‘EmitErrorReport’: elog.c:1699:29: warning: initialization discards ‘const’ qualifier from pointer target type [-Wdiscarded-qualifiers] 1699 | char* str = debug_query_string; | ^~~~~~~~~~~~~~~~~~ 2) The new parameter cannot be set within a session with SET. Is it supposed to be like this? IMHO it would be nice to able to temporarily set this parameter without having to reload the postgresql.conf postgres=# SET max_log_size TO 100; ERROR: parameter "max_log_size" cannot be changed now 3) I personally find -1 more intuitive than 0 to disable a parameter, but I have no strong opinion about it. 4) It still lacks documentation. -- Jim