Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pTw1G-0005aK-TP for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Feb 2023 02:35:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pTw1F-0005Ub-OW for pgsql-hackers@arkaria.postgresql.org; Mon, 20 Feb 2023 02:35:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pTw1F-0005UR-2U for pgsql-hackers@lists.postgresql.org; Mon, 20 Feb 2023 02:35:09 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pTw1B-0008PN-0L for pgsql-hackers@lists.postgresql.org; Mon, 20 Feb 2023 02:35:08 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id 3B2405C00DF; Sun, 19 Feb 2023 21:35:04 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute5.internal (MEProxy); Sun, 19 Feb 2023 21:35:04 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:date:date:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to; s=fm1; t=1676860504; x=1676946904; bh=y/ayE/Gbo/ JXxmvCvs/uYUbglJSM8fgql10L8v0PK6U=; b=j9fmdvU49zgqy9i8uhdex8WHxk XhpVikfBIGQH6PF8lQCbQGZJqsUTCbDL6bgaft2BsFnxIA7i8xuFRIby31j5R4JD 5bHh+ud32Ku71vhFApp3w3xsqNvmyck8wz2zY+LRIU5c7SdflFXhqTYALSNaZLob 8a1bgO1CYx+xP7z7AgRWcMChX+46tyrfK/LVUCT4DtN9+/ATwVIfsCyHoM/pEKGD DZLo49usR8yHWwwXpMkKfu81JCvZDah6Do++QQ8b5B7N2ec6iXE+EztGYPUiMK0q EzxyWeq6+mgMbT+2rlBLyXMDDGuVhDPTjxy3w1Sgsj9Nkv6XQdqOOEchUwyQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:date:date:feedback-id :feedback-id:from:from:in-reply-to:in-reply-to:message-id :mime-version:references:reply-to:sender:subject:subject:to:to :x-me-proxy:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm1; t=1676860504; x=1676946904; bh=y/ayE/Gbo/JXxmvCvs/uYUbglJSM 8fgql10L8v0PK6U=; b=ZlXBOBy/lbbxL38a5VvsWxXjv2f2GIFnR/Q2P/NTBCE2 G3UX+xzV7zpkvKqoiOeox53FMYspNsJ6iw2nwmR4XhEoPT4O6VypMJ4qf+57yMI1 b9A8u1ewIrpQkc1NYl8wFsq7bpmto7D030ULqDNorr6bKOivF9c5LT1ieJr6kRZs NScUlIrGoCTG2mkF9n1PGpyTuZZ57nmrn1jDu9VKX14bV5MeJV6LdKqeR26UxIjP BhaGt7WUdC4449UZTnaP1zIy+AJs60MwZ9aS4WI4QObHs8wCkvk0ZOUWClOBgbZP s9B68hIG3LYRu/ImwGbvcOWcuHpGXftHXnSZcHv7dw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrudejgedggeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne gfrhhlucfvnfffucdljedtmdenucfjughrpeffhffvvefukfhfgggtuggjsehgtderredt tddvnecuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesph grqhhuihgvrhdrgiihiieqnecuggftrfgrthhtvghrnhepteelieefudffhffhtdetleeg geegfffhkeeuveetiefgudduvedutefggeeivdejnecuvehluhhsthgvrhfuihiivgepud enucfrrghrrghmpehmrghilhhfrhhomhepmhhitghhrggvlhesphgrqhhuihgvrhdrgiih ii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 19 Feb 2023 21:35:02 -0500 (EST) Date: Mon, 20 Feb 2023 11:34:59 +0900 From: Michael Paquier To: "Drouvot, Bertrand" Cc: Postgres hackers Subject: Re: Normalization of utility queries in pg_stat_statements Message-ID: References: <0e067034-e92c-302b-24ed-492bca228e26@gmail.com> <14965f63-6d78-45ff-d029-daafe8eddb40@gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="LCtTpdE0yDFueWxR" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --LCtTpdE0yDFueWxR Content-Type: multipart/mixed; boundary="hkSjNufW++3pjfwS" Content-Disposition: inline --hkSjNufW++3pjfwS Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Feb 20, 2023 at 11:32:23AM +0900, Michael Paquier wrote: > These last ones are staying around for a few more weeks, until the > middle of the next CF, I guess. After all this is done, the final > changes are very short, showing the effects of the normalization, as > of: > 6 files changed, 45 insertions(+), 35 deletions(-) With the patches.. -- Michael --hkSjNufW++3pjfwS Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0003-Apply-normalization-to-A_Const-and-utilities-in-p.patch" Content-Transfer-Encoding: quoted-printable =46rom da6b7a7c5199c4db6ea8906df6c0e256559735f1 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Wed, 8 Feb 2023 11:00:01 +0900 Subject: [PATCH v4 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 | 14 +- .../expected/level_tracking.out | 20 +- .../pg_stat_statements/expected/utility.out | 174 ++++++++---------- .../pg_stat_statements/pg_stat_statements.c | 4 +- 8 files changed, 117 insertions(+), 142 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 evaluat= ed, * 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; =20 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; =20 diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query= jumblefuncs.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); } =20 static void diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatem= ents.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, SELECT, INSERT<= /command>, UPDATE, DELETE, and MERG= E) are combined into a single pg_stat_statements entry whenever they have id= entical 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 norm= alized + 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, howe= ver. + appearing in the query. =20 diff --git a/contrib/pg_stat_statements/expected/cursors.out b/contrib/pg_s= tat_statements/expected/cursors.out index 5d0dc196f9..46375ea905 100644 --- a/contrib/pg_stat_statements/expected/cursors.out +++ b/contrib/pg_stat_statements/expected/cursors.out @@ -16,10 +16,10 @@ CLOSE cursor_stats_1; DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 2; CLOSE cursor_stats_1; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query =20 --------+------+------------------------------------------------------ + calls | rows | query =20 +-------+------+------------------------------------------------------- 2 | 0 | CLOSE cursor_stats_1 - 2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT 1 + 2 | 0 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 1 | 1 | SELECT pg_stat_statements_reset() (3 rows) =20 @@ -49,14 +49,14 @@ CLOSE cursor_stats_1; CLOSE cursor_stats_2; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query =20 --------+------+------------------------------------------------------ + calls | rows | query =20 +-------+------+------------------------------------------------------- 1 | 0 | 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 | DECLARE cursor_stats_1 CURSOR WITH HOLD FOR SELECT $1 + 1 | 0 | DECLARE cursor_stats_2 CURSOR WITH HOLD FOR SELECT $1 1 | 1 | FETCH 1 IN cursor_stats_1 1 | 1 | FETCH 1 IN cursor_stats_2 1 | 1 | SELECT pg_stat_statements_reset() diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contr= ib/pg_stat_statements/expected/level_tracking.out index c824ebdac5..615be0775f 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -49,22 +49,22 @@ BEGIN END; $$; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", toplevel; - toplevel | calls | query =20 -----------+-------+-------------------------------------- + toplevel | calls | query =20 +----------+-------+----------------------------------- f | 1 | DELETE FROM stats_track_tab t | 1 | DELETE FROM stats_track_tab - t | 1 | DO $$ + - | | BEGIN + - | | DELETE FROM stats_track_tab; + + t | 1 | DO $$ + + | | BEGIN + + | | DELETE FROM stats_track_tab; + | | END; $$ - t | 1 | DO LANGUAGE plpgsql $$ + - | | BEGIN + - | | -- this is a SELECT + - | | PERFORM 'hello world'::TEXT; + + t | 1 | DO LANGUAGE plpgsql $$ + + | | BEGIN + + | | -- this is a SELECT + + | | PERFORM 'hello world'::TEXT; + | | END; $$ f | 1 | SELECT $1::TEXT t | 1 | SELECT pg_stat_statements_reset() - t | 1 | SET pg_stat_statements.track =3D 'all' + t | 1 | SET pg_stat_statements.track =3D $1 (7 rows) =20 -- PL/pgSQL function - top-level tracking. diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_s= tat_statements/expected/utility.out index dbb8f661c0..f50de29440 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -24,13 +24,13 @@ SELECT calls, rows, query 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 - calls | rows | query = =20 --------+------+-----------------------------------------------------------= --------------------------- - 1 | 0 | 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)) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ------------------ + 1 | 0 | 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)) 3 | 0 | DROP TABLE IF EXISTS tab_stats 1 | 0 | DROP TABLE tab_stats 1 | 1 | SELECT pg_stat_statements_reset() @@ -96,40 +96,40 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= -------------------------- - 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEF= AULT 1 - 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero= CHECK (b <> 0) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ------------------------ + 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEF= AULT $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 (0) TO (100) - 1 | 0 | ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT 2 + 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_s= tats - 1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT 'a_data', b text= DEFAULT lower('b_data'))+ + 1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAU= LT lower($2)) + | | RETURNS text AS $$ SELECT $1::text || '_' || $2::text; $= $ LANGUAGE SQL - 1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANG= UAGE plpgsql + + 1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANG= UAGE plpgsql + | | AS $$ BEGIN return OLD; end; $$ 1 | 0 | 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 = =3D 5) WITH CHECK (b < 5) - 1 | 0 | CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO = INSTEAD + - | | INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) + 1 | 0 | CREATE POLICY policy_stats ON tab_policy_stats USING (a = =3D $1) WITH CHECK (b < $2) + 1 | 0 | CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO = INSTEAD + + | | INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2) 1 | 0 | CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_st= ats - 1 | 0 | CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, (2*a), (3*b= ) FROM tab_expr_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 FR= OM (100) TO (200) + 1 | 0 | CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FR= OM ($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 = + - | | AFTER UPDATE ON trigger_tab_stats = + - | | FOR EACH ROW WHEN (OLD.a < 0 AND OLD.b < 1 AND true) = + + 1 | 0 | CREATE TRIGGER trigger_tab_stats = + + | | AFTER UPDATE ON trigger_tab_stats = + + | | FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3) = + | | EXECUTE FUNCTION trigger_func_stats() - 1 | 0 | CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(3= 5, 2)) - 1 | 0 | CREATE VIEW view_stats AS SELECT 1::int AS a, 2::int AS b + 1 | 0 | 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 @@ -179,14 +179,14 @@ COMMIT; BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ---------- + calls | rows | query =20 +-------+------+-------------------------------------------------------- 4 | 0 | ABORT 6 | 0 | BEGIN - 2 | 0 | BEGIN ISOLATION LEVEL SERIALIZABLE - 1 | 0 | BEGIN TRANSACTION DEFERRABLE - 1 | 0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, D= EFERRABLE - 1 | 0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT D= EFERRABLE + 2 | 0 | BEGIN ISOLATION LEVEL $1 + 1 | 0 | BEGIN TRANSACTION $1 + 1 | 0 | BEGIN TRANSACTION $1 DEFERRABLE, $2 ONLY, $3 WRITE, $4 + 1 | 0 | BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE 7 | 0 | COMMIT WORK 1 | 1 | SELECT pg_stat_statements_reset() (8 rows) @@ -226,10 +226,10 @@ EXPLAIN (costs off) SELECT a FROM generate_series(1,1= 0) AS tab(a) WHERE a =3D 7; (2 rows) =20 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= -------------------- - 2 | 0 | EXPLAIN (costs off) SELECT 1 - 2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series(1,10) AS= tab(a) WHERE a =3D 3 + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ---------------------- + 2 | 0 | EXPLAIN (costs off) SELECT $1 + 2 | 0 | EXPLAIN (costs off) SELECT a FROM generate_series($1,$2) A= S tab(a) WHERE a =3D $3 1 | 1 | SELECT pg_stat_statements_reset() (3 rows) =20 @@ -259,12 +259,10 @@ CALL sum_two(1,2); SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; calls | rows | query =20 -------+------+----------------------------------- - 1 | 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) + 2 | 0 | CALL sum_one($1) + 2 | 0 | CALL sum_two($1,$2) 1 | 1 | SELECT pg_stat_statements_reset() -(5 rows) +(3 rows) =20 -- COPY CREATE TABLE copy_stats (a int, b int); @@ -292,17 +290,14 @@ COPY (UPDATE copy_stats SET b =3D b + 2 RETURNING *) = TO STDOUT; COPY (DELETE FROM copy_stats WHERE a =3D 1 RETURNING *) TO STDOUT; 1 4 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= -------- - 1 | 1 | COPY (DELETE FROM copy_stats WHERE a =3D 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 | 2 | COPY (UPDATE copy_stats SET b =3D b + 1 RETURNING *) TO ST= DOUT - 1 | 2 | COPY (UPDATE copy_stats SET b =3D b + 2 RETURNING *) TO ST= DOUT + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ---------- + 1 | 1 | COPY (DELETE FROM copy_stats WHERE a =3D $1 RETURNING *) T= O STDOUT + 2 | 2 | COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) = TO STDOUT + 2 | 2 | COPY (SELECT $1) TO STDOUT + 2 | 4 | COPY (UPDATE copy_stats SET b =3D b + $1 RETURNING *) TO S= TDOUT 1 | 1 | SELECT pg_stat_statements_reset() -(8 rows) +(5 rows) =20 DROP TABLE copy_stats; SELECT pg_stat_statements_reset(); @@ -326,12 +321,12 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------ - 2 | 2 | CREATE TABLE ctas_stats_1 AS SELECT 1 AS a - 2 | 4 | 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 + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= --------- + 2 | 2 | CREATE TABLE ctas_stats_1 AS SELECT $1 AS a + 2 | 4 | CREATE TABLE ctas_stats_2 AS = + + | | SELECT a AS col1, $1::int AS col2 = + + | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AN= D a > $5 2 | 0 | DROP TABLE ctas_stats_1 2 | 0 | DROP TABLE ctas_stats_2 1 | 1 | SELECT pg_stat_statements_reset() @@ -354,11 +349,11 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------ - 2 | 2 | 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 + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= --------- + 2 | 2 | CREATE MATERIALIZED VIEW matview_stats_1 AS = + + | | SELECT a AS col1, $1::int AS col2 = + + | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AN= D a > $5 2 | 0 | DROP MATERIALIZED VIEW matview_stats_1 1 | 1 | SELECT pg_stat_statements_reset() (3 rows) @@ -379,17 +374,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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------ - 1 | 0 | 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 - 1 | 0 | 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 + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= --------- + 2 | 0 | CREATE VIEW view_stats_1 AS = + + | | SELECT a AS col1, $1::int AS col2 = + + | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AN= D a > $5 2 | 0 | DROP VIEW view_stats_1 1 | 1 | SELECT pg_stat_statements_reset() -(4 rows) +(3 rows) =20 SELECT pg_stat_statements_reset(); pg_stat_statements_reset=20 @@ -403,11 +395,11 @@ ALTER DOMAIN domain_stats SET DEFAULT '3'; ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1); DROP DOMAIN domain_stats; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ----------------- - 1 | 0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one C= HECK (VALUE > 1) - 1 | 0 | ALTER DOMAIN domain_stats SET DEFAULT '3' - 1 | 0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > 0) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ------------------ + 1 | 0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one C= HECK (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 | 1 | SELECT pg_stat_statements_reset() (5 rows) @@ -441,8 +433,8 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT; RESET SESSION AUTHORIZATION; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query =20 --------+------+------------------------------------------------- + calls | rows | query =20 +-------+------+---------------------------------------------- 2 | 0 | BEGIN 2 | 0 | COMMIT 2 | 0 | RESET SESSION AUTHORIZATION @@ -451,14 +443,10 @@ SELECT calls, rows, query FROM pg_stat_statements ORD= ER BY query COLLATE "C"; 1 | 1 | SELECT pg_stat_statements_reset() 1 | 0 | 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 =3D off - 1 | 0 | SET enable_seqscan =3D on - 2 | 0 | SET work_mem =3D '1MB' - 1 | 0 | SET work_mem =3D '2MB' -(15 rows) + 3 | 0 | SET TRANSACTION ISOLATION LEVEL $1 COMMITTED + 2 | 0 | SET enable_seqscan =3D $1 + 3 | 0 | SET work_mem =3D $1 +(11 rows) =20 SELECT pg_stat_statements_reset(); pg_stat_statements_reset=20 @@ -508,19 +496,19 @@ FETCH FORWARD ALL pgss_cursor; =20 COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ----------------- + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= -------------- 1 | 0 | BEGIN 1 | 0 | COMMIT 1 | 3 | COPY pgss_ctas (a, b) FROM STDIN 1 | 13 | CREATE MATERIALIZED VIEW pgss_matv AS SELECT * FROM pgss_c= tas - 1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, 'ctas' b FROM generate= _series(1, 10) a + 1 | 10 | CREATE TABLE pgss_ctas AS SELECT a, $1 b FROM generate_ser= ies($2, $3) a 1 | 0 | DECLARE pgss_cursor CURSOR FOR SELECT * FROM pgss_matv 1 | 5 | FETCH FORWARD 5 pgss_cursor 1 | 7 | FETCH FORWARD ALL pgss_cursor 1 | 1 | FETCH NEXT pgss_cursor 1 | 13 | REFRESH MATERIALIZED VIEW pgss_matv - 1 | 10 | SELECT generate_series(1, 10) c INTO pgss_select_into + 1 | 10 | SELECT generate_series($1, $2) c INTO pgss_select_into 1 | 1 | SELECT pg_stat_statements_reset() (12 rows) =20 @@ -548,9 +536,7 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER= BY query COLLATE "C"; 1 | 0 | RESET enable_seqscan 1 | 0 | RESET work_mem 1 | 1 | SELECT pg_stat_statements_reset() - 1 | 0 | SET enable_seqscan =3D off - 1 | 0 | SET enable_seqscan =3D on - 2 | 0 | SET work_mem =3D '1MB' - 1 | 0 | SET work_mem =3D '2MB' -(7 rows) + 2 | 0 | SET enable_seqscan =3D $1 + 3 | 0 | SET work_mem =3D $1 +(5 rows) =20 diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_s= tat_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 *que= ry, JumbleState *jstate) if (query->utilityStmt) { if (pgss_track_utility && !PGSS_HANDLED_UTILITY(query->utilityStmt)) + { query->queryId =3D UINT64CONST(0); - return; + return; + } } =20 /* --=20 2.39.2 --hkSjNufW++3pjfwS Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0004-Remove-normalization-of-A_Const-nodes.patch" Content-Transfer-Encoding: quoted-printable =46rom dfe2b9d15788fe04c9205738ba66201c0730fe90 Mon Sep 17 00:00:00 2001 =46rom: Michael Paquier Date: Thu, 16 Feb 2023 09:17:32 +0900 Subject: [PATCH v4 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/level_tracking.out | 20 +-- .../pg_stat_statements/expected/utility.out | 136 ++++++++++-------- 3 files changed, 107 insertions(+), 72 deletions(-) diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/query= jumblefuncs.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); } =20 static void diff --git a/contrib/pg_stat_statements/expected/level_tracking.out b/contr= ib/pg_stat_statements/expected/level_tracking.out index 615be0775f..c824ebdac5 100644 --- a/contrib/pg_stat_statements/expected/level_tracking.out +++ b/contrib/pg_stat_statements/expected/level_tracking.out @@ -49,22 +49,22 @@ BEGIN END; $$; SELECT toplevel, calls, query FROM pg_stat_statements ORDER BY query COLLATE "C", toplevel; - toplevel | calls | query =20 -----------+-------+----------------------------------- + toplevel | calls | query =20 +----------+-------+-------------------------------------- f | 1 | DELETE FROM stats_track_tab t | 1 | DELETE FROM stats_track_tab - t | 1 | DO $$ + - | | BEGIN + - | | DELETE FROM stats_track_tab; + + t | 1 | DO $$ + + | | BEGIN + + | | DELETE FROM stats_track_tab; + | | END; $$ - t | 1 | DO LANGUAGE plpgsql $$ + - | | BEGIN + - | | -- this is a SELECT + - | | PERFORM 'hello world'::TEXT; + + t | 1 | DO LANGUAGE plpgsql $$ + + | | BEGIN + + | | -- this is a SELECT + + | | PERFORM 'hello world'::TEXT; + | | END; $$ f | 1 | SELECT $1::TEXT t | 1 | SELECT pg_stat_statements_reset() - t | 1 | SET pg_stat_statements.track =3D $1 + t | 1 | SET pg_stat_statements.track =3D 'all' (7 rows) =20 -- PL/pgSQL function - top-level tracking. diff --git a/contrib/pg_stat_statements/expected/utility.out b/contrib/pg_s= tat_statements/expected/utility.out index f50de29440..826ada4eaa 100644 --- a/contrib/pg_stat_statements/expected/utility.out +++ b/contrib/pg_stat_statements/expected/utility.out @@ -24,13 +24,13 @@ SELECT calls, rows, query 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 - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------------------ - 1 | 0 | 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)) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= --------------------------- + 1 | 0 | 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)) 3 | 0 | DROP TABLE IF EXISTS tab_stats 1 | 0 | DROP TABLE tab_stats 1 | 1 | SELECT pg_stat_statements_reset() @@ -96,40 +96,40 @@ 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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------------------------ - 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEF= AULT $1 - 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD CONSTRAINT b_nonzero= CHECK (b <> $1) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= -------------------------- + 1 | 0 | ALTER FOREIGN TABLE foreign_stats ADD COLUMN b integer DEF= AULT 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 ($1) TO ($2) - 1 | 0 | ALTER VIEW view_stats ALTER COLUMN a SET DEFAULT $1 + 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_s= tats - 1 | 0 | CREATE FUNCTION func_stats(a text DEFAULT $1, b text DEFAU= LT lower($2)) + + 1 | 0 | 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 - 1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANG= UAGE plpgsql + + 1 | 0 | CREATE FUNCTION trigger_func_stats () RETURNS trigger LANG= UAGE plpgsql + | | AS $$ BEGIN return OLD; end; $$ 1 | 0 | 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 = =3D $1) WITH CHECK (b < $2) - 1 | 0 | CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO = INSTEAD + - | | INSERT INTO tab_rule_stats_2 VALUES(new.*, $1, $2) + 1 | 0 | CREATE POLICY policy_stats ON tab_policy_stats USING (a = =3D 5) WITH CHECK (b < 5) + 1 | 0 | CREATE RULE rules_stats AS ON INSERT TO tab_rule_stats DO = INSTEAD + + | | INSERT INTO tab_rule_stats_2 VALUES(new.*, 1, 2) 1 | 0 | CREATE SERVER server_stats FOREIGN DATA WRAPPER wrapper_st= ats - 1 | 0 | CREATE STATISTICS tab_expr_stats_1 (mcv) ON a, ($1*a), ($2= *b) FROM tab_expr_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 FR= OM ($1) TO ($2) + 1 | 0 | CREATE TABLE pt_stats2 PARTITION OF pt_stats FOR VALUES FR= OM (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 = + - | | AFTER UPDATE ON trigger_tab_stats = + - | | FOR EACH ROW WHEN (OLD.a < $1 AND OLD.b < $2 AND $3) = + + 1 | 0 | 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() - 1 | 0 | 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 | CREATE TYPE stats_type as (f1 numeric(35, 6), f2 numeric(3= 5, 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 @@ -179,14 +179,14 @@ COMMIT; BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, DEFERRABLE; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query =20 --------+------+-------------------------------------------------------- + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ---------- 4 | 0 | ABORT 6 | 0 | BEGIN - 2 | 0 | BEGIN ISOLATION LEVEL $1 - 1 | 0 | BEGIN TRANSACTION $1 - 1 | 0 | BEGIN TRANSACTION $1 DEFERRABLE, $2 ONLY, $3 WRITE, $4 - 1 | 0 | BEGIN TRANSACTION $1 ONLY, $2 WRITE, $3, $4 DEFERRABLE + 2 | 0 | BEGIN ISOLATION LEVEL SERIALIZABLE + 1 | 0 | BEGIN TRANSACTION DEFERRABLE + 1 | 0 | BEGIN TRANSACTION NOT DEFERRABLE, READ ONLY, READ WRITE, D= EFERRABLE + 1 | 0 | BEGIN TRANSACTION READ ONLY, READ WRITE, DEFERRABLE, NOT D= EFERRABLE 7 | 0 | COMMIT WORK 1 | 1 | SELECT pg_stat_statements_reset() (8 rows) @@ -259,10 +259,12 @@ CALL sum_two(1,2); SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; calls | rows | query =20 -------+------+----------------------------------- - 2 | 0 | CALL sum_one($1) - 2 | 0 | CALL sum_two($1,$2) + 1 | 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 | 1 | SELECT pg_stat_statements_reset() -(3 rows) +(5 rows) =20 -- COPY CREATE TABLE copy_stats (a int, b int); @@ -290,14 +292,17 @@ COPY (UPDATE copy_stats SET b =3D b + 2 RETURNING *) = TO STDOUT; COPY (DELETE FROM copy_stats WHERE a =3D 1 RETURNING *) TO STDOUT; 1 4 SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ---------- - 1 | 1 | COPY (DELETE FROM copy_stats WHERE a =3D $1 RETURNING *) T= O STDOUT - 2 | 2 | COPY (INSERT INTO copy_stats VALUES ($1, $2) RETURNING *) = TO STDOUT - 2 | 2 | COPY (SELECT $1) TO STDOUT - 2 | 4 | COPY (UPDATE copy_stats SET b =3D b + $1 RETURNING *) TO S= TDOUT + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= -------- + 1 | 1 | COPY (DELETE FROM copy_stats WHERE a =3D 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 | 2 | COPY (UPDATE copy_stats SET b =3D b + 1 RETURNING *) TO ST= DOUT + 1 | 2 | COPY (UPDATE copy_stats SET b =3D b + 2 RETURNING *) TO ST= DOUT 1 | 1 | SELECT pg_stat_statements_reset() -(5 rows) +(8 rows) =20 DROP TABLE copy_stats; SELECT pg_stat_statements_reset(); @@ -374,14 +379,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 calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= --------- - 2 | 0 | CREATE VIEW view_stats_1 AS = + - | | SELECT a AS col1, $1::int AS col2 = + - | | FROM generate_series($2, $3) AS tab(a) WHERE a < $4 AN= D a > $5 + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ------ + 1 | 0 | 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 + 1 | 0 | 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 2 | 0 | DROP VIEW view_stats_1 1 | 1 | SELECT pg_stat_statements_reset() -(3 rows) +(4 rows) =20 SELECT pg_stat_statements_reset(); pg_stat_statements_reset=20 @@ -395,11 +403,11 @@ ALTER DOMAIN domain_stats SET DEFAULT '3'; ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one CHECK (VALUE > 1); DROP DOMAIN domain_stats; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query = =20 --------+------+-----------------------------------------------------------= ------------------ - 1 | 0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one C= HECK (VALUE > $1) - 1 | 0 | ALTER DOMAIN domain_stats SET DEFAULT $1 - 1 | 0 | CREATE DOMAIN domain_stats AS int CHECK (VALUE > $1) + calls | rows | query = =20 +-------+------+-----------------------------------------------------------= ----------------- + 1 | 0 | ALTER DOMAIN domain_stats ADD CONSTRAINT higher_than_one C= HECK (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 | 1 | SELECT pg_stat_statements_reset() (5 rows) @@ -433,8 +441,8 @@ SET LOCAL SESSION AUTHORIZATION DEFAULT; RESET SESSION AUTHORIZATION; COMMIT; SELECT calls, rows, query FROM pg_stat_statements ORDER BY query COLLATE "= C"; - calls | rows | query =20 --------+------+---------------------------------------------- + calls | rows | query =20 +-------+------+------------------------------------------------- 2 | 0 | BEGIN 2 | 0 | COMMIT 2 | 0 | RESET SESSION AUTHORIZATION @@ -443,10 +451,14 @@ SELECT calls, rows, query FROM pg_stat_statements ORD= ER BY query COLLATE "C"; 1 | 1 | SELECT pg_stat_statements_reset() 1 | 0 | SET LOCAL SESSION AUTHORIZATION DEFAULT 1 | 0 | SET SESSION SESSION AUTHORIZATION DEFAULT - 3 | 0 | SET TRANSACTION ISOLATION LEVEL $1 COMMITTED - 2 | 0 | SET enable_seqscan =3D $1 - 3 | 0 | SET work_mem =3D $1 -(11 rows) + 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 =3D off + 1 | 0 | SET enable_seqscan =3D on + 2 | 0 | SET work_mem =3D '1MB' + 1 | 0 | SET work_mem =3D '2MB' +(15 rows) =20 SELECT pg_stat_statements_reset(); pg_stat_statements_reset=20 @@ -536,7 +548,9 @@ SELECT calls, rows, query FROM pg_stat_statements ORDER= BY query COLLATE "C"; 1 | 0 | RESET enable_seqscan 1 | 0 | RESET work_mem 1 | 1 | SELECT pg_stat_statements_reset() - 2 | 0 | SET enable_seqscan =3D $1 - 3 | 0 | SET work_mem =3D $1 -(5 rows) + 1 | 0 | SET enable_seqscan =3D off + 1 | 0 | SET enable_seqscan =3D on + 2 | 0 | SET work_mem =3D '1MB' + 1 | 0 | SET work_mem =3D '2MB' +(7 rows) =20 --=20 2.39.2 --hkSjNufW++3pjfwS-- --LCtTpdE0yDFueWxR Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmPy3FMACgkQnvQgOdby QH0TJhAAp/p2BA7VKkNCV//co1sqp3NkIdvgNOIdZSAPXP0ipnII5iBhw11mHhYa GqGfv/yXyCTI0isG3YPbHAbNpCxTv0aZe4RZoT7pPHMq6RqrKrlyIkaTblA/9BZ4 I+qB5WNDStJEHeZebMTFCxaXI0FAHAEXTEMu/58hgyVKJ3RaJMqJUj1QzbgdEwpl LPkYHqs1etGhVbRia6pxMYH+PNXAQDJ93AwLn90I+9/CxcI+J2gOq0SXCB8kjHzs Z/S2kRjuVI06xYGUvBYm0R2cJq4HgloTb30Rh54BzEO1giSc5FdM30HokMxEwQSz Vp7F7bfKLsCKyf88ru0+ppxaArXTrXuBSPuC4h3FUaOGux4mopl/eJ63Mi4yhgwN mbgAGr7f+ZHmLgO+lKDxV2VhEXyaWg821UEnRscjYRIeBnzozLGb0GXc5LNNY9vs /kTN7GJ16SRdPVPYa97nMnczpmIeiQwQlYsIuzj6zpgijcWL4mflwBTlqVjOAMtc U8qgSi60bY4KdrPvtivaeoDBQd239UA0BSRWPNKyaUNY5j1KAfl22zztGJNP/dvg FctuPm6OXX1Rjd6FAoITZfnuNb4jF+SHJvKUVmliCvs9TjZZM490chQvuJfmknxy F69KhffvFoGUONvlxuoCrKeCNHx9l48sCg95Yknvh8q6M+0CxC0= =uLRt -----END PGP SIGNATURE----- --LCtTpdE0yDFueWxR--