PostgreSQL parameter log_statement
Tags: devops, database, postgres, sql
Published: 2023-03-11 19:42:12

The `log_statement` is configuration parameter of PostgreSQL.

It controls which SQL statements are logged.

The valid values are 

none [ default ] - no statements logged
ddl - logs all DDL data definition statements, for example CREATE, ALTER, and DROP statements
mod - logs all DDL data definition statements plus DML data modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM, PREPARE, EXECUTE, EXPLAIN ANALYZE
all - logs all statements

Usage examples.

`Example #1. Check out the PostgreSQL parameter log_statement set to ddl.`

```
postgres=# set log_statement='ddl';
SET
postgres=# show log_statement;
 log_statement
---------------
 ddl
(1 row)

postgres=# create table some_table(a varchar(200));
CREATE TABLE
postgres=# insert into some_table values('aaaaa');
INSERT 0 1
postgres=# explain select * from some_table;
                          QUERY PLAN
---------------------------------------------------------------
 Seq Scan on some_table  (cost=0.00..11.80 rows=180 width=418)
(1 row)

postgres=# set log_min_duration_statement=-1;
SET
postgres=# create table some_table_1(a varchar(200));
CREATE TABLE
postgres=# insert into some_table_1 values('aaaaa');
INSERT 0 1
postgres=# explain select * from some_table_1;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on some_table_1  (cost=0.00..11.80 rows=180 width=418)
(1 row)
```

Postgres log file (fragment):

```
...
2023-03-11 18:50:25.840 UTC [119] LOG:  statement: create table some_table_1(a varchar(200));
...
```

`Example #2. Check out the PostgreSQL parameter log_statement set to mod.`

```
postgres=#  set log_statement='mod';
SET
postgres=# show log_statement;
 log_statement
---------------
 mod
(1 row)

postgres=# create table some_table_2(a varchar(200));
CREATE TABLE
postgres=# insert into some_table_2 values('aaaaa');
INSERT 0 1
postgres=# explain select * from some_table_2;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on some_table_2  (cost=0.00..11.80 rows=180 width=418)
(1 row)

postgres=#

postgres=# explain analyze select * from some_table_2;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on some_table_2  (cost=0.00..11.80 rows=180 width=418) (actual time=0.044..0.045 rows=1 loops=1)
 Planning Time: 0.040 ms
 Execution Time: 0.062 ms
(3 rows)

postgres=# explain (buffers, analyze) select * from some_table_2;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on some_table_2  (cost=0.00..11.80 rows=180 width=418) (actual time=0.013..0.015 rows=1 loops=1)
   Buffers: shared hit=1
 Planning Time: 0.049 ms
 Execution Time: 0.035 ms
(4 rows)

postgres=#

postgres=# insert into some_table_2 values('bbbbb');
INSERT 0 1
postgres=# explain insert into some_table_2 values('bbbbb');
                        QUERY PLAN
----------------------------------------------------------
 Insert on some_table_2  (cost=0.00..0.01 rows=0 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=418)
(2 rows)

postgres=# explain analyze insert into some_table_2 values('bbbbb');
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Insert on some_table_2  (cost=0.00..0.01 rows=0 width=0) (actual time=0.032..0.033 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=418) (actual time=0.001..0.002 rows=1 loops=1)
 Planning Time: 0.044 ms
 Execution Time: 0.056 ms
(4 rows)

postgres=# explain (buffers, analyze) insert into some_table_2 values('bbbbb');
                                             QUERY PLAN
----------------------------------------------------------------------------------------------------
 Insert on some_table_2  (cost=0.00..0.01 rows=0 width=0) (actual time=0.029..0.029 rows=0 loops=1)
   Buffers: shared hit=1
   ->  Result  (cost=0.00..0.01 rows=1 width=418) (actual time=0.002..0.002 rows=1 loops=1)
 Planning Time: 0.041 ms
 Execution Time: 0.051 ms
(5 rows)

postgres=#
```

Postgres log file (fragment):

```
...
2023-03-11 18:55:16.078 UTC [119] LOG:  statement: create table some_table_2(a varchar(200));
2023-03-11 18:55:28.726 UTC [119] LOG:  statement: insert into some_table_2 values('aaaaa');
2023-03-11 19:06:47.068 UTC [119] LOG:  statement: insert into some_table_2 values('bbbbb');
2023-03-11 19:07:03.168 UTC [119] LOG:  statement: explain analyze insert into some_table_2 values('bbbbb');
2023-03-11 19:07:10.745 UTC [119] LOG:  statement: explain (buffers, analyze) insert into some_table_2 values('bbbbb');
...
```

`Example #3. Check out the PostgreSQL parameter log_statement set to all.`

```
postgres=# set log_statement='all';
SET
postgres=# create table some_table_3(a varchar(100));
CREATE TABLE
postgres=# insert into some_table_3 values('aaaaa');
INSERT 0 1
postgres=# update some_table_3 set a='bbbbb';
UPDATE 1
postgres=# alter table some_table_3 add column b varchar(100);
ALTER TABLE
postgres=# explain select * from some_table_3;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on some_table_3  (cost=0.00..11.70 rows=170 width=436)
(1 row)

postgres=# explain analyze select * from some_table_3;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on some_table_3  (cost=0.00..11.70 rows=170 width=436) (actual time=0.017..0.019 rows=1 loops=1)
 Planning Time: 0.053 ms
 Execution Time: 0.041 ms
(3 rows)

postgres=# explain (buffers, analyze) select * from some_table_3;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on some_table_3  (cost=0.00..11.70 rows=170 width=436) (actual time=0.014..0.016 rows=1 loops=1)
   Buffers: shared hit=1
 Planning Time: 0.054 ms
 Execution Time: 0.039 ms
(4 rows)

postgres=#
```

Postgres log file (fragment):

```
...
2023-03-11 19:10:53.775 UTC [119] LOG:  statement: create table some_table_3(a varchar(100));
2023-03-11 19:11:03.991 UTC [119] LOG:  statement: insert into some_table_3 values('aaaaa');
2023-03-11 19:11:16.799 UTC [119] LOG:  statement: update some_table set a='bbbbb';
2023-03-11 19:11:34.159 UTC [119] LOG:  statement: alter table some_table_3 add column b varchar(100);
2023-03-11 19:11:52.184 UTC [119] LOG:  statement: explain select * from some_table_3;
2023-03-11 19:11:57.567 UTC [119] LOG:  statement: explain analyze select * from some_table_3;
2023-03-11 19:12:06.047 UTC [119] LOG:  statement: explain (buffers, analyze) select * from some_table_3;
...
```

`Example #4. Check out the PostgreSQL parameter log_statement set to none.`

```
postgres=# set log_statement='none';
SET

postgres=# create table some_table_4(a varchar(100));
CREATE TABLE
postgres=# update some_table_4 set a='bbbbb';
UPDATE 0
postgres=# alter table some_table_4  add column b varchar(100);
ALTER TABLE
postgres=# explain select * from some_table_4;
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on some_table_4  (cost=0.00..11.70 rows=170 width=436)
(1 row)

postgres=# select * from some_table_4;
 a | b
---+---
(0 rows)

postgres=#
```

Postgres log file (fragment):

There is no information about the queries.