Re: Reducing the log spam - Mailing list pgsql-hackers

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