pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18616: Long-running hash index build can not be interrupted
5+ messages / 4 participants
[nested] [flat]
* BUG #18616: Long-running hash index build can not be interrupted
@ 2024-09-13 10:00 PG Bug reporting form <noreply@postgresql.org>
2024-09-13 14:51 ` Re: BUG #18616: Long-running hash index build can not be interrupted Pavel Borisov <pashkin.elfe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2024-09-13 10:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 18616
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 17rc1
Operating system: Ubuntu 22.04
Description:
The following script:
CREATE TABLE t(i int);
INSERT INTO t SELECT 1 FROM generate_series(1, 10000000);
SET maintenance_work_mem = '1GB';
SET statement_timeout = '90s';
CREATE INDEX hi ON t USING hash (i);
reaches a state, when the backend can not be interrupted.
gdb shows that the code execution loops inside:
#0 _h_indexbuild (...) at hashsort.c:151
#1 0x0000557f3671cbf3 in hashbuild (...)
at hash.c:183
...
with tups_done increasing slowly (given the total number of tuples).
Reproduced on all supported versions.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18616: Long-running hash index build can not be interrupted
2024-09-13 10:00 BUG #18616: Long-running hash index build can not be interrupted PG Bug reporting form <noreply@postgresql.org>
@ 2024-09-13 14:51 ` Pavel Borisov <pashkin.elfe@gmail.com>
2024-09-13 18:00 ` Re: BUG #18616: Long-running hash index build can not be interrupted Alexander Lakhin <exclusion@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Pavel Borisov @ 2024-09-13 14:51 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Hi, Alexander!
On Fri, 13 Sept 2024 at 18:02, PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18616
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 17rc1
> Operating system: Ubuntu 22.04
> Description:
>
> The following script:
> CREATE TABLE t(i int);
> INSERT INTO t SELECT 1 FROM generate_series(1, 10000000);
>
> SET maintenance_work_mem = '1GB';
>
> SET statement_timeout = '90s';
> CREATE INDEX hi ON t USING hash (i);
>
> reaches a state, when the backend can not be interrupted.
>
> gdb shows that the code execution loops inside:
> #0 _h_indexbuild (...) at hashsort.c:151
> #1 0x0000557f3671cbf3 in hashbuild (...)
> at hash.c:183
> ...
> with tups_done increasing slowly (given the total number of tuples).
>
> Reproduced on all supported versions.
>
I was unable to reproduce it on my machine with these settings. Tried
statement timeouts 30-120s. Index build appears to be in interruptible
phase on my system.
But could you check in your environment with the following patch?
Kind regards,
Pavel Borisov
Supabase
Attachments:
[application/octet-stream] 0001-Check-for-interrupts-during-hash-index-builds.patch (882B, ../../CALT9ZEF8uLBizGLFEQArNv1t6aoQnjKuo04yEQfyup9nodj6Bg@mail.gmail.com/3-0001-Check-for-interrupts-during-hash-index-builds.patch)
download | inline diff:
From adb062a422c68adc2b2861b934386d308271f744 Mon Sep 17 00:00:00 2001
From: Pavel Borisov <pashkin.elfe@gmail.com>
Date: Fri, 13 Sep 2024 18:46:41 +0400
Subject: [PATCH] Check for interrupts during hash index builds
This is to avoid non-interruptible phase of index build.
---
src/backend/access/hash/hashsort.c | 1 +
1 file changed, 1 insertion(+)
diff --git a/src/backend/access/hash/hashsort.c b/src/backend/access/hash/hashsort.c
index b67b2207c05..a3fcff9e99e 100644
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -144,6 +144,7 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
hspool->low_mask);
Assert(hashkey >= lasthashkey);
#endif
+ CHECK_FOR_INTERRUPTS();
/* the tuples are sorted by hashkey, so pass 'sorted' as true */
_hash_doinsert(hspool->index, itup, heapRel, true);
--
2.39.2 (Apple Git-143)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18616: Long-running hash index build can not be interrupted
2024-09-13 10:00 BUG #18616: Long-running hash index build can not be interrupted PG Bug reporting form <noreply@postgresql.org>
2024-09-13 14:51 ` Re: BUG #18616: Long-running hash index build can not be interrupted Pavel Borisov <pashkin.elfe@gmail.com>
@ 2024-09-13 18:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-09-13 19:22 ` Re: BUG #18616: Long-running hash index build can not be interrupted Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Alexander Lakhin @ 2024-09-13 18:00 UTC (permalink / raw)
To: Pavel Borisov <pashkin.elfe@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Pavel,
13.09.2024 17:51, Pavel Borisov wrote:
>
>
> Reproduced on all supported versions.
>
> I was unable to reproduce it on my machine with these settings. Tried statement timeouts 30-120s. Index build appears
> to be in interruptible phase on my system.
> But could you check in your environment with the following patch?
>
Thank you for paying attention to this!
Yes, the patch works for me. The query is interrupted as expected.
But I wonder, why don't you the see same?
Could you please show the server log, with the following change applied and
log_statement = 'all'
backtrace_functions = 'ProcessInterrupts'
?
--- a/src/backend/access/hash/hashsort.c
+++ b/src/backend/access/hash/hashsort.c
@@ -125,6 +125,7 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
uint32 hashkey = 0;
#endif
+elog(LOG, "_h_indexbuild() start");
tuplesort_performsort(hspool->sortstate);
while ((itup = tuplesort_getindextuple(hspool->sortstate, true)) != NULL)
@@ -151,4 +152,5 @@ _h_indexbuild(HSpool *hspool, Relation heapRel)
pgstat_progress_update_param(PROGRESS_CREATEIDX_TUPLES_DONE,
++tups_done);
}
+elog(LOG, "_h_indexbuild() end; tups_done: %ld", tups_done);
}
Best regards,
Alexander
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18616: Long-running hash index build can not be interrupted
2024-09-13 10:00 BUG #18616: Long-running hash index build can not be interrupted PG Bug reporting form <noreply@postgresql.org>
2024-09-13 14:51 ` Re: BUG #18616: Long-running hash index build can not be interrupted Pavel Borisov <pashkin.elfe@gmail.com>
2024-09-13 18:00 ` Re: BUG #18616: Long-running hash index build can not be interrupted Alexander Lakhin <exclusion@gmail.com>
@ 2024-09-13 19:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-13 19:45 ` Re: BUG #18616: Long-running hash index build can not be interrupted Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2024-09-13 19:22 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Pavel Borisov <pashkin.elfe@gmail.com>; pgsql-bugs@lists.postgresql.org
Alexander Lakhin <exclusion@gmail.com> writes:
> 13.09.2024 17:51, Pavel Borisov wrote:
>> I was unable to reproduce it on my machine with these settings. Tried statement timeouts 30-120s. Index build appears
>> to be in interruptible phase on my system.
> Yes, the patch works for me. The query is interrupted as expected.
> But I wonder, why don't you the see same?
I see the same result as Alexander: the query fails to time out
after the expected 90 seconds, and it's looping in _h_indexbuild.
Looking at hashbuild, the effective sort_threshold depends on
NBuffers, so maybe if you have that set to a high enough value
it fails to go into the sort path? If I use
SET maintenance_work_mem = '128MB';
instead of the suggested 1GB, I don't see the problem.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #18616: Long-running hash index build can not be interrupted
2024-09-13 10:00 BUG #18616: Long-running hash index build can not be interrupted PG Bug reporting form <noreply@postgresql.org>
2024-09-13 14:51 ` Re: BUG #18616: Long-running hash index build can not be interrupted Pavel Borisov <pashkin.elfe@gmail.com>
2024-09-13 18:00 ` Re: BUG #18616: Long-running hash index build can not be interrupted Alexander Lakhin <exclusion@gmail.com>
2024-09-13 19:22 ` Re: BUG #18616: Long-running hash index build can not be interrupted Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-09-13 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2024-09-13 19:45 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Pavel Borisov <pashkin.elfe@gmail.com>; pgsql-bugs@lists.postgresql.org
I wrote:
> Looking at hashbuild, the effective sort_threshold depends on
> NBuffers, so maybe if you have that set to a high enough value
> it fails to go into the sort path? If I use
> SET maintenance_work_mem = '128MB';
> instead of the suggested 1GB, I don't see the problem.
Oh, false alarm: that test in hashbuild takes basically the
min of maintenance_work_mem and NBuffers, so that with
default NBuffers of 128MB, there's no difference here between
those two settings.
The reason I see a difference in behavior seems to be that
with maintenance_work_mem = 1GB, the tuple sorting step
completes faster, allowing control to reach _h_indexbuild
before the 10sec timeout I was testing with. With the
smaller maintenance_work_mem setting, we're still sorting
when it times out --- and there are CHECK_FOR_INTERRUPTS
calls in the sort code.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-13 19:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-13 10:00 BUG #18616: Long-running hash index build can not be interrupted PG Bug reporting form <noreply@postgresql.org>
2024-09-13 14:51 ` Pavel Borisov <pashkin.elfe@gmail.com>
2024-09-13 18:00 ` Alexander Lakhin <exclusion@gmail.com>
2024-09-13 19:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-09-13 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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