DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on

2

PostgreSQL parameter log_statement

#devops #database #postgres #sql

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.

profile
Stellar Development Foundation
 Promoted

Image of Stellar post

From Hackathon to Funded - Stellar Dev Diaries Ep. 1 🎥

Ever wondered what it takes to go from idea to funding? In episode 1 of the Stellar Dev Diaries, we hear how the Freelii team did just that. Check it out and follow along to see the rest of their dev journey!

Watch the video

Top comments (0)

profile
DreamFactory
 Promoted

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Explore a trove of insights in this engaging article, celebrated within our welcoming DEV Community. Developers from every background are invited to join and enhance our shared wisdom.

A genuine "thank you" can truly uplift someone’s day. Feel free to express your gratitude in the comments below!

On DEV, our collective exchange of knowledge lightens the road ahead and strengthens our community bonds. Found something valuable here? A small thank you to the author can make a big difference.

Okay