agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedfetch all from "<unnamed portal 1>"
6+ messages / 5 participants
[nested] [flat]
* fetch all from "<unnamed portal 1>"
@ 2026-09-09 04:31 Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Siraj G @ 2026-09-09 04:31 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Admin experts!
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?
Regards
Siraj
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fetch all from "<unnamed portal 1>"
@ 2026-09-09 05:12 Ganesh Korde <ganeshakorde@gmail.com>
parent: Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ganesh Korde @ 2026-09-09 05:12 UTC (permalink / raw)
To: Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 9 Sept 2026, 10:01 am Siraj G, <tosiraj.g@gmail.com> wrote:
> Hello Admin experts!
>
> 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?
>
> Regards
> Siraj
>
What do you see in wait events column in pg_stat_activity?
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fetch all from "<unnamed portal 1>"
@ 2026-09-09 06:14 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Ganesh Korde <ganeshakorde@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2026-09-09 06:14 UTC (permalink / raw)
To: Ganesh Korde <ganeshakorde@gmail.com>; Siraj G <tosiraj.g@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2026-09-09 at 10:42 +0530, Ganesh Korde wrote:
> On Wed, 9 Sept 2026, 10:01 am Siraj G, <tosiraj.g@gmail.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fetch all from "<unnamed portal 1>"
@ 2026-09-09 11:45 Siraj G <tosiraj.g@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Siraj G @ 2026-09-09 11:45 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Ganesh Korde <ganeshakorde@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.albe@cybertec.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.g@gmail.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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fetch all from "<unnamed portal 1>"
@ 2026-09-09 12:36 Thomas Carroll <tomfecarroll@yahoo.com>
parent: Siraj G <tosiraj.g@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Carroll @ 2026-09-09 12:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Siraj G <tosiraj.g@gmail.com>; +Cc: Ganesh Korde <ganeshakorde@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.g@gmail.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.albe@cybertec.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.g@gmail.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: fetch all from "<unnamed portal 1>"
@ 2026-09-09 12:56 Shane Borden <shaneborden@google.com>
parent: Thomas Carroll <tomfecarroll@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Shane Borden @ 2026-09-09 12:56 UTC (permalink / raw)
To: Thomas Carroll <tomfecarroll@yahoo.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Siraj G <tosiraj.g@gmail.com>; Ganesh Korde <ganeshakorde@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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@yahoo.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.g@gmail.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.albe@cybertec.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.g@gmail.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@google.com
(786) 688-1412
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-09-09 12:56 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-09 04:31 fetch all from "<unnamed portal 1>" Siraj G <tosiraj.g@gmail.com>
2026-09-09 05:12 ` Ganesh Korde <ganeshakorde@gmail.com>
2026-09-09 06:14 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-09-09 11:45 ` Siraj G <tosiraj.g@gmail.com>
2026-09-09 12:36 ` Thomas Carroll <tomfecarroll@yahoo.com>
2026-09-09 12:56 ` Shane Borden <shaneborden@google.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox