Subject: how to use trace_lock_oidmin config correctly

From: Vijaykumar Jain <[email-masked]>
Date: Mon, 14 Oct 2024 16:30:37 +0530

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>

---

From: "Wong, Kam Fook (TR Technology)" <[email-masked]>
Date: Mon, 14 Oct 2024 17:59:22 +0000

I am trying to copy a table (Postgres) that is close to 1 billion rows into a Partition table (Postgres) within the same DB.  What is the fastest way to copy the data?   This table has 37 columns where some of which are text data types.

Thank you
Kam Fook Wong

This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

---

From: Juan Rodrigo Alejandro Burgos Mella <[email-masked]>
Date: Tue, 15 Oct 2024 01:27:57 -0300

Hi  Wong
On one occasion I had to upload 600 million records, and the most viable
and safest option was to generate plans and upload them through a massively
parallelized process (because for each process we audited that everything
was correct)

Atte.
JRBM

El lun, 14 oct 2024 a las 14:59, Wong, Kam Fook (TR Technology) (<
[email-masked]>) escribió:

> I am trying to copy a table (Postgres) that is close to 1 billion rows
> into a Partition table (Postgres) within the same DB.  What is the fastest
> way to copy the data?   This table has 37 columns where some of which are
> text data types.
>
> Thank you
> Kam Fook Wong
>
>
> This e-mail is for the sole use of the intended recipient and contains
> information that may be privileged and/or confidential. If you are not an
> intended recipient, please notify the sender by return e-mail and delete
> this e-mail and any attachments. Certain required legal entity disclosures
> can be accessed on our website:
> https://www.thomsonreuters.com/en/resources/disclosures.html
>