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 1t0J0j-00Aetr-FU for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 11:13:13 +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 1t0J0h-00CrUZ-9L for pgsql-general@arkaria.postgresql.org; Mon, 14 Oct 2024 11:13:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0J0g-00CrUR-Mb for pgsql-general@lists.postgresql.org; Mon, 14 Oct 2024 11:13:11 +0000 Received: from mail-ed1-x541.google.com ([2a00:1450:4864:20::541]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0J0Z-000p85-Ve for pgsql-general@postgresql.org; Mon, 14 Oct 2024 11:13:09 +0000 Received: by mail-ed1-x541.google.com with SMTP id 4fb4d7f45d1cf-5c984352742so544504a12.1 for ; Mon, 14 Oct 2024 04:13:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1728904382; x=1729509182; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=FQUMIGG5iCpTcLytsTnjVek7I4vuB68yHKvCxBPdcaQ=; b=gkAGjo0qeCcQ5jLEBmVqpd2UH30i9tCajJR14e+v5ocCInEysOsXK+VkUiojDnjtrY UZr1z7OzvyTA2YuBqvWYZ4M+5UmfASeAdb4bdw0jLxEfIECh84preXvW4UZPTuCF1Dmx FU5d3dtdl9JUlz/nMhFlsrJVLhjxoALaPzxwyGjLZl5G/KC72pLMZPqGsnYlRsuZvovz L9sfR7u7gq541nj4f2cIBBXP6DhcmO2nIPN8ij1L3AQhKhndJRAbUGZYM44ZcBx+Yj7G BPV4lpRRr35ciIVGKM1aU+gEAd+92xtxd6HQgP+AmIHkejBLHVmHVk+mkFMd30YkeQXW 4rjg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1728904382; x=1729509182; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FQUMIGG5iCpTcLytsTnjVek7I4vuB68yHKvCxBPdcaQ=; b=JQSbj5dBE9++F146CZ2EfKhY4GQek+jHbOkeacopw4tJmCw4neb8m1uF2OqlLed1BM +zIcvs0CXWRZlVJU9GRYx3sugSbRkHnU3k7XFV4L3CoawNnOeTy8IqXPHzoChPjazqPf pd3pTGFvkM1O+nXGsPkYUMptajLvG3DZiEzs5CXZc/pxmQcB3kMWx4VuEm25+sijfujr NHz9QIVdF4+wJGmK64HJ/ignAfG9H8KmIAHFm2vGmfJ1P6PH9rxDzgL4EqG55F/szD/C XenWKi0sLk/eQBxX9k6RtdXn0RHCg+lzM18o9PEaIroPir3rvNXZUM64DtBNdnjbQm+a wtFQ== X-Gm-Message-State: AOJu0YxCXHqaIp0SywE0u7HXULej1fEmFPKNvF3kDnCibJEppiuI8qs+ OX6S3EAFaQbMLCBsM7bk/4rNPn7zUeAVaDd7M/QQmWYanpG9ux19e8HaZCArGHODzCgSYf/4bmP zUP3sts2IQq3u026/JB6SuGw9vXNqC+C6CGA= X-Google-Smtp-Source: AGHT+IG5lSAtkc5tH/16VnqGwfuB4vIq4ZV9c9gcOBZGGwCMv9jIdeTTj4U5gwczpGejKwva9NlEx+Ap5SlqLAtEnGY= X-Received: by 2002:a05:6402:254b:b0:5c7:1f61:314 with SMTP id 4fb4d7f45d1cf-5c948daf852mr8166112a12.35.1728904381195; Mon, 14 Oct 2024 04:13:01 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Vijaykumar Jain Date: Mon, 14 Oct 2024 16:42:50 +0530 Message-ID: Subject: Re: how to use trace_lock_oidmin config correctly To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000007defb006246deb95" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007defb006246deb95 Content-Type: text/plain; charset="UTF-8" ok pls ignore. i think this flag has to be used along with trace_locks = on flag. now it works. PostgreSQL: Documentation: 17: 19.17. Developer Options sorry for the noise. /* postgres=# show trace_lock_oidmin; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done trace_lock_oidmin ------------------- 16406 (1 row) postgres=# show trace_locks; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done trace_locks ------------- on (1 row) postgres=# select 't'::regclass::oid; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done oid ------- 16401 (1 row) postgres=# drop table t; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done DROP TABLE */ On Mon, 14 Oct 2024 at 16:30, Vijaykumar Jain < vijaykumarjain.github@gmail.com> wrote: > > > 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 > -- Thanks, Vijay Open to work Resume - Vijaykumar Jain --0000000000007defb006246deb95 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, 14 Oct 2024 = at 16:30, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:


I do not see logs for locks linked to attachin= g and detaching partitions if I use the=C2=A0
trace_lock_oidmin c= onfig set to oid below the table of concern, basically any locking on objec= ts above the threshold oid do not log.

from th= e doc:

trace_lock_oidmin (integer)

If set, do not trace locks for table= s 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/inc= lude
INCLUDEDIR-SERVER =3D /opt/postgresql/include/server
LIBDIR =3D /opt/postgresql/lib
PKGLIBDIR =3D /opt/postgresql/l= ib
LOCALEDIR =3D /opt/postgresql/share/locale
MANDIR = =3D /opt/postgresql/share/man
SHAREDIR =3D /opt/postgresql/share<= /div>
SYSCONFDIR =3D /opt/postgresql/etc
PGXS =3D /opt/postgr= esql/lib/pgxs/src/makefiles/pg= xs.mk
CONFIGURE =3D=C2=A0 '--prefix=3D/opt/postgresql'= ; '--with-openssl' '--enable-debug' '--enable-profiling= ' '--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
=


--
Thanks,
Vijay

Open to work
Resume -=C2=A0Vijaykumar Jain
--0000000000007defb006246deb95--