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. Fix intra-query memory leak when a SRF returns zero rows.

  2. Adjust parallel_schedule with event triggers on authenticated login

  1. BUG #18172: High memory usage in tSRF function context

    PG Bug reporting form <noreply@postgresql.org> — 2023-10-27T11:02:14Z

    The following bug has been logged on the website:

Bug reference:      18172
Logged by:          Sergei Kornilov
Email address:      sk@zsrv.org
PostgreSQL version: Unsupported/Unknown
Operating system:   various Linux
Description:        

Hello

While researching several cases of OOM, I discovered an atypically high
memory consumption in this case:

/*
 * test data looks like {"pay": [{"id": 1, "test": 1}], "name": "foo1"}
 * about 1,3GiB total
 */
create table test (id serial, data jsonb);
insert into test (data) select jsonb_build_object('name', 'foo'||i, 'pay',
jsonb_build_array(jsonb_build_object('id', i, 'test', i % 10))) from
generate_series(1,1e7,1) as i;

Using gdb I called MemoryContextStats(TopPortalContext) on break point
standard_ExecutorEnd

statement: select distinct (jsonb_array_elements(data->'pay2')->>'test')
from test;
TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); 512 used
  PortalContext: 1024 total in 1 blocks; 616 free (0 chunks); 408 used:
<unnamed>
    ExecutorState: 16384 total in 2 blocks; 1760 free (5 chunks); 14624
used
      HashAgg meta context: 24576 total in 2 blocks; 13880 free (2 chunks);
10696 used
        ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240
used
      tSRF function arguments: 1442840576 total in 182 blocks; 2833096 free
(356 chunks); 1440007480 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
Grand total: 1442956288 bytes in 196 blocks; 2920648 free (363 chunks);
1440035640 used

statement: select distinct (jsonb_array_elements(data->'pay2')->>'test')
from test where data ? 'pay2';
TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); 512 used
  PortalContext: 1024 total in 1 blocks; 616 free (0 chunks); 408 used:
<unnamed>
    ExecutorState: 32768 total in 3 blocks; 16680 free (6 chunks); 16088
used
      HashAgg meta context: 57408 total in 2 blocks; 5744 free (0 chunks);
51664 used
        ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240
used
      tSRF function arguments: 8192 total in 1 blocks; 7952 free (0 chunks);
240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
Grand total: 173120 bytes in 16 blocks; 102288 free (6 chunks); 70832 used

statement: select distinct (jsonb_array_elements(data->'pay')->>'test') from
test;
TopPortalContext: 8192 total in 1 blocks; 7680 free (0 chunks); 512 used
  PortalContext: 1024 total in 1 blocks; 616 free (0 chunks); 408 used:
<unnamed>
    ExecutorState: 32768 total in 3 blocks; 17912 free (10 chunks); 14856
used
      HashAgg meta context: 1581120 total in 2 blocks; 5744 free (0 chunks);
1575376 used
        ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240
used
      tSRF function arguments: 8192 total in 1 blocks; 7952 free (0 chunks);
240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7472 free (0 chunks); 720 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
      ExprContext: 8192 total in 1 blocks; 7952 free (0 chunks); 240 used
Grand total: 1696832 bytes in 16 blocks; 103040 free (10 chunks); 1593792
used

The first query consumes much more memory; note that it refers to a
non-existent json key. Querying for a key that exists in JSON or checking
for the existence of a key in where clause corrects memory consumption. But,
unfortunately, data analysts write the query exactly like the first
statement, not like the second statement.

Queries, of course, are corrected later, but maybe it is possible to improve
memory consumption for this case?

MemoryContextStats above are from my local 17devel
(83510534d5f3f116efa035639b9b62b8c6c4df34 commit), occurs for all supported
versions.

regards, Sergei

  2. Re: BUG #18172: High memory usage in tSRF function context

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-10-28T03:19:02Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> While researching several cases of OOM, I discovered an atypically high
> memory consumption in this case:
> ...
> The first query consumes much more memory; note that it refers to a
> non-existent json key. Querying for a key that exists in JSON or checking
> for the existence of a key in where clause corrects memory consumption.

Thanks for the report!  It seems that ExecProjectSet neglected to
think hard about what happens when the SRF returns zero rows, so
it leaks whatever memory the SRF expression might have leaked.
The attached fixes it for me.

			regards, tom lane

  3. Re:BUG #18172: High memory usage in tSRF function context

    Sergei Kornilov <sk@zsrv.org> — 2023-10-28T07:56:15Z

    Hi

Thank you! The patch works for me too.

regards, Sergei

  4. Re: BUG #18172: High memory usage in tSRF function context

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-10-28T18:15:08Z

    Sergei Kornilov <sk@zsrv.org> writes:
> Thank you! The patch works for me too.

Thanks for testing!  I looked at ExecProjectSet a second time
and realized that I probably still hadn't thought hard enough,
because it's also responsible for doing ResetExprContext(econtext)
and that wasn't happening either when looping around after the
SRF returns no rows.  So if the SRF itself leaks some memory
in its CurrentMemoryContext and then returns nothing, we can
see bloat in that context.  I failed to produce such a behavior
with jsonb_array_elements, but after trying some other things
I found a test case that still leaked even with the initial
patch:

create table zed as
  select repeat('xyzzy', 10000) as f1, repeat('g', 10000) as flags
  from generate_series(1,100000);

select regexp_matches(f1, 'q', flags) from zed;

The leak here stems from regexp_matches detoasting its flags
argument in the CurrentMemoryContext.  A big flags argument
is surely not real-world usage, but perhaps there are other
cases that are more likely to happen.

The fix of course is just to make sure we also do ResetExprContext
when looping around.  Applied to all branches.

			regards, tom lane

  5. Re:BUG #18172: High memory usage in tSRF function context

    Sergei Kornilov <sk@zsrv.org> — 2023-10-28T18:47:40Z

    Great! Thanks!

regards, Sergei