It happens quite frequently that PostgreSQL client applications are flooded with messages from the server. This is both annoying and bad for performance as well as network bandwidth. It seems that many users are not aware of the fact that this flood of log messages can easily be reduced by simply changing the server configuration.
Here is an example showing how log messages can reach the client:
|
1 2 3 4 5 6 7 8 9 |
test=# CREATE OR REPLACE FUNCTION foo() RETURNS void AS $$ BEGIN RAISE NOTICE 'some message'; RETURN; END; $$ LANGUAGE 'plpgsql'; CREATE FUNCTION |
In this example we issue a simple NOTICE. This message will end up in the log as well as on the client:
|
1 2 3 4 5 6 |
test=# SELECT foo(); NOTICE: some message foo ----- (1 row) |
Just imagine calling this function millions of times (which is perfectly realistic) - all those messages have to go somewhere.
Closing the flood gates …
Reducing the amount of log to a normal level is actually quite simple:
|
1 2 3 4 5 6 7 8 |
test=# SET client_min_messages TO 'ERROR'; SET test=# SELECT foo(); foo ----- (1 row) |
In our example client_min_messages makes sure that an error message has to be at least an ERROR to make it to the client application.
Of course, you can also configure that per user or per database:
|
1 2 3 4 5 |
test=# ALTER DATABASE test SET client_min_messages TO 'ERROR'; ALTER DATABASE test=# ALTER ROLE hs SET client_min_messages TO 'ERROR'; ALTER ROLE |
PostgreSQL allows a pretty fine-grained configuration here, which can be tweaked to meet your requirements.
Find out more related PostgreSQL hints and tips in our blog posts about setup and configuration.
+43 (0) 2622 93022-0
office@cybertec.at