Kaarel Moppel: Logging of data modifications and the “log_statement” configuration parameter

Datetime:2016-08-23 02:18:15          Topic: PostgreSQL           Share

PostgreSQL has a bagful of server configuration parameters (249 according to my counting for version 9.5) at your disposal, which mostly is a good thing as it enables to take the maximum out of your hardware if you’re willing to put in the necessary time. But some of the parameters might leave the door open for misinterpretation when one hasn’t looked into the documentation properly. So here’s a short reminder on 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:

#log_statement = 'none'      # none, ddl, mod, all

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:

SELECT * FROM increment_login_count();

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!

Kaarel Moppel

I’ve been interested with databases for the last 9 years, working last 5 years exclusively with PostgreSQL. And still I’m constantly surprised by it’s powerful set of features and the fast pace of development by the globally friendly community. On my spare time I enjoy playing soccer and travelling.





About List