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
signature.asc
Description: PGP signature
