public inbox for [email protected]
help / color / mirror / Atom feedhow to use trace_lock_oidmin config correctly
3+ messages / 3 participants
[nested] [flat]
* how to use trace_lock_oidmin config correctly
@ 2024-10-14 11:00 Vijaykumar Jain <[email protected]>
2024-10-14 17:59 ` How to Copy/Load 1 billions rows into a Partition Tables Fast Wong, Kam Fook (TR Technology) <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Vijaykumar Jain @ 2024-10-14 11:00 UTC (permalink / raw)
To: pgsql-general
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* How to Copy/Load 1 billions rows into a Partition Tables Fast
2024-10-14 11:00 how to use trace_lock_oidmin config correctly Vijaykumar Jain <[email protected]>
@ 2024-10-14 17:59 ` Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-15 04:27 ` Re: How to Copy/Load 1 billions rows into a Partition Tables Fast Juan Rodrigo Alejandro Burgos Mella <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Wong, Kam Fook (TR Technology) @ 2024-10-14 17:59 UTC (permalink / raw)
To: pgsql-general
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How to Copy/Load 1 billions rows into a Partition Tables Fast
2024-10-14 11:00 how to use trace_lock_oidmin config correctly Vijaykumar Jain <[email protected]>
2024-10-14 17:59 ` How to Copy/Load 1 billions rows into a Partition Tables Fast Wong, Kam Fook (TR Technology) <[email protected]>
@ 2024-10-15 04:27 ` Juan Rodrigo Alejandro Burgos Mella <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Juan Rodrigo Alejandro Burgos Mella @ 2024-10-15 04:27 UTC (permalink / raw)
To: Wong, Kam Fook (TR Technology) <[email protected]>; +Cc: pgsql-general
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 protected]>) 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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-10-15 04:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-10-14 11:00 how to use trace_lock_oidmin config correctly Vijaykumar Jain <[email protected]>
2024-10-14 17:59 ` How to Copy/Load 1 billions rows into a Partition Tables Fast Wong, Kam Fook (TR Technology) <[email protected]>
2024-10-15 04:27 ` Re: How to Copy/Load 1 billions rows into a Partition Tables Fast Juan Rodrigo Alejandro Burgos Mella <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox