Re: [HACKERS] pg_stat_statements cluttered with "DEALLOCATE dbdpg_p*"

[Fabien COELHO]

Hello devs,

I noticed that my pg_stat_statements is cluttered with hundreds of entries like "DEALLOCATE dbdpg_p123456_7", occuring each only once.

Here is a patch and sql test file to:

* normalize DEALLOCATE utility statements in pg_stat_statements






--
Fabien.
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 07f09e1..4b3348a 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -966,10 +966,19 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
 		BufferUsage bufusage_start,
 					bufusage;
 		uint32		queryId;
+		const char *normalQueryString;
 
 		bufusage_start = pgBufferUsage;
 		INSTR_TIME_SET_CURRENT(start);
 
+		/*
+		 * normalize queryString for DEALLOCATE, as drivers such as DBD::Pg
+		 * generate counter-based names for prepared statements which can
+		 * result in many useless entries.
+		 */
+		normalQueryString = strncasecmp(queryString, "DEALLOCATE", 10)==0 ?
+			"DEALLOCATE ?;": queryString;
+
 		nested_level++;
 		PG_TRY();
 		{
@@ -1025,9 +1034,9 @@ pgss_ProcessUtility(Node *parsetree, const char *queryString,
 		INSTR_TIME_SUBTRACT(bufusage.blk_write_time, bufusage_start.blk_write_time);
 
 		/* For utility statements, we just hash the query string directly */
-		queryId = pgss_hash_string(queryString);
+		queryId = pgss_hash_string(normalQueryString);
 
-		pgss_store(queryString,
+		pgss_store(normalQueryString,
 				   queryId,
 				   INSTR_TIME_GET_MILLISEC(duration),
 				   rows,

Some drivers such as DBD:Pg generate process/counter-based identifiers for PREPARE, which result in hundreds of DEALLOCATE being tracked, although the prepared query may be the same. This is also consistent with the corresponding PREPARE not being tracked (although the underlying prepared query *is* tracked). ** Note **: another simpler option would be to skip deallocates altogether by inserting a "&& !IsA(parsetree, DeallocateStmt)" at the beginning of pgss_ProcessUtility(). I'm not sure what is best.

pgss-norm-deallocate.sql
Description: application/sql

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers