Re: how to use trace_lock_oidmin config correctly

Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> · Mon, 14 Oct 2024 16:42:50 +0530

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@xxxxxxxxx> 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