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 1t0Iou-00Adn8-ED for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 11:01:00 +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 1t0Ios-00Cj2u-EH for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 11:00:58 +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 1t0Ios-00Cj1b-0u for pgsql-general@lists.postgresql.org; Mon, 14 Oct 2024 11:00:58 +0000 Received: from mail-ej1-x641.google.com ([2a00:1450:4864:20::641]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0Iok-000zdu-FE for pgsql-general@postgresql.org; Mon, 14 Oct 2024 11:00:57 +0000 Received: by mail-ej1-x641.google.com with SMTP id a640c23a62f3a-a99ea294480so210292866b.2 for ; Mon, 14 Oct 2024 04:00:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728903649; x=1729508449; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=2y9+2UImye13JcZ01yLmfq+hNikWXL4Eiy34xAJKa5M=; b=jJtcHLByCZfguJkcn5Z6L9Bf9jCcjJ4ITBXOksrYSk/5xr0lnuL2REjZ8FxW3jMVNa NEsvWUXeX8y3RdC1JkC77rv4Wqc7CvxYfhkVJrsOwiaM/fI2V6s1JMRu/q1P92uHoSVF RVIdhrUtgQPNufuMPp2/btwLVS6Lsorr4LmQWcIJ4JLrNxWcDEXRO+uqHeKj2VPeMkET n4Lgoveck7Uv0Ptl0xjZ8hJg25/CRknNAJE9XJXGpFDiz57goYcv0HVr0+dTyKYZuVD6 Bc4aociblDCq+baqL8PMdXDf12dMCWlXlANiZ+kIUIxN6xp0u7pdfDkmAtno+bhF5q58 ZYOA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728903649; x=1729508449; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=2y9+2UImye13JcZ01yLmfq+hNikWXL4Eiy34xAJKa5M=; b=QybBzm9jkDqADeDFcYwOEFCcOYX+dYAGvrVfsED5KsYb+v5Spm213xJrGQR4QiBITy mmhdzv8htGBT1bLeCGRr9omRwCGbJCO6p2VBmOQC90r9KhX/+CDAG39cUmRfcBpHeIVw C9d9SrAB/0kv/EVTfOpYyGITG0amNmNaqoNIE5ic+mgSYaWb+bI6cvUX1K+Fvfr94PgT ElyFy7o7d6mK4FncoJfkXONh5tOrM85JqpGYxDA0GZiD4ckw1th4WW9jYC/nVSTBvxw7 HkVLSSzaJUQocOsIrrQx00yco9JiqdJnFlmOkdUVhvnJFoAge7lQS7Z26rd1sqAeGoCO eFBQ== X-Gm-Message-State: AOJu0Yy0NimqPTSA8BWIVt3L8BGLJAWtXVlwzhrRJsn2mjmJl0wmDZdw FfOyY69q62TGIVBmAFfcyNJfb6KRInU+zyNu4PCJhg2btoqxQfx8AgWMTordClK86teNWdHqoVU cm2xGrC9gqVFE7Y0/Dg5lqNGS9vH1aLeWKT0= X-Google-Smtp-Source: AGHT+IFZVzF58VXH3+o7/hsXIn27gUsDLBHV0TIsupMRcLVvmVHvEmejro5cUsSQF2EJW413z1l3syWGr/ZhzGQgb4E= X-Received: by 2002:a05:6402:51cd:b0:5c9:492c:f7fa with SMTP id 4fb4d7f45d1cf-5c95ac09939mr12067806a12.1.1728903647911; Mon, 14 Oct 2024 04:00:47 -0700 (PDT) MIME-Version: 1.0 From: Vijaykumar Jain Date: Mon, 14 Oct 2024 16:30:37 +0530 Message-ID: Subject: how to use trace_lock_oidmin config correctly To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000c8e91806246dbf2f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c8e91806246dbf2f Content-Type: text/plain; charset="UTF-8" I do not see logs for locks linked to attaching and detaching partitions if I use the trace_lock_oidmin config set to oid below the table of concern, basically any locking on objects above the threshold oid do not log. from the doc: PostgreSQL: Documentation: 17: 19.17. Developer Options trace_lock_oidmin (integer) If set, do not trace locks for tables below this OID (used to avoid output on system tables). This parameter is only available if the LOCK_DEBUG macro was defined when PostgreSQL was compiled i compiled my postgres with LOCK_DEBUG macro /* postgres@ubuntu:/tmp$ pg_config BINDIR = /opt/postgresql/bin DOCDIR = /opt/postgresql/share/doc HTMLDIR = /opt/postgresql/share/doc INCLUDEDIR = /opt/postgresql/include PKGINCLUDEDIR = /opt/postgresql/include INCLUDEDIR-SERVER = /opt/postgresql/include/server LIBDIR = /opt/postgresql/lib PKGLIBDIR = /opt/postgresql/lib LOCALEDIR = /opt/postgresql/share/locale MANDIR = /opt/postgresql/share/man SHAREDIR = /opt/postgresql/share SYSCONFDIR = /opt/postgresql/etc PGXS = /opt/postgresql/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--prefix=/opt/postgresql' '--with-openssl' '--enable-debug' '--enable-profiling' '--enable-cassert' '--enable-tap-tests' 'CFLAGS=-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL_DEBUG ' CC = gcc CPPFLAGS = -D_GNU_SOURCE CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -pg -DLINUX_PROFILE -ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL_DEBUG CFLAGS_SL = -fPIC LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/opt/postgresql/lib',--enable-new-dtags LDFLAGS_EX = LDFLAGS_SL = LIBS = -lpgcommon -lpgport -lssl -lcrypto -lz -lreadline -lm VERSION = PostgreSQL 18devel postgres@ubuntu:/tmp$ psql psql (18devel) Type "help" for help. postgres=# show client_min_messages; client_min_messages --------------------- log (1 row) postgres=# show trace_lock_oidmin; trace_lock_oidmin ------------------- 16400 (1 row) postgres=# select 't'::regclass::oid; oid ------- 16401 (1 row) postgres=# select 't1'::regclass::oid; oid ------- 16404 (1 row) postgres=# alter table t detach partition t1; ALTER TABLE postgres=# alter table t attach partition t1 for values in (0); ALTER TABLE */ but if i map the trace_lock_table to the oid of one table, it logs locking fine. /* postgres=# alter system set trace_lock_table = 16401; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# alter table t detach partition t1; LOG: LockAcquire: lock [5,16401] AccessExclusiveLock LOG: LockAcquire: new: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(AccessExclusiveLock) LOG: LockAcquire: new: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) hold(0) LOG: LockCheckConflicts: no conflict: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) hold(0) LOG: GrantLock: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=1 grant(0,0,0,0,0,0,0)=1 wait(0) type(AccessExclusiveLock) LOG: LockAcquire: lock [5,16401] AccessExclusiveLock LOG: LockReleaseAll: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) hold(100) LOG: LockReleaseAll: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=1 grant(0,0,0,0,0,0,0)=1 wait(0) type(INVALID) LOG: UnGrantLock: updated: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(AccessExclusiveLock) LOG: UnGrantLock: updated: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) hold(0) LOG: LockReleaseAll: updated: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID) LOG: CleanUpLock: deleting: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) hold(0) LOG: CleanUpLock: deleting: lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID) ALTER TABLE */ -- Thanks, Vijay Open to work Resume - Vijaykumar Jain --000000000000c8e91806246dbf2f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


I do not= see logs for locks linked to attaching and detaching partitions if I use t= he=C2=A0
trace_lock_oidmin config set to oid below the table of c= oncern, basically any locking on objects above the threshold oid do not log= .

from the doc:

trace_lock_oidmin (integer)

If set, do not t= race locks for tables below this OID (used to avoid output on system tables= ).
This parameter is only available if the LOCK_DEBUG macro was defined = when PostgreSQL was compiled

i compiled my postgres with = LOCK_DEBUG macro

/*
postgres@ubuntu:/tmp= $ pg_config
BINDIR =3D /opt/postgresql/bin
DOCDIR =3D /= opt/postgresql/share/doc
HTMLDIR =3D /opt/postgresql/share/doc
INCLUDEDIR =3D /opt/postgresql/include
PKGINCLUDEDIR =3D = /opt/postgresql/include
INCLUDEDIR-SERVER =3D /opt/postgresql/inc= lude/server
LIBDIR =3D /opt/postgresql/lib
PKGLIBDIR = =3D /opt/postgresql/lib
LOCALEDIR =3D /opt/postgresql/share/local= e
MANDIR =3D /opt/postgresql/share/man
SHAREDIR =3D /op= t/postgresql/share
SYSCONFDIR =3D /opt/postgresql/etc
P= GXS =3D /opt/postgresql/lib/pgxs/src/makefiles/p= gxs.mk
CONFIGURE =3D=C2=A0 '--prefix=3D/opt/postgresql= 9; '--with-openssl' '--enable-debug' '--enable-profilin= g' '--enable-cassert' '--enable-tap-tests' 'CFLAGS= =3D-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -= DWAL_DEBUG '
CC =3D gcc
CPPFLAGS =3D -D_GNU_SOURCE<= /div>
CFLAGS =3D -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclarati= on-after-statement -Werror=3Dvla -Wendif-labels -Wmissing-format-attribute = -Wimplicit-fallthrough=3D3 -Wcast-function-type -Wshadow=3Dcompatible-local= -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=3Dstanda= rd -Wno-format-truncation -Wno-stringop-truncation -g -pg -DLINUX_PROFILE -= ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG -DBTREE_BUILD_STATS -DWAL= _DEBUG
CFLAGS_SL =3D -fPIC
LDFLAGS =3D -Wl,--as-needed = -Wl,-rpath,'/opt/postgresql/lib',--enable-new-dtags
LDFLA= GS_EX =3D
LDFLAGS_SL =3D
LIBS =3D -lpgcommon -lpgport -= lssl -lcrypto -lz -lreadline -lm
VERSION =3D PostgreSQL 18devel


postgres@ubuntu:/tmp$ psql
psql (18devel)
Type "help" for help.

postgres=3D# show client_min_messages;
=C2=A0client_min_m= essages
---------------------
=C2=A0log
(1 ro= w)

postgres=3D# show trace_lock_oidmin;
= =C2=A0trace_lock_oidmin
-------------------
=C2=A016400=
(1 row)

postgres=3D# select 't'= ::regclass::oid;
=C2=A0 oid
-------
=C2= =A016401
(1 row)

postgres=3D# select = 9;t1'::regclass::oid;
=C2=A0 oid
-------
= =C2=A016404
(1 row)

postgres=3D# alter t= able t detach partition t1;
ALTER TABLE
postgres=3D# al= ter table t attach partition t1 for values in (0);
ALTER TABLE
*/


but if i map the trace_l= ock_table to the oid of one table, it logs locking fine.


/*
postgres=3D# alter system set trace_lock= _table=C2=A0 =3D 16401;
ALTER SYSTEM
postgres=3D# selec= t pg_reload_conf();
=C2=A0pg_reload_conf
--------------= --
=C2=A0t
(1 row)

postgres=3D= # alter table t detach partition t1;
LOG:=C2=A0 LockAcquire: lock= [5,16401] AccessExclusiveLock
LOG:=C2=A0 LockAcquire: new: lock(= 0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=3D0 gra= nt(0,0,0,0,0,0,0)=3D0 wait(0) type(AccessExclusiveLock)
LOG:=C2= =A0 LockAcquire: new: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(= 1) proc(0x708b6d4b8250) hold(0)
LOG:=C2=A0 LockCheckConflicts: no= conflict: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x7= 08b6d4b8250) hold(0)
LOG:=C2=A0 GrantLock: lock(0x708b6d12ae78) i= d(5,16401,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=3D1 grant(0,0,0,0,0,0,= 0)=3D1 wait(0) type(AccessExclusiveLock)
LOG:=C2=A0 LockAcquire: = lock [5,16401] AccessExclusiveLock
LOG:=C2=A0 LockReleaseAll: pro= clock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) proc(0x708b6d4b8250) h= old(100)
LOG:=C2=A0 LockReleaseAll: lock(0x708b6d12ae78) id(5,164= 01,0,0,0,1) grantMask(100) req(0,0,0,0,0,0,0)=3D1 grant(0,0,0,0,0,0,0)=3D1 = wait(0) type(INVALID)
LOG:=C2=A0 UnGrantLock: updated: lock(0x708= b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=3D0 grant(0,= 0,0,0,0,0,0)=3D0 wait(0) type(AccessExclusiveLock)
LOG:=C2=A0 UnG= rantLock: updated: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) = proc(0x708b6d4b8250) hold(0)
LOG:=C2=A0 LockReleaseAll: updated: = lock(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=3D= 0 grant(0,0,0,0,0,0,0)=3D0 wait(0) type(INVALID)
LOG:=C2=A0 Clean= UpLock: deleting: proclock(0x708b6d1da680) lock(0x708b6d12ae78) method(1) p= roc(0x708b6d4b8250) hold(0)
LOG:=C2=A0 CleanUpLock: deleting: loc= k(0x708b6d12ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=3D0 g= rant(0,0,0,0,0,0,0)=3D0 wait(0) type(INVALID)
ALTER TABLE
*/

-- =
Thanks,Vijay

Open to work
Resume -=C2=A0Vijaykumar Jain
=
--000000000000c8e91806246dbf2f--