RE: Commit with wait event on advisory lock!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> 
Envoyé : mercredi 22 janvier 2025 01:49
À : msalais@xxxxxxx
Cc : 'Rajesh Kumar' <rajeshkumar.dba09@xxxxxxxxx>;
pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Objet : Re: Commit with wait event on advisory lock!

<msalais@xxxxxxx> 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







[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux