PostgreSQL parameter log_statement

Dmitry Romanoff - Mar 11 '23 - - Dev Community

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)
Enter fullscreen mode Exit fullscreen mode

Postgres log file (fragment):

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

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=#
Enter fullscreen mode Exit fullscreen mode

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');
...
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

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;
...
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

Postgres log file (fragment):

There is no information about the queries.

Image
Dive into Hadoop: Mastering the Hadoop Practice Labs Course

labex, hadoop, programming, course
Image
Top English Teacher in Kolkata - Trust Educator | Call now- +91-9874593539
Image
Do You Have a Website? Are You Worried About Hackers Attacking It?

cybersecurity, webdev, opensource, waf
Image
How to Horizontally Scale Amazon ElastiCache Instance

aws, elasticache, autoscaling
Image
Remote Team Building: Nurturing Connections in a Virtual World

career, learning, tutorial, beginners
Image
Why I Think Django Rest Framework is the Best for Developing Robust APIs
Image
What is Laparoscopic Surgery used for in Infertility Treatment?

infertilitytreatment, infertility, gynecologistdoctor
Image
OAuth 2 Token Exchange with Spring Security and Keycloak

oauth2, tokenexchange, springsecurity, keycloak
Image
Common Mistakes Developers Make with useState in React (And How to Fix Them)

javascript, webdev, react, tutorial
Image
So... I created yet another Neovim plugin

neovim, lua
Image
마추자 코드 【마추자주소.COM 코드BB77】 땅콩가입

마추자코드, 땅콩가입
Image
6 NRG Steering Wheel Safety Tips Every Driver Should Follow
Image
How to Create a Blazor Web App for Document PDF Viewing and Annotation

webdev, dotnet, blazor, pdf
Image
마추자 경찰 X 마추자벳.COM 코드:BB77 토토재판소

마추자경찰x, 토토재판소
Image
How to accidentally create read-only DynamoDB items

dynamodb, aws, cloud
Image
SQL, extract unique values of JSON format field from each group #eg42

sql, json, programming, esproc
Image
맞추자토토 【마추자벳.COM 코드:BB77】 땅콩추천인

맞추자토토, 땅콩추천인
Image
Beware the Language-as-Fixed-Effect Fallacy: Rethinking Claims about GPT-4's Capabilities

machinelearning, ai, beginners, datascience
Image
Unlocking language models' potential through synthetic pretraining

machinelearning, ai, beginners, datascience
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player