Re: Reducing the log spam

On Thu, May 02, 2024 at 12:47:45PM +0200, Laurenz Albe wrote:
> On Mon, 2024-03-11 at 09:33 +0100, Jelte Fennema-Nio wrote:
> > -   the subscriber's server log.
> > +   the subscriber's server log if you remove <literal>23505</literal> from
> > +   <xref linkend="guc-log-suppress-errcodes"/>.
> >
> > This seems like a pretty big regression. Being able to know why your
> > replication got closed seems pretty critical.
>
> Yes. But I'd argue that that is a shortcoming of logical replication:
> there should be a ways to get this information via SQL. Having to look into
> the log file is not a very useful option.
>
> The feature will become much less useful if unique voilations keep getting logged.

Uh, to be clear, your patch is changing the *defaults*, which I found
surprising, even after reaading the thread. Evidently, the current
behavior is not what you want, and you want to change it, but I'm *sure*
that whatever default you want to use at your site/with your application
is going to make someone else unhappy. I surely want unique violations
to be logged, for example.

> @@ -6892,6 +6892,41 @@ local0.* /var/log/postgresql
> </listitem>
> </varlistentry>
>
> + <varlistentry id="guc-log-suppress-errcodes" xreflabel="log_suppress_errcodes">
> + <term><varname>log_suppress_errcodes</varname> (<type>string</type>)
> + <indexterm>
> + <primary><varname>log_suppress_errcodes</varname> configuration parameter</primary>
> + </indexterm>
> + </term>
> + <listitem>
> + <para>
> + Causes <literal>ERROR</literal> and <literal>FATAL</literal> messages
> + from client backend processes with certain error codes to be excluded
> + from the log.
> + The value is a comma-separated list of five-character error codes as
> + listed in <xref linkend="errcodes-appendix"/>. An error code that
> + represents a class of errors (ends with three zeros) suppresses logging
> + of all error codes within that class. For example, the entry
> + <literal>08000</literal> (<literal>connection_exception</literal>)
> + would suppress an error with code <literal>08P01</literal>
> + (<literal>protocol_violation</literal>). The default setting is
> + <literal>23505,3D000,3F000,42601,42704,42883,42P01,57P03</literal>.
> + Only superusers and users with the appropriate <literal>SET</literal>
> + privilege can change this setting.
> + </para>

> +
> + <para>
> + This setting is useful to exclude error messages from the log that are
> + frequent but irrelevant.

I think you should phrase the feature as ".. *allow* skipping error
logging for messages ... that are frequent but irrelevant for a given
site/role/DB/etc."

I suggest that this patch should not change the default behavior at all:
its default should be empty. That you, personally, would plan to
exclude this or that error code is pretty uninteresting. I think the
idea of changing the default behavior will kill the patch, and even if
you want to propose to do that, it should be a separate discussion.
Maybe you should make it an 002 patch.

> + {"log_suppress_errcodes", PGC_SUSET, LOGGING_WHEN,
> + gettext_noop("ERROR and FATAL messages with these error codes don't get logged."),
> + NULL,
> + GUC_LIST_INPUT
> + },
> + &log_suppress_errcodes,
> + DEFAULT_LOG_SUPPRESS_ERRCODES,
> + check_log_suppress_errcodes, assign_log_suppress_errcodes, NULL

> +/*
> + * Default value for log_suppress_errcodes. ERROR or FATAL messages with
> + * these error codes are never logged. Error classes (error codes ending with
> + * three zeros) match all error codes in the class. The idea is to suppress
> + * messages that usually don't indicate a serious problem but tend to pollute
> + * the log file.
> + */
> +
> +#define DEFAULT_LOG_SUPPRESS_ERRCODES "23505,3D000,3F000,42601,42704,42883,42P01,57P03"
> +

../src/backend/utils/errcodes.txt:23505 E ERRCODE_UNIQUE_VIOLATION unique_violation
../src/backend/utils/errcodes.txt:3D000 E ERRCODE_INVALID_CATALOG_NAME invalid_catalog_name
../src/backend/utils/errcodes.txt:3F000 E ERRCODE_INVALID_SCHEMA_NAME invalid_schema_name
../src/backend/utils/errcodes.txt:42601 E ERRCODE_SYNTAX_ERROR syntax_error
../src/backend/utils/errcodes.txt:3D000 E ERRCODE_UNDEFINED_DATABASE
../src/backend/utils/errcodes.txt:42883 E ERRCODE_UNDEFINED_FUNCTION undefined_function
../src/backend/utils/errcodes.txt:3F000 E ERRCODE_UNDEFINED_SCHEMA
../src/backend/utils/errcodes.txt:42P01 E ERRCODE_UNDEFINED_TABLE undefined_table
../src/backend/utils/errcodes.txt:42704 E ERRCODE_UNDEFINED_OBJECT undefined_object
../src/backend/utils/errcodes.txt:57P03 E ERRCODE_CANNOT_CONNECT_NOW cannot_connect_now

--
Justin