> Hmm. What are the workloads that you have seen as problematic? Do
> these involve cursor names generated randomly, where most of them are
> similar with a random factor for the name?
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.
```
create foreign table t2(id int) server r1;
create foreign table t1(id int) server r1;
postgres=# select * from t2, t ;
id | id
----+----
1 | 1
(1 row)
postgres=# select * from t, t2 ;
id | id
----+----
1 | 1
(1 row)
```
on the remote side
```
postgres=# select calls, query from pg_stat_statements where query like '% c%';
calls | query
-------+-----------------------------------------------------------------
1 | DECLARE c2 CURSOR FOR +
| SELECT id FROM public.t2
2 | DECLARE c1 CURSOR FOR +
| SELECT id FROM public.t2
3 | CLOSE c2
3 | CLOSE c1
2 | DECLARE c2 CURSOR FOR +
| SELECT id FROM public.t
3 | FETCH 100 FROM c1
3 | FETCH 100 FROM c2
1 | DECLARE c1 CURSOR FOR +
| SELECT id FROM public.t
2 | select calls, query from pg_stat_statements where query like $1
(9 rows)
```
> Too much normalization
> here would limit the amount of verbosity that we have for this area,
> especially if we are dealing with query patterns that rely on few
> CLOSE naming patterns spread across a couple of key queries, because
> we would now know anymore about their distribution.
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 ).
--
Sami
