RE: Commit with wait event on advisory lock!

Hi,
Just an example of my result today:
<code>
postgres=# select pid, xact_start, query_start, ts_now, stmt_duration,
state, wait_event_type, wait_event, query from public.ms_slow_stmts where
query ilike 'commit' and wait_event_type in ('Client', 'Lock');
pid | xact_start | query_start |
ts_now | stmt_duration | state | wait_event_type | wait_event
| query
---------+-------------------------------+-------------------------------+--
-----------------------------+-----------------+--------+-----------------+-
-----------+--------
529392 | 2025-01-21 05:55:19.427214+01 | 2025-01-21 05:55:19.429889+01 |
2025-01-21 05:55:21.569339+01 | 00:00:02.13945 | active | Client |
ClientRead | COMMIT
2285947 | 2025-01-21 05:55:19.426945+01 | 2025-01-21 05:55:19.428651+01 |
2025-01-21 05:55:21.569342+01 | 00:00:02.140691 | active | Lock |
advisory | COMMIT
4106658 | 2025-01-22 13:04:29.551289+01 | 2025-01-22 13:04:29.726165+01 |
2025-01-22 13:04:33.191303+01 | 00:00:03.465138 | active | Client |
ClientRead | COMMIT
4106652 | 2025-01-22 13:04:30.068406+01 | 2025-01-22 13:04:30.073688+01 |
2025-01-22 13:04:33.191322+01 | 00:00:03.117634 | active | Client |
ClientRead | COMMIT
4106625 | 2025-01-22 13:04:30.064757+01 | 2025-01-22 13:04:30.070484+01 |
2025-01-22 13:04:33.191402+01 | 00:00:03.120918 | active | Client |
ClientRead | COMMIT
119472 | 2025-01-22 19:19:43.156342+01 | 2025-01-22 19:19:43.159275+01 |
2025-01-22 19:19:45.208617+01 | 00:00:02.049342 | active | Client |
ClientRead | COMMIT
</code>

Michel SALAIS

-----Message d'origine-----
De : msalais(at)msym(dot)fr <msalais(at)msym(dot)fr>
Envoyé : jeudi 23 janvier 2025 19:36
À : 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc : 'Rajesh Kumar' <rajeshkumar(dot)dba09(at)gmail(dot)com>;
pgsql-admin(at)lists(dot)postgresql(dot)org
Objet : RE: Commit with wait event on advisory lock!

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(at)sss(dot)pgh(dot)pa(dot)us>
Envoyé : mercredi 22 janvier 2025 23:18
À : msalais(at)msym(dot)fr
Cc : 'Rajesh Kumar' <rajeshkumar(dot)dba09(at)gmail(dot)com>;
pgsql-admin(at)lists(dot)postgresql(dot)org Objet : Re: Commit with wait event on
advisory lock!

<msalais(at)msym(dot)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