Hello,
The session is marked ***active***. It is not in idle state and the query is
'commit' and the event is ***ClientRead***! There is no doubt...
This one and the wait on advisory lock by a commit statement let me think
about Oracle saying that it doesn't guarantee consistence for dynamic tables
data, and it could be the same thing here for pg_stat_activity. Could it be?
It could depend on protocol and when exactly this dynamic table is
updated...
Best regards
---
Michel SALAIS
-----Message d'origine-----
De : Tom Lane <tgl@sss.pgh.pa.us>
Envoyé : mercredi 22 janvier 2025 23:18
À : msalais@msym.fr
Cc : 'Rajesh Kumar' <rajeshkumar.dba09@gmail.com>;
pgsql-admin@lists.postgresql.org
Objet : Re: Commit with wait event on advisory lock!
<msalais@msym.fr> writes:
> But my concern here is really to understand in which situations, a
> COMMIT statement can wait on a lock especially that I have no
> triggers, and no deferrable constraints...
The core code does nothing with advisory locks. If COMMIT is blocking on
one of those, it must be a consequence of user-defined code that is running
at pre-commit time. You've given us nothing to go on about what that could
be.
> By the way I also have commits which are waiting on ClientRead...
That, on the other hand, is surely impossible. I think maybe you are
misreading the stats display. Typically I'd expect that such a case
indicates that the session is idle (awaiting a new command) and the COMMIT
is the last thing it did before that.
regards, tom lane
