Conflicting transaction is not terminated if it is open during pg_repack execution

[markosutic]

Hello,

With --wait-timeout parameter pg_repack will kill conflicting transaction if it is holding locks before pg_repack started with repacking.

$ /usr/pgsql-11/bin/pg_repack --table=t1 --dbname=postgres --no-order --wait-timeout=5
INFO: repacking table "public.t1"
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: canceling conflicted backends
WARNING: terminating conflicted backends

But if we open transaction during pg_repack execution pg_repack is waiting for a conflicting transaction to finish indefinitely. Conflicting backend is not terminated as it was terminated when transaction started before pg_repack started with repacking.

SESSION2:

postgres=# begin;
BEGIN
postgres=# update t1 set name='MARKO' where id=7;
UPDATE 1

SESSION1:

$ /usr/pgsql-11/bin/pg_repack --table=t1 --dbname=postgres --no-order --wait-timeout=5
INFO: repacking table "public.t1"
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
NOTICE: Waiting for 1 transactions to finish. First PID: 20492

From the log:

LOG: (query) CREATE INDEX index_21526 ON repack.table_21486 USING btree (name, article) TABLESPACE pg_default
LOG: (query) CREATE INDEX index_21525 ON repack.table_21486 USING btree (code) TABLESPACE pg_default
LOG: (query) CREATE UNIQUE INDEX index_21495 ON repack.table_21486 USING btree (code, department) TABLESPACE pg_default
LOG: (query) CREATE UNIQUE INDEX index_21493 ON repack.table_21486 USING btree (id) TABLESPACE pg_default
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_21486 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_21486 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_21486 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_21486 SET (id, code, article, name, department) = ($2.id, $2.code, $2.article, $2.name, $2.department) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_21486 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) = {24/31486}
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG:    (param:0) = SELECT * FROM repack.log_21486 ORDER BY id LIMIT $1
LOG:    (param:1) = INSERT INTO repack.table_21486 VALUES ($1.*)
LOG:    (param:2) = DELETE FROM repack.table_21486 WHERE (id) = ($1.id)
LOG:    (param:3) = UPDATE repack.table_21486 SET (id, code, article, name, department) = ($2.id, $2.code, $2.article, $2.name, $2.department) WHERE (id) = ($1.id)
LOG:    (param:4) = DELETE FROM repack.log_21486 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) = {24/31486}
NOTICE: Waiting for 1 transactions to finish. First PID: 20492
...
...
...

OS: Centos 7.6
PG 11
pg_repack 1.4.7