差分

このページの2つのバージョン間の差分を表示します。

--- it技術:データベース:postgresql:pg_repack [2025/07/08 14:29] – [relhasoids列がないエラー] yajuadmin
+++ it技術:データベース:postgresql:pg_repack [2025/07/08 16:50] (現在) – [ビルドできても例外エラー] yajuadmin
@@ 行 9: / 行 9: @@
   * [[https://stackoverflow.com/questions/45819855/call-of-statically-link-function-crash-everytimes-on-windows-8-10-but-not-7/45827816|Call of statically link function crash everytimes on windows 8/10 but not 7]]
+<wrap em>この問題を解決して、Windowsでもpg_repackを使えるようにしたい。</wrap>
 ===== 調査 =====
 pg_repack のデバッグでは、set_pglocale_pgservice(argv[0], "pgscripts"); を実行すると例外エラーとなる。\\
@@ 行 94: / 行 95: @@
 ==== versionの指定 ====
 「pg_repack --version」を実行したら、unknown となった。
-REPACK_VERSION を指定していないため、プロジェクトのプロパティ C/C++のプリプロセッサに REPACK_VERSION=1.4.7 を追加する。\\
+Makefileでは、コンパイル時にREPACK_VERSIONにバージョン番号をセットしているが、Windowsでは REPACK_VERSION を指定していないため、プロジェクトのプロパティ C/C++のプリプロセッサに REPACK_VERSION=1.4.7 を追加する。\\
 lib側も同様に設定すること
@@ 行 178: / 行 180: @@
         END
     FROM pg_class
+</code>
+===== 簡易テスト =====
+ようやく動作させることが出来るようになったので、簡易テスト
+[[https://www.sraoss.co.jp/tech-blog/pgsql/pg_repack/|pg_repack (オンラインテーブル再編成ツール)]]
+<code sql テストデータ作成>
+CREATE TABLE t1 (id int primary key, val text);
+SELECT pg_size_pretty(pg_total_relation_size('t1'));
+INSERT INTO t1 VALUES(generate_series(1,10000),'AAA');
+SELECT pg_size_pretty(pg_total_relation_size('t1'));
+-- 5回ほど実行
+UPDATE t1 SET VAL = 'BBB';
+UPDATE t1 SET VAL = 'BBB';
+UPDATE t1 SET VAL = 'BBB';
+UPDATE t1 SET VAL = 'BBB';
+UPDATE t1 SET VAL = 'BBB';
+SELECT pg_size_pretty(pg_total_relation_size('t1'));
+-- 2736 kB
+-- バキュームしてもサイズは小さくならないことを確認
+VACUUM t1;
+SELECT pg_size_pretty(pg_total_relation_size('t1'));
+-- 2736 kB
+</code>
+DB:wh_test、ユーザー:wh_test、スキーマ:wh_test にある t1 テーブル で pg_repack を実行する。\\
+また、-e オプション(--echo)で、サーバに送信するSQLを表示するようにしておきます。
+[[https://reorg.github.io/pg_repack/|pg_repack Usage]]
+<code>
+pg_repack -n -t wh_test.t1 -d wh_test -U wh_test -e
+LOG: (query) SET search_path TO pg_catalog, pg_temp, public
+LOG: (query) SET search_path TO pg_catalog, pg_temp, public
+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 r FROM (VALUES ($1)) AS given_t(r) WHERE NOT EXISTS(  SELECT FROM repack.tables WHERE relid=to_regclass(given_t.r) )
+LOG:    (param:0) = wh_test.t1
+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) = wh_test.t1
+INFO: repacking table "wh_test.t1"
+LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
+LOG:    (param:0) = 16185446
+LOG:    (param:1) = 16738
+LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
+LOG: (query) SET LOCAL statement_timeout = 100
+LOG: (query) LOCK TABLE wh_test.t1 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) = 16738
+LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
+LOG:    (param:0) = 16738
+LOG:    (param:1) = (null)
+LOG: (query) SELECT repack.conflicted_triggers($1)
+LOG:    (param:0) = 16738
+LOG: (query) CREATE TYPE repack.pk_16738 AS (id integer)
+LOG: (query) CREATE TABLE repack.log_16738 (id bigserial PRIMARY KEY, pk repack.pk_16738, row wh_test.t1)
+LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON wh_test.t1 FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_16738(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_16738) END, $2)')
+LOG: (query) ALTER TABLE wh_test.t1 ENABLE ALWAYS TRIGGER repack_trigger
+LOG: (query) SELECT repack.disable_autovacuum('repack.log_16738')
+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 = 16738 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
+LOG: (query) COMMIT
+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 coalesce(array_agg(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) = 6804
+LOG:    (param:1) = pg_repack
+LOG: (query) DELETE FROM repack.log_16738
+LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 16738 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
+LOG: (query) SET LOCAL statement_timeout = 100
+LOG: (query) LOCK TABLE wh_test.t1 IN ACCESS SHARE MODE
+LOG: (query) RESET statement_timeout
+LOG: (query) CREATE TABLE repack.table_16738 WITH (oids = false) TABLESPACE pg_default AS SELECT id,val FROM ONLY wh_test.t1 WITH NO DATA
+LOG: (query) INSERT INTO repack.table_16738 SELECT id,val FROM ONLY wh_test.t1
+LOG: (query) SELECT repack.disable_autovacuum('repack.table_16738')
+LOG: (query) COMMIT
+LOG: (query) CREATE UNIQUE INDEX index_16743 ON repack.table_16738 USING btree (id)
+LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
+LOG:    (param:0) = SELECT * FROM repack.log_16738 ORDER BY id LIMIT $1
+LOG:    (param:1) = INSERT INTO repack.table_16738 VALUES ($1.*)
+LOG:    (param:2) = DELETE FROM repack.table_16738 WHERE (id) = ($1.id)
+LOG:    (param:3) = UPDATE repack.table_16738 SET (id, val) = ($2.id, $2.val) WHERE (id) = ($1.id)
+LOG:    (param:4) = DELETE FROM repack.log_16738 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
+LOG: (query) SET LOCAL statement_timeout = 100
+LOG: (query) LOCK TABLE wh_test.t1 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_16738 ORDER BY id LIMIT $1
+LOG:    (param:1) = INSERT INTO repack.table_16738 VALUES ($1.*)
+LOG:    (param:2) = DELETE FROM repack.table_16738 WHERE (id) = ($1.id)
+LOG:    (param:3) = UPDATE repack.table_16738 SET (id, val) = ($2.id, $2.val) WHERE (id) = ($1.id)
+LOG:    (param:4) = DELETE FROM repack.log_16738 WHERE id IN (
+LOG:    (param:5) = 0
+LOG: (query) SELECT repack.repack_swap($1)
+LOG:    (param:0) = 16738
+LOG: (query) COMMIT
+LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
+LOG: (query) SAVEPOINT repack_sp1
+LOG: (query) SET LOCAL statement_timeout = 100
+LOG: (query) LOCK TABLE wh_test.t1 IN ACCESS EXCLUSIVE MODE
+LOG: (query) RESET statement_timeout
+LOG: (query) SELECT repack.repack_drop($1, $2)
+LOG:    (param:0) = 16738
+LOG:    (param:1) = 4
+LOG: (query) COMMIT
+LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
+LOG: (query) ANALYZE wh_test.t1
+LOG: (query) COMMIT
+LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
+LOG:    (param:0) = 16185446
+LOG:    (param:1) = 16738
+</code>
+kB から 632 kBまでサイズが小さくなった。
+<code sql 結果>
+SELECT pg_size_pretty(pg_total_relation_size('t1'));
+-- 632 kB
 </code>