pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
[reorg/pg_repack] PR #491: Terminate pg_repack's own old backends in the cleanup callback
2+ messages / 2 participants
[nested] [flat]

* [reorg/pg_repack] PR #491: Terminate pg_repack's own old backends in the cleanup callback
@ 2026-11-11 17:01  "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-11 17:01 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

Hi, and thank you for maintaining pg_repack.

This is a proposal for #456, along the lines of the second option suggested there (terminate only the backends that pg_repack itself started). If the connection is lost while the data copy is still running, the old backend keeps running the INSERT and holds its lock on the target table. The cleanup callback then waits for ACCESS EXCLUSIVE, and with `--no-kill-backend` it gives up after `--wait-timeout`, so the trigger and the temporary objects stay behind.

The patch records the backend PIDs of `connection` and `conn2` right after the cleanup callback is pushed (PQbackendPID() returns 0 once a connection is lost, so it cannot be read later), and terminates them in the callback after reconnecting. These backends belong to the run being abandoned, so other sessions are still not touched when `--no-kill-backend` is set.

I added a regression test, `lost-connection-cleanup`, that cuts the connection with socat during the copy and counts the objects left behind for the table. It prints "skipped" (second expected file) if socat is not available. It takes about 100 seconds here, so please tell me if you would prefer a lighter approach.

Tested on PostgreSQL 17 (docker image postgres:17): with the patch, `make installcheck` passes all 14 tests. With the code from master plus the new test, `lost-connection-cleanup` fails (2 objects left instead of 0). I have not tested other PostgreSQL versions or --jobs.

Fixes #456


