Re: fetch all from "<unnamed portal 1>"

What kind of application is this? Java? Is the running statement a large
fetch or does it paginate rows?

If this is a Java application, is it possible that you have the JDBC fetch
size set to the default of 0 (all rows)?

Just thinking out loud, would changing "cursor_tuple_fraction" ( at the
user level) help here to create a bias toward retrieving all rows faster?

On Wed, Sep 9, 2026 at 8:37 AM Thomas Carroll <tomfecarroll(at)yahoo(dot)com>
wrote:

> In my experience, that query text indicates that a refcursor is in use - a
> way for a function to return a large result set back to its caller.
>
> So whatever is processing those refcursors could be stepping through many
> rows. Sounds like you have ruled out the possibility that the caller went
> idle partway through processing those rows.
>
> TC
>
>
> On Wednesday, September 9, 2026 at 07:45:39 AM EDT, Siraj G <
> tosiraj(dot)g(at)gmail(dot)com> wrote:
>
>
> wait events are just blank. I think I will try to figure out the minimal
> logging to figure out the SQLs.
> Thank you!
>
> On Wed, Sep 9, 2026 at 11:44 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
>
> On Wed, 2026-09-09 at 10:42 +0530, Ganesh Korde wrote:
> > On Wed, 9 Sept 2026, 10:01 am Siraj G, <tosiraj(dot)g(at)gmail(dot)com> wrote:
> > > Postgres version 14 and the instance is a GCP cloud SQL.
> > >
> > > We have several application connections in ACTIVE state for several
> hours and the query text shows just fetch all from "<unnamed portal 1>".
> > > What does it indicate? Could these sessions be in hung state?
> >
> > What do you see in wait events column in pg_stat_activity?
>
> A good hint for debugging, but let me answer the question as it is:
>
> Your application uses cursors to query the database. A cursor is
> first declared (that statement contains the query text), and then
> you fetch the result rows from the cursor.
>
> The query is taking a long time, but you don't get to see the query
> text - that is only known to the executing session.
>
> You should ask the people who wrote the application.
>
> If that is not feasible, you could set
>
> log_min_duration_statement = 0
> log_line_prefix = '%m [%v] '
>
> if you can afford to log all statements.
>
> Then locate a slow FETCH statement in the log (you have to wait until
> it completes) and find the preceding statements with the same virtual
> transaction ID. One of them will be the statement that declared the
> cursor.
>
> If you are more adventurous, you can break into one of the stalled backends
> with a debugger and tickle out the statement. That requires knowledge
> of PostgreSQL's internals.
>
> Yours,
> Laurenz Albe
>
>

--
[image: Google Logo]
Shane Borden
Staff Technical Solutions Consultant
shaneborden(at)google(dot)com
(786) 688-1412