Improve pg_stat_statements by making jumble handle savepoint names better

From: Greg Sabino Mullane <htamfids(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Improve pg_stat_statements by making jumble handle savepoint names better
Date: 2023-07-24 20:09:23
Message-ID: CAKAnmm+2s9PA4OaumwMJReWHk8qvJ_-g1WqxDRDAN1BSUfxyTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Please find attached a patch to jumble savepoint name, to prevent certain
transactional commands from filling up pg_stat_statements.
This has been a problem with some busy systems that use django, which likes
to wrap everything in uniquely named savepoints. Soon, over 50% of
your pg_stat_statements buffer is filled with savepoint stuff, pushing out
the more useful queries. As each query is unique, it looks like this:

postgres=# select calls, query, queryid from pg_stat_statements where query
~ 'save|release|rollback' order by 2;

calls | query | queryid
-------+----------------------------------------------+----------------------
1 | release b900150983cd24fb0d6963f7d28e17f7 | 8797482500264589878
1 | release ed9407630eb1000c0f6b63842defa7de | -9206510099095862114
1 | rollback | -2049453941623996126
1 | rollback to c900150983cd24fb0d6963f7d28e17f7 | -5335832667999552746
1 | savepoint b900150983cd24fb0d6963f7d28e17f7 | -1888817254996647181
1 | savepoint c47bce5c74f589f4867dbd57e9ca9f80 | 355123032993044571
1 | savepoint c900150983cd24fb0d6963f7d28e17f7 | -5921314469994822125
1 | savepoint d8f8e0260c64418510cefb2b06eee5cd | -981090856656063578
1 | savepoint ed9407630eb1000c0f6b63842defa7de | -25952890433218603

As the actual name of the savepoint is not particularly useful, the patch
will basically ignore the savepoint name and allow things to be collapsed:

calls | query | queryid
-------+----------------+----------------------
2 | release $1 | -7998168840889089775
1 | rollback | 3749380189022910195
1 | rollback to $1 | -1816677871228308673
5 | savepoint $1 | 6160699978368237767

Without the patch, the only solution is to keep raising
pg_stat_statements.max to larger and larger values to compensate for the
pollution of the
statement pool.

Cheers,
Greg

Attachment Content-Type Size
savepoint_jumble.v1.patch application/octet-stream 3.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2023-07-24 20:17:56 Re: odd buildfarm failure - "pg_ctl: control file appears to be corrupt"
Previous Message Tristan Partin 2023-07-24 19:57:58 Re: psql not responding to SIGINT upon db reconnection