Can't insert more than 657 records into a table

[satob]

What I want to do

I want to insert more than 1000 records into a table to see the performance of Postgres WASM.

What I did

I have a file to load data to a table like https://gist.github.com/satob/421f19ed438a9abe56b7139022df44d2 .
I have uploaded the file with [Transfer Files] and run \i /mnt/test.sql on the psql console.

What I expected

All INSERT INTO statements are executed and the employee table has 1,000 records.

What I got

psql returns an error with the following message:

postgres=# \i /mnt/test.sql
CREATE TABLE
INSERT 0 1
  (snip)
INSERT 0 1
psql:/mnt/test.sql:665: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/mnt/test.sql:665: fatal: connection to server was lost
#

The employee table only has 657 records.

postgres=# select count(*) from employee;
 count 
-------
   657
(1 row)

After the error, I couldn't insert any records into the table.

# psql -U postgres
psql (14.5)
Type "help" for help.

postgres=# insert into employee values (998, 'Guy Zimmerman');
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: Failed.
!?>

Question

How can I insert more than 1000 records into a table on Postgres WASM?

[burggraf]

Yes, I have inserted hundreds of thousands of records. You might just try batching them using the technique I wrote about here: speeding-up-bulk-loading-in-postgresql

Basically, use: insert into table (f1, f2) values ('a','b'),('c','d'),('e','f'); etc. It's way more efficient.

[satob]

@burggraf Thank you for your comment. I have successfully inserted the records with bulk insert.

But is there any way that doesn't change SQL itself?
A SQL file provided in a learning course (Advanced SQL for Query Tuning and Performance Optimization) is written without the bulk insert and causes the same problem. I want to use Postgres WASM for the test environment in this course and let trainees use the SQL file without any changes, so I'm looking for a way to work around the error with configurations of PostgreSQL or something.

[burggraf]

There's no reason why large sql inserts would not work other than resource limitations on your browser. We're running a 32-bit Linux vm in the browser and then loading PostgreSQL inside of that. You might try a larger memory setting for Postgres WASM and see if that helps a bit.

[satob]

I have changed the memory setting from 128mb to 1024mb, but the problem wasn't solved.

128mb

postgres=# \! free
              total        used        free      shared  buff/cache   available
Mem:         109400        9820       79152        6416       20428       87312
Swap:             0           0           0
postgres=# \i /mnt/test2.sql
CREATE TABLE
INSERT 0 1
 ...
psql:/mnt/test2.sql:663: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/mnt/test2.sql:663: fatal: connection to server was lost

256mb

postgres=# \! free
              total        used        free      shared  buff/cache   available
Mem:         239196        9976      208768        6416       20452      213936
Swap:             0           0           0
postgres=# \i /mnt/test2.sql
CREATE TABLE
INSERT 0 1
 ...
psql:/mnt/test2.sql:663: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/mnt/test2.sql:663: fatal: connection to server was lost

512mb

postgres=# \! free
              total        used        free      shared  buff/cache   available
Mem:         499068       11620      468428        6420       19020      468772
Swap:             0           0           0
postgres=# \i /mnt/test2.sql
CREATE TABLE
INSERT 0 1
 ...
psql:/mnt/test2.sql:665: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/mnt/test2.sql:665: fatal: connection to server was lost

1024mb

postgres=# \! free
              total        used        free      shared  buff/cache   available
Mem:        1018800       11848      986504        6416       20448      978960
Swap:             0           0           0
postgres=# \i /mnt/test2.sql
CREATE TABLE
INSERT 0 1
 ...
psql:/mnt/test2.sql:661: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
psql:/mnt/test2.sql:661: fatal: connection to server was lost

[satob]

I have set log_error_verbosity = verbose in /var/lib/pgsql/postgresql.conf and run the same SQL. In psql, I have set \set VERBOSITY verbose.

When the error occured, the following messages wrote to /var/lib/pgsql/logfile:

2022-09-27 12:12:29.638 UTC [138] LOG:  starting PostgreSQL 14.5 on i686-buildroot-linux-musl, compiled by i686-buildroot-linux-musl-gcc.br_real (Buildroot 2022.08) 12.1.0, 32-bit
2022-09-27 12:12:29.643 UTC [138] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-09-27 12:12:29.645 UTC [138] LOG:  listening on IPv6 address "::", port 5432
2022-09-27 12:12:29.653 UTC [138] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-09-27 12:12:29.807 UTC [139] LOG:  database system was shut down at 2022-09-03 15:57:06 UTC
2022-09-27 12:12:30.059 UTC [138] LOG:  database system is ready to accept connections
2022-09-27 12:14:06.008 UTC [138] LOG:  received SIGHUP, reloading configuration files
2022-09-27 12:14:06.168 UTC [138] LOG:  00000: parameter "log_error_verbosity" changed to "verbose"
2022-09-27 12:14:06.168 UTC [138] LOCATION:  ProcessConfigFileInternal, guc-file.l:454
2022-09-27 12:14:13.917 UTC [138] LOG:  00000: received SIGHUP, reloading configuration files
2022-09-27 12:14:13.917 UTC [138] LOCATION:  SIGHUP_handler, postmaster.c:2709
2022-09-27 12:15:00.100 UTC [138] LOG:  00000: server process (PID 167) was terminated by signal 11: Segmentation fault
2022-09-27 12:15:00.100 UTC [138] DETAIL:  Failed process was running: insert into staff values (103,'Reed','[email protected]','Male','Automotive','7/19/2003',126001,'Structural Engineer',6);
2022-09-27 12:15:00.100 UTC [138] LOCATION:  LogChildExit, postmaster.c:3751
2022-09-27 12:15:00.106 UTC [138] LOG:  00000: terminating any other active server processes
2022-09-27 12:15:00.106 UTC [138] LOCATION:  HandleChildCrash, postmaster.c:3481
2022-09-27 12:15:00.214 UTC [138] LOG:  00000: all server processes terminated; reinitializing
2022-09-27 12:15:00.214 UTC [138] LOCATION:  PostmasterStateMachine, postmaster.c:4037
2022-09-27 12:15:00.817 UTC [320] LOG:  00000: database system was interrupted; last known up at 2022-09-27 12:12:29 UTC
2022-09-27 12:15:00.817 UTC [320] LOCATION:  StartupXLOG, xlog.c:6574
2022-09-27 12:15:05.887 UTC [320] LOG:  00000: database system was not properly shut down; automatic recovery in progress
2022-09-27 12:15:05.887 UTC [320] LOCATION:  StartupXLOG, xlog.c:7098
2022-09-27 12:15:05.976 UTC [320] LOG:  00000: redo starts at 0/163E034
2022-09-27 12:15:05.976 UTC [320] LOCATION:  StartupXLOG, xlog.c:7376
2022-09-27 12:15:06.175 UTC [320] LOG:  00000: invalid record length at 0/16703BC: wanted 24, got 0
2022-09-27 12:15:06.175 UTC [320] LOCATION:  ReadRecord, xlog.c:4447
2022-09-27 12:15:06.177 UTC [320] LOG:  00000: redo done at 0/1670398 system usage: CPU: user: 0.18 s, system: 0.00 s, elapsed: 0.20 s
2022-09-27 12:15:06.177 UTC [320] LOCATION:  StartupXLOG, xlog.c:7639
2022-09-27 12:15:06.886 UTC [138] LOG:  00000: database system is ready to accept connections
2022-09-27 12:15:06.886 UTC [138] LOCATION:  reaper, postmaster.c:3066