public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Paquier <[email protected]>
To: Drouvot, Bertrand <[email protected]>
Cc: Postgres hackers <[email protected]>
Subject: Re: Normalization of utility queries in pg_stat_statements
Date: Fri, 17 Feb 2023 11:35:38 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
On Thu, Feb 16, 2023 at 10:55:32AM +0100, Drouvot, Bertrand wrote:
> In the new pg_stat_statements.sql? That way pg_stat_statements.sql would always behave
> with default values for those (currently we are setting both of them as non default).
>
> Then, with the default values in place, if we feel that some tests
> are missing we could add them in > utility.sql or planning.sql
> accordingly.
I am not sure about this part, TBH, so I have left these as they are.
Anyway, while having a second look at that, I have noticed that it is
possible to extract as an independent piece all the tests related to
level tracking. Things are worse than I thought initially, actually,
because we had test scenarios mixing planning and level tracking, but
the tests don't care about measuring plans at all, see around FETCH
FORWARD, meaning that queries on the table pg_stat_statements have
just been copy-pasted around for the last few years. There were more
tests that used "test" for a table name ;)
I have been pondering about this part, and the tracking matters for DO
blocks and PL functions, so I have moved all these cases into a new,
separate file. There is a bit more that can be done, for WAL tracking
and roles near the end of pg_stat_statements.sql, but I have left that
out for now. I have checked the output of the tests before and after
the refactoring for quite a bit of time, and the outputs match so
there is no loss of coverage.
0001 looks quite committable at this stage, and that's independent on
the rest. At the end this patch creates four new test files that are
extended in the next patches: utility, planning, track and cleanup.
> 0002:
>
> Produces:
> v2-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch:834: trailing whitespace.
> CREATE VIEW view_stats_1 AS
> v2-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch:838: trailing whitespace.
> CREATE VIEW view_stats_1 AS
> warning: 2 lines add whitespace errors.
Thanks, fixed.
> +-- SET TRANSACTION ISOLATION
> +BEGIN;
> +SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
> +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
> What about adding things like "SET SESSION CHARACTERISTICS AS
> TRANSACTION..." too?
That's a good idea. It is again one of these fancy cases, better to
keep a track of them in the long-term..
> 0003 and 0004:
> Thanks for keeping 0003 that's useful to see the impact of A_Const normalization.
>
> Looking at the diff they produce, I also do think that 0004 is what
> could be done for PG16.
I am wondering if others have an opinion to share about that, but,
yes, 0004 seems enough to begin with. We could always study more
normalization areas in future releases, taking it slowly.
--
Michael
Attachments:
[text/x-diff] v3-0001-Refactor-tests-of-pg_stat_statements-for-planning.patch (43.0K, ../[email protected]/2-v3-0001-Refactor-tests-of-pg_stat_statements-for-planning.patch)
download | inline diff:
From 6c83825d1517a9706aca1518b23ba72e086151f0 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Fri, 17 Feb 2023 10:57:05 +0900
Subject: [PATCH v3 1/4] Refactor tests of pg_stat_statements for planning,
utilities and tracking
pg_stat_statements.sql acts as the main file for all the core tests of
the module, but things have become complicated to follow over the years
as some of the sub-scenarios tested in it rely on assumptions that come
from completely different areas of this file, like a GUC setup or a
different relation created previously. For example, row tracking for
CTAS/COPY was looking at the number of plans, which was not necessary,
or level tracking was mixed with planner counts.
This commit refactors the tests of pg_stat_statements, by moving a test
cases out of pg_stat_statements.sql into their own file, as of:
- Planning-related tests in planning.sql, for [re-]plan counts and
top-level handling. These depend on pg_stat_statements.track_planning.
- Utilities in utility.sql (pg_stat_statements.track_utility), that
includes now the tests for:
-- Row tracking for CTAS, CREATE MATERIALIZED VIEW, COPY.
-- Basic utility statements.
-- SET statements.
- Tracking level, depending on pg_stat_statements.track. This part has
been looking at scenarios with DO blocks, PL functions and SQL
functions.
Test scenarios and their results match exactly with what was happening
before in terms of calls, number of plans, number of rows, cached data
or level tracking, so this has no effect on the coverage in terms of
what is produced by the reports in the table pg_stat_statements. The
follow-up plan is to extend more the tests of pg_stat_statements for
utilities, so this split creates a foundation for this purpose.
Reviewed-by: Bertrand Drouvot
Discussion: https://postgr.es/m/[email protected]
---
contrib/pg_stat_statements/Makefile | 2 +-
.../pg_stat_statements/expected/cleanup.out | 1 +
.../expected/pg_stat_statements.out | 389 +-----------------
.../pg_stat_statements/expected/planning.out | 88 ++++
contrib/pg_stat_statements/expected/track.out | 194 +++++++++
.../pg_stat_statements/expected/utility.out | 132 ++++++
contrib/pg_stat_statements/meson.build | 4 +
contrib/pg_stat_statements/sql/cleanup.sql | 1 +
.../sql/pg_stat_statements.sql | 176 +-------
contrib/pg_stat_statements/sql/planning.sql | 31 ++
contrib/pg_stat_statements/sql/track.sql | 97 +++++
contrib/pg_stat_statements/sql/utility.sql | 60 +++
12 files changed, 611 insertions(+), 564 deletions(-)
create mode 100644 contrib/pg_stat_statements/expected/cleanup.out
create mode 100644 contrib/pg_stat_statements/expected/planning.out
create mode 100644 contrib/pg_stat_statements/expected/track.out
create mode 100644 contrib/pg_stat_statements/expected/utility.out
create mode 100644 contrib/pg_stat_statements/sql/cleanup.sql
create mode 100644 contrib/pg_stat_statements/sql/planning.sql
create mode 100644 contrib/pg_stat_statements/sql/track.sql
create mode 100644 contrib/pg_stat_statements/sql/utility.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index edc40c8bbf..8b6d16369c 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -17,7 +17,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
-REGRESS = pg_stat_statements oldextversions
+REGRESS = pg_stat_statements utility planning track 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/cleanup.out b/contrib/pg_stat_statements/expected/cleanup.out
new file mode 100644
index 0000000000..36bec35c40
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/cleanup.out
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 8c0b2235e8..05722db413 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -315,153 +315,6 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
UPDATE pgss_test SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
(7 rows)
---
--- pg_stat_statements.track = none
---
-SET pg_stat_statements.track = 'none';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-SELECT 1 AS "one";
- one
------
- 1
-(1 row)
-
-SELECT 1 + 1 AS "two";
- two
------
- 2
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
--------+-------+------
-(0 rows)
-
---
--- pg_stat_statements.track = top
---
-SET pg_stat_statements.track = 'top';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-DO LANGUAGE plpgsql $$
-BEGIN
- -- this is a SELECT
- PERFORM 'hello world'::TEXT;
-END;
-$$;
--- PL/pgSQL function
-CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
-DECLARE
- r INTEGER;
-BEGIN
- SELECT (i + 1 + 1.0)::INTEGER INTO r;
- RETURN r;
-END; $$ LANGUAGE plpgsql;
-SELECT PLUS_TWO(3);
- plus_two
-----------
- 5
-(1 row)
-
-SELECT PLUS_TWO(7);
- plus_two
-----------
- 9
-(1 row)
-
--- SQL function --- use LIMIT to keep it from being inlined
-CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
-$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
-SELECT PLUS_ONE(8);
- plus_one
-----------
- 9
-(1 row)
-
-SELECT PLUS_ONE(10);
- plus_one
-----------
- 11
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------------------------------+-------+------
- SELECT $1::TEXT | 1 | 1
- SELECT PLUS_ONE($1) | 2 | 2
- SELECT PLUS_TWO($1) | 2 | 2
- SELECT pg_stat_statements_reset() | 1 | 1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
-(5 rows)
-
---
--- pg_stat_statements.track = all
---
-SET pg_stat_statements.track = 'all';
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
--- we drop and recreate the functions to avoid any caching funnies
-DROP FUNCTION PLUS_ONE(INTEGER);
-DROP FUNCTION PLUS_TWO(INTEGER);
--- PL/pgSQL function
-CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
-DECLARE
- r INTEGER;
-BEGIN
- SELECT (i + 1 + 1.0)::INTEGER INTO r;
- RETURN r;
-END; $$ LANGUAGE plpgsql;
-SELECT PLUS_TWO(-1);
- plus_two
-----------
- 1
-(1 row)
-
-SELECT PLUS_TWO(2);
- plus_two
-----------
- 4
-(1 row)
-
--- SQL function --- use LIMIT to keep it from being inlined
-CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
-$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
-SELECT PLUS_ONE(3);
- plus_one
-----------
- 4
-(1 row)
-
-SELECT PLUS_ONE(1);
- plus_one
-----------
- 2
-(1 row)
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------------------------------+-------+------
- SELECT (i + $2 + $3)::INTEGER | 2 | 2
- SELECT (i + $2)::INTEGER LIMIT $3 | 2 | 2
- SELECT PLUS_ONE($1) | 2 | 2
- SELECT PLUS_TWO($1) | 2 | 2
- SELECT pg_stat_statements_reset() | 1 | 1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
-(6 rows)
-
--
-- queries with locking clauses
--
@@ -550,131 +403,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
(12 rows)
DROP TABLE pgss_a, pgss_b CASCADE;
---
--- utility commands
---
-SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-SELECT 1;
- ?column?
-----------
- 1
-(1 row)
-
-CREATE INDEX test_b ON test(b);
-DROP TABLE test \;
-DROP TABLE IF EXISTS test \;
-DROP FUNCTION PLUS_ONE(INTEGER);
-NOTICE: table "test" does not exist, skipping
--- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS test \;
-Drop Table If Exists test \;
-DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
-NOTICE: table "test" does not exist, skipping
-NOTICE: table "test" does not exist, skipping
-NOTICE: function plus_one(pg_catalog.int4) does not exist, skipping
-DROP FUNCTION PLUS_TWO(INTEGER);
--- This SET query uses two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------------------------------+-------+------
- CREATE INDEX test_b ON test(b) | 1 | 0
- DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER) | 1 | 0
- DROP FUNCTION PLUS_ONE(INTEGER) | 1 | 0
- DROP FUNCTION PLUS_TWO(INTEGER) | 1 | 0
- DROP TABLE IF EXISTS test | 3 | 0
- DROP TABLE test | 1 | 0
- RESET enable_seqscan | 1 | 0
- RESET work_mem | 1 | 0
- SELECT $1 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
- SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 0 | 0
- SET enable_seqscan = off | 1 | 0
- SET enable_seqscan = on | 1 | 0
- SET work_mem = '1MB' | 2 | 0
- SET work_mem = '2MB' | 1 | 0
-(15 rows)
-
---
--- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
--- REFRESH MATERIALIZED VIEW and SELECT INTO
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
-SELECT generate_series(1, 10) c INTO pgss_select_into;
-COPY pgss_ctas (a, b) FROM STDIN;
-CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
-REFRESH MATERIALIZED VIEW pgss_matv;
-BEGIN;
-DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
-FETCH NEXT pgss_cursor;
- a | b
----+------
- 1 | ctas
-(1 row)
-
-FETCH FORWARD 5 pgss_cursor;
- a | b
----+------
- 2 | ctas
- 3 | ctas
- 4 | ctas
- 5 | ctas
- 6 | ctas
-(5 rows)
-
-FETCH FORWARD ALL pgss_cursor;
- a | b
-----+------
- 7 | ctas
- 8 | ctas
- 9 | ctas
- 10 | ctas
- 11 | copy
- 12 | copy
- 13 | copy
-(7 rows)
-
-COMMIT;
-SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | plans | calls | rows
--------------------------------------------------------------------------------------+-------+-------+------
- BEGIN | 0 | 1 | 0
- COMMIT | 0 | 1 | 0
- COPY pgss_ctas (a, b) FROM STDIN | 0 | 1 | 3
- CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 0 | 1 | 13
- CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 0 | 1 | 10
- DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 0 | 1 | 0
- FETCH FORWARD 5 pgss_cursor | 0 | 1 | 5
- FETCH FORWARD ALL pgss_cursor | 0 | 1 | 7
- FETCH NEXT pgss_cursor | 0 | 1 | 1
- REFRESH MATERIALIZED VIEW pgss_matv | 0 | 1 | 13
- SELECT generate_series(1, 10) c INTO pgss_select_into | 0 | 1 | 10
- SELECT pg_stat_statements_reset() | 0 | 1 | 1
- SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C" | 1 | 0 | 0
-(13 rows)
-
--
-- Track user activity and reset them
--
+SET pg_stat_statements.track_utility = TRUE;
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
@@ -872,84 +604,6 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
DROP ROLE regress_stats_user1;
DROP ROLE regress_stats_user2;
-DROP MATERIALIZED VIEW pgss_matv;
-DROP TABLE pgss_ctas;
-DROP TABLE pgss_select_into;
---
--- [re]plan counting
---
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-CREATE TABLE test ();
-PREPARE prep1 AS SELECT COUNT(*) FROM test;
-EXECUTE prep1;
- count
--------
- 0
-(1 row)
-
-EXECUTE prep1;
- count
--------
- 0
-(1 row)
-
-EXECUTE prep1;
- count
--------
- 0
-(1 row)
-
-ALTER TABLE test ADD COLUMN x int;
-EXECUTE prep1;
- count
--------
- 0
-(1 row)
-
-SELECT 42;
- ?column?
-----------
- 42
-(1 row)
-
-SELECT 42;
- ?column?
-----------
- 42
-(1 row)
-
-SELECT 42;
- ?column?
-----------
- 42
-(1 row)
-
-SELECT query, plans, calls, rows FROM pg_stat_statements
- WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
- query | plans | calls | rows
-----------------------------------------------------------+-------+-------+------
- ALTER TABLE test ADD COLUMN x int | 0 | 1 | 0
- CREATE TABLE test () | 0 | 1 | 0
- SELECT $1 | 3 | 3 | 3
- SELECT pg_stat_statements_reset() | 0 | 1 | 1
- SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0
- WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | |
-(5 rows)
-
--- for the prepared statement we expect at least one replan, but cache
--- invalidations could force more
-SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
- WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
- query | plans_ok | calls | rows
---------------------------------------------+----------+-------+------
- PREPARE prep1 AS SELECT COUNT(*) FROM test | t | 4 | 4
-(1 row)
-
--
-- access to pg_stat_statements_info view
--
@@ -965,46 +619,6 @@ SELECT dealloc FROM pg_stat_statements_info;
0
(1 row)
---
--- top level handling
---
-SET pg_stat_statements.track = 'top';
-DELETE FROM test;
-DO $$
-BEGIN
- DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
- query | toplevel | plans | calls
------------------------+----------+-------+-------
- DELETE FROM test | t | 1 | 1
- DO $$ +| t | 0 | 1
- BEGIN +| | |
- DELETE FROM test;+| | |
- END; +| | |
- $$ LANGUAGE plpgsql | | |
-(2 rows)
-
-SET pg_stat_statements.track = 'all';
-DELETE FROM test;
-DO $$
-BEGIN
- DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
- query | toplevel | plans | calls
------------------------+----------+-------+-------
- DELETE FROM test | f | 1 | 1
- DELETE FROM test | t | 2 | 2
- DO $$ +| t | 0 | 2
- BEGIN +| | |
- DELETE FROM test;+| | |
- END; +| | |
- $$ LANGUAGE plpgsql | | |
-(3 rows)
-
-- FROM [ONLY]
CREATE TABLE tbl_inh(id integer);
CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
@@ -1156,4 +770,3 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
2
(1 row)
-DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/expected/planning.out b/contrib/pg_stat_statements/expected/planning.out
new file mode 100644
index 0000000000..e3b028bc92
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/planning.out
@@ -0,0 +1,88 @@
+--
+-- Information related to planning
+--
+-- These tests require track_planning to be enabled.
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+--
+-- [re]plan counting
+--
+CREATE TABLE stats_plan_test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test;
+EXECUTE prep1;
+ count
+-------
+ 0
+(1 row)
+
+EXECUTE prep1;
+ count
+-------
+ 0
+(1 row)
+
+EXECUTE prep1;
+ count
+-------
+ 0
+(1 row)
+
+ALTER TABLE stats_plan_test ADD COLUMN x int;
+EXECUTE prep1;
+ count
+-------
+ 0
+(1 row)
+
+SELECT 42;
+ ?column?
+----------
+ 42
+(1 row)
+
+SELECT 42;
+ ?column?
+----------
+ 42
+(1 row)
+
+SELECT 42;
+ ?column?
+----------
+ 42
+(1 row)
+
+SELECT query, plans, calls, rows FROM pg_stat_statements
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+ query | plans | calls | rows
+----------------------------------------------------------+-------+-------+------
+ ALTER TABLE stats_plan_test ADD COLUMN x int | 0 | 1 | 0
+ CREATE TABLE stats_plan_test () | 0 | 1 | 0
+ SELECT $1 | 3 | 3 | 3
+ SELECT pg_stat_statements_reset() | 0 | 1 | 1
+ SELECT query, plans, calls, rows FROM pg_stat_statements+| 1 | 0 | 0
+ WHERE query NOT LIKE $1 ORDER BY query COLLATE "C" | | |
+(5 rows)
+
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+ query | plans_ok | calls | rows
+-------------------------------------------------------+----------+-------+------
+ PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test | t | 4 | 4
+(1 row)
+
+-- Cleanup
+DROP TABLE stats_plan_test;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/track.out b/contrib/pg_stat_statements/expected/track.out
new file mode 100644
index 0000000000..a0063157a6
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/track.out
@@ -0,0 +1,194 @@
+--
+-- Statement level tracking
+--
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- DO block - top-level tracking.
+CREATE TABLE stats_track_tab (x int);
+SET pg_stat_statements.track = 'top';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+ DELETE FROM stats_track_tab;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+ query | toplevel | calls
+--------------------------------+----------+-------
+ DELETE FROM stats_track_tab | t | 1
+ DO $$ +| t | 1
+ BEGIN +| |
+ DELETE FROM stats_track_tab;+| |
+ END; +| |
+ $$ LANGUAGE plpgsql | |
+(2 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- DO block - all-level tracking.
+SET pg_stat_statements.track = 'all';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+ DELETE FROM stats_track_tab;
+END; $$;
+DO LANGUAGE plpgsql $$
+BEGIN
+ -- this is a SELECT
+ PERFORM 'hello world'::TEXT;
+END; $$;
+SELECT query, toplevel, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+ query | toplevel | calls
+--------------------------------+----------+-------
+ DELETE FROM stats_track_tab | f | 1
+ DELETE FROM stats_track_tab | t | 1
+ DO $$ +| t | 1
+ BEGIN +| |
+ DELETE FROM stats_track_tab;+| |
+ END; $$ | |
+(3 rows)
+
+-- PL/pgSQL function - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(3);
+ plus_two
+----------
+ 5
+(1 row)
+
+SELECT PLUS_TWO(7);
+ plus_two
+----------
+ 9
+(1 row)
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+SELECT PLUS_ONE(8);
+ plus_one
+----------
+ 9
+(1 row)
+
+SELECT PLUS_ONE(10);
+ plus_one
+----------
+ 11
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------+-------+------
+ SELECT PLUS_ONE($1) | 2 | 2
+ SELECT PLUS_TWO($1) | 2 | 2
+ SELECT pg_stat_statements_reset() | 1 | 1
+(3 rows)
+
+-- PL/pgSQL function - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- we drop and recreate the functions to avoid any caching funnies
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+SELECT PLUS_TWO(-1);
+ plus_two
+----------
+ 1
+(1 row)
+
+SELECT PLUS_TWO(2);
+ plus_two
+----------
+ 4
+(1 row)
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+SELECT PLUS_ONE(3);
+ plus_one
+----------
+ 4
+(1 row)
+
+SELECT PLUS_ONE(1);
+ plus_one
+----------
+ 2
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------+-------+------
+ SELECT (i + $2 + $3)::INTEGER | 2 | 2
+ SELECT (i + $2)::INTEGER LIMIT $3 | 2 | 2
+ SELECT PLUS_ONE($1) | 2 | 2
+ SELECT PLUS_TWO($1) | 2 | 2
+ SELECT pg_stat_statements_reset() | 1 | 1
+(5 rows)
+
+DROP FUNCTION PLUS_ONE(INTEGER);
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1 AS "one";
+ one
+-----
+ 1
+(1 row)
+
+SELECT 1 + 1 AS "two";
+ two
+-----
+ 2
+(1 row)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-------+-------+------
+(0 rows)
+
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
new file mode 100644
index 0000000000..8247591130
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -0,0 +1,132 @@
+--
+-- Utility commands
+--
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT 1;
+ ?column?
+----------
+ 1
+(1 row)
+
+CREATE TEMP TABLE stats_util_tab (a int, b char(20));
+CREATE INDEX test_b ON stats_util_tab(b);
+DROP TABLE stats_util_tab \;
+DROP TABLE IF EXISTS stats_util_tab;
+NOTICE: table "stats_util_tab" does not exist, skipping
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS stats_util_tab \;
+Drop Table If Exists stats_util_tab;
+NOTICE: table "stats_util_tab" does not exist, skipping
+NOTICE: table "stats_util_tab" does not exist, skipping
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+------------------------------------------------------+-------+------
+ CREATE INDEX test_b ON stats_util_tab(b) | 1 | 0
+ CREATE TEMP TABLE stats_util_tab (a int, b char(20)) | 1 | 0
+ DROP TABLE IF EXISTS stats_util_tab | 3 | 0
+ DROP TABLE stats_util_tab | 1 | 0
+ SELECT $1 | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
+(6 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+ a | b
+---+------
+ 1 | ctas
+(1 row)
+
+FETCH FORWARD 5 pgss_cursor;
+ a | b
+---+------
+ 2 | ctas
+ 3 | ctas
+ 4 | ctas
+ 5 | ctas
+ 6 | ctas
+(5 rows)
+
+FETCH FORWARD ALL pgss_cursor;
+ a | b
+----+------
+ 7 | ctas
+ 8 | ctas
+ 9 | ctas
+ 10 | ctas
+ 11 | copy
+ 12 | copy
+ 13 | copy
+(7 rows)
+
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+----------------------------------------------------------------------------+-------+------
+ BEGIN | 1 | 0
+ COMMIT | 1 | 0
+ COPY pgss_ctas (a, b) FROM STDIN | 1 | 3
+ CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 1 | 13
+ CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 1 | 10
+ DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 1 | 0
+ FETCH FORWARD 5 pgss_cursor | 1 | 5
+ FETCH FORWARD ALL pgss_cursor | 1 | 7
+ FETCH NEXT pgss_cursor | 1 | 1
+ REFRESH MATERIALIZED VIEW pgss_matv | 1 | 13
+ SELECT generate_series(1, 10) c INTO pgss_select_into | 1 | 10
+ SELECT pg_stat_statements_reset() | 1 | 1
+(12 rows)
+
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- SET statements.
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------+-------+------
+ RESET enable_seqscan | 1 | 0
+ RESET work_mem | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SET enable_seqscan = off | 1 | 0
+ SET enable_seqscan = on | 1 | 0
+ SET work_mem = '1MB' | 2 | 0
+ SET work_mem = '2MB' | 1 | 0
+(7 rows)
+
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 508b53b4a2..0fb4a9f2e0 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -41,6 +41,10 @@ tests += {
'regress': {
'sql': [
'pg_stat_statements',
+ 'utility',
+ 'planning',
+ 'track',
+ 'cleanup',
'oldextversions',
],
'regress_args': ['--temp-config', files('pg_stat_statements.conf')],
diff --git a/contrib/pg_stat_statements/sql/cleanup.sql b/contrib/pg_stat_statements/sql/cleanup.sql
new file mode 100644
index 0000000000..36bec35c40
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cleanup.sql
@@ -0,0 +1 @@
+DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index cebde7392b..58bdb3c4b3 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -147,82 +147,6 @@ wal_records > 0 as wal_records_generated,
wal_records >= rows as wal_records_ge_rows
FROM pg_stat_statements ORDER BY query COLLATE "C";
---
--- pg_stat_statements.track = none
---
-SET pg_stat_statements.track = 'none';
-SELECT pg_stat_statements_reset();
-
-SELECT 1 AS "one";
-SELECT 1 + 1 AS "two";
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- pg_stat_statements.track = top
---
-SET pg_stat_statements.track = 'top';
-SELECT pg_stat_statements_reset();
-
-DO LANGUAGE plpgsql $$
-BEGIN
- -- this is a SELECT
- PERFORM 'hello world'::TEXT;
-END;
-$$;
-
--- PL/pgSQL function
-CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
-DECLARE
- r INTEGER;
-BEGIN
- SELECT (i + 1 + 1.0)::INTEGER INTO r;
- RETURN r;
-END; $$ LANGUAGE plpgsql;
-
-SELECT PLUS_TWO(3);
-SELECT PLUS_TWO(7);
-
--- SQL function --- use LIMIT to keep it from being inlined
-CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
-$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
-
-SELECT PLUS_ONE(8);
-SELECT PLUS_ONE(10);
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- pg_stat_statements.track = all
---
-SET pg_stat_statements.track = 'all';
-SELECT pg_stat_statements_reset();
-
--- we drop and recreate the functions to avoid any caching funnies
-DROP FUNCTION PLUS_ONE(INTEGER);
-DROP FUNCTION PLUS_TWO(INTEGER);
-
--- PL/pgSQL function
-CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
-DECLARE
- r INTEGER;
-BEGIN
- SELECT (i + 1 + 1.0)::INTEGER INTO r;
- RETURN r;
-END; $$ LANGUAGE plpgsql;
-
-SELECT PLUS_TWO(-1);
-SELECT PLUS_TWO(2);
-
--- SQL function --- use LIMIT to keep it from being inlined
-CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
-$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
-
-SELECT PLUS_ONE(3);
-SELECT PLUS_ONE(1);
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
--
-- queries with locking clauses
--
@@ -254,61 +178,10 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
DROP TABLE pgss_a, pgss_b CASCADE;
---
--- utility commands
---
-SET pg_stat_statements.track_utility = TRUE;
-SELECT pg_stat_statements_reset();
-
-SELECT 1;
-CREATE INDEX test_b ON test(b);
-DROP TABLE test \;
-DROP TABLE IF EXISTS test \;
-DROP FUNCTION PLUS_ONE(INTEGER);
--- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS test \;
-Drop Table If Exists test \;
-DROP FUNCTION IF EXISTS PLUS_ONE(INTEGER);
-DROP FUNCTION PLUS_TWO(INTEGER);
--- This SET query uses two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
---
--- Track the total number of rows retrieved or affected by the utility
--- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
--- REFRESH MATERIALIZED VIEW and SELECT INTO
---
-SELECT pg_stat_statements_reset();
-
-CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
-SELECT generate_series(1, 10) c INTO pgss_select_into;
-COPY pgss_ctas (a, b) FROM STDIN;
-11 copy
-12 copy
-13 copy
-\.
-CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
-REFRESH MATERIALIZED VIEW pgss_matv;
-BEGIN;
-DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
-FETCH NEXT pgss_cursor;
-FETCH FORWARD 5 pgss_cursor;
-FETCH FORWARD ALL pgss_cursor;
-COMMIT;
-
-SELECT query, plans, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
-
--
-- Track user activity and reset them
--
+SET pg_stat_statements.track_utility = TRUE;
SELECT pg_stat_statements_reset();
CREATE ROLE regress_stats_user1;
CREATE ROLE regress_stats_user2;
@@ -369,30 +242,6 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
DROP ROLE regress_stats_user1;
DROP ROLE regress_stats_user2;
-DROP MATERIALIZED VIEW pgss_matv;
-DROP TABLE pgss_ctas;
-DROP TABLE pgss_select_into;
-
---
--- [re]plan counting
---
-SELECT pg_stat_statements_reset();
-CREATE TABLE test ();
-PREPARE prep1 AS SELECT COUNT(*) FROM test;
-EXECUTE prep1;
-EXECUTE prep1;
-EXECUTE prep1;
-ALTER TABLE test ADD COLUMN x int;
-EXECUTE prep1;
-SELECT 42;
-SELECT 42;
-SELECT 42;
-SELECT query, plans, calls, rows FROM pg_stat_statements
- WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
--- for the prepared statement we expect at least one replan, but cache
--- invalidations could force more
-SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
- WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
--
-- access to pg_stat_statements_info view
@@ -400,27 +249,6 @@ SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_sta
SELECT pg_stat_statements_reset();
SELECT dealloc FROM pg_stat_statements_info;
---
--- top level handling
---
-SET pg_stat_statements.track = 'top';
-DELETE FROM test;
-DO $$
-BEGIN
- DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-
-SET pg_stat_statements.track = 'all';
-DELETE FROM test;
-DO $$
-BEGIN
- DELETE FROM test;
-END;
-$$ LANGUAGE plpgsql;
-SELECT query, toplevel, plans, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
-
-- FROM [ONLY]
CREATE TABLE tbl_inh(id integer);
CREATE TABLE tbl_inh_1() INHERITS (tbl_inh);
@@ -472,5 +300,3 @@ SELECT (
) FROM (VALUES(6,7)) v3(e,f) GROUP BY ROLLUP(e,f);
SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%';
-
-DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/sql/planning.sql b/contrib/pg_stat_statements/sql/planning.sql
new file mode 100644
index 0000000000..856f5e4124
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/planning.sql
@@ -0,0 +1,31 @@
+--
+-- Information related to planning
+--
+
+-- These tests require track_planning to be enabled.
+SET pg_stat_statements.track_planning = TRUE;
+SELECT pg_stat_statements_reset();
+
+--
+-- [re]plan counting
+--
+CREATE TABLE stats_plan_test ();
+PREPARE prep1 AS SELECT COUNT(*) FROM stats_plan_test;
+EXECUTE prep1;
+EXECUTE prep1;
+EXECUTE prep1;
+ALTER TABLE stats_plan_test ADD COLUMN x int;
+EXECUTE prep1;
+SELECT 42;
+SELECT 42;
+SELECT 42;
+SELECT query, plans, calls, rows FROM pg_stat_statements
+ WHERE query NOT LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+-- for the prepared statement we expect at least one replan, but cache
+-- invalidations could force more
+SELECT query, plans >= 2 AND plans <= calls AS plans_ok, calls, rows FROM pg_stat_statements
+ WHERE query LIKE 'PREPARE%' ORDER BY query COLLATE "C";
+
+-- Cleanup
+DROP TABLE stats_plan_test;
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/track.sql b/contrib/pg_stat_statements/sql/track.sql
new file mode 100644
index 0000000000..a9ec3b7f4d
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/track.sql
@@ -0,0 +1,97 @@
+--
+-- Statement level tracking
+--
+
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- DO block - top-level tracking.
+CREATE TABLE stats_track_tab (x int);
+SET pg_stat_statements.track = 'top';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+ DELETE FROM stats_track_tab;
+END;
+$$ LANGUAGE plpgsql;
+SELECT query, toplevel, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+SELECT pg_stat_statements_reset();
+
+-- DO block - all-level tracking.
+SET pg_stat_statements.track = 'all';
+DELETE FROM stats_track_tab;
+DO $$
+BEGIN
+ DELETE FROM stats_track_tab;
+END; $$;
+DO LANGUAGE plpgsql $$
+BEGIN
+ -- this is a SELECT
+ PERFORM 'hello world'::TEXT;
+END; $$;
+SELECT query, toplevel, calls FROM pg_stat_statements WHERE query LIKE '%DELETE%' ORDER BY query COLLATE "C", toplevel;
+
+-- PL/pgSQL function - top-level tracking.
+SET pg_stat_statements.track = 'top';
+SET pg_stat_statements.track_utility = FALSE;
+SELECT pg_stat_statements_reset();
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+
+SELECT PLUS_TWO(3);
+SELECT PLUS_TWO(7);
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+
+SELECT PLUS_ONE(8);
+SELECT PLUS_ONE(10);
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- PL/pgSQL function - all-level tracking.
+SET pg_stat_statements.track = 'all';
+SELECT pg_stat_statements_reset();
+
+-- we drop and recreate the functions to avoid any caching funnies
+DROP FUNCTION PLUS_ONE(INTEGER);
+DROP FUNCTION PLUS_TWO(INTEGER);
+
+-- PL/pgSQL function
+CREATE FUNCTION PLUS_TWO(i INTEGER) RETURNS INTEGER AS $$
+DECLARE
+ r INTEGER;
+BEGIN
+ SELECT (i + 1 + 1.0)::INTEGER INTO r;
+ RETURN r;
+END; $$ LANGUAGE plpgsql;
+
+SELECT PLUS_TWO(-1);
+SELECT PLUS_TWO(2);
+
+-- SQL function --- use LIMIT to keep it from being inlined
+CREATE FUNCTION PLUS_ONE(i INTEGER) RETURNS INTEGER AS
+$$ SELECT (i + 1.0)::INTEGER LIMIT 1 $$ LANGUAGE SQL;
+
+SELECT PLUS_ONE(3);
+SELECT PLUS_ONE(1);
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP FUNCTION PLUS_ONE(INTEGER);
+
+--
+-- pg_stat_statements.track = none
+--
+SET pg_stat_statements.track = 'none';
+SELECT pg_stat_statements_reset();
+
+SELECT 1 AS "one";
+SELECT 1 + 1 AS "two";
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
new file mode 100644
index 0000000000..db34e32986
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -0,0 +1,60 @@
+--
+-- Utility commands
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+SELECT 1;
+CREATE TEMP TABLE stats_util_tab (a int, b char(20));
+CREATE INDEX test_b ON stats_util_tab(b);
+DROP TABLE stats_util_tab \;
+DROP TABLE IF EXISTS stats_util_tab;
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS stats_util_tab \;
+Drop Table If Exists stats_util_tab;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+--
+-- Track the total number of rows retrieved or affected by the utility
+-- commands of COPY, FETCH, CREATE TABLE AS, CREATE MATERIALIZED VIEW,
+-- REFRESH MATERIALIZED VIEW and SELECT INTO
+--
+CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a;
+SELECT generate_series(1, 10) c INTO pgss_select_into;
+COPY pgss_ctas (a, b) FROM STDIN;
+11 copy
+12 copy
+13 copy
+\.
+CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas;
+REFRESH MATERIALIZED VIEW pgss_matv;
+BEGIN;
+DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv;
+FETCH NEXT pgss_cursor;
+FETCH FORWARD 5 pgss_cursor;
+FETCH FORWARD ALL pgss_cursor;
+COMMIT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
+
+SELECT pg_stat_statements_reset();
+
+-- SET statements.
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
2.39.1
[text/x-diff] v3-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch (38.7K, ../[email protected]/3-v3-0002-Add-more-test-for-utility-queries-in-pg_stat_stat.patch)
download | inline diff:
From b803bf4388125a481b7d7cc07463de34b82740b1 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Fri, 17 Feb 2023 11:23:14 +0900
Subject: [PATCH v3 2/4] Add more test for utility queries in
pg_stat_statements
This adds coverage for utility statements that are directly impacted by
the normalization of their internal queries, showing the different in
older versions of Postgres and what's on HEAD now that all the utility
nodes are jumbled through their nodes.
This also provides coverage when more normalization is applied to
A_Const and pg_stat_statements, that should check all the DDL patterns
with nodes related to utilities that make use of constants.
---
contrib/pg_stat_statements/Makefile | 2 +-
.../pg_stat_statements/expected/cursors.out | 70 +++
.../pg_stat_statements/expected/utility.out | 494 ++++++++++++++++--
contrib/pg_stat_statements/meson.build | 1 +
contrib/pg_stat_statements/sql/cursors.sql | 30 ++
contrib/pg_stat_statements/sql/utility.sql | 246 ++++++++-
6 files changed, 774 insertions(+), 69 deletions(-)
create mode 100644 contrib/pg_stat_statements/expected/cursors.out
create mode 100644 contrib/pg_stat_statements/sql/cursors.sql
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 8b6d16369c..08147854cf 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -17,7 +17,7 @@ PGFILEDESC = "pg_stat_statements - execution statistics of SQL statements"
LDFLAGS_SL += $(filter -lm, $(LIBS))
REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_stat_statements/pg_stat_statements.conf
-REGRESS = pg_stat_statements utility planning track cleanup oldextversions
+REGRESS = pg_stat_statements cursors utility planning track 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/cursors.out b/contrib/pg_stat_statements/expected/cursors.out
new file mode 100644
index 0000000000..b31a4c77bb
--- /dev/null
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -0,0 +1,70 @@
+--
+-- Cursors
+--
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- DECLARE
+-- SELECT is normalized.
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1;
+CLOSE cursor_stats_1;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+CLOSE cursor_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+------------------------------------------------------+-------+------
+ CLOSE cursor_stats_1 | 2 | 0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(3 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- FETCH
+BEGIN;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3;
+FETCH 1 IN cursor_stats_1;
+ ?column?
+----------
+ 2
+(1 row)
+
+FETCH 1 IN cursor_stats_2;
+ ?column?
+----------
+ 3
+(1 row)
+
+CLOSE cursor_stats_1;
+CLOSE cursor_stats_2;
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+------------------------------------------------------+-------+------
+ BEGIN | 1 | 0
+ CLOSE cursor_stats_1 | 1 | 0
+ CLOSE cursor_stats_2 | 1 | 0
+ COMMIT | 1 | 0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2 | 1 | 0
+ DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3 | 1 | 0
+ FETCH 1 IN cursor_stats_1 | 1 | 1
+ FETCH 1 IN cursor_stats_2 | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
+(9 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 8247591130..4073ea850f 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -9,32 +9,453 @@ SELECT pg_stat_statements_reset();
(1 row)
-SELECT 1;
- ?column?
-----------
- 1
+-- Tables, indexes, triggers
+CREATE TEMP TABLE tab_stats (a int, b char(20));
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0;
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a';
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b;
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0);
+DROP TABLE tab_stats \;
+DROP TABLE IF EXISTS tab_stats \;
+-- This DROP query uses two different strings, still they count as one entry.
+DROP TABLE IF EXISTS tab_stats \;
+Drop Table If Exists tab_stats \;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+NOTICE: table "tab_stats" does not exist, skipping
+NOTICE: table "tab_stats" does not exist, skipping
+NOTICE: table "tab_stats" does not exist, skipping
+ query | calls | rows
+--------------------------------------------------------------------------------------+-------+------
+ ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0) | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b set default 'a' | 1 | 0
+ CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0 | 1 | 0
+ CREATE TEMP TABLE tab_stats (a int, b char(20)) | 1 | 0
+ DROP TABLE IF EXISTS tab_stats | 3 | 0
+ DROP TABLE tab_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(8 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
(1 row)
-CREATE TEMP TABLE stats_util_tab (a int, b char(20));
-CREATE INDEX test_b ON stats_util_tab(b);
-DROP TABLE stats_util_tab \;
-DROP TABLE IF EXISTS stats_util_tab;
-NOTICE: table "stats_util_tab" does not exist, skipping
--- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS stats_util_tab \;
-Drop Table If Exists stats_util_tab;
-NOTICE: table "stats_util_tab" does not exist, skipping
-NOTICE: table "stats_util_tab" does not exist, skipping
+-- Partitions
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
+CREATE TABLE pt_stats1 (a int, b int);
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100);
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200);
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a);
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a);
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index;
+DROP TABLE pt_stats;
+-- Views
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b;
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2;
+DROP VIEW view_stats;
+-- Foreign tables
+CREATE FOREIGN DATA WRAPPER wrapper_stats;
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats;
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats;
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1;
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0);
+DROP FOREIGN TABLE foreign_stats;
+DROP SERVER server_stats;
+DROP FOREIGN DATA WRAPPER wrapper_stats;
+-- Functions
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+ RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
+DROP FUNCTION func_stats;
+-- Rules
+CREATE TABLE tab_rule_stats (a int, b int);
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int);
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+ INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2);
+DROP RULE rules_stats ON tab_rule_stats;
+DROP TABLE tab_rule_stats, tab_rule_stats_2;
+-- Types
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2));
+DROP TYPE stats_type;
+-- Triggers
+CREATE TABLE trigger_tab_stats (a int, b int);
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+ AS $$ BEGIN return OLD; end; $$;
+CREATE TRIGGER trigger_tab_stats
+ AFTER UPDATE ON trigger_tab_stats
+ FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+ EXECUTE FUNCTION trigger_func_stats();
+DROP TABLE trigger_tab_stats;
+-- Policies
+CREATE TABLE tab_policy_stats (a int, b int);
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5);
+DROP TABLE tab_policy_stats;
+-- Statistics
+CREATE TABLE tab_expr_stats (a int, b int);
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
+DROP TABLE tab_expr_stats;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------+-------+------
- CREATE INDEX test_b ON stats_util_tab(b) | 1 | 0
- CREATE TEMP TABLE stats_util_tab (a int, b char(20)) | 1 | 0
- DROP TABLE IF EXISTS stats_util_tab | 3 | 0
- DROP TABLE stats_util_tab | 1 | 0
- SELECT $1 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
-(6 rows)
+ query | calls | rows
+-------------------------------------------------------------------------------------+-------+------
+ ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1 | 1 | 0
+ ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0) | 1 | 0
+ ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index | 1 | 0
+ ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100) | 1 | 0
+ ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2 | 1 | 0
+ CREATE FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats | 1 | 0
+ CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+| 1 | 0
+ RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL | |
+ CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +| 1 | 0
+ AS $$ BEGIN return OLD; end; $$ | |
+ CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) | 1 | 0
+ CREATE INDEX pt_stats_index ON ONLY pt_stats (a) | 1 | 0
+ CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5) | 1 | 0
+ CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD +| 1 | 0
+ INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) | |
+ CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats | 1 | 0
+ CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) | 1 | 0
+ CREATE TABLE pt_stats1 (a int, b int) | 1 | 0
+ CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200) | 1 | 0
+ CREATE TABLE tab_expr_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_policy_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) | 1 | 0
+ CREATE TABLE trigger_tab_stats (a int, b int) | 1 | 0
+ CREATE TRIGGER trigger_tab_stats +| 1 | 0
+ AFTER UPDATE ON trigger_tab_stats +| |
+ FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) +| |
+ EXECUTE FUNCTION trigger_func_stats() | |
+ CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)) | 1 | 0
+ CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b | 1 | 0
+ DROP FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ DROP FOREIGN TABLE foreign_stats | 1 | 0
+ DROP FUNCTION func_stats | 1 | 0
+ DROP RULE rules_stats ON tab_rule_stats | 1 | 0
+ DROP SERVER server_stats | 1 | 0
+ DROP TABLE pt_stats | 1 | 0
+ DROP TABLE tab_expr_stats | 1 | 0
+ DROP TABLE tab_policy_stats | 1 | 0
+ DROP TABLE tab_rule_stats, tab_rule_stats_2 | 1 | 0
+ DROP TABLE trigger_tab_stats | 1 | 0
+ DROP TYPE stats_type | 1 | 0
+ DROP VIEW view_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(39 rows)
+
+-- Transaction statements
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+BEGIN;
+ABORT;
+BEGIN;
+ROLLBACK;
+-- WORK
+BEGIN WORK;
+COMMIT WORK;
+BEGIN WORK;
+ABORT WORK;
+-- TRANSACTION
+BEGIN TRANSACTION;
+COMMIT TRANSACTION;
+BEGIN TRANSACTION;
+ABORT TRANSACTION;
+-- More isolation levels
+BEGIN TRANSACTION DEFERRABLE;
+COMMIT TRANSACTION AND NO CHAIN;
+BEGIN ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- List of A_Const nodes
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+---------------------------------------------------------------------+-------+------
+ ABORT | 4 | 0
+ BEGIN | 6 | 0
+ BEGIN ISOLATION LEVEL SERIALIZABLE | 2 | 0
+ BEGIN TRANSACTION DEFERRABLE | 1 | 0
+ BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE | 1 | 0
+ COMMIT WORK | 6 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(7 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- EXPLAIN statements
+-- A Query is used, normalized by the query jumbling.
+EXPLAIN (costs off) SELECT 1;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+EXPLAIN (costs off) SELECT 2;
+ QUERY PLAN
+------------
+ Result
+(1 row)
+
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3;
+ QUERY PLAN
+--------------------------------------
+ Function Scan on generate_series tab
+ Filter: (a = 3)
+(2 rows)
+
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
+ QUERY PLAN
+--------------------------------------
+ Function Scan on generate_series tab
+ Filter: (a = 7)
+(2 rows)
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-------------------------------------------------------------------------------+-------+------
+ EXPLAIN (costs off) SELECT 1 | 2 | 0
+ EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(3 rows)
+
+-- CALL
+CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$
+DECLARE
+ r int;
+BEGIN
+ SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
+DECLARE
+ r int;
+BEGIN
+ SELECT (i + j)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+CALL sum_one(3);
+CALL sum_one(199);
+CALL sum_two(1,1);
+CALL sum_two(1,2);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------+-------+------
+ CALL sum_one(199) | 1 | 0
+ CALL sum_one(3) | 1 | 0
+ CALL sum_two(1,1) | 1 | 0
+ CALL sum_two(1,2) | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(5 rows)
+
+-- COPY
+CREATE TABLE copy_stats (a int, b int);
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- Some queries with A_Const nodes.
+COPY (SELECT 1) TO STDOUT;
+1
+COPY (SELECT 2) TO STDOUT;
+2
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT;
+1 1
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT;
+2 2
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT;
+1 2
+2 3
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
+1 4
+2 5
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
+1 4
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-------------------------------------------------------------------+-------+------
+ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT | 1 | 1
+ COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT | 1 | 1
+ COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT | 1 | 1
+ COPY (SELECT 1) TO STDOUT | 1 | 1
+ COPY (SELECT 2) TO STDOUT | 1 | 1
+ COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT | 1 | 2
+ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT | 1 | 2
+ SELECT pg_stat_statements_reset() | 1 | 1
+(8 rows)
+
+DROP TABLE copy_stats;
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- CREATE TABLE AS
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_1 AS SELECT 2 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_2 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP TABLE ctas_stats_2;
+CREATE TABLE ctas_stats_2 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
+DROP TABLE ctas_stats_2;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------------------------------------+-------+------
+ CREATE TABLE ctas_stats_1 AS SELECT 1 AS a | 2 | 2
+ CREATE TABLE ctas_stats_2 AS +| 2 | 4
+ SELECT a AS col1, 2::int AS col2 +| |
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
+ DROP TABLE ctas_stats_1 | 2 | 0
+ DROP TABLE ctas_stats_2 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(5 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- CREATE MATERIALIZED VIEW
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP MATERIALIZED VIEW matview_stats_1;
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP MATERIALIZED VIEW matview_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------------------------------------+-------+------
+ CREATE MATERIALIZED VIEW matview_stats_1 AS +| 2 | 2
+ SELECT a AS col1, 2::int AS col2 +| |
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
+ DROP MATERIALIZED VIEW matview_stats_1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(3 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- CREATE VIEW
+CREATE VIEW view_stats_1 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP VIEW view_stats_1;
+CREATE VIEW view_stats_1 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP VIEW view_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-----------------------------------------------------------------+-------+------
+ CREATE VIEW view_stats_1 AS +| 1 | 0
+ SELECT a AS col1, 2::int AS col2 +| |
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
+ CREATE VIEW view_stats_1 AS +| 1 | 0
+ SELECT a AS col1, 4::int AS col2 +| |
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3 | |
+ DROP VIEW view_stats_1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(4 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- Domains
+CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
+ALTER DOMAIN domain_stats SET DEFAULT '3';
+ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
+DROP DOMAIN domain_stats;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+----------------------------------------------------------------------------+-------+------
+ ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1) | 1 | 0
+ ALTER DOMAIN domain_stats SET DEFAULT '3' | 1 | 0
+ CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0) | 1 | 0
+ DROP DOMAIN domain_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(5 rows)
+
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+-- SET statements.
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+-- SET TRANSACTION ISOLATION
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- SET SESSION CHARACTERISTICS
+SET SESSION SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+COMMIT;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+ query | calls | rows
+-------------------------------------------------+-------+------
+ BEGIN | 2 | 0
+ COMMIT | 2 | 0
+ RESET SESSION AUTHORIZATION | 2 | 0
+ RESET enable_seqscan | 1 | 0
+ RESET work_mem | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SET LOCAL SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET SESSION SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL READ COMMITTED | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | 1 | 0
+ SET enable_seqscan = off | 1 | 0
+ SET enable_seqscan = on | 1 | 0
+ SET work_mem = '1MB' | 2 | 0
+ SET work_mem = '2MB' | 1 | 0
+(15 rows)
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
@@ -100,33 +521,12 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset() | 1 | 1
(12 rows)
-DROP MATERIALIZED VIEW pgss_matv;
-DROP TABLE pgss_ctas;
-DROP TABLE pgss_select_into;
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--------------------------
(1 row)
--- SET statements.
--- These use two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------+-------+------
- RESET enable_seqscan | 1 | 0
- RESET work_mem | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
- SET enable_seqscan = off | 1 | 0
- SET enable_seqscan = on | 1 | 0
- SET work_mem = '1MB' | 2 | 0
- SET work_mem = '2MB' | 1 | 0
-(7 rows)
-
+DROP MATERIALIZED VIEW pgss_matv;
+DROP TABLE pgss_ctas;
+DROP TABLE pgss_select_into;
diff --git a/contrib/pg_stat_statements/meson.build b/contrib/pg_stat_statements/meson.build
index 0fb4a9f2e0..3384a0c9c7 100644
--- a/contrib/pg_stat_statements/meson.build
+++ b/contrib/pg_stat_statements/meson.build
@@ -41,6 +41,7 @@ tests += {
'regress': {
'sql': [
'pg_stat_statements',
+ 'cursors',
'utility',
'planning',
'track',
diff --git a/contrib/pg_stat_statements/sql/cursors.sql b/contrib/pg_stat_statements/sql/cursors.sql
new file mode 100644
index 0000000000..60894e675b
--- /dev/null
+++ b/contrib/pg_stat_statements/sql/cursors.sql
@@ -0,0 +1,30 @@
+--
+-- Cursors
+--
+
+-- These tests require track_utility to be enabled.
+SET pg_stat_statements.track_utility = TRUE;
+SELECT pg_stat_statements_reset();
+
+-- DECLARE
+-- SELECT is normalized.
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1;
+CLOSE cursor_stats_1;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+CLOSE cursor_stats_1;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- FETCH
+BEGIN;
+DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
+DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3;
+FETCH 1 IN cursor_stats_1;
+FETCH 1 IN cursor_stats_2;
+CLOSE cursor_stats_1;
+CLOSE cursor_stats_2;
+COMMIT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/sql/utility.sql b/contrib/pg_stat_statements/sql/utility.sql
index db34e32986..2a44d9ee7e 100644
--- a/contrib/pg_stat_statements/sql/utility.sql
+++ b/contrib/pg_stat_statements/sql/utility.sql
@@ -6,14 +6,231 @@
SET pg_stat_statements.track_utility = TRUE;
SELECT pg_stat_statements_reset();
-SELECT 1;
-CREATE TEMP TABLE stats_util_tab (a int, b char(20));
-CREATE INDEX test_b ON stats_util_tab(b);
-DROP TABLE stats_util_tab \;
-DROP TABLE IF EXISTS stats_util_tab;
+-- Tables, indexes, triggers
+CREATE TEMP TABLE tab_stats (a int, b char(20));
+CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0;
+ALTER TABLE tab_stats ALTER COLUMN b set default 'a';
+ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b;
+ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0);
+DROP TABLE tab_stats \;
+DROP TABLE IF EXISTS tab_stats \;
-- This DROP query uses two different strings, still they count as one entry.
-DROP TABLE IF EXISTS stats_util_tab \;
-Drop Table If Exists stats_util_tab;
+DROP TABLE IF EXISTS tab_stats \;
+Drop Table If Exists tab_stats \;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Partitions
+CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a);
+CREATE TABLE pt_stats1 (a int, b int);
+ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100);
+CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200);
+CREATE INDEX pt_stats_index ON ONLY pt_stats (a);
+CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a);
+ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index;
+DROP TABLE pt_stats;
+
+-- Views
+CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b;
+ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2;
+DROP VIEW view_stats;
+
+-- Foreign tables
+CREATE FOREIGN DATA WRAPPER wrapper_stats;
+CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats;
+CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats;
+ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1;
+ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0);
+DROP FOREIGN TABLE foreign_stats;
+DROP SERVER server_stats;
+DROP FOREIGN DATA WRAPPER wrapper_stats;
+
+-- Functions
+CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))
+ RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL;
+DROP FUNCTION func_stats;
+
+-- Rules
+CREATE TABLE tab_rule_stats (a int, b int);
+CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int);
+CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD
+ INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2);
+DROP RULE rules_stats ON tab_rule_stats;
+DROP TABLE tab_rule_stats, tab_rule_stats_2;
+
+-- Types
+CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2));
+DROP TYPE stats_type;
+
+-- Triggers
+CREATE TABLE trigger_tab_stats (a int, b int);
+CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql
+ AS $$ BEGIN return OLD; end; $$;
+CREATE TRIGGER trigger_tab_stats
+ AFTER UPDATE ON trigger_tab_stats
+ FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true)
+ EXECUTE FUNCTION trigger_func_stats();
+DROP TABLE trigger_tab_stats;
+
+-- Policies
+CREATE TABLE tab_policy_stats (a int, b int);
+CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5);
+DROP TABLE tab_policy_stats;
+
+-- Statistics
+CREATE TABLE tab_expr_stats (a int, b int);
+CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
+DROP TABLE tab_expr_stats;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- Transaction statements
+SELECT pg_stat_statements_reset();
+BEGIN;
+ABORT;
+BEGIN;
+ROLLBACK;
+-- WORK
+BEGIN WORK;
+COMMIT WORK;
+BEGIN WORK;
+ABORT WORK;
+-- TRANSACTION
+BEGIN TRANSACTION;
+COMMIT TRANSACTION;
+BEGIN TRANSACTION;
+ABORT TRANSACTION;
+-- More isolation levels
+BEGIN TRANSACTION DEFERRABLE;
+COMMIT TRANSACTION AND NO CHAIN;
+BEGIN ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- List of A_Const nodes
+BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
+COMMIT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- EXPLAIN statements
+-- A Query is used, normalized by the query jumbling.
+EXPLAIN (costs off) SELECT 1;
+EXPLAIN (costs off) SELECT 2;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3;
+EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- CALL
+CREATE OR REPLACE PROCEDURE sum_one(i int) AS $$
+DECLARE
+ r int;
+BEGIN
+ SELECT (i + i)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+CREATE OR REPLACE PROCEDURE sum_two(i int, j int) AS $$
+DECLARE
+ r int;
+BEGIN
+ SELECT (i + j)::int INTO r;
+END; $$ LANGUAGE plpgsql;
+SELECT pg_stat_statements_reset();
+CALL sum_one(3);
+CALL sum_one(199);
+CALL sum_two(1,1);
+CALL sum_two(1,2);
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- COPY
+CREATE TABLE copy_stats (a int, b int);
+SELECT pg_stat_statements_reset();
+-- Some queries with A_Const nodes.
+COPY (SELECT 1) TO STDOUT;
+COPY (SELECT 2) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT;
+COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT;
+COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
+COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
+
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+DROP TABLE copy_stats;
+SELECT pg_stat_statements_reset();
+
+-- CREATE TABLE AS
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE TABLE ctas_stats_1 AS SELECT 1 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_1 AS SELECT 2 AS a;
+DROP TABLE ctas_stats_1;
+CREATE TABLE ctas_stats_2 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP TABLE ctas_stats_2;
+CREATE TABLE ctas_stats_2 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
+DROP TABLE ctas_stats_2;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE MATERIALIZED VIEW
+-- SELECT queries are normalized, creating matching query IDs.
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP MATERIALIZED VIEW matview_stats_1;
+CREATE MATERIALIZED VIEW matview_stats_1 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP MATERIALIZED VIEW matview_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- CREATE VIEW
+CREATE VIEW view_stats_1 AS
+ SELECT a AS col1, 2::int AS col2
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2;
+DROP VIEW view_stats_1;
+CREATE VIEW view_stats_1 AS
+ SELECT a AS col1, 4::int AS col2
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
+DROP VIEW view_stats_1;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- Domains
+CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0);
+ALTER DOMAIN domain_stats SET DEFAULT '3';
+ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
+DROP DOMAIN domain_stats;
+SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
+
+-- SET statements.
+-- These use two different strings, still they count as one entry.
+SET work_mem = '1MB';
+Set work_mem = '1MB';
+SET work_mem = '2MB';
+RESET work_mem;
+SET enable_seqscan = off;
+SET enable_seqscan = on;
+RESET enable_seqscan;
+-- SET TRANSACTION ISOLATION
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+-- SET SESSION CHARACTERISTICS
+SET SESSION SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+BEGIN;
+SET LOCAL SESSION AUTHORIZATION DEFAULT;
+RESET SESSION AUTHORIZATION;
+COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset();
@@ -40,21 +257,8 @@ FETCH FORWARD ALL pgss_cursor;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+SELECT pg_stat_statements_reset();
DROP MATERIALIZED VIEW pgss_matv;
DROP TABLE pgss_ctas;
DROP TABLE pgss_select_into;
-
-SELECT pg_stat_statements_reset();
-
--- SET statements.
--- These use two different strings, still they count as one entry.
-SET work_mem = '1MB';
-Set work_mem = '1MB';
-SET work_mem = '2MB';
-RESET work_mem;
-SET enable_seqscan = off;
-SET enable_seqscan = on;
-RESET enable_seqscan;
-
-SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
--
2.39.1
[text/x-diff] v3-0003-Apply-normalization-to-A_Const-and-utilities-in-p.patch (37.8K, ../[email protected]/4-v3-0003-Apply-normalization-to-A_Const-and-utilities-in-p.patch)
download | inline diff:
From 9b873a776c6b9c5bd02fee896d60bd45c8ff7a1d Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Wed, 8 Feb 2023 11:00:01 +0900
Subject: [PATCH v3 3/4] Apply normalization to A_Const and utilities in
pg_stat_statements
Its value is now ignored and location is stored, so as it is possible to
apply query normalization across more query types:
- SET
- CALL
- COPY TO with queries
- View, matviews and CTAS
- EXPLAIN
- Triggers
- Rules
- Statistics
---
src/include/nodes/parsenodes.h | 8 +-
src/include/nodes/primnodes.h | 9 +-
src/backend/nodes/queryjumblefuncs.c | 23 +-
doc/src/sgml/pgstatstatements.sgml | 7 +-
.../pg_stat_statements/expected/cursors.out | 32 +-
.../expected/pg_stat_statements.out | 2 +-
.../pg_stat_statements/expected/utility.out | 294 +++++++++---------
.../pg_stat_statements/pg_stat_statements.c | 4 +-
8 files changed, 178 insertions(+), 201 deletions(-)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f7d7f10f7d..259e814253 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3221,14 +3221,18 @@ typedef struct InlineCodeBlock
* list contains copies of the expressions for all output arguments, in the
* order of the procedure's declared arguments. (outargs is never evaluated,
* but is useful to the caller as a reference for what to assign to.)
+ * The transformed call state is not relevant in the query jumbling, only the
+ * function call is.
* ----------------------
*/
typedef struct CallStmt
{
NodeTag type;
FuncCall *funccall; /* from the parser */
- FuncExpr *funcexpr; /* transformed call, with only input args */
- List *outargs; /* transformed output-argument expressions */
+ /* transformed call, with only input args */
+ FuncExpr *funcexpr pg_node_attr(query_jumble_ignore);
+ /* transformed output-argument expressions */
+ List *outargs pg_node_attr(query_jumble_ignore);
} CallStmt;
typedef struct CallContext
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 1be1642d92..188ff1d249 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -128,8 +128,10 @@ typedef struct TableFunc
* CREATE MATERIALIZED VIEW
*
* For CREATE MATERIALIZED VIEW, viewQuery is the parsed-but-not-rewritten
- * SELECT Query for the view; otherwise it's NULL. (Although it's actually
- * Query*, we declare it as Node* to avoid a forward reference.)
+ * SELECT Query for the view; otherwise it's NULL. This is irrelevant in
+ * the query jumbling as CreateTableAsStmt already includes a reference to
+ * its own Query, so ignore it. (Although it's actually Query*, we declare
+ * it as Node* to avoid a forward reference.)
*/
typedef struct IntoClause
{
@@ -141,7 +143,8 @@ typedef struct IntoClause
List *options; /* options from WITH clause */
OnCommitAction onCommit; /* what do we do at COMMIT? */
char *tableSpaceName; /* table space to use, or NULL */
- Node *viewQuery; /* materialized view's SELECT query */
+ /* materialized view's SELECT query */
+ Node *viewQuery pg_node_attr(query_jumble_ignore);
bool skipData; /* true for WITH NO DATA */
} IntoClause;
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index d7fd72d70f..0f08f4c75e 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -323,29 +323,8 @@ _jumbleA_Const(JumbleState *jstate, Node *node)
if (!expr->isnull)
{
JUMBLE_FIELD(val.node.type);
- switch (nodeTag(&expr->val))
- {
- case T_Integer:
- JUMBLE_FIELD(val.ival.ival);
- break;
- case T_Float:
- JUMBLE_STRING(val.fval.fval);
- break;
- case T_Boolean:
- JUMBLE_FIELD(val.boolval.boolval);
- break;
- case T_String:
- JUMBLE_STRING(val.sval.sval);
- break;
- case T_BitString:
- JUMBLE_STRING(val.bsval.bsval);
- break;
- default:
- elog(ERROR, "unrecognized node type: %d",
- (int) nodeTag(&expr->val));
- break;
- }
}
+ JUMBLE_LOCATION(location);
}
static void
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
index efc36da602..cee9376916 100644
--- a/doc/src/sgml/pgstatstatements.sgml
+++ b/doc/src/sgml/pgstatstatements.sgml
@@ -489,11 +489,12 @@
Plannable queries (that is, <command>SELECT</command>, <command>INSERT</command>,
<command>UPDATE</command>, <command>DELETE</command>, and <command>MERGE</command>) are combined into a single
<structname>pg_stat_statements</structname> entry whenever they have identical query
- structures according to an internal hash calculation. Typically, two
+ structures according to an internal hash calculation. The same rule
+ applies to utility commands (that is, all other commands), and are normalized
+ when they have an identical hash calculation. Typically, two
queries will be considered the same for this purpose if they are
semantically equivalent except for the values of literal constants
- appearing in the query. Utility commands (that is, all other commands)
- are compared strictly on the basis of their textual query strings, however.
+ appearing in the query.
</para>
<note>
diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_stat_statements/expected/cursors.out
index b31a4c77bb..fd2bef87bd 100644
--- a/contrib/pg_stat_statements/expected/cursors.out
+++ b/contrib/pg_stat_statements/expected/cursors.out
@@ -16,11 +16,11 @@ CLOSE cursor_stats_1;
DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2;
CLOSE cursor_stats_1;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------+-------+------
- CLOSE cursor_stats_1 | 2 | 0
- DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-------------------------------------------------------+-------+------
+ CLOSE cursor_stats_1 | 2 | 0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(3 rows)
SELECT pg_stat_statements_reset();
@@ -49,17 +49,17 @@ CLOSE cursor_stats_1;
CLOSE cursor_stats_2;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-------------------------------------------------------+-------+------
- BEGIN | 1 | 0
- CLOSE cursor_stats_1 | 1 | 0
- CLOSE cursor_stats_2 | 1 | 0
- COMMIT | 1 | 0
- DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2 | 1 | 0
- DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT 3 | 1 | 0
- FETCH 1 IN cursor_stats_1 | 1 | 1
- FETCH 1 IN cursor_stats_2 | 1 | 1
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-------------------------------------------------------+-------+------
+ BEGIN | 1 | 0
+ CLOSE cursor_stats_1 | 1 | 0
+ CLOSE cursor_stats_2 | 1 | 0
+ COMMIT | 1 | 0
+ DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 | 1 | 0
+ DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 | 1 | 0
+ FETCH 1 IN cursor_stats_1 | 1 | 1
+ FETCH 1 IN cursor_stats_2 | 1 | 1
+ SELECT pg_stat_statements_reset() | 1 | 1
(9 rows)
SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 05722db413..da0909a6b2 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -311,7 +311,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
wal_records > $2 as wal_records_generated, +| | | | |
wal_records >= rows as wal_records_ge_rows +| | | | |
FROM pg_stat_statements ORDER BY query COLLATE "C" | | | | |
- SET pg_stat_statements.track_utility = FALSE | 1 | 0 | f | f | t
+ SET pg_stat_statements.track_utility = $1 | 1 | 0 | f | f | t
UPDATE pgss_test SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
(7 rows)
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 4073ea850f..62601e1787 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -24,16 +24,16 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
NOTICE: table "tab_stats" does not exist, skipping
NOTICE: table "tab_stats" does not exist, skipping
NOTICE: table "tab_stats" does not exist, skipping
- query | calls | rows
---------------------------------------------------------------------------------------+-------+------
- ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0) | 1 | 0
- ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b | 1 | 0
- ALTER TABLE tab_stats ALTER COLUMN b set default 'a' | 1 | 0
- CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0 | 1 | 0
- CREATE TEMP TABLE tab_stats (a int, b char(20)) | 1 | 0
- DROP TABLE IF EXISTS tab_stats | 3 | 0
- DROP TABLE tab_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-----------------------------------------------------------------------------+-------+------
+ ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> $1) | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING $1 || b | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b set default $1 | 1 | 0
+ CREATE INDEX index_stats ON tab_stats(b, (b || $1), (b || $2)) WHERE a > $3 | 1 | 0
+ CREATE TEMP TABLE tab_stats (a int, b char($1)) | 1 | 0
+ DROP TABLE IF EXISTS tab_stats | 3 | 0
+ DROP TABLE tab_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(8 rows)
SELECT pg_stat_statements_reset();
@@ -96,53 +96,53 @@ CREATE TABLE tab_expr_stats (a int, b int);
CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
DROP TABLE tab_expr_stats;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
--------------------------------------------------------------------------------------+-------+------
- ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1 | 1 | 0
- ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0) | 1 | 0
- ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index | 1 | 0
- ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100) | 1 | 0
- ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2 | 1 | 0
- CREATE FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats | 1 | 0
- CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+| 1 | 0
- RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL | |
- CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +| 1 | 0
- AS $$ BEGIN return OLD; end; $$ | |
- CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) | 1 | 0
- CREATE INDEX pt_stats_index ON ONLY pt_stats (a) | 1 | 0
- CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5) | 1 | 0
- CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD +| 1 | 0
- INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) | |
- CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats | 1 | 0
- CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) | 1 | 0
- CREATE TABLE pt_stats1 (a int, b int) | 1 | 0
- CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200) | 1 | 0
- CREATE TABLE tab_expr_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_policy_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_rule_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) | 1 | 0
- CREATE TABLE trigger_tab_stats (a int, b int) | 1 | 0
- CREATE TRIGGER trigger_tab_stats +| 1 | 0
- AFTER UPDATE ON trigger_tab_stats +| |
- FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) +| |
- EXECUTE FUNCTION trigger_func_stats() | |
- CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)) | 1 | 0
- CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b | 1 | 0
- DROP FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- DROP FOREIGN TABLE foreign_stats | 1 | 0
- DROP FUNCTION func_stats | 1 | 0
- DROP RULE rules_stats ON tab_rule_stats | 1 | 0
- DROP SERVER server_stats | 1 | 0
- DROP TABLE pt_stats | 1 | 0
- DROP TABLE tab_expr_stats | 1 | 0
- DROP TABLE tab_policy_stats | 1 | 0
- DROP TABLE tab_rule_stats, tab_rule_stats_2 | 1 | 0
- DROP TABLE trigger_tab_stats | 1 | 0
- DROP TYPE stats_type | 1 | 0
- DROP VIEW view_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-----------------------------------------------------------------------------------+-------+------
+ ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT $1 | 1 | 0
+ ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> $1) | 1 | 0
+ ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index | 1 | 0
+ ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM ($1) TO ($2) | 1 | 0
+ ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT $1 | 1 | 0
+ CREATE FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats | 1 | 0
+ CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAULT lower($2)) +| 1 | 0
+ RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL | |
+ CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +| 1 | 0
+ AS $$ BEGIN return OLD; end; $$ | |
+ CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) | 1 | 0
+ CREATE INDEX pt_stats_index ON ONLY pt_stats (a) | 1 | 0
+ CREATE POLICY policy_stats ON tab_policy_stats USING (a = $1) WITH CHECK (b < $2) | 1 | 0
+ CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD +| 1 | 0
+ INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2) | |
+ CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, ($1*a), ($2*b) FROM tab_expr_stats | 1 | 0
+ CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) | 1 | 0
+ CREATE TABLE pt_stats1 (a int, b int) | 1 | 0
+ CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM ($1) TO ($2) | 1 | 0
+ CREATE TABLE tab_expr_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_policy_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) | 1 | 0
+ CREATE TABLE trigger_tab_stats (a int, b int) | 1 | 0
+ CREATE TRIGGER trigger_tab_stats +| 1 | 0
+ AFTER UPDATE ON trigger_tab_stats +| |
+ FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3) +| |
+ EXECUTE FUNCTION trigger_func_stats() | |
+ CREATE TYPE stats_type as (f1 numeric($1, $2), f2 numeric($3, $4)) | 1 | 0
+ CREATE VIEW view_stats AS SELECT $1::int AS a, $2::int AS b | 1 | 0
+ DROP FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ DROP FOREIGN TABLE foreign_stats | 1 | 0
+ DROP FUNCTION func_stats | 1 | 0
+ DROP RULE rules_stats ON tab_rule_stats | 1 | 0
+ DROP SERVER server_stats | 1 | 0
+ DROP TABLE pt_stats | 1 | 0
+ DROP TABLE tab_expr_stats | 1 | 0
+ DROP TABLE tab_policy_stats | 1 | 0
+ DROP TABLE tab_rule_stats, tab_rule_stats_2 | 1 | 0
+ DROP TABLE trigger_tab_stats | 1 | 0
+ DROP TYPE stats_type | 1 | 0
+ DROP VIEW view_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(39 rows)
-- Transaction statements
@@ -177,15 +177,15 @@ COMMIT;
BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
----------------------------------------------------------------------+-------+------
- ABORT | 4 | 0
- BEGIN | 6 | 0
- BEGIN ISOLATION LEVEL SERIALIZABLE | 2 | 0
- BEGIN TRANSACTION DEFERRABLE | 1 | 0
- BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE | 1 | 0
- COMMIT WORK | 6 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+--------------------------------------------------------+-------+------
+ ABORT | 4 | 0
+ BEGIN | 6 | 0
+ BEGIN ISOLATION LEVEL $1 | 2 | 0
+ BEGIN TRANSACTION $1 | 1 | 0
+ BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE | 1 | 0
+ COMMIT WORK | 6 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(7 rows)
SELECT pg_stat_statements_reset();
@@ -223,11 +223,11 @@ EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 7;
(2 rows)
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
--------------------------------------------------------------------------------+-------+------
- EXPLAIN (costs off) SELECT 1 | 2 | 0
- EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS tab(a) WHERE a = 3 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+---------------------------------------------------------------------------------+-------+------
+ EXPLAIN (costs off) SELECT $1 | 2 | 0
+ EXPLAIN (costs off) SELECT a FROM generate_series($1,$2) AS tab(a) WHERE a = $3 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(3 rows)
-- CALL
@@ -256,12 +256,10 @@ CALL sum_two(1,2);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls | rows
-----------------------------------+-------+------
- CALL sum_one(199) | 1 | 0
- CALL sum_one(3) | 1 | 0
- CALL sum_two(1,1) | 1 | 0
- CALL sum_two(1,2) | 1 | 0
+ CALL sum_one($1) | 2 | 0
+ CALL sum_two($1,$2) | 2 | 0
SELECT pg_stat_statements_reset() | 1 | 1
-(5 rows)
+(3 rows)
-- COPY
CREATE TABLE copy_stats (a int, b int);
@@ -289,17 +287,14 @@ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
1 4
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
--------------------------------------------------------------------+-------+------
- COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT | 1 | 1
- COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT | 1 | 1
- COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT | 1 | 1
- COPY (SELECT 1) TO STDOUT | 1 | 1
- COPY (SELECT 2) TO STDOUT | 1 | 1
- COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT | 1 | 2
- COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT | 1 | 2
- SELECT pg_stat_statements_reset() | 1 | 1
-(8 rows)
+ query | calls | rows
+---------------------------------------------------------------------+-------+------
+ COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT | 1 | 1
+ COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT | 2 | 2
+ COPY (SELECT $1) TO STDOUT | 2 | 2
+ COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT | 2 | 4
+ SELECT pg_stat_statements_reset() | 1 | 1
+(5 rows)
DROP TABLE copy_stats;
SELECT pg_stat_statements_reset();
@@ -323,15 +318,15 @@ CREATE TABLE ctas_stats_2 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 1;
DROP TABLE ctas_stats_2;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------------------------------------+-------+------
- CREATE TABLE ctas_stats_1 AS SELECT 1 AS a | 2 | 2
- CREATE TABLE ctas_stats_2 AS +| 2 | 4
- SELECT a AS col1, 2::int AS col2 +| |
- FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
- DROP TABLE ctas_stats_1 | 2 | 0
- DROP TABLE ctas_stats_2 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+--------------------------------------------------------------------+-------+------
+ CREATE TABLE ctas_stats_1 AS SELECT $1 AS a | 2 | 2
+ CREATE TABLE ctas_stats_2 AS +| 2 | 4
+ SELECT a AS col1, $1::int AS col2 +| |
+ FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 | |
+ DROP TABLE ctas_stats_1 | 2 | 0
+ DROP TABLE ctas_stats_2 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(5 rows)
SELECT pg_stat_statements_reset();
@@ -351,13 +346,13 @@ CREATE MATERIALIZED VIEW matview_stats_1 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
DROP MATERIALIZED VIEW matview_stats_1;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------------------------------------+-------+------
- CREATE MATERIALIZED VIEW matview_stats_1 AS +| 2 | 2
- SELECT a AS col1, 2::int AS col2 +| |
- FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
- DROP MATERIALIZED VIEW matview_stats_1 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+--------------------------------------------------------------------+-------+------
+ CREATE MATERIALIZED VIEW matview_stats_1 AS +| 2 | 2
+ SELECT a AS col1, $1::int AS col2 +| |
+ FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 | |
+ DROP MATERIALIZED VIEW matview_stats_1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(3 rows)
SELECT pg_stat_statements_reset();
@@ -376,17 +371,14 @@ CREATE VIEW view_stats_1 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
DROP VIEW view_stats_1;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------------------------------------+-------+------
- CREATE VIEW view_stats_1 AS +| 1 | 0
- SELECT a AS col1, 2::int AS col2 +| |
- FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
- CREATE VIEW view_stats_1 AS +| 1 | 0
- SELECT a AS col1, 4::int AS col2 +| |
- FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3 | |
- DROP VIEW view_stats_1 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
-(4 rows)
+ query | calls | rows
+--------------------------------------------------------------------+-------+------
+ CREATE VIEW view_stats_1 AS +| 2 | 0
+ SELECT a AS col1, $1::int AS col2 +| |
+ FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 | |
+ DROP VIEW view_stats_1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(3 rows)
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
@@ -400,13 +392,13 @@ ALTER DOMAIN domain_stats SET DEFAULT '3';
ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
DROP DOMAIN domain_stats;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-----------------------------------------------------------------------------+-------+------
- ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1) | 1 | 0
- ALTER DOMAIN domain_stats SET DEFAULT '3' | 1 | 0
- CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0) | 1 | 0
- DROP DOMAIN domain_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-----------------------------------------------------------------------------+-------+------
+ ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > $1) | 1 | 0
+ ALTER DOMAIN domain_stats SET DEFAULT $1 | 1 | 0
+ CREATE DOMAIN domain_stats AS int CHECK (VALUE > $1) | 1 | 0
+ DROP DOMAIN domain_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(5 rows)
SELECT pg_stat_statements_reset();
@@ -438,24 +430,20 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT;
RESET SESSION AUTHORIZATION;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
--------------------------------------------------+-------+------
- BEGIN | 2 | 0
- COMMIT | 2 | 0
- RESET SESSION AUTHORIZATION | 2 | 0
- RESET enable_seqscan | 1 | 0
- RESET work_mem | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
- SET LOCAL SESSION AUTHORIZATION DEFAULT | 1 | 0
- SET SESSION SESSION AUTHORIZATION DEFAULT | 1 | 0
- SET TRANSACTION ISOLATION LEVEL READ COMMITTED | 1 | 0
- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ | 1 | 0
- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | 1 | 0
- SET enable_seqscan = off | 1 | 0
- SET enable_seqscan = on | 1 | 0
- SET work_mem = '1MB' | 2 | 0
- SET work_mem = '2MB' | 1 | 0
-(15 rows)
+ query | calls | rows
+----------------------------------------------+-------+------
+ BEGIN | 2 | 0
+ COMMIT | 2 | 0
+ RESET SESSION AUTHORIZATION | 2 | 0
+ RESET enable_seqscan | 1 | 0
+ RESET work_mem | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SET LOCAL SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET SESSION SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL $1 COMMITTED | 3 | 0
+ SET enable_seqscan = $1 | 2 | 0
+ SET work_mem = $1 | 3 | 0
+(11 rows)
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
@@ -505,20 +493,20 @@ FETCH FORWARD ALL pgss_cursor;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-----------------------------------------------------------------------------+-------+------
- BEGIN | 1 | 0
- COMMIT | 1 | 0
- COPY pgss_ctas (a, b) FROM STDIN | 1 | 3
- CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 1 | 13
- CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate_series(1, 10) a | 1 | 10
- DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 1 | 0
- FETCH FORWARD 5 pgss_cursor | 1 | 5
- FETCH FORWARD ALL pgss_cursor | 1 | 7
- FETCH NEXT pgss_cursor | 1 | 1
- REFRESH MATERIALIZED VIEW pgss_matv | 1 | 13
- SELECT generate_series(1, 10) c INTO pgss_select_into | 1 | 10
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-------------------------------------------------------------------------+-------+------
+ BEGIN | 1 | 0
+ COMMIT | 1 | 0
+ COPY pgss_ctas (a, b) FROM STDIN | 1 | 3
+ CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_ctas | 1 | 13
+ CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_series($2, $3) a | 1 | 10
+ DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv | 1 | 0
+ FETCH FORWARD 5 pgss_cursor | 1 | 5
+ FETCH FORWARD ALL pgss_cursor | 1 | 7
+ FETCH NEXT pgss_cursor | 1 | 1
+ REFRESH MATERIALIZED VIEW pgss_matv | 1 | 13
+ SELECT generate_series($1, $2) c INTO pgss_select_into | 1 | 10
+ SELECT pg_stat_statements_reset() | 1 | 1
(12 rows)
SELECT pg_stat_statements_reset();
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index ad1fe44496..5285c3f7fa 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -836,8 +836,10 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query, JumbleState *jstate)
if (query->utilityStmt)
{
if (pgss_track_utility && !PGSS_HANDLED_UTILITY(query->utilityStmt))
+ {
query->queryId = UINT64CONST(0);
- return;
+ return;
+ }
}
/*
--
2.39.1
[text/x-diff] v3-0004-Remove-normalization-of-A_Const-nodes.patch (24.1K, ../[email protected]/5-v3-0004-Remove-normalization-of-A_Const-nodes.patch)
download | inline diff:
From 9c4d7501877ef0b8a2680196f7c86950ac80c430 Mon Sep 17 00:00:00 2001
From: Michael Paquier <[email protected]>
Date: Thu, 16 Feb 2023 09:17:32 +0900
Subject: [PATCH v3 4/4] Remove normalization of A_Const nodes
Doing so leads to weird cases with commands that can define a
transaction isolation (SET TRANSACTION and BEGIN), as the normalization
is not able to copy with the full field, yet.
Applying normalization of Const nodes to DDLs changes the states of the
following commands:
- DECLARE
- EXPLAIN
- CREATE MATERIALIZED VIEW
- CTAS
At the end, this should be merged with the previous patch, but keeping
it separate shows the difference of behavior between the two approaches
in the regression tests of pg_stat_statements.
---
src/backend/nodes/queryjumblefuncs.c | 23 +-
.../expected/pg_stat_statements.out | 2 +-
.../pg_stat_statements/expected/utility.out | 224 +++++++++---------
3 files changed, 141 insertions(+), 108 deletions(-)
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 0f08f4c75e..d7fd72d70f 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -323,8 +323,29 @@ _jumbleA_Const(JumbleState *jstate, Node *node)
if (!expr->isnull)
{
JUMBLE_FIELD(val.node.type);
+ switch (nodeTag(&expr->val))
+ {
+ case T_Integer:
+ JUMBLE_FIELD(val.ival.ival);
+ break;
+ case T_Float:
+ JUMBLE_STRING(val.fval.fval);
+ break;
+ case T_Boolean:
+ JUMBLE_FIELD(val.boolval.boolval);
+ break;
+ case T_String:
+ JUMBLE_STRING(val.sval.sval);
+ break;
+ case T_BitString:
+ JUMBLE_STRING(val.bsval.bsval);
+ break;
+ default:
+ elog(ERROR, "unrecognized node type: %d",
+ (int) nodeTag(&expr->val));
+ break;
+ }
}
- JUMBLE_LOCATION(location);
}
static void
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index da0909a6b2..05722db413 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -311,7 +311,7 @@ FROM pg_stat_statements ORDER BY query COLLATE "C";
wal_records > $2 as wal_records_generated, +| | | | |
wal_records >= rows as wal_records_ge_rows +| | | | |
FROM pg_stat_statements ORDER BY query COLLATE "C" | | | | |
- SET pg_stat_statements.track_utility = $1 | 1 | 0 | f | f | t
+ SET pg_stat_statements.track_utility = FALSE | 1 | 0 | f | f | t
UPDATE pgss_test SET b = $1 WHERE a > $2 | 1 | 3 | t | t | t
(7 rows)
diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_stat_statements/expected/utility.out
index 62601e1787..061898e003 100644
--- a/contrib/pg_stat_statements/expected/utility.out
+++ b/contrib/pg_stat_statements/expected/utility.out
@@ -24,16 +24,16 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
NOTICE: table "tab_stats" does not exist, skipping
NOTICE: table "tab_stats" does not exist, skipping
NOTICE: table "tab_stats" does not exist, skipping
- query | calls | rows
------------------------------------------------------------------------------+-------+------
- ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> $1) | 1 | 0
- ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING $1 || b | 1 | 0
- ALTER TABLE tab_stats ALTER COLUMN b set default $1 | 1 | 0
- CREATE INDEX index_stats ON tab_stats(b, (b || $1), (b || $2)) WHERE a > $3 | 1 | 0
- CREATE TEMP TABLE tab_stats (a int, b char($1)) | 1 | 0
- DROP TABLE IF EXISTS tab_stats | 3 | 0
- DROP TABLE tab_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+--------------------------------------------------------------------------------------+-------+------
+ ALTER TABLE tab_stats ADD CONSTRAINT a_nonzero CHECK (a <> 0) | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b TYPE text USING 'data' || b | 1 | 0
+ ALTER TABLE tab_stats ALTER COLUMN b set default 'a' | 1 | 0
+ CREATE INDEX index_stats ON tab_stats(b, (b || 'data1'), (b || 'data2')) WHERE a > 0 | 1 | 0
+ CREATE TEMP TABLE tab_stats (a int, b char(20)) | 1 | 0
+ DROP TABLE IF EXISTS tab_stats | 3 | 0
+ DROP TABLE tab_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(8 rows)
SELECT pg_stat_statements_reset();
@@ -96,53 +96,53 @@ CREATE TABLE tab_expr_stats (a int, b int);
CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats;
DROP TABLE tab_expr_stats;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------------------------------------------------------+-------+------
- ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT $1 | 1 | 0
- ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> $1) | 1 | 0
- ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index | 1 | 0
- ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM ($1) TO ($2) | 1 | 0
- ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT $1 | 1 | 0
- CREATE FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats | 1 | 0
- CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAULT lower($2)) +| 1 | 0
- RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL | |
- CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +| 1 | 0
- AS $$ BEGIN return OLD; end; $$ | |
- CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) | 1 | 0
- CREATE INDEX pt_stats_index ON ONLY pt_stats (a) | 1 | 0
- CREATE POLICY policy_stats ON tab_policy_stats USING (a = $1) WITH CHECK (b < $2) | 1 | 0
- CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD +| 1 | 0
- INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2) | |
- CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, ($1*a), ($2*b) FROM tab_expr_stats | 1 | 0
- CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) | 1 | 0
- CREATE TABLE pt_stats1 (a int, b int) | 1 | 0
- CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM ($1) TO ($2) | 1 | 0
- CREATE TABLE tab_expr_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_policy_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_rule_stats (a int, b int) | 1 | 0
- CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) | 1 | 0
- CREATE TABLE trigger_tab_stats (a int, b int) | 1 | 0
- CREATE TRIGGER trigger_tab_stats +| 1 | 0
- AFTER UPDATE ON trigger_tab_stats +| |
- FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3) +| |
- EXECUTE FUNCTION trigger_func_stats() | |
- CREATE TYPE stats_type as (f1 numeric($1, $2), f2 numeric($3, $4)) | 1 | 0
- CREATE VIEW view_stats AS SELECT $1::int AS a, $2::int AS b | 1 | 0
- DROP FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
- DROP FOREIGN TABLE foreign_stats | 1 | 0
- DROP FUNCTION func_stats | 1 | 0
- DROP RULE rules_stats ON tab_rule_stats | 1 | 0
- DROP SERVER server_stats | 1 | 0
- DROP TABLE pt_stats | 1 | 0
- DROP TABLE tab_expr_stats | 1 | 0
- DROP TABLE tab_policy_stats | 1 | 0
- DROP TABLE tab_rule_stats, tab_rule_stats_2 | 1 | 0
- DROP TABLE trigger_tab_stats | 1 | 0
- DROP TYPE stats_type | 1 | 0
- DROP VIEW view_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+-------------------------------------------------------------------------------------+-------+------
+ ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEFAULT 1 | 1 | 0
+ ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero CHECK (b <> 0) | 1 | 0
+ ALTER INDEX pt_stats_index ATTACH PARTITION pt_stats2_index | 1 | 0
+ ALTER TABLE pt_stats ATTACH PARTITION pt_stats1 FOR VALUES FROM (0) TO (100) | 1 | 0
+ ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2 | 1 | 0
+ CREATE FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE FOREIGN TABLE foreign_stats (a int) SERVER server_stats | 1 | 0
+ CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text DEFAULT lower('b_data'))+| 1 | 0
+ RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $$ LANGUAGE SQL | |
+ CREATE FUNCTION trigger_func_stats () RETURNS trigger LANGUAGE plpgsql +| 1 | 0
+ AS $$ BEGIN return OLD; end; $$ | |
+ CREATE INDEX pt_stats2_index ON ONLY pt_stats2 (a) | 1 | 0
+ CREATE INDEX pt_stats_index ON ONLY pt_stats (a) | 1 | 0
+ CREATE POLICY policy_stats ON tab_policy_stats USING (a = 5) WITH CHECK (b < 5) | 1 | 0
+ CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO INSTEAD +| 1 | 0
+ INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) | |
+ CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b) FROM tab_expr_stats | 1 | 0
+ CREATE TABLE pt_stats (a int, b int) PARTITION BY range (a) | 1 | 0
+ CREATE TABLE pt_stats1 (a int, b int) | 1 | 0
+ CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FROM (100) TO (200) | 1 | 0
+ CREATE TABLE tab_expr_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_policy_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats (a int, b int) | 1 | 0
+ CREATE TABLE tab_rule_stats_2 (a int, b int, c int, d int) | 1 | 0
+ CREATE TABLE trigger_tab_stats (a int, b int) | 1 | 0
+ CREATE TRIGGER trigger_tab_stats +| 1 | 0
+ AFTER UPDATE ON trigger_tab_stats +| |
+ FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) +| |
+ EXECUTE FUNCTION trigger_func_stats() | |
+ CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(35, 2)) | 1 | 0
+ CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b | 1 | 0
+ DROP FOREIGN DATA WRAPPER wrapper_stats | 1 | 0
+ DROP FOREIGN TABLE foreign_stats | 1 | 0
+ DROP FUNCTION func_stats | 1 | 0
+ DROP RULE rules_stats ON tab_rule_stats | 1 | 0
+ DROP SERVER server_stats | 1 | 0
+ DROP TABLE pt_stats | 1 | 0
+ DROP TABLE tab_expr_stats | 1 | 0
+ DROP TABLE tab_policy_stats | 1 | 0
+ DROP TABLE tab_rule_stats, tab_rule_stats_2 | 1 | 0
+ DROP TABLE trigger_tab_stats | 1 | 0
+ DROP TYPE stats_type | 1 | 0
+ DROP VIEW view_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(39 rows)
-- Transaction statements
@@ -177,15 +177,15 @@ COMMIT;
BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
---------------------------------------------------------+-------+------
- ABORT | 4 | 0
- BEGIN | 6 | 0
- BEGIN ISOLATION LEVEL $1 | 2 | 0
- BEGIN TRANSACTION $1 | 1 | 0
- BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE | 1 | 0
- COMMIT WORK | 6 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+---------------------------------------------------------------------+-------+------
+ ABORT | 4 | 0
+ BEGIN | 6 | 0
+ BEGIN ISOLATION LEVEL SERIALIZABLE | 2 | 0
+ BEGIN TRANSACTION DEFERRABLE | 1 | 0
+ BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT DEFERRABLE | 1 | 0
+ COMMIT WORK | 6 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(7 rows)
SELECT pg_stat_statements_reset();
@@ -256,10 +256,12 @@ CALL sum_two(1,2);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
query | calls | rows
-----------------------------------+-------+------
- CALL sum_one($1) | 2 | 0
- CALL sum_two($1,$2) | 2 | 0
+ CALL sum_one(199) | 1 | 0
+ CALL sum_one(3) | 1 | 0
+ CALL sum_two(1,1) | 1 | 0
+ CALL sum_two(1,2) | 1 | 0
SELECT pg_stat_statements_reset() | 1 | 1
-(3 rows)
+(5 rows)
-- COPY
CREATE TABLE copy_stats (a int, b int);
@@ -287,14 +289,17 @@ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT;
COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT;
1 4
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
----------------------------------------------------------------------+-------+------
- COPY (DELETE FROM copy_stats WHERE a = $1 RETURNING *) TO STDOUT | 1 | 1
- COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) TO STDOUT | 2 | 2
- COPY (SELECT $1) TO STDOUT | 2 | 2
- COPY (UPDATE copy_stats SET b = b + $1 RETURNING *) TO STDOUT | 2 | 4
- SELECT pg_stat_statements_reset() | 1 | 1
-(5 rows)
+ query | calls | rows
+-------------------------------------------------------------------+-------+------
+ COPY (DELETE FROM copy_stats WHERE a = 1 RETURNING *) TO STDOUT | 1 | 1
+ COPY (INSERT INTO copy_stats VALUES (1, 1) RETURNING *) TO STDOUT | 1 | 1
+ COPY (INSERT INTO copy_stats VALUES (2, 2) RETURNING *) TO STDOUT | 1 | 1
+ COPY (SELECT 1) TO STDOUT | 1 | 1
+ COPY (SELECT 2) TO STDOUT | 1 | 1
+ COPY (UPDATE copy_stats SET b = b + 1 RETURNING *) TO STDOUT | 1 | 2
+ COPY (UPDATE copy_stats SET b = b + 2 RETURNING *) TO STDOUT | 1 | 2
+ SELECT pg_stat_statements_reset() | 1 | 1
+(8 rows)
DROP TABLE copy_stats;
SELECT pg_stat_statements_reset();
@@ -371,14 +376,17 @@ CREATE VIEW view_stats_1 AS
FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3;
DROP VIEW view_stats_1;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
---------------------------------------------------------------------+-------+------
- CREATE VIEW view_stats_1 AS +| 2 | 0
- SELECT a AS col1, $1::int AS col2 +| |
- FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AND a > $5 | |
- DROP VIEW view_stats_1 | 2 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
-(3 rows)
+ query | calls | rows
+-----------------------------------------------------------------+-------+------
+ CREATE VIEW view_stats_1 AS +| 1 | 0
+ SELECT a AS col1, 2::int AS col2 +| |
+ FROM generate_series(1, 10) AS tab(a) WHERE a < 5 AND a > 2 | |
+ CREATE VIEW view_stats_1 AS +| 1 | 0
+ SELECT a AS col1, 4::int AS col2 +| |
+ FROM generate_series(1, 5) AS tab(a) WHERE a < 4 AND a > 3 | |
+ DROP VIEW view_stats_1 | 2 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+(4 rows)
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
@@ -392,13 +400,13 @@ ALTER DOMAIN domain_stats SET DEFAULT '3';
ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1);
DROP DOMAIN domain_stats;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
------------------------------------------------------------------------------+-------+------
- ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > $1) | 1 | 0
- ALTER DOMAIN domain_stats SET DEFAULT $1 | 1 | 0
- CREATE DOMAIN domain_stats AS int CHECK (VALUE > $1) | 1 | 0
- DROP DOMAIN domain_stats | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
+ query | calls | rows
+----------------------------------------------------------------------------+-------+------
+ ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1) | 1 | 0
+ ALTER DOMAIN domain_stats SET DEFAULT '3' | 1 | 0
+ CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0) | 1 | 0
+ DROP DOMAIN domain_stats | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
(5 rows)
SELECT pg_stat_statements_reset();
@@ -430,20 +438,24 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT;
RESET SESSION AUTHORIZATION;
COMMIT;
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
- query | calls | rows
-----------------------------------------------+-------+------
- BEGIN | 2 | 0
- COMMIT | 2 | 0
- RESET SESSION AUTHORIZATION | 2 | 0
- RESET enable_seqscan | 1 | 0
- RESET work_mem | 1 | 0
- SELECT pg_stat_statements_reset() | 1 | 1
- SET LOCAL SESSION AUTHORIZATION DEFAULT | 1 | 0
- SET SESSION SESSION AUTHORIZATION DEFAULT | 1 | 0
- SET TRANSACTION ISOLATION LEVEL $1 COMMITTED | 3 | 0
- SET enable_seqscan = $1 | 2 | 0
- SET work_mem = $1 | 3 | 0
-(11 rows)
+ query | calls | rows
+-------------------------------------------------+-------+------
+ BEGIN | 2 | 0
+ COMMIT | 2 | 0
+ RESET SESSION AUTHORIZATION | 2 | 0
+ RESET enable_seqscan | 1 | 0
+ RESET work_mem | 1 | 0
+ SELECT pg_stat_statements_reset() | 1 | 1
+ SET LOCAL SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET SESSION SESSION AUTHORIZATION DEFAULT | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL READ COMMITTED | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL REPEATABLE READ | 1 | 0
+ SET TRANSACTION ISOLATION LEVEL SERIALIZABLE | 1 | 0
+ SET enable_seqscan = off | 1 | 0
+ SET enable_seqscan = on | 1 | 0
+ SET work_mem = '1MB' | 2 | 0
+ SET work_mem = '2MB' | 1 | 0
+(15 rows)
SELECT pg_stat_statements_reset();
pg_stat_statements_reset
--
2.39.1
[application/pgp-signature] signature.asc (833B, ../[email protected]/6-signature.asc)
download
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Normalization of utility queries in pg_stat_statements
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox