pg.ddx.io reorg/pg_repack GitHub issues and pull requests
help / color / mirror / Atom feed[reorg/pg_repack] PR #183: Add options for lock statement timeout
15+ messages / 6 participants
[nested] [flat]
* [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-06-06 11:29 "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
0 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2018-06-06 11:29 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
This change was needed to get pg_repack to get the locks in our reasonably busy servers.
inline patch 1: bin/pg_repack.c, doc/pg_repack.rst, doc/pg_repack_jp.rst (+20/-2 lines) download inline-1.patch
diff --git a/bin/pg_repack.c b/bin/pg_repack.c
index 5bcee125..6382bb58 100644
--- a/bin/pg_repack.c
+++ b/bin/pg_repack.c
@@ -256,6 +256,7 @@ static unsigned int temp_obj_num = 0; /* temporary objects counter */
static bool no_kill_backend = false; /* abandon when timed-out */
static bool no_superuser_check = false;
static SimpleStringList exclude_extension_list = {NULL, NULL}; /* don't repack tables of these extensions */
+static int lock_wait_max = 1; /* Max lock statement timeout, in seconds */
/* buffer should have at least 11 bytes */
static char *
@@ -285,6 +286,7 @@ static pgut_option options[] =
{ 'b', 'D', "no-kill-backend", &no_kill_backend },
{ 'b', 'k', "no-superuser-check", &no_superuser_check },
{ 'l', 'C', "exclude-extension", &exclude_extension_list },
+ { 'i', 'L', "lock-wait-max", &lock_wait_max },
{ 0 },
};
@@ -308,6 +310,12 @@ main(int argc, char *argv[])
if (dryrun)
elog(INFO, "Dry run enabled, not executing repack");
+ if (wait_timeout < lock_wait_max)
+ {
+ ereport(ERROR, (errcode(EINVAL),
+ errmsg("wait-timeout needs to be >= lock-wait-max")));
+ }
+
if (r_index.head || only_indexes)
{
if (r_index.head && table_list.head)
@@ -1789,7 +1797,7 @@ lock_access_share(PGconn *conn, Oid relid, const char *target_name)
break;
/* wait for a while to lock the table. */
- wait_msec = Min(1000, i * 100);
+ wait_msec = Min(lock_wait_max * 1000, Max(lock_wait_max * 1000 * i / 10, 100));
printfStringInfo(&sql, "SET LOCAL statement_timeout = %d", wait_msec);
pgut_command(conn, sql.data, 0, NULL);
@@ -1930,7 +1938,7 @@ lock_exclusive(PGconn *conn, const char *relid, const char *lock_query, bool sta
}
/* wait for a while to lock the table. */
- wait_msec = Min(1000, i * 100);
+ wait_msec = Min(lock_wait_max * 1000, Max(lock_wait_max * 1000 * i / 10, 100));
snprintf(sql, lengthof(sql), "SET LOCAL statement_timeout = %d", wait_msec);
pgut_command(conn, sql, 0, NULL);
@@ -2351,6 +2359,7 @@ pgut_help(bool details)
printf(" -i, --index=INDEX move only the specified index\n");
printf(" -x, --only-indexes move only indexes of the specified table\n");
printf(" -T, --wait-timeout=SECS timeout to cancel other backends on conflict\n");
+ printf(" -L, --lock-wait-max=SECS lock statement max wait time\n");
printf(" -D, --no-kill-backend don't kill other backends when timed out\n");
printf(" -Z, --no-analyze don't analyze at end\n");
printf(" -k, --no-superuser-check skip superuser checks in client\n");
diff --git a/doc/pg_repack.rst b/doc/pg_repack.rst
index 2aecb26e..d65a838e 100644
--- a/doc/pg_repack.rst
+++ b/doc/pg_repack.rst
@@ -119,6 +119,7 @@ Options:
-i, --index=INDEX move only the specified index
-x, --only-indexes move only indexes of the specified table
-T, --wait-timeout=SECS timeout to cancel other backends on conflict
+ -L, --lock-wait-max=SECS lock statement max wait time
-D, --no-kill-backend don't kill other backends when timed out
-Z, --no-analyze don't analyze at end
-k, --no-superuser-check skip superuser checks in client
@@ -207,6 +208,13 @@ Reorg Options
backends after twice this timeout has passed.
The default is 60 seconds.
+``-L SECS``, ``--lock-wait-max=SECS``
+ When pg_repack needs to take table locks (shared or exclusive) it'll wait
+ up to this many seconds before canceling the lock request and trying again.
+ This is done to allow concurrent queries to make progress and not get
+ queued up behind the pg_repack lock request. Consider increasing this
+ timeout if pg_repack fails to acquire table locks. The default is 1 second.
+
``-D``, ``--no-kill-backend``
Skip to repack table if the lock cannot be taken for duration specified
``--wait-timeout``, instead of cancelling conflicting queries. The default
diff --git a/doc/pg_repack_jp.rst b/doc/pg_repack_jp.rst
index b4ac5c5c..4b49af5f 100644
--- a/doc/pg_repack_jp.rst
+++ b/doc/pg_repack_jp.rst
@@ -207,6 +207,7 @@ pg_repackもしくはpg_reorgの古いバージョンからのアップグレー
-i, --index=INDEX move only the specified index
-x, --only-indexes move only indexes of the specified table
-T, --wait-timeout=SECS timeout to cancel other backends on conflict
+ -L, --lock-wait-max=SECS lock statement max wait time
-D, --no-kill-backend don't kill other backends when timed out
-Z, --no-analyze don't analyze at end
-k, --no-superuser-check skip superuser checks in client
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-06-06 13:39 ` "schmiddy (@schmiddy)" <noreply+schmiddy@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: schmiddy (@schmiddy) @ 2018-06-06 13:39 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Thanks, I think a configurable `lock_wait_max` is useful. I'm less enthused about adding yet another option for `--lock-wait-step`, this seems like something we should be able to guesstimate on our own, without contributing to the explosion of command-line options.
What if we just had the lock wait step be 10% of the `lock_wait_max`, with some minimum like 100 ms.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-06-10 14:41 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2018-06-10 14:41 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
I don't have strong opinions but I'd suggest keeping the step parameter as it's strictly more useful (arguably to a small group but it doesn't matter to the other).
Defaulting wait-step to 10% of max-wait sounds nice a nice default though.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2018-08-22 07:42 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@arthurprs closed this.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2018-08-22 07:42 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
I finally had the time to revisit this. Change updated as you suggested.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2018-08-22 07:42 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@arthurprs reopened this.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-10-30 15:45 ` "andreasscherman (@andreasscherman)" <noreply+andreasscherman@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: andreasscherman (@andreasscherman) @ 2018-10-30 15:45 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Thanks for the pull request @arthurprs! This is exactly what I was looking for --- we have a highly contested server and was in need of having a variable timeout for the lock.
@schmiddy would you mind merging this?
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2018-11-14 14:46 ` "hstefan (@hstefan)" <noreply+hstefan@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: hstefan (@hstefan) @ 2018-11-14 14:46 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
We have a similar patch internally, and would greatly appreciate having it merged so that we can use the upstream package.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: andreasscherbaum (@andreasscherbaum) @ 2023-03-23 11:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@arthurprs Can you please rebase this PR? This will trigger the Checks.
Also the documentation is not updated with the new flag.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-23 11:44 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2023-03-23 11:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@arthurprs assigned @arthurprs.
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: andreasscherbaum (@andreasscherbaum) @ 2023-03-23 11:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@andreasscherbaum applied label "waiting for author".
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: andreasscherbaum (@andreasscherbaum) @ 2023-03-23 11:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@andreasscherbaum applied label "needs more work".
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: andreasscherbaum (@andreasscherbaum) @ 2023-03-23 11:44 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@andreasscherbaum applied label "enhancement".
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2023-03-31 16:19 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: za-arthur (@za-arthur) @ 2023-03-31 16:19 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@za-arthur removed label "waiting for author".
^ permalink raw [nested|flat] 15+ messages in thread
* Re: [reorg/pg_repack] PR #183: Add options for lock statement timeout
@ 2026-02-12 10:04 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
13 siblings, 0 replies; 15+ messages in thread
From: arthurprs (@arthurprs) @ 2026-02-12 10:04 UTC (permalink / raw)
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
@arthurprs closed this.
^ permalink raw [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-02-12 10:04 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-06 11:29 [reorg/pg_repack] PR #183: Add options for lock statement timeout "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2018-06-06 13:39 ` "schmiddy (@schmiddy)" <noreply+schmiddy@github.com>
2018-06-10 14:41 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2018-08-22 07:42 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2018-10-30 15:45 ` "andreasscherman (@andreasscherman)" <noreply+andreasscherman@github.com>
2018-11-14 14:46 ` "hstefan (@hstefan)" <noreply+hstefan@github.com>
2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
2023-03-23 11:44 ` "arthurprs (@arthurprs)" <noreply+arthurprs@github.com>
2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
2023-03-23 11:44 ` "andreasscherbaum (@andreasscherbaum)" <noreply+andreasscherbaum@github.com>
2023-03-31 16:19 ` "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
2026-02-12 10:04 ` "arthurprs (@arthurprs)" <noreply+arthurprs@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