Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow _h_indexbuild() to be interrupted.

  1. BUG #18616: Long-running hash index build can not be interrupted

    PG Bug reporting form <noreply@postgresql.org> — 2024-09-13T10:00:00Z

    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.

  2. Re: BUG #18616: Long-running hash index build can not be interrupted

    Pavel Borisov <pashkin.elfe@gmail.com> — 2024-09-13T14:51:30Z

    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

  3. Re: BUG #18616: Long-running hash index build can not be interrupted

    Alexander Lakhin <exclusion@gmail.com> — 2024-09-13T18:00:00Z

    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

  4. Re: BUG #18616: Long-running hash index build can not be interrupted

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-13T19:22:26Z

    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

  5. Re: BUG #18616: Long-running hash index build can not be interrupted

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-09-13T19:45:11Z

    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