Subject: constant crashing

From: jack <[email-masked]>
Date: Sun, 14 Apr 2024 14:24:44 +0000

Hello,
I am trying to load about 1.4 billion records into a postgresql table.
The data is in about 100 individual CSV files which I load individually into the table using the COPY command.
After the data is loaded I perform updates to the data, and this is when I get errors. Some updates work, some crash.
I am using psql and an SQL script file (update.sql) to perform the updating (UPDATE table SET field=UPPER(field), etc.).

The errors are all "Server closed the connection unexpectedly"

The problem is that the errors are never the same and occur at different times and at different steps in the process.
If I update the machine (apt update/upgrade), reboot and try again, the errors occur at different places.
The errors messages and logs are not helpful and I have not been able to determine the cause.

I decided to load the CSV files into temporary tables and then perform the updates on the temporary tables before moving the data to the main table.
This works for most of the tables, but there are always 1 or 2 that crash when I try to update them.
In my last attempt, all tables updated successfully except the last one, with about 100 million records.

I tried changing these settings to 60 minutes and it still crashes.
SET statement_timeout = '60min';
SET idle_in_transaction_session_timeout = '60min';

Then I tried to update the data in blocks of 100,000 records and it crashed 4 times on 4 different blocks.
So I updated the first crashed block down to the a block of 10 records, until it crashed.
Then I updated each of the 10 records individually to identify the record that is problemantic, but then all 10 records updated without crashing!
Pure insanity!

I am using a dedicated machine which is a 10 core i9 with 128 GIG of RAM and 2 x 4 TB NVMEs.
There is only 1 user on this stand-alone machine, me, and the machine is not connected to the internet or a network.
I have reinstalled ubuntu 22.04 server many times, wiping out the discs and starting over.
Last attempts have been with ubuntu 22.04.04 and postgreSQL 16.
But the errors persist.

I have been working on this for just over 1 year now, documenting every step, and I am still unable to get this to work without it crashing somewhere along the way.
I am beginning to wonder if postgreSQL is bi-polar.

Any help would be greatly appreciated.
Thank you

---

From: David Rowley <[email-masked]>
Date: Mon, 15 Apr 2024 13:59:00 +1200

> I am beginning to wonder if postgreSQL is bi-polar.

There are two things I can think of that might make PostgreSQL appear
"bi-polar" here.

1) The plan isn't the same when you update a small number of rows vs a
large number rows.  The planner may prefer an index depending on
what's in your WHERE clause.
2) JIT compilation is less likely with a small number or rows as the
plan will come out cheaper.

A stack trace as mentioned by Tom would be ideal, but if you can't get that...

I'd try:

a) Disable "jit" in postgresql.conf and reload the config file with
SELECT pg_reload_conf();

b) Still crashing? Try dropping all indexes before performing the UPDATE.

c)

Still crashing? Can you make it consistently crash with one
problematic CSV file and UPDATE statement?

Does it still crash if you drop all indexes from the table before
performing the UPDATE?
Can you narrow the UPDATE's scope by forcing a TID Range scan by
starting with a WHERE clause such as:

WHERE ctid >= '(0,0)' AND ctid <= '(4294967294,0)';

then increasly try to home in on the problematic value by "binary
searching" for the problematic record.  You can get the number of
blocks in the table with:

select pg_relation_size('your_table'::regclass) /
current_setting('block_size')::int;

replace the 4294967294 with the value from that and bisect the table
until you home in on the block with the issue.

David