RAM is being full and updates error

[avpco]

Hello.
I tested new TOAST (https://github.com/postgrespro/postgres/tree/jsonb_toaster) mechanism for jsonb data types. Version (15.1 (Debian 15.1-1.pgdg110+1), server 15beta1). Test table has 6 columns which has jsonb data type.
I was inserting a "large" amount of data (over 1 million) and noticed the session “eats up” the entire RAM. (I saw it in Zabbix monitoring system)

Also, each I run update statement I get the following error:

WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

[obartunov]

On Mon, Dec 26, 2022 at 4:07 PM Valerian ***@***.***> wrote: Hello. I tested new TOAST mechanism for jsonb data types. Version (15.1 (Debian 15.1-1.pgdg110+1), server 15beta1). Test table has 6 columns which has jsonb data type.

New TOAST doesn't available yet, what did you tested ?

…

I was inserting a "large" amount of data (over 1 million) and noticed the session “eats up” the entire RAM. (I saw it in Zabbix monitoring system) Also, each I run update statement I get the following error: WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. — Reply to this email directly, view it on GitHub <#5>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYTNKE37BYXA2HDVPKTWPGKAFANCNFSM6AAAAAATJUSFZY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[avpco]

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

[nik1tam]

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

[avpco]

queries.zip

[avpco]

dayInfos_ddl.sql.zip

[nik1tam]

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

[avpco]

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

[nik1tam]

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

[nik1tam]

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.

[nik1tam]

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.