pg_repack --no-order 某个表的过程分析

pg_repack 某个表

$ pg_repack --wait-timeout 3600 --no-order --table public.tmp_t0 -d peiybdb --echo

分析日志

LOG: (query) SET search_path TO pg_catalog, pg_temp
LOG: (query) SET search_path TO pg_catalog, pg_temp
LOG: (query) select repack.version(), repack.version_sql()
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES (quote_ident($1::text))) as v (tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemaname
LOG: 	(param:0) = (null)
LOG: 	(param:1) = public.tmp_t0
INFO: repacking table "public.tmp_t0"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: 	(param:0) = 16185446
LOG: 	(param:1) = 1447603

使用 advisory 咨询锁

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.tmp_t0 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG: 	(param:0) = 1447603
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG: 	(param:0) = 1447603
LOG: 	(param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG: 	(param:0) = 1447603
LOG: (query) CREATE TYPE repack.pk_1447603 AS (c1 bigint)
LOG: (query) CREATE TABLE repack.log_1447603 (id bigserial PRIMARY KEY, pk repack.pk_1447603, row public.tmp_t0)
LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON public.tmp_t0 FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_1447603(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.c1)::repack.pk_1447603) END, $2)')
LOG: (query) ALTER TABLE public.tmp_t0 ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_1447603')

设置RC事务级别，锁定表为 ACCESS EXCLUSIVE 模式，此时其他session不能对表做任何操作。
创建数据变化的 repack.log_1447603 表。
创建触发器 AFTER INSERT OR DELETE OR UPDATE ON public.tmp_t0 FOR EACH ROW。
将 public.tmp_t0 变化的数据插入到 repack.log_1447603 表。

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT pg_backend_pid()
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1447603 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) COMMIT

设置RC事务级别,查询除自身外对该表有AccessExclusiveLock 的进程号。

LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SET LOCAL synchronize_seqscans = off
LOG: (query) SELECT repack.array_accum(l.virtualtransaction)   FROM pg_locks AS l   LEFT JOIN pg_stat_activity AS a     ON l.pid = a.pid   LEFT JOIN pg_database AS d     ON a.datid = d.oid   WHERE l.locktype = 'virtualxid'   AND l.pid NOT IN (pg_backend_pid(), $1)   AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0')   AND (a.application_name IS NULL OR a.application_name <> $2)  AND a.query !~* E'^\\s*vacuum\\s+'   AND a.query !~ E'^autovacuum: '   AND ((d.datname IS NULL OR d.datname = current_database()) OR l.database = 0)
LOG: 	(param:0) = 29480
LOG: 	(param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_1447603
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1447603 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.tmp_t0 IN ACCESS SHARE MODE
LOG: (query) RESET statement_timeout
LOG: (query) CREATE TABLE repack.table_1447603 WITH (oids = false) TABLESPACE pg_default AS SELECT c0,c1 FROM ONLY public.tmp_t0 WITH NO DATA
LOG: (query) INSERT INTO repack.table_1447603 SELECT c0,c1 FROM ONLY public.tmp_t0
LOG: (query) SELECT repack.disable_autovacuum('repack.table_1447603')
LOG: (query) COMMIT

设置RS事务级别，串行操作，创建 repack.table_1447603 表结构，插入数据

LOG: (query) CREATE UNIQUE INDEX index_1447752 ON repack.table_1447603 USING btree (c1) TABLESPACE pg_default
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG: 	(param:0) = SELECT * FROM repack.log_1447603 ORDER BY id LIMIT $1
LOG: 	(param:1) = INSERT INTO repack.table_1447603 VALUES ($1.*)
LOG: 	(param:2) = DELETE FROM repack.table_1447603 WHERE (c1) = ($1.c1)
LOG: 	(param:3) = UPDATE repack.table_1447603 SET (c0, c1) = ($2.c0, $2.c1) WHERE (c1) = ($1.c1)
LOG: 	(param:4) = DELETE FROM repack.log_1447603 WHERE id IN (
LOG: 	(param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
LOG: 	(param:0) = {}
LOG: (query) SAVEPOINT repack_sp1

创建索引
repack.table_1447603 插入原始的基础的数据。

LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.tmp_t0 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG: 	(param:0) = SELECT * FROM repack.log_1447603 ORDER BY id LIMIT $1
LOG: 	(param:1) = INSERT INTO repack.table_1447603 VALUES ($1.*)
LOG: 	(param:2) = DELETE FROM repack.table_1447603 WHERE (c1) = ($1.c1)
LOG: 	(param:3) = UPDATE repack.table_1447603 SET (c0, c1) = ($2.c0, $2.c1) WHERE (c1) = ($1.c1)
LOG: 	(param:4) = DELETE FROM repack.log_1447603 WHERE id IN (
LOG: 	(param:5) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG: 	(param:0) = 1447603
LOG: (query) COMMIT

需要 LOCK TABLE public.tmp_t0 IN ACCESS EXCLUSIVE MODE
repack.table_1447603 插入变化的数据。
做切换 repack.repack_swap

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE public.tmp_t0 IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG: 	(param:0) = 1447603
LOG: 	(param:1) = 4
LOG: (query) COMMIT

设置RC事务级别，处理旧表。

LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE public.tmp_t0
LOG: (query) COMMIT

设置RC事务级别，分析表

LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: 	(param:0) = 16185446
LOG: 	(param:1) = 1447603

释放 advisory 咨询锁。