agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: Thomas Carroll <tomfecarroll@yahoo.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: Siraj G <tosiraj.g@gmail.com>
Cc: Ganesh Korde <ganeshakorde@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: fetch all from "<unnamed portal 1>"
Date: Wed, 9 Sep 2026 12:36:54 +0000 (UTC)
Message-ID: <900472209.835285.1788957414025@mail.yahoo.com> (raw)
In-Reply-To: <CAC5iy62SQshSj7FYH1dD8waqfGiU7c2BvU_ixD667a9Tz1Ac0w@mail.gmail.com>
References: <CAC5iy630jOKqU0Qdj3F6Z4jMyVoA-uKzkES7jgAm=b0maPj2gg@mail.gmail.com>
<CAPNyb0Uih2BR1fWXCKGjeREvys2qG_ZL7EDk+Q96LKTLmFxywg@mail.gmail.com>
<6c4d94fe1b8aa391ae63c3863193fec4b9c87e16.camel@cybertec.at>
<CAC5iy62SQshSj7FYH1dD8waqfGiU7c2BvU_ixD667a9Tz1Ac0w@mail.gmail.com>
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
view thread (6+ messages) latest in thread
Message-ID: <900472209.835285.1788957414025@mail.yahoo.com>
Permalink: ../900472209.835285.1788957414025@mail.yahoo.com/
Also on: postgresql.org/message-id/900472209.835285.1788957414025@mail.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tomfecarroll@yahoo.com, laurenz.albe@cybertec.at, tosiraj.g@gmail.com, ganeshakorde@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: fetch all from "<unnamed portal 1>"
In-Reply-To: <900472209.835285.1788957414025@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox