Subject: Re: constant crashing

From: Adrian Klaver <[email-masked]>
Date: Sun, 14 Apr 2024 10:05:21 -0700

On 4/14/24 09:20, jack wrote:
> The full error reads:
> server closed the connection expectantly
> This probably means the server terminated abnormally
> before or while processing the request.
> error: connection to server was lost

Look at the OS system log.

> 
> PostgreSQL 16.2
> 
> I also believe it is a resource issue which can be rectified with a 
> setting, but which setting?
> If you were updating 100 million records what settings would you adjust?
> 
> Here are the updates I am performing on the 100 million records:
> UPDATE table SET category_modified = UPPER(category);
> UPDATE table SET category_modified = 
> REGEXP_REPLACE(REPLACE(REPLACE(category_modified, '''','-'), '`', '-'), 
> '\s{2,}', ' ', 'g') WHERE  AND LENGTH(category_modified)>1 AND 
> POSITION('--' IN category_modified)>0;
> UPDATE table SET category_modified = REPLACE(category_modified,' ','-');
> UPDATE table SET category_modified = CASE WHEN category_modified IS NOT 
> NULL THEN regexp_replace(category_modified, '[^a-zA-Z]$', '') ELSE NULL END;
> UPDATE table SET category_modified = regexp_replace(category_modified, 
> '-{2,}', '-', 'g');
> UPDATE table SET category_modified = SUBSTRING(category_modified FROM 1 
> FOR LENGTH(category_modified) - 1) WHERE LENGTH(category_modified)>1 AND 
> category_modified LIKE '%-';

Is the above all being done in one script/transaction?

Again what are the table definitions for the tables being copied into 
and/or modified?

-- 
Adrian Klaver
[email-masked]

---

From: Tom Lane <[email-masked]>
Date: Sun, 14 Apr 2024 13:13:38 -0400

> Look at the OS system log.

Really the most detail would be in postgres' own log.  I'd only
expect the system log to contain relevant info if the problem
turns out to be an overeager OOM killer --- but if that's what's
happening, we should be able to diagnose from the postmaster's
log too (because it'd show up as the backend dying from SIGKILL).

			regards, tom lane