Ignore my thread, I guess there might be a bug given it segfaulted.
On Mon, Apr 15, 2024, 12:48 AM Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:
>
>
> On Sun, 14 Apr 2024 at 21:50, jack <jack...@a7q.com> 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
>>
>> 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 '%-';
>>
>>
> independent of best practices, i just want to check if there is a leak.
> I created a sample table with text data and ran updates like yours and I
> could not see mem growth, but I have a small vm and ofc your
> category_modified field might be more complex than simple text fields for
> 30-40 chars.
>
> can you grab the pid of your psql backend and (if you have pidstat
> installed) monitor resource usage for that pid
>
> postgres@pg:~/udemy/16$ psql
> psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
> Type "help" for help.
>
> postgres=# select pg_backend_pid();
> pg_backend_pid
> ----------------
> 1214
> (1 row)
>
> # pidstat 2 100 -rud -h -p 1214
> (get all stats for that pid) that might help to figure out if there is a
> leak or the server has other things competing for memory and your updates
> were picked by the killer.
>
> Linux 5.15.0-101-generic (pg) 04/15/24 _x86_64_ (1 CPU)
>
> # Time UID PID %usr %system %guest %wait %CPU CPU
> minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s
> iodelay Command
> 00:40:25 113 1214 0.00 0.00 0.00 0.00 0.00 0
> 0.00 0.00 354112 220940 24.18 0.00 0.00 0.00
> 0 postgres
>
> # Time UID PID %usr %system %guest %wait %CPU CPU
> minflt/s majflt/s VSZ RSS %MEM kB_rd/s kB_wr/s kB_ccwr/s
> iodelay Command
> 00:40:27 113 1214 0.00 0.00 0.00 0.00 0.00 0
> 0.00 0.00 354112 220940 24.18 0.00 0.00 0.00
> 0 postgres
> ....
>
> ofc, if there is a genuine leak , then there might be more digging needed
> Finding
> memory leaks in Postgres C code (enterprisedb.com)
> <https://www.enterprisedb.com/blog/finding-memory-leaks-postgres-c-code>
> just kill the process requesting more mem than available Memory context:
> how PostgreSQL allocates memory - CYBERTEC (cybertec-postgresql.com)
> <https://www.cybertec-postgresql.com/en/memory-context-for-postgresql-memory-management/>
>
>
> --
> Thanks,
> Vijay
> LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>
>
