Skip to content

Commit a072cc9

Browse files
committed
Ignore other pg_repack clients which may be running concurrently for the purposes of SQL_XID_SNAPSHOT.
Use application_name from pg_stat_activity, if available, to identify other pg_repack clients. Fixes Issue #1.
1 parent 1a0a28d commit a072cc9

File tree

1 file changed

+46
-6
lines changed

1 file changed

+46
-6
lines changed

bin/pg_repack.c

Lines changed: 46 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -35,13 +35,52 @@ const char *PROGRAM_VERSION = "unknown";
3535
*/
3636
#define APPLY_COUNT 1000
3737

38-
/* The '1/1, -1/0' lock skipped is from the bgwriter on newly promoted
39-
* servers. See GH ticket #1.
38+
39+
/* Compile an array of existing transactions which are active during
40+
* pg_repack's setup. Some transactions we can safely ignore:
41+
* a. The '1/1, -1/0' lock skipped is from the bgwriter on newly promoted
42+
* servers. See https://github.com/reorg/pg_reorg/issues/1
43+
* b. Our own database connection
44+
* c. Other pg_repack clients, as distinguished by application_name, which
45+
* may be operating on other tables at the same time. See
46+
* https://github.com/reorg/pg_repack/issues/1
47+
*
48+
* Note, there is some redundancy in how the filtering is done (e.g. excluding
49+
* based on pg_backend_pid() and application_name), but that shouldn't hurt
50+
* anything.
4051
*/
52+
#define SQL_XID_SNAPSHOT_90200 \
53+
"SELECT repack.array_accum(l.virtualtransaction) " \
54+
" FROM pg_locks AS l " \
55+
" LEFT JOIN pg_stat_activity AS a " \
56+
" ON l.pid = a.pid " \
57+
" WHERE l.locktype = 'virtualxid' AND l.pid <> pg_backend_pid() " \
58+
"AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0') " \
59+
"AND (a.application_name IS NULL OR a.application_name <> $1)"
60+
61+
#define SQL_XID_SNAPSHOT_90000 \
62+
"SELECT repack.array_accum(l.virtualtransaction) " \
63+
" FROM pg_locks AS l " \
64+
" LEFT JOIN pg_stat_activity AS a " \
65+
" ON l.pid = a.procpid " \
66+
" WHERE l.locktype = 'virtualxid' AND l.pid <> pg_backend_pid() " \
67+
"AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0') " \
68+
"AND (a.application_name IS NULL OR a.application_name <> $1)"
69+
70+
/* application_name is not available before 9.0. The last clause of
71+
* the WHERE clause is just to eat the $1 parameter (application name).
72+
*/
73+
#define SQL_XID_SNAPSHOT_80300 \
74+
"SELECT repack.array_accum(virtualtransaction) FROM pg_locks" \
75+
" WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid()" \
76+
" AND (virtualxid, virtualtransaction) <> ('1/1', '-1/0') " \
77+
" AND ($1 IS NOT NULL)"
78+
4179
#define SQL_XID_SNAPSHOT \
42-
"SELECT repack.array_accum(virtualtransaction) FROM pg_locks"\
43-
" WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid()"\
44-
" AND (virtualxid, virtualtransaction) <> ('1/1', '-1/0')"
80+
(PQserverVersion(connection) >= 90200 ? SQL_XID_SNAPSHOT_90200 : \
81+
(PQserverVersion(connection) >= 90000 ? SQL_XID_SNAPSHOT_90000 : \
82+
SQL_XID_SNAPSHOT_80300))
83+
4584

4685
#define SQL_XID_ALIVE \
4786
"SELECT pid FROM pg_locks WHERE locktype = 'virtualxid'"\
@@ -554,7 +593,8 @@ repack_one_table(const repack_table *table, const char *orderby)
554593
command("SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)", 0, NULL);
555594
if (orderby && !orderby[0])
556595
command("SET LOCAL synchronize_seqscans = off", 0, NULL);
557-
res = execute(SQL_XID_SNAPSHOT, 0, NULL);
596+
params[0] = PROGRAM_NAME;
597+
res = execute(SQL_XID_SNAPSHOT, 1, params);
558598
vxid = strdup(PQgetvalue(res, 0, 0));
559599
PQclear(res);
560600

0 commit comments

Comments
 (0)