Dmitry Romanoff
Posted on March 11, 2023
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.
💖 💪 🙅 🚩
Dmitry Romanoff
Posted on March 11, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.