mirror of
https://github.com/postgres/postgres.git
synced 2025-04-18 13:44:19 +03:00
ce5bcc4a9f
wal_buffers_full tracks the number of times WAL buffers become full, giving hints to be able to tune the GUC wal_buffers. Up to now, this information was only available in pg_stat_wal. With this field available in WalUsage since eaf502747bac, exposing it in pg_stat_statements is straight-forward, and it offers more granularity at query level. pg_stat_statements does not need a version bump as one has been done in commit cf54a2c00254 for this development cycle. Author: Bertrand Drouvot Reviewed-by: Ilia Evdokimov Discussion: https://postgr.es/m/Z6SOha5YFFgvpwQY@ip-10-97-1-34.eu-west-3.compute.internal
411 lines
25 KiB
Plaintext
411 lines
25 KiB
Plaintext
-- test old extension version entry points
|
|
CREATE EXTENSION pg_stat_statements WITH VERSION '1.4';
|
|
-- Execution of pg_stat_statements_reset() is granted only to
|
|
-- superusers in 1.4, so this fails.
|
|
SET SESSION AUTHORIZATION pg_read_all_stats;
|
|
SELECT pg_stat_statements_reset();
|
|
ERROR: permission denied for function pg_stat_statements_reset
|
|
RESET SESSION AUTHORIZATION;
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.5';
|
|
-- Execution of pg_stat_statements_reset() should be granted to
|
|
-- pg_read_all_stats now, so this works.
|
|
SET SESSION AUTHORIZATION pg_read_all_stats;
|
|
SELECT pg_stat_statements_reset();
|
|
pg_stat_statements_reset
|
|
--------------------------
|
|
|
|
(1 row)
|
|
|
|
RESET SESSION AUTHORIZATION;
|
|
-- In 1.6, it got restricted back to superusers.
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.6';
|
|
SET SESSION AUTHORIZATION pg_read_all_stats;
|
|
SELECT pg_stat_statements_reset();
|
|
ERROR: permission denied for function pg_stat_statements_reset
|
|
RESET SESSION AUTHORIZATION;
|
|
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
|
|
pg_get_functiondef
|
|
-------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset() +
|
|
RETURNS void +
|
|
LANGUAGE c +
|
|
PARALLEL SAFE +
|
|
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset$function$+
|
|
|
|
(1 row)
|
|
|
|
-- New function for pg_stat_statements_reset introduced, still
|
|
-- restricted for non-superusers.
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.7';
|
|
SET SESSION AUTHORIZATION pg_read_all_stats;
|
|
SELECT pg_stat_statements_reset();
|
|
ERROR: permission denied for function pg_stat_statements_reset
|
|
RESET SESSION AUTHORIZATION;
|
|
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
|
|
pg_get_functiondef
|
|
--------------------------------------------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
|
|
RETURNS void +
|
|
LANGUAGE c +
|
|
PARALLEL SAFE STRICT +
|
|
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$ +
|
|
|
|
(1 row)
|
|
|
|
SELECT pg_stat_statements_reset();
|
|
pg_stat_statements_reset
|
|
--------------------------
|
|
|
|
(1 row)
|
|
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
---------------------+------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
calls | bigint | | |
|
|
total_time | double precision | | |
|
|
min_time | double precision | | |
|
|
max_time | double precision | | |
|
|
mean_time | double precision | | |
|
|
stddev_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
blk_read_time | double precision | | |
|
|
blk_write_time | double precision | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- New functions and views for pg_stat_statements in 1.8
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.8';
|
|
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
|
|
pg_get_functiondef
|
|
--------------------------------------------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0)+
|
|
RETURNS void +
|
|
LANGUAGE c +
|
|
PARALLEL SAFE STRICT +
|
|
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_7$function$ +
|
|
|
|
(1 row)
|
|
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
---------------------+------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
plans | bigint | | |
|
|
total_plan_time | double precision | | |
|
|
min_plan_time | double precision | | |
|
|
max_plan_time | double precision | | |
|
|
mean_plan_time | double precision | | |
|
|
stddev_plan_time | double precision | | |
|
|
calls | bigint | | |
|
|
total_exec_time | double precision | | |
|
|
min_exec_time | double precision | | |
|
|
max_exec_time | double precision | | |
|
|
mean_exec_time | double precision | | |
|
|
stddev_exec_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
blk_read_time | double precision | | |
|
|
blk_write_time | double precision | | |
|
|
wal_records | bigint | | |
|
|
wal_fpi | bigint | | |
|
|
wal_bytes | numeric | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- New function pg_stat_statement_info, and new function
|
|
-- and view for pg_stat_statements introduced in 1.9
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.9';
|
|
SELECT pg_get_functiondef('pg_stat_statements_info'::regproc);
|
|
pg_get_functiondef
|
|
-------------------------------------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone)+
|
|
RETURNS record +
|
|
LANGUAGE c +
|
|
PARALLEL SAFE STRICT +
|
|
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_info$function$ +
|
|
|
|
(1 row)
|
|
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
---------------------+------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
toplevel | boolean | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
plans | bigint | | |
|
|
total_plan_time | double precision | | |
|
|
min_plan_time | double precision | | |
|
|
max_plan_time | double precision | | |
|
|
mean_plan_time | double precision | | |
|
|
stddev_plan_time | double precision | | |
|
|
calls | bigint | | |
|
|
total_exec_time | double precision | | |
|
|
min_exec_time | double precision | | |
|
|
max_exec_time | double precision | | |
|
|
mean_exec_time | double precision | | |
|
|
stddev_exec_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
blk_read_time | double precision | | |
|
|
blk_write_time | double precision | | |
|
|
wal_records | bigint | | |
|
|
wal_fpi | bigint | | |
|
|
wal_bytes | numeric | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- New functions and views for pg_stat_statements in 1.10
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.10';
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
------------------------+------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
toplevel | boolean | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
plans | bigint | | |
|
|
total_plan_time | double precision | | |
|
|
min_plan_time | double precision | | |
|
|
max_plan_time | double precision | | |
|
|
mean_plan_time | double precision | | |
|
|
stddev_plan_time | double precision | | |
|
|
calls | bigint | | |
|
|
total_exec_time | double precision | | |
|
|
min_exec_time | double precision | | |
|
|
max_exec_time | double precision | | |
|
|
mean_exec_time | double precision | | |
|
|
stddev_exec_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
blk_read_time | double precision | | |
|
|
blk_write_time | double precision | | |
|
|
temp_blk_read_time | double precision | | |
|
|
temp_blk_write_time | double precision | | |
|
|
wal_records | bigint | | |
|
|
wal_fpi | bigint | | |
|
|
wal_bytes | numeric | | |
|
|
jit_functions | bigint | | |
|
|
jit_generation_time | double precision | | |
|
|
jit_inlining_count | bigint | | |
|
|
jit_inlining_time | double precision | | |
|
|
jit_optimization_count | bigint | | |
|
|
jit_optimization_time | double precision | | |
|
|
jit_emission_count | bigint | | |
|
|
jit_emission_time | double precision | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- New functions and views for pg_stat_statements in 1.11
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.11';
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
------------------------+--------------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
toplevel | boolean | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
plans | bigint | | |
|
|
total_plan_time | double precision | | |
|
|
min_plan_time | double precision | | |
|
|
max_plan_time | double precision | | |
|
|
mean_plan_time | double precision | | |
|
|
stddev_plan_time | double precision | | |
|
|
calls | bigint | | |
|
|
total_exec_time | double precision | | |
|
|
min_exec_time | double precision | | |
|
|
max_exec_time | double precision | | |
|
|
mean_exec_time | double precision | | |
|
|
stddev_exec_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
shared_blk_read_time | double precision | | |
|
|
shared_blk_write_time | double precision | | |
|
|
local_blk_read_time | double precision | | |
|
|
local_blk_write_time | double precision | | |
|
|
temp_blk_read_time | double precision | | |
|
|
temp_blk_write_time | double precision | | |
|
|
wal_records | bigint | | |
|
|
wal_fpi | bigint | | |
|
|
wal_bytes | numeric | | |
|
|
jit_functions | bigint | | |
|
|
jit_generation_time | double precision | | |
|
|
jit_inlining_count | bigint | | |
|
|
jit_inlining_time | double precision | | |
|
|
jit_optimization_count | bigint | | |
|
|
jit_optimization_time | double precision | | |
|
|
jit_emission_count | bigint | | |
|
|
jit_emission_time | double precision | | |
|
|
jit_deform_count | bigint | | |
|
|
jit_deform_time | double precision | | |
|
|
stats_since | timestamp with time zone | | |
|
|
minmax_stats_since | timestamp with time zone | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
-- New parameter minmax_only of pg_stat_statements_reset function
|
|
SELECT pg_get_functiondef('pg_stat_statements_reset'::regproc);
|
|
pg_get_functiondef
|
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|
|
CREATE OR REPLACE FUNCTION public.pg_stat_statements_reset(userid oid DEFAULT 0, dbid oid DEFAULT 0, queryid bigint DEFAULT 0, minmax_only boolean DEFAULT false)+
|
|
RETURNS timestamp with time zone +
|
|
LANGUAGE c +
|
|
PARALLEL SAFE STRICT +
|
|
AS '$libdir/pg_stat_statements', $function$pg_stat_statements_reset_1_11$function$ +
|
|
|
|
(1 row)
|
|
|
|
SELECT pg_stat_statements_reset() IS NOT NULL AS t;
|
|
t
|
|
---
|
|
t
|
|
(1 row)
|
|
|
|
-- New functions and views for pg_stat_statements in 1.12
|
|
AlTER EXTENSION pg_stat_statements UPDATE TO '1.12';
|
|
\d pg_stat_statements
|
|
View "public.pg_stat_statements"
|
|
Column | Type | Collation | Nullable | Default
|
|
----------------------------+--------------------------+-----------+----------+---------
|
|
userid | oid | | |
|
|
dbid | oid | | |
|
|
toplevel | boolean | | |
|
|
queryid | bigint | | |
|
|
query | text | | |
|
|
plans | bigint | | |
|
|
total_plan_time | double precision | | |
|
|
min_plan_time | double precision | | |
|
|
max_plan_time | double precision | | |
|
|
mean_plan_time | double precision | | |
|
|
stddev_plan_time | double precision | | |
|
|
calls | bigint | | |
|
|
total_exec_time | double precision | | |
|
|
min_exec_time | double precision | | |
|
|
max_exec_time | double precision | | |
|
|
mean_exec_time | double precision | | |
|
|
stddev_exec_time | double precision | | |
|
|
rows | bigint | | |
|
|
shared_blks_hit | bigint | | |
|
|
shared_blks_read | bigint | | |
|
|
shared_blks_dirtied | bigint | | |
|
|
shared_blks_written | bigint | | |
|
|
local_blks_hit | bigint | | |
|
|
local_blks_read | bigint | | |
|
|
local_blks_dirtied | bigint | | |
|
|
local_blks_written | bigint | | |
|
|
temp_blks_read | bigint | | |
|
|
temp_blks_written | bigint | | |
|
|
shared_blk_read_time | double precision | | |
|
|
shared_blk_write_time | double precision | | |
|
|
local_blk_read_time | double precision | | |
|
|
local_blk_write_time | double precision | | |
|
|
temp_blk_read_time | double precision | | |
|
|
temp_blk_write_time | double precision | | |
|
|
wal_records | bigint | | |
|
|
wal_fpi | bigint | | |
|
|
wal_bytes | numeric | | |
|
|
wal_buffers_full | bigint | | |
|
|
jit_functions | bigint | | |
|
|
jit_generation_time | double precision | | |
|
|
jit_inlining_count | bigint | | |
|
|
jit_inlining_time | double precision | | |
|
|
jit_optimization_count | bigint | | |
|
|
jit_optimization_time | double precision | | |
|
|
jit_emission_count | bigint | | |
|
|
jit_emission_time | double precision | | |
|
|
jit_deform_count | bigint | | |
|
|
jit_deform_time | double precision | | |
|
|
parallel_workers_to_launch | bigint | | |
|
|
parallel_workers_launched | bigint | | |
|
|
stats_since | timestamp with time zone | | |
|
|
minmax_stats_since | timestamp with time zone | | |
|
|
|
|
SELECT count(*) > 0 AS has_data FROM pg_stat_statements;
|
|
has_data
|
|
----------
|
|
t
|
|
(1 row)
|
|
|
|
DROP EXTENSION pg_stat_statements;
|