pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feed[PATCH v1] Jumble temp tables by name
1+ messages / 1 participants
[nested] [flat]
* [PATCH v1] Jumble temp tables by name
@ 2025-03-17 16:17 Christoph Berg <myon@debian.org>
0 siblings, 0 replies; 1+ messages in thread
From: Christoph Berg @ 2025-03-17 16:17 UTC (permalink / raw)
Query jumbling considers everything by oid, which is fine for regular
objects. But for temp tables, which have to be recreated in each session
(or even transaction), this means that the same temp table query run
from the next session will never be aggregated in pg_stat_statements.
Instead, the statistics are polluted with a large number of 1-call
entries.
Fix by using the temp table name instead. This has the risk of
aggregating structurally different temp tables together if they same the
same name, but practically, the queries will likely differ in other
details anyway. And even if not, aggregating the entries in
pg_stat_statements instead of polluting the stats seems the better
choice. (The user has still the option to simply change the name of the
temp table to have the queries separated. In the old scheme, the user
does not have any chance to change behavior.)
---
.../pg_stat_statements/expected/select.out | 31 ++++++++++++++++
contrib/pg_stat_statements/sql/select.sql | 12 +++++++
src/backend/nodes/queryjumblefuncs.c | 35 +++++++++++++++++++
src/include/nodes/parsenodes.h | 2 +-
4 files changed, 79 insertions(+), 1 deletion(-)
inline patch 1: contrib/pg_stat_statements/expected/select.out, contrib/pg_stat_statements/sql/select.sql, src/backend/nodes/queryjumblefuncs.c and 1 more (+79/-1 lines) download inline-1.patch
diff --git a/contrib/pg_stat_statements/expected/select.out b/contrib/pg_stat_statements/expected/select.out
index 37a30af034a..8a7e237298c 100644
--- a/contrib/pg_stat_statements/expected/select.out
+++ b/contrib/pg_stat_statements/expected/select.out
@@ -241,6 +241,37 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
(12 rows)
DROP TABLE pgss_a, pgss_b CASCADE;
+-- temp tables
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+BEGIN;
+ CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
+ SELECT * FROM temp_t;
+ id
+----
+(0 rows)
+
+COMMIT;
+BEGIN;
+ CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
+ SELECT * FROM temp_t;
+ id
+----
+(0 rows)
+
+COMMIT;
+SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -- one query with two calls
+ calls | query
+-------+------------------------------------------------------------------------
+ 2 | SELECT * FROM temp_t
+ 0 | SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"
+ 1 | SELECT pg_stat_statements_reset() IS NOT NULL AS t
+(3 rows)
+
--
-- access to pg_stat_statements_info view
--
diff --git a/contrib/pg_stat_statements/sql/select.sql b/contrib/pg_stat_statements/sql/select.sql
index e0be58d5e24..81b9d50ecec 100644
--- a/contrib/pg_stat_statements/sql/select.sql
+++ b/contrib/pg_stat_statements/sql/select.sql
@@ -90,6 +90,18 @@ SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C";
DROP TABLE pgss_a, pgss_b CASCADE;
+-- temp tables
+SELECT pg_stat_statements_reset() IS NOT NULL AS t;
+BEGIN;
+ CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
+ SELECT * FROM temp_t;
+COMMIT;
+BEGIN;
+ CREATE TEMP TABLE temp_t (id int) ON COMMIT DROP;
+ SELECT * FROM temp_t;
+COMMIT;
+SELECT calls, query FROM pg_stat_statements ORDER BY query COLLATE "C"; -- one query with two calls
+
--
-- access to pg_stat_statements_info view
--
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index b103a281936..bbddc92e28c 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -32,10 +32,12 @@
*/
#include "postgres.h"
+#include "catalog/namespace.h"
#include "common/hashfn.h"
#include "miscadmin.h"
#include "nodes/queryjumble.h"
#include "parser/scansup.h"
+#include "utils/lsyscache.h"
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
@@ -58,6 +60,7 @@ static void _jumbleNode(JumbleState *jstate, Node *node);
static void _jumbleA_Const(JumbleState *jstate, Node *node);
static void _jumbleList(JumbleState *jstate, Node *node);
static void _jumbleVariableSetStmt(JumbleState *jstate, Node *node);
+static void _jumbleRangeTblEntry(JumbleState *jstate, Node *node);
/*
* Given a possibly multi-statement source string, confine our attention to the
@@ -377,3 +380,35 @@ _jumbleVariableSetStmt(JumbleState *jstate, Node *node)
JUMBLE_FIELD(is_local);
JUMBLE_LOCATION(location);
}
+
+static void
+_jumbleRangeTblEntry(JumbleState *jstate, Node *node)
+{
+ char *rel_name = NULL;
+ RangeTblEntry *expr = (RangeTblEntry *) node;
+
+ JUMBLE_FIELD(rtekind);
+
+ /*
+ * If this is a temp table, jumble the name instead of the table oid.
+ */
+ if (expr->rtekind == RTE_RELATION && isAnyTempNamespace(get_rel_namespace(expr->relid)))
+ {
+ rel_name = get_rel_name(expr->relid);
+ AppendJumble(jstate, (const unsigned char *)rel_name, strlen(rel_name));
+ }
+ else
+ JUMBLE_FIELD(relid);
+
+ JUMBLE_FIELD(inh);
+ JUMBLE_NODE(tablesample);
+ JUMBLE_NODE(subquery);
+ JUMBLE_FIELD(jointype);
+ JUMBLE_NODE(functions);
+ JUMBLE_FIELD(funcordinality);
+ JUMBLE_NODE(tablefunc);
+ JUMBLE_NODE(values_lists);
+ JUMBLE_STRING(ctename);
+ JUMBLE_FIELD(ctelevelsup);
+ JUMBLE_STRING(enrname);
+}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 23c9e3c5abf..deec95c7a26 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1039,7 +1039,7 @@ typedef enum RTEKind
typedef struct RangeTblEntry
{
- pg_node_attr(custom_read_write)
+ pg_node_attr(custom_read_write, custom_query_jumble)
NodeTag type;
--
2.47.2
only message in thread
Thread overview: 1+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-17 16:17 [PATCH v1] Jumble temp tables by name Christoph Berg <myon@debian.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox