citusdata
pg_cron writing every client_min_messages to log file
Based on the 'client_min_messages' parameter in postgresql.conf, all functions that have RAISE something have their messages written to the file specified in 'log_filename', regardless of the parameter log_min_messages = warning.
Example: if I have client_min_messages = notice, all functions executed via pg_cron job that have RAISE INFO 'hello' or RAISE NOTICE 'hello', have the RAISE parameters saved in the file log_filename, even if log_min_messages = warning. It would be interesting to have a way to disable this, since the messages in 'client_min_messages' should only appear on the screen when the functions are executed by the user, not by pg_cron.
Yep!
That is really annoying.
E.g. I have a plsql block that executes every 10 seconds. It writes tons of notice message, all of them appearing in my log making it almost a TB per during the day.
Although I have set "log_min_messages" to "warning".
This is not a bug in pg_cron, but expected behavior for Postgres.
| Severity | Usage |
|---|---|
| DEBUG1 .. DEBUG5 | Provides successively-more-detailed information for use by developers. |
| INFO | Provides information implicitly requested by the user, e.g., output from VACUUM VERBOSE. |
| NOTICE | Provides information that might be helpful to users, e.g., notice of truncation of long identifiers. |
| WARNING | Provides warnings of likely problems, e.g., COMMIT outside a transaction block. |
| ERROR | Reports an error that caused the current command to abort. |
| LOG | Reports information of interest to administrators, e.g., checkpoint activity. |
| FATAL | Reports an error that caused the current session to abort. |
| PANIC | Reports an error that caused all database sessions to abort. |
Each logging level includes all subsequent levels as well.
For example, WARNING encompasses ERROR, LOG, FATAL, and PANIC because it sits above them in the hierarchy.
If you do not want to log RAISE LOG values, you'll have to set log_min_messages to FATAL or PANIC. Alternatively, you could also just use RAISE NOTICE instead.
I am also annoyed by this functionality and I'm not sure you've addressed the issue @TheOtherBrian1.
For example, currently, I have log_min_messages = WARNING which means that NOTICE, INFO etc should NOT show up in the logs. But they do; though only when a function containing a RAISE NOTICE/INFO is run by pg_cron (and not any other time). This is because pg_cron seems to be circumventing the log_min_messages parameter and using instead the client_min_messages instead, which normally needs to be set to something like NOTICE because its determining what gets sent to the client.
The proper behavior should be that jobs run on an automated basis should be honoring the log_min_messages setting, even if behind the scenes they are running as clients (or whatever the reason is for the current behavior).
@alexitheodore, what platform are you using? Supabase, Aiven, etc? The reason I ask is because this may be a side-effect of a permission granting extension, such as aiven-pg-security or supautils. Does it happen in regular functions, too, or just ones executed by pg_cron?
@TheOtherBrian1 I am using an old school build. pgv13 on Ubuntu. Does not happen on regular functions - just pg_cron.
@alexitheodore, that removes a lot of the noise. This does appear to be a bug