Logging of data modifications and the “log_statement” configuration parameter

08.2016 / Category: / Tags: |

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.


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.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Douglas J Hunley
7 years ago

Doesn't 'track_functions' help with your second point?

7 years ago

Well not quite. Setting 'track_functions' would populate the "pg_stat_user_functions" view which would only store aggregate stats and would not affect writing to the log.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram