Logging of data modifications and the “log_statement” configuration parameter

By Kaarel Moppel - PostgreSQL has a bagful of server configuration parameters (249 according to my counting for version 9.5) at your disposal, which is mostly a good thing, as it enables you to take the maximum out of your hardware, if you’re willing to put in the necessary time. However, some of the parameters might leave the door open for misinterpretation, if you haven’t looked into the documentation properly. So here’s a short reminder about the behavior of the “log_statement” parameter’s “mod” mode, as I’ve seen it misinterpreted when trying to track down how some table rows got modified during the development phase.

When you’re editing the “postgresql.conf” file for your newly created database cluster, under “What to Log” section you’ll see something like that:

Looking at the four possible options, it’s easy to think that setting it to “mod” will log all statements doing data modifications on your tables... but not quite so. It actually applies more to the statement type, covering INSERT, UPDATE, DELETE, TRUNCATE and some more exotic options. So what about a common case of calling a simple stored procedure that updates a row for example? Something like:

Will something be logged then? Nope. As it is a SELECT statement. In this case setting log_statements=‘all’ would do the trick, but most of the time you would want to avoid that for “production” use.

Baseline

In short - it’s only possible to log top level statements with everything happening down the stream (triggers, stored procedures code, CTE parts doing data modification) not logged. So one cannot think about PostgreSQL logging as “log all changes done to table rows” (this could generate billions of log lines easily) but rather “log statements issued by the client”. The parameter name “log_statement” actually hints at that also.

Have a nice day!

In case you need any assistance, please feel free to contact us.
 

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.