RAM is being full and updates error about postgres HOT 10 OPEN

from postgres.

So I build it from source https://github.com/postgrespro/postgres/tree/jsonb_toaster and tested it.

from postgres.

Hi!
Please provide your test scripts/queries, we'll try to reproduce your problem.

from postgres.

queries.zip

from postgres.

dayInfos_ddl.sql.zip

from postgres.

Thanks! Could you please also provide Postgres logfile with a crash? And did you try to get backtrace for it?

from postgres.

Hi. I didn't turn on logging on PostgreSQL. I just have reproduced insert and update statements with enabled logging. Unexpected insert statement (2000000 rows) executed successfully but update statement still generates error.

postgresql-Wed.log.zip

from postgres.

I've reproduced it, checking out what's going on.

from postgres.

Iterators in jsonb Toaster need to be reworked. Here's output from Heaptrack (update of 1000000 records):

709 calls to allocation functions with 5.68G peak consumption from
AllocSetAlloc
at /home/user/ppg15toast/src/backend/utils/mmgr/aset.c:920
in /usr/local/pgsql/bin/postgres
230 calls with 1.90G peak consumption from:
palloc
at /home/user/ppg15toast/src/backend/utils/mmgr/mcxt.c:1082
in /usr/local/pgsql/bin/postgres
create_toast_buffer
at /home/user/ppg15toast/src/backend/access/common/toast_internals.c:1356
in /usr/local/pgsql/bin/postgres
jsonx_create_detoast_iterator
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toast_internals.c:1739
in /usr/local/pgsql/lib/jsonb_toaster.so
jsonxzInitWithHeader
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toaster.c:2113
in /usr/local/pgsql/lib/jsonb_toaster.so
jsonxzInitContainerFromDatum
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toaster.c:2402
in /usr/local/pgsql/lib/jsonb_toaster.so
jsonb_toaster_save_object
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toaster.c:2644
in /usr/local/pgsql/lib/jsonb_toaster.so
jsonb_toaster_save
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toaster.c:2762
in /usr/local/pgsql/lib/jsonb_toaster.so
jsonb_toaster_toast
at /home/user/ppg15toast/contrib/jsonb_toaster/jsonb_toaster.c:3191
in /usr/local/pgsql/lib/jsonb_toaster.so
toast_tuple_externalize
at /home/user/ppg15toast/src/backend/access/table/toast_helper.c:343
in /usr/local/pgsql/bin/postgres
heap_toast_tuple_externalize
at /home/user/ppg15toast/src/backend/access/heap/heaptoast.c:119
in /usr/local/pgsql/bin/postgres
heap_toast_insert_or_update
at /home/user/ppg15toast/src/backend/access/heap/heaptoast.c:262
in /usr/local/pgsql/bin/postgres
heap_update
at /home/user/ppg15toast/src/backend/access/heap/heapam.c:3727
in /usr/local/pgsql/bin/postgres

The same is with bulk insert (with generate_series, not in cycle)
I'll rebase jsonb toaster onto the latest master, since there were some changes
that may affect such behavior (bulk insert and update patch), and checking out
how we can deal with such memory consumption.

from postgres.

Hi! We're working on memory consumption, there are some improvements already, please check them out. Bulk insert (with generate_series) now also works fine for large number of records.

from postgres.