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 <https://www.postgresql.org/docs/current/runtime-config-developer.html#GUC-TRACE-LOCK-OIDMIN> 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 <https://github.com/cabecada>
