Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Thu, 26 Sep 2024 21:30:05 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: diPhantxm <diphantxm@gmail.com>, pgsql-hackers@lists.postgresql.org
Message-Id: <94d53a41-d1f5-4fd1-8d1e-ff36ac569848@app.fastmail.com>
In-Reply-To: 
 <CA+E0NR4S+NC6+QHyY_vUuQZMzLhKqczMx-jJVqtjAxF6+=JwAA@mail.gmail.com>
References: 
 <CA+E0NR4S+NC6+QHyY_vUuQZMzLhKqczMx-jJVqtjAxF6+=JwAA@mail.gmail.com>
Subject: Re: Truncate logs by max_log_size
Content-Type: multipart/alternative;
 boundary=dd500bf3a9cd451d800441a1b9285d20
Archived-At: <https://www.postgresql.org/message-id/94d53a41-d1f5-4fd1-8d1e-ff36ac569848%40app.fastmail.com>
Precedence: bulk

--dd500bf3a9cd451d800441a1b9285d20
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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/

--dd500bf3a9cd451d800441a1b9285d20
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>On Thu, Sep 26,=
 2024, at 3:30 PM, diPhantxm wrote:<br></div><blockquote type=3D"cite" i=
d=3D"qt" style=3D""><div dir=3D"ltr"><div>I would like to suggest a patc=
h 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 truncate=
d.<br></div></div></blockquote><div><br></div><div>I don't know how usef=
ul is this proposal. IMO the whole query is usually<br></div><div>crucia=
l for an analysis. Let's say you arbitrarily provide max_log_size =3D 10=
0<br></div><div>but it means you cannot see a WHERE clause and you have =
a performance issue in<br></div><div>that query. It won't be possible to=
 obtain the whole query for an EXPLAIN. It<br></div><div>would break aud=
it systems that requires the whole query. I don't know if there<br></div=
><div>are some log-based replication systems but it would break such too=
ls too.<br></div><div><br></div><div>There are other ways to avoid loggi=
ng such long queries. The GRANT ... ON<br></div><div>PARAMETER and SET L=
OCAL commands are your friends. Hence, you can disable<br></div><div>spe=
cific long queries even if you are not a superuser.<br></div><div><br></=
div><div>If your main problem is disk space, you can adjust the rotation=
 settings or have<br></div><div>an external tool to manage your log file=
s (or even use syslog).<br></div><div><br></div><div><br></div><div id=3D=
"sig112409080"><div class=3D"signature">--<br></div><div class=3D"signat=
ure">Euler Taveira<br></div><div class=3D"signature">EDB&nbsp;&nbsp; <a =
href=3D"https://www.enterprisedb.com/">https://www.enterprisedb.com/</a>=
<br></div></div><div><br></div></body></html>
--dd500bf3a9cd451d800441a1b9285d20--