Re: Improve explicit cursor handling in pg_stat_statements

On Fri, May 02, 2025 at 04:21:21PM -0500, Sami Imseih wrote:
> postgres_fdw, as an example, in which cursor name get reused
> for different queries. Notice below "c1" and "c2" is reused for different
> queries, so now what underlying sql is FETCH, i.e. FETCH 100 FROM c1 referring
> to? v2-0001 does not help us with the FETCH problem
> because as I mentioned we don't have access to the underlying sql
> ( and parsing is even too early to do a portal lookup to find the
> underlying sql to
> base the queryId on). What v2-0001 will do is at least group the DECLARE CURSOR
> statements together for cursors referencing the same query and reduce the #
> of entries.

This case relies on postgres_fdw's GetCursorNumber() that assigns a
unique number for a cursor, ensuring uniqueness per connection within
a transaction, and the counter is reset at the end of the
transactions. So good point for this case that this hurts. If that
holds for the most common cases where this is seen as bloating pgss,
that brings some solid ground, especially more for applications that
use many cursor numbers in long-ish transactions states done under
postgres_fdw.

I'm still slightly worried about workloads where cursor names could be
used to track some balancing of this kind of activity, for example if
cursor names are fixed on a transaction-basis depending on the
application involved, as that would mean less visibility in the
information. Perhaps we would have more confidence by looking closer
at drivers or some ORMs that make use of cursors, seeing how the end
user would be impacted? That seems hard to measure, though..

> The FETCH and CLOSE are already not clear to what underlying SQL
> they are referring to, and there is not much chance to actually
> improve that unless
> we track a cursor queryId in pg_stat_statements ( at that point we can show that
> FETCH or CLOSE refer to this specific cursor statement ).

I don't really have an issue for FETCH with the number as the name is
still around, but I'm equally worrying about the loss of information
for CLOSE that this new normalization would imply. Perhaps my worries
don't have a reason to exist here and I'm just a naturally-pessimistic
being.
--
Michael