RE: Commit with wait event on advisory lock!

Hi Tom,

Sorry,
- It is PG 14.4
- Replication is used (2 standby, one sync and the other potential) but no
application connection to replica.
- No deferrable constraints:
some_db=# select count(*) from pg_constraint where condeferrable ;
count
-------
0
(1 row)

- No triggers:
some_db=# select count(*) from pg_trigger ;
count
-------
0
(1 row)

I only capture statements executed within database some_db.

And finally, this is how I take snaps (this is launched by crontab every
hour). I also tried to capture 'idle in transaction' ...

do
$$
declare
debut timestamptz := current_timestamp;
begin
set synchronous_commit = local; -- I don't care about replication for my
table
<<main>>
loop
insert into public.ms_slow_stmts(datname, pid, leader_pid, usename,
application_name, backend_start, xact_start, query_start, stmt_duration,
tx_idle_duration, ts_now, state, wait_event_type, wait_event, query_id,
query)
select
datname,
pid,
leader_pid,
usename,
application_name,
backend_start,
xact_start,
query_start,
case
when state = 'idle in transaction' then state_change - query_start
else clock_timestamp - query_start
end stmt_duration,
case
when state = 'idle in transaction' then clock_timestamp -
state_change
else interval '0s'
end tx_idle_duration,
clock_timestamp ts_now,
state,
wait_event_type,
wait_event,
query_id,
query
from pg_stat_activity
where
backend_type = 'client backend'
and
datname = 'some_db'
and
state != 'idle'
and
(
case
when state = 'idle in transaction' then state_change - query_start
-- last query execution time if in 'idle in transaction' state
else clock_timestamp - query_start -- time from query start for
current query
end >= interval'2s'
or
case
when state = 'idle in transaction' then clock_timestamp -
state_change
else interval'0s'
end >= interval'1s' -- in state 'idle in transaction' for more than
1 s
);
commit;
exit when clock_timestamp - debut >= interval'00:59:55';
perform pg_sleep(1);
end loop main;
end;
$$;

Best regards

---
Michel SALAIS

-----Message d'origine-----
De : Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Envoyé : mercredi 22 janvier 2025 01:49
À : 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:
> How a COMMIT statement could be blocked by a lock whatever the kind of the
lock could be. COMMIT releases locks. How could it be blocked by a lock?!

There could be pre-commit actions (for example, firing deferred
triggers) that need to take locks the transaction didn't already hold.
You've provided no details that would let anyone diagnose the exact cause,
though.

regards, tom lane