Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rAt44-008aoo-KJ for pgsql-www@arkaria.postgresql.org; Wed, 06 Dec 2023 14:39:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rAt43-00A85G-AC for pgsql-www@arkaria.postgresql.org; Wed, 06 Dec 2023 14:39:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rAkuE-007cz8-FS for pgsql-www@lists.postgresql.org; Wed, 06 Dec 2023 05:57:10 +0000 Received: from mail-pl1-x62c.google.com ([2607:f8b0:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rAku8-00ALFk-8F for pgsql-www@postgresql.org; Wed, 06 Dec 2023 05:57:07 +0000 Received: by mail-pl1-x62c.google.com with SMTP id d9443c01a7336-1d076ebf79cso21377715ad.1 for ; Tue, 05 Dec 2023 21:57:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=lastyard.com; s=google; t=1701842221; x=1702447021; darn=postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=pkb3uaNOsmjD/JIl96wNdzgSKgpOQxKqORUIwSw/iew=; b=PzalL5exu0qKqmvN1QhZXmr1qBUJsqGVHfKr+Me91swuA6cPzhqe/469AvDqDpBlaP 7A818pbHwTXtkH8AO11yigPMPEdk+NslRZ4Fm27OPo3Nzfy4nBOW1RHJ+D+wD+ArJFaY nOpKeLVTT8mVJDoryKNMFAnjJ6vx1IFOqi6vytMzeREdvdzuR+kQjUAseDd06MWIedZA AZjazXGWdYgax7E6HYxaKVpKUVKjvygmbyP3FGKb2Jsnh1VVAbwr/oUqUNQ8EqYptxKi 8BUUzCfJh1jL2p9cd6ixcbA+sAjm/syDCYuWpPyORIqLJamZlMpdrD5ivL3EOfIpjYI2 YmWg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1701842221; x=1702447021; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=pkb3uaNOsmjD/JIl96wNdzgSKgpOQxKqORUIwSw/iew=; b=v94Z6LS3jEm+BS9YVcqnw9DVj+lEAupgT/VBf8n5yV6iYMc+o85QoaFFBln0gjEsYj iC9/qGXXs68aHWb8r7bn57IMhmivXGK8KMY5yWFXS08uxLq0+4WNNHiUPLJkzu1IPg6M CgQFo4cs2/+o9HZugg11VcfB+9GG79jh+Dw7SFfHHrfogEEawMvuUHw9wDtDjaCvOnXX ub12r3eNnsCv26c11huPlDhO+t4x/zmfQOY7Or2s8L+/KlYfAbt7/fu0/+jKp9Fyxp3e 2eePeOoZsXMU3jx7y2ftgtTR+c+4Ss9S7W2XnDEj69Q3VX7XEVvNAa4ZlMtjzmFB7yRC teaQ== X-Gm-Message-State: AOJu0YzuKTEuWXN5yU/CPnl/jibzp6aQ9Tujk0JuKBrwftw3apY2NA+u tqqowBGi3AOroIqIifPy2SzJP010Ao+vAqEV9J0= X-Google-Smtp-Source: AGHT+IGHOub1y1WEGXRUwkJCxKe2qHda1IcXX569V70yCdYehSkJjH86Xk7r7NS7n5hOLQPHQz7RYw== X-Received: by 2002:a17:902:e54b:b0:1d0:9e84:6a93 with SMTP id n11-20020a170902e54b00b001d09e846a93mr261646plf.81.1701842221118; Tue, 05 Dec 2023 21:57:01 -0800 (PST) Received: from smtpclient.apple (175-45-85-70.core.vocus.network. [175.45.85.70]) by smtp.gmail.com with ESMTPSA id w17-20020a170902e89100b001b53953f306sm11140968plg.178.2023.12.05.21.56.59 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 05 Dec 2023 21:57:00 -0800 (PST) From: Andrew Morris X-Google-Original-From: Andrew Morris Content-Type: multipart/alternative; boundary="Apple-Mail=_88EAFF63-40A7-48A1-9E25-63F0832B6BA8" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3731.700.6\)) Subject: Wiki editor request Message-Id: <8EACC548-F3FD-4CF0-AB6E-054D8D561E52@gridmon.com.au> Date: Wed, 6 Dec 2023 13:56:46 +0800 To: pgsql-www@postgresql.org X-Mailer: Apple Mail (2.3731.700.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_88EAFF63-40A7-48A1-9E25-63F0832B6BA8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii I would like editor access to the wiki, my username is amorris and I = would like to modify https://wiki.postgresql.org/wiki/Lock_Monitoring The existing query: SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS = current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON = blocked_activity.pid =3D blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks=20 ON blocking_locks.locktype =3D blocked_locks.locktype AND blocking_locks.database IS NOT DISTINCT FROM = blocked_locks.database AND blocking_locks.relation IS NOT DISTINCT FROM = blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM = blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM = blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM = blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM = blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM = blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM = blocked_locks.objsubid AND blocking_locks.pid !=3D blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON = blocking_activity.pid =3D blocking_locks.pid WHERE NOT blocked_locks.granted; does not work for cases where there are multiple transactions, it starts = reporting additional bloced/blocking pairs that don't exist! I have the following query that works for simple and complex cases with = multiple transactions blocked by the same transaction: SELECT blocker.pid AS blocker_id, blocker.datname AS blocker_db, blocker.application_name AS blocker_app, blocker.xact_start AS blocker_tx_start, blocker.query AS blocker_cur_query, -- blocked.pid as blocked_pid, blocked.datname as blocked_db, blocked.application_name AS blocked_app, now() - blocked.query_start AS blocked_duration,=20 blocked.query as blocked_query FROM pg_stat_activity AS blocked JOIN pg_stat_activity AS blocker ON blocker.pid =3D = ANY(pg_blocking_pids(blocked.pid)) order by blocked_duration desc;= --Apple-Mail=_88EAFF63-40A7-48A1-9E25-63F0832B6BA8 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 
I would =
like editor access to the wiki, my username is amorris and I =
would like to modify =
https://wiki.postgresql.org/wiki/Lock_Monitoring

<=
/b>
The existing query:
  SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid =3D blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks=20
        ON blocking_locks.locktype =3D blocked_locks.locktype
        AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid !=3D blocked_locks.pid
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid =3D blocking_locks.pid
   WHERE NOT blocked_locks.granted;

does not work for =
cases where there are multiple transactions, it starts reporting =
additional bloced/blocking pairs that don't =
exist!

I have the following query that =
works for simple and complex cases with multiple transactions blocked by =
the same transaction:

SELECT
    blocker.pid AS blocker_id,
    blocker.datname AS blocker_db,
    blocker.application_name AS blocker_app,
    blocker.xact_start AS blocker_tx_start,
    blocker.query AS blocker_cur_query,
    --
    blocked.pid as blocked_pid,
    blocked.datname as blocked_db,
    blocked.application_name AS blocked_app,
    now() - blocked.query_start AS blocked_duration,=20
    blocked.query as blocked_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocker ON blocker.pid =3D =
ANY(pg_blocking_pids(blocked.pid))
order by blocked_duration desc;
= --Apple-Mail=_88EAFF63-40A7-48A1-9E25-63F0832B6BA8--