On Tue, Feb 14, 2023 at 10:11:21AM -0800, Andres Freund wrote: > I didn't mean printing in the sense of outputting the statements to the tap > log. Maybe creating a temp table or such for all the queries. And yes, then > doing some top-level analysis on it like you describe sounds like a good idea.
One idea would be something like that, that makes sure that reports
are generated for the most common query patterns:
WITH select_stats AS
(SELECT upper(substr(query, 1, 6)) AS select_query
FROM pg_stat_statements
WHERE upper(substr(query, 1, 6)) IN ('SELECT', 'UPDATE',
'INSERT', 'DELETE',
'CREATE'))
SELECT select_query, count(select_query) > 1 AS some_rows
FROM select_stats
GROUP BY select_query ORDER BY select_query;
Other ideas are welcome. At least this would be a start.
--
Michael
diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile
index 570bf42b58..c60314d195 100644
--- a/src/test/recovery/Makefile
+++ b/src/test/recovery/Makefile
@@ -9,7 +9,7 @@
#
#-------------------------------------------------------------------------
-EXTRA_INSTALL=contrib/test_decoding contrib/pg_prewarm
+EXTRA_INSTALL=contrib/pg_prewarm contrib/pg_stat_statements contrib/test_decoding
subdir = src/test/recovery
top_builddir = ../../..
diff --git a/src/test/recovery/t/027_stream_regress.pl b/src/test/recovery/t/027_stream_regress.pl
index 13482adbaf..4f26192ffd 100644
--- a/src/test/recovery/t/027_stream_regress.pl
+++ b/src/test/recovery/t/027_stream_regress.pl
@@ -14,6 +14,17 @@ $node_primary->init(allows_streaming => 1);
$node_primary->adjust_conf('postgresql.conf', 'max_connections', '25');
$node_primary->append_conf('postgresql.conf',
'max_prepared_transactions = 10');
+
+# Enable pg_stat_statements to force tests with do query jumbling.
+# pg_stat_statements.max should be large enough to hold all the entries
+# of the regression database.
+$node_primary->append_conf(
+ 'postgresql.conf',
+ qq{shared_preload_libraries = 'pg_stat_statements'
+pg_stat_statements.max = 50000
+compute_query_id = 'regress'
+});
+
# We'll stick with Cluster->new's small default shared_buffers, but since that
# makes synchronized seqscans more probable, it risks changing the results of
# some test queries. Disable synchronized seqscans to prevent that.
@@ -106,6 +117,27 @@ command_ok(
[ 'diff', $outputdir . '/primary.dump', $outputdir . '/standby.dump' ],
'compare primary and standby dumps');
+# Check some data from pg_stat_statements.
+$node_primary->safe_psql('postgres', 'CREATE EXTENSION pg_stat_statements');
+# This gathers data based on the first characters for some common query types,
+# providing coverage for SELECT, DMLs, and some DDLs.
+my $result = $node_primary->safe_psql(
+ 'postgres',
+ qq{WITH select_stats AS
+ (SELECT upper(substr(query, 1, 6)) AS select_query
+ FROM pg_stat_statements
+ WHERE upper(substr(query, 1, 6)) IN ('SELECT', 'UPDATE',
+ 'INSERT', 'DELETE',
+ 'CREATE'))
+ SELECT select_query, count(select_query) > 1 AS some_rows
+ FROM select_stats
+ GROUP BY select_query ORDER BY select_query;});
+is( $result, qq(CREATE|t
+DELETE|t
+INSERT|t
+SELECT|t
+UPDATE|t), 'check contents of pg_stat_statements on regression database');
+
$node_standby_1->stop;
$node_primary->stop;
signature.asc
Description: PGP signature