inline patch 1: bin/pg_repack.c, regress/Makefile, regress/expected/lost-connection-cleanup.out and 3 more (+101/-1 lines) download inline-1.patchdiff --git a/bin/pg_repack.c b/bin/pg_repack.c index 9ff415f..1cd032c 100644 --- a/bin/pg_repack.c +++ b/bin/pg_repack.c @@ -256,6 +256,7 @@ static int jobs = 0; /* number of concurrent worker conns. */ static bool dryrun = false; static unsigned int temp_obj_num = 0; /* temporary objects counter */ static bool no_kill_backend = false; /* abandon when timed-out */ +static int own_pids[2] = {0, 0}; /* backend PIDs of connection and conn2 */ static bool no_superuser_check = false; static SimpleStringList exclude_extension_list = {NULL, NULL}; /* don't repack tables of these extensions */ static bool no_error_on_publication = false; /* repack even though publication FOR ALL TABLES is found */ @@ -1324,6 +1325,13 @@ repack_one_table(repack_table *table, const char *orderby) /* push repack_cleanup_callback() on stack to clean temporary objects */ pgut_atexit_push(repack_cleanup_callback, table); + /* + * Remember our own backend PIDs now: once a connection is lost, + * PQbackendPID() returns 0 for it. + */ + own_pids[0] = PQbackendPID(connection); + own_pids[1] = PQbackendPID(conn2); + /* * 1. Setup advisory lock and trigger on main table. */ @@ -2056,6 +2064,7 @@ repack_cleanup_callback(bool fatal, void *userdata) const char *params[2]; char buffer[12]; char num_buff[12]; + int i; if(fatal) { @@ -2068,6 +2077,29 @@ repack_cleanup_callback(bool fatal, void *userdata) */ reconnect(ERROR); + /* + * The old backends belong to this pg_repack run, which is being + * abandoned. If the connection was lost while a long query (such as + * the data copy) was running, such a backend may still be busy and + * would block the ACCESS EXCLUSIVE lock needed below, so terminate + * them even with --no-kill-backend. + */ + for (i = 0; i < 2; i++) + { + char pidbuf[12]; + const char *pidparams[1]; + PGresult *res; + + if (own_pids[i] <= 0) + continue; + pidparams[0] = utoa(own_pids[i], pidbuf); + res = pgut_execute_elevel(connection, + "SELECT pg_terminate_backend(pid) FROM pg_stat_activity" + " WHERE pid = $1::int AND pid <> pg_backend_pid()", + 1, pidparams, DEBUG2); + CLEARPGRES(res); + } + command("BEGIN ISOLATION LEVEL READ COMMITTED", 0, NULL); if (!(lock_exclusive(connection, params[0], table->lock_table, false))) { diff --git a/regress/Makefile b/regress/Makefile index 43c4d33..33482d7 100644 --- a/regress/Makefile +++ b/regress/Makefile @@ -17,7 +17,7 @@ INTVERSION := $(shell echo $$(($$(echo $(VERSION).0 | sed 's/\([[:digit:]]\{1,\} # Test suite # -REGRESS := init-extension repack-setup repack-run error-on-invalid-idx no-error-on-invalid-idx after-schema repack-check nosuper tablespace get_order_by trigger trigger_privs publication +REGRESS := init-extension repack-setup repack-run error-on-invalid-idx no-error-on-invalid-idx after-schema repack-check nosuper tablespace get_order_by trigger trigger_privs publication lost-connection-cleanup USE_PGXS = 1 # use pgxs if not in contrib directory PGXS := $(shell $(PG_CONFIG) --pgxs) diff --git a/regress/expected/lost-connection-cleanup.out b/regress/expected/lost-connection-cleanup.out new file mode 100644 index 0000000..161558f --- /dev/null +++ b/regress/expected/lost-connection-cleanup.out @@ -0,0 +1,6 @@ +-- +-- lost connection during the data copy: cleanup must not be blocked +-- by pg_repack's own backend (issue #456) +-- +\! sh ./lost-connection-cleanup.sh +0 diff --git a/regress/expected/lost-connection-cleanup_1.out b/regress/expected/lost-connection-cleanup_1.out new file mode 100644 index 0000000..da3b925 --- /dev/null +++ b/regress/expected/lost-connection-cleanup_1.out @@ -0,0 +1,6 @@ +-- +-- lost connection during the data copy: cleanup must not be blocked +-- by pg_repack's own backend (issue #456) +-- +\! sh ./lost-connection-cleanup.sh +skipped diff --git a/regress/lost-connection-cleanup.sh b/regress/lost-connection-cleanup.sh new file mode 100644 index 0000000..8c6739a --- /dev/null +++ b/regress/lost-connection-cleanup.sh @@ -0,0 +1,51 @@ +#!/bin/sh +# +# Regression helper for issue #456: when the connection of pg_repack is lost +# while the data copy is still running on the server, the cleanup must not be +# blocked by pg_repack's own (still running) backend, even with +# --no-kill-backend. +# +# Prints "skipped" if socat or timeout is not available, otherwise prints the +# number of repack objects left behind (expected: 0). + +DB=contrib_regression +SRV_PORT=${PGPORT:-5432} +SRV_HOST=${PGHOST:-127.0.0.1} +case "$SRV_HOST" in /*) SRV_HOST=127.0.0.1 ;; esac +PROXY_PORT=$((SRV_PORT + 1000)) + +command -v socat >/dev/null 2>&1 && command -v timeout >/dev/null 2>&1 || { echo skipped; exit 0; } + +psql -X -q -d $DB <<'SQL' >/dev/null 2>&1 +DROP TABLE IF EXISTS lost_conn_t; +CREATE TABLE lost_conn_t (id int PRIMARY KEY, v text); +INSERT INTO lost_conn_t SELECT g, md5(g::text) || md5((g+1)::text) FROM generate_series(1, 12000000) g; +SQL + +socat -d0 TCP-LISTEN:$PROXY_PORT,fork,reuseaddr TCP:$SRV_HOST:$SRV_PORT & +SOCAT=$! +sleep 1 + +timeout 120 pg_repack --dbname=$DB --host=127.0.0.1 --port=$PROXY_PORT \ + --table=lost_conn_t --no-kill-backend --wait-timeout=3 >/dev/null 2>&1 & +RP=$! + +# wait for the data copy to start, then cut the established connections +for i in $(seq 1 30); do + n=$(psql -X -Atq -d $DB -c "SELECT count(*) FROM pg_stat_activity WHERE query LIKE 'INSERT INTO repack.table_%'") + [ "$n" -ge 1 ] && break + sleep 1 +done +pkill -P $SOCAT +wait $RP + +# let the server notice that the copy ended, then count leftovers +for i in $(seq 1 60); do + n=$(psql -X -Atq -d $DB -c "SELECT count(*) FROM pg_stat_activity WHERE query LIKE 'INSERT INTO repack.table_%' AND state = 'active'") + [ "$n" -eq 0 ] && break + sleep 1 +done +psql -X -Atq -d $DB -c "SELECT (SELECT count(*) FROM pg_trigger WHERE tgname = 'repack_trigger' AND tgrelid = 'lost_conn_t'::regclass) + (SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'repack' AND c.relname IN ('table_' || 'lost_conn_t'::regclass::oid, 'log_' || 'lost_conn_t'::regclass::oid))" + +kill $SOCAT 2>/dev/null +psql -X -q -d $DB -c "DROP TABLE IF EXISTS lost_conn_t" >/dev/null 2>&1 diff --git a/regress/sql/lost-connection-cleanup.sql b/regress/sql/lost-connection-cleanup.sql new file mode 100644 index 0000000..d3c007d --- /dev/null +++ b/regress/sql/lost-connection-cleanup.sql @@ -0,0 +1,5 @@ +-- +-- lost connection during the data copy: cleanup must not be blocked +-- by pg_repack's own backend (issue #456) +-- +\! sh ./lost-connection-cleanup.sh
^ permalink raw [nested|flat] 2+ messages in thread

* Re: [reorg/pg_repack] PR #491: Terminate pg_repack's own old backends in the cleanup callback
@ 2026-11-12 15:40  "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: za-arthur (@za-arthur) @ 2026-11-12 15:40 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

@za-arthur applied label "enhancement".

^ permalink  raw  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-12 15:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-11 17:01 [reorg/pg_repack] PR #491: Terminate pg_repack's own old backends in the cleanup callback "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-12 15:40 Re: [reorg/pg_repack] PR #491: Terminate pg_repack's own old backends in the cleanup callback "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox