On 27.12.23 09:08, Julien Rouhaud wrote:
Hi,
On Tue, Dec 26, 2023 at 10:03 PM Peter Eisentraut <pe...@eisentraut.org> wrote:
On 24.12.23 03:03, Michael Paquier wrote:
- Use a DO block of a PL function, say with something like that to
ensure an amount of N queries? Say with something like that after
tweaking pg_stat_statements.track:
CREATE OR REPLACE FUNCTION create_tables(num_tables int)
RETURNS VOID AS
$func$
BEGIN
FOR i IN 1..num_tables LOOP
EXECUTE format('
CREATE TABLE IF NOT EXISTS %I (id int)', 't_' || i);
END LOOP;
END
$func$ LANGUAGE plpgsql;
I tried it like this first, but this doesn't register as separately
executed commands for pg_stat_statements.
I was a bit surprised by that so I checked locally. It does work as
expected provided that you set pg_stat_statements.track to all:
Ok, here is an updated patch set that does it that way.
I have committed the patches 0002 and 0003 from the previous patch set
already.
I have also enhanced the TAP test a bit to check the actual content of
the output across restarts.
I'm not too hung up on the 0001 patch if others don't like that approach.
From 8938017869025598024f0aba950c0aeccbcbe651 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Sat, 23 Dec 2023 14:25:26 +0100
Subject: [PATCH v2 1/3] pg_stat_statements: Exclude from lcov functions that
are impossible to test
The current code only supports installing version 1.4 of
pg_stat_statements and upgrading from there. So the C code entry
points for older versions are not reachable from the tests. To
prevent this from ruining the test coverage figures, mark those
functions are excluded from lcov.
Discussion:
https://www.postgresql.org/message-id/flat/40d1e4f2-835f-448f-a541-8ff5db75b...@eisentraut.org
---
contrib/pg_stat_statements/pg_stat_statements.c | 8 +++++++-
1 file changed, 7 insertions(+), 1 deletion(-)
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c
b/contrib/pg_stat_statements/pg_stat_statements.c
index 6f62a531f7..8ac73bf55e 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -311,13 +311,15 @@ static bool pgss_save = true; /* whether to save
stats across shutdown */
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_7);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset_1_11);
+/* LCOV_EXCL_START */
+PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
+/* LCOV_EXCL_STOP */
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_8);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_9);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_10);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_11);
-PG_FUNCTION_INFO_V1(pg_stat_statements);
PG_FUNCTION_INFO_V1(pg_stat_statements_info);
static void pgss_shmem_request(void);
@@ -1610,6 +1612,8 @@ pg_stat_statements_1_3(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/* LCOV_EXCL_START */
+
Datum
pg_stat_statements_1_2(PG_FUNCTION_ARGS)
{
@@ -1633,6 +1637,8 @@ pg_stat_statements(PG_FUNCTION_ARGS)
return (Datum) 0;
}
+/* LCOV_EXCL_STOP */
+
/* Common code for all versions of pg_stat_statements() */
static void
pg_stat_statements_internal(FunctionCallInfo fcinfo,
base-commit: 7e6fb5da41d8ee1bddcd5058b7204018ef68d193
--
2.43.0
From 6fa54b21ae6060fd459f5330130491662361f2d5 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Sat, 23 Dec 2023 14:25:26 +0100
Subject: [PATCH v2 2/3] pg_stat_statements: Add coverage for entry_dealloc()
This involves creating pg_stat_statements.max entries, then creating
even more entries, and checking that the limit is kept and the least
used entries are kicked out.
Discussion:
https://www.postgresql.org/message-id/flat/40d1e4f2-835f-448f-a541-8ff5db75b...@eisentraut.org
---
contrib/pg_stat_statements/Makefile | 2 +-
contrib/pg_stat_statements/expected/max.out | 66 +++++++++++++++++++
contrib/pg_stat_statements/meson.build | 1 +
.../pg_stat_statements.conf | 2 +
contrib/pg_stat_statements/sql/max.sql | 44 +++++++++++++
5 files changed, 114 insertions(+), 1 deletion(-)
create mode 100644 contrib/pg_stat_statements/expected/max.out
create mode 100644 contrib/pg_stat_statements/sql/max.sql
diff --git a/contrib/pg_stat_statements/Makefile
b/contrib/pg_stat_statements/Makefile
index aecd1d6a2a..7ee16e8350 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -19,7 +19,7 @@ LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config
$(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
REGRESS = select dml cursors utility level_tracking planning \
- user_activity wal entry_timestamp cleanup oldextversions
+ user_activity wal entry_timestamp max cleanup oldextversions
# Disabled because these tests require
"shared_preload_libraries=pg_stat_statements",
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
diff --git a/contrib/pg_stat_statements/expected/max.out
b/contrib/pg_stat_statements/expected/max.out
new file mode 100644
index 0000000000..49c3f56efd
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/max.out
@@ -0,0 +1,66 @@
+SHOW pg_stat_statements.max;
+ pg_stat_statements.max
+------------------------
+ 100
+(1 row)
+
+SET pg_stat_statements.track = 'all';
+DO $$
+BEGIN
+ FOR i IN 1..101 LOOP
+ EXECUTE format('create table t%s (a int)', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+DO $$
+BEGIN
+ FOR i IN 1..98 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+SELECT count(*) <= 100 AND count(*) > 0 FROM pg_stat_statements;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT query FROM pg_stat_statements WHERE query LIKE '%t001%' OR query LIKE
'%t098%' ORDER BY query;
+ query
+--------------------
+ select * from t001
+ select * from t098
+(2 rows)
+
+DO $$
+BEGIN
+ FOR i IN 2..98 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+DO $$
+BEGIN
+ FOR i IN 99..100 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+SELECT count(*) <= 100 AND count(*) > 0 FROM pg_stat_statements;
+ ?column?
+----------
+ t
+(1 row)
+
+-- record for t001 has been kicked out
+SELECT query FROM pg_stat_statements WHERE query LIKE '%t001%' ORDER BY query;
+ query
+-------
+(0 rows)
+
diff --git a/contrib/pg_stat_statements/meson.build
b/contrib/pg_stat_statements/meson.build
index 81fe1eb917..a66acaa5b8 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -50,6 +50,7 @@ tests += {
'user_activity',
'wal',
'entry_timestamp',
+ 'max',
'cleanup',
'oldextversions',
],
diff --git a/contrib/pg_stat_statements/pg_stat_statements.conf
b/contrib/pg_stat_statements/pg_stat_statements.conf
index 0e900d7119..0119f681d7 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.conf
+++ b/contrib/pg_stat_statements/pg_stat_statements.conf
@@ -1,2 +1,4 @@
shared_preload_libraries = 'pg_stat_statements'
max_prepared_transactions = 5
+
+pg_stat_statements.max = 100
diff --git a/contrib/pg_stat_statements/sql/max.sql
b/contrib/pg_stat_statements/sql/max.sql
new file mode 100644
index 0000000000..741fbc9851
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/max.sql
@@ -0,0 +1,44 @@
+SHOW pg_stat_statements.max;
+
+SET pg_stat_statements.track = 'all';
+
+DO $$
+BEGIN
+ FOR i IN 1..101 LOOP
+ EXECUTE format('create table t%s (a int)', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+
+DO $$
+BEGIN
+ FOR i IN 1..98 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+
+SELECT count(*) <= 100 AND count(*) > 0 FROM pg_stat_statements;
+SELECT query FROM pg_stat_statements WHERE query LIKE '%t001%' OR query LIKE
'%t098%' ORDER BY query;
+
+DO $$
+BEGIN
+ FOR i IN 2..98 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+
+DO $$
+BEGIN
+ FOR i IN 99..100 LOOP
+ EXECUTE format('select * from t%s', lpad(i::text, 3, '0'));
+ END LOOP;
+END
+$$;
+
+SELECT count(*) <= 100 AND count(*) > 0 FROM pg_stat_statements;
+-- record for t001 has been kicked out
+SELECT query FROM pg_stat_statements WHERE query LIKE '%t001%' ORDER BY query;
--
2.43.0
From 3129dd32ff62350acbd179eea3683d27bea6adc7 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Wed, 27 Dec 2023 13:08:57 +0100
Subject: [PATCH v2 3/3] pg_stat_statements: Add TAP test for testing restarts
This tests that pg_stat_statement contents are successfully kept
across restart. (This similar to
src/test/recovery/t/029_stats_restart.pl for the stats collector.)
Discussion:
https://www.postgresql.org/message-id/flat/40d1e4f2-835f-448f-a541-8ff5db75b...@eisentraut.org
---
contrib/pg_stat_statements/Makefile | 2 +
contrib/pg_stat_statements/meson.build | 5 ++
contrib/pg_stat_statements/t/010_restart.pl | 53 +++++++++++++++++++++
3 files changed, 60 insertions(+)
create mode 100644 contrib/pg_stat_statements/t/010_restart.pl
diff --git a/contrib/pg_stat_statements/Makefile
b/contrib/pg_stat_statements/Makefile
index 7ee16e8350..20834bb0ee 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -24,6 +24,8 @@ REGRESS = select dml cursors utility level_tracking planning \
# which typical installcheck users do not have (e.g. buildfarm clients).
NO_INSTALLCHECK = 1
+TAP_TESTS = 1
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/pg_stat_statements/meson.build
b/contrib/pg_stat_statements/meson.build
index a66acaa5b8..3e42328f6c 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -60,4 +60,9 @@ tests += {
# runningcheck users do not have (e.g. buildfarm clients).
'runningcheck': false,
},
+ 'tap': {
+ 'tests': [
+ 't/010_restart.pl',
+ ],
+ },
}
diff --git a/contrib/pg_stat_statements/t/010_restart.pl
b/contrib/pg_stat_statements/t/010_restart.pl
new file mode 100644
index 0000000000..83a2bf0db8
--- /dev/null
+++ b/contrib/pg_stat_statements/t/010_restart.pl
@@ -0,0 +1,53 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Tests for checking that pg_stat_statements contents are preserved
+# across restarts.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->append_conf('postgresql.conf',
+ "shared_preload_libraries = 'pg_stat_statements'");
+$node->start;
+
+$node->safe_psql('postgres', 'CREATE EXTENSION pg_stat_statements');
+
+$node->safe_psql('postgres', 'CREATE TABLE t1 (a int)');
+$node->safe_psql('postgres', 'SELECT a FROM t1');
+
+is( $node->safe_psql(
+ 'postgres',
+ "SELECT query FROM pg_stat_statements WHERE query NOT LIKE
'%pg_stat_statements%' ORDER BY query"
+ ),
+ "CREATE TABLE t1 (a int)\nSELECT a FROM t1",
+ 'pg_stat_statements populated');
+
+$node->restart;
+
+is( $node->safe_psql(
+ 'postgres',
+ "SELECT query FROM pg_stat_statements WHERE query NOT LIKE
'%pg_stat_statements%' ORDER BY query"
+ ),
+ "CREATE TABLE t1 (a int)\nSELECT a FROM t1",
+ 'pg_stat_statements data kept across restart');
+
+$node->append_conf('postgresql.conf', "pg_stat_statements.save = false");
+$node->reload;
+
+$node->restart;
+
+is( $node->safe_psql(
+ 'postgres',
+ "SELECT count(*) FROM pg_stat_statements WHERE query NOT LIKE
'%pg_stat_statements%'"
+ ),
+ '0',
+ 'pg_stat_statements data not kept across restart with .save=false');
+
+$node->stop;
+
+done_testing();
--
2.43.0
