On Fri, Oct 13, 2023 at 10:53 AM Jean-Christophe Boggio <postgresql@xxxxxxxxxxxxxx> wrote:
Le 13/10/2023 à 15:12, MichaelDBA a écrit :
Turn off the OOM killer so you would get a nicer me ssage in PG log file instead of crashing the PG service.
Did this and now the process dies much quicker (without seemingly consume all the available memory)... Also I can not launch thunderbird anymore with this setting...
Yes, turning off overcommit doesn't play with graphical environments, in my experience. But a production database probably shouldn't be running on a system like that. On non-prod systems, you can either turn it off temporarily, or you could try to catch the problem before it becomes fatal and get the log with pg_log_backend_memory_contexts.
Anyway, I also reduced work_mem to 128Mb
You can find the corresponding logs attached.
We can see what the problem is (over 137,000 concurrent tuple sorts), but we can't tell what is ultimately causing it. You will need to dig into, or disclose, the contents of the procedure.
Cheers,
Jeff
