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 https://github.com/postgrespro/postgres/issues/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.