Subject: Re: Truncate logs by max_log_size

From: "Euler Taveira" <[email-masked]>
Date: Thu, 26 Sep 2024 21:30:05 -0300

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).

--
Euler Taveira
EDB   https://www.enterprisedb.com/

---

From: "Andrey M. Borodin" <[email-masked]>
Date: Fri, 27 Sep 2024 13:36:58 +0300

> On 27 Sep 2024, at 03:30, Euler Taveira <[email-masked]> 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 against heavy abuse.

The feature looks very important for me.

Best regards, Andrey Borodin.

---

From: Jim Jones <[email-masked]>
Date: Tue, 1 Oct 2024 13:46:44 +0200

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.
>
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