Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, The problem Joe spotted is that a variable name is misspelled in one > place. It should (probably) be external_complete_id, not > _external_complete_id). Oh, copy-paste issue :-) > > better solution proposal? > > I think you can make that clearer by using IS [NOT] DISTINCT FROM: > >

Re: PostgreSQL 14.4 ERROR: out of memory issues

On 2022-08-05 17:47:02 +0200, Aleš Zelený wrote: > the construct surprised me when I saw it in the function the first time, but > it > is correct and works as expected - it allows writing the function as SQL > instead of PLPGSQL while it ensures that for a parameter null value it > evaluates to tr

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, the construct surprised me when I saw it in the function the first time, but it is correct and works as expected - it allows writing the function as SQL instead of PLPGSQL while it ensures that for a parameter null value it evaluates to true instead of filtering the resultset. What is the i

Re: PostgreSQL 14.4 ERROR: out of memory issues

On 8/4/22 08:34, Aleš Zelený wrote: SELECT ... simple join of two tables...       WHERE opd.id_data_provider = _id_data_provider         AND CASE WHEN _external_id IS NULL                  THEN external_id IS NULL                  ELSE external_id = _external_id             END         AND CASE W

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, thanks for the information and the link! Ales čt 4. 8. 2022 v 1:05 odesílatel Tom Lane napsal: > =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > > CREATE OR REPLACE FUNCTION tescase (_id_data_provider integer, > _external_id > > bigint DEFAULT NULL::bigint, _external_complete_id character v

Re: PostgreSQL 14.4 ERROR: out of memory issues

=?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > CREATE OR REPLACE FUNCTION tescase (_id_data_provider integer, _external_id > bigint DEFAULT NULL::bigint, _external_complete_id character varying > DEFAULT NULL::character varying) > RETURNS TABLE(id_gm bigint, id_opp bigint, id_opp_state integer) > LA

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, I did some testing and the result is that on pg12 there are no such problems. Pg13 and Pg 14.3 tests will follow but based on monitoring processed RssAnon memory, I've found a correlation that only processes by a single user are suffering from the memory allocation (it looks to be a memory

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, po 18. 7. 2022 v 21:04 odesílatel Tom Lane napsal: > =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > > postgres=# show shared_buffers ; > > shared_buffers > > > > 10GB > > (1 row) > > Oh! In that case, there is absolutely nothing to see here. > This chunk: > > > 7fd9b0

Re: PostgreSQL 14.4 ERROR: out of memory issues

=?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > postgres=# show shared_buffers ; > shared_buffers > > 10GB > (1 row) Oh! In that case, there is absolutely nothing to see here. This chunk: > 7fd9b0551000 10827040K rw-s- zero (deleted) must be your shared-memory region, and it'

Re: PostgreSQL 14.4 ERROR: out of memory issues

po 18. 7. 2022 v 20:26 odesílatel Aleš Zelený napsal: > > po 18. 7. 2022 v 16:25 odesílatel Tom Lane napsal: > >> =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: >> > after some time, I've found a process consuming over 1GB of memory" >> > -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail >> >

Re: PostgreSQL 14.4 ERROR: out of memory issues

po 18. 7. 2022 v 16:25 odesílatel Tom Lane napsal: > =?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > > after some time, I've found a process consuming over 1GB of memory" > > -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail > > /proc/17048/status:RssAnon: 1053952 kB > > > Here are memory con

Re: PostgreSQL 14.4 ERROR: out of memory issues

=?UTF-8?B?QWxlxaEgWmVsZW7DvQ==?= writes: > after some time, I've found a process consuming over 1GB of memory" > -bash-4.2$ grep RssAnon /proc/*/status | sort -nk2 | tail > /proc/17048/status:RssAnon: 1053952 kB > Here are memory contexts for PID 17048: > TopMemoryContext: 422592 total in 14 blo

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, čt 14. 7. 2022 v 23:31 odesílatel Aleš Zelený napsal: > > > Dne čt 14. 7. 2022 23:11 uživatel Tomas Vondra < > tomas.von...@enterprisedb.com> napsal: > >> >> >> On 7/14/22 21:25, Aleš Zelený wrote: >> > >> > st 13. 7. 2022 v 2:20 odesílatel Michael Paquier > >

Re: PostgreSQL 14.4 ERROR: out of memory issues

Dne čt 14. 7. 2022 23:11 uživatel Tomas Vondra < tomas.von...@enterprisedb.com> napsal: > > > On 7/14/22 21:25, Aleš Zelený wrote: > > > > st 13. 7. 2022 v 2:20 odesílatel Michael Paquier > > napsal: > > > > On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wro

Re: PostgreSQL 14.4 ERROR: out of memory issues

On 7/14/22 21:25, Aleš Zelený wrote: > > st 13. 7. 2022 v 2:20 odesílatel Michael Paquier > napsal: > > On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote: > > So far, it has happened three times (during a single week) from > the 14.3 -> >

Re: PostgreSQL 14.4 ERROR: out of memory issues

čt 14. 7. 2022 v 21:26 odesílatel Aleš Zelený napsal: > > st 13. 7. 2022 v 2:20 odesílatel Michael Paquier > napsal: > >> On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote: >> > So far, it has happened three times (during a single week) from the >> 14.3 -> >> > 14.4 upgrade, before 14.

Re: PostgreSQL 14.4 ERROR: out of memory issues

st 13. 7. 2022 v 2:20 odesílatel Michael Paquier napsal: > On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote: > > So far, it has happened three times (during a single week) from the 14.3 > -> > > 14.4 upgrade, before 14.4 we haven't suffered from such an issue. > > > > Questions: > > 1)

Re: PostgreSQL 14.4 ERROR: out of memory issues

On Mon, Jul 11, 2022 at 10:50:23AM +0200, Aleš Zelený wrote: > So far, it has happened three times (during a single week) from the 14.3 -> > 14.4 upgrade, before 14.4 we haven't suffered from such an issue. > > Questions: > 1) Can we safely downgrade from 14.4 to 14.3 by shutting down the instanc

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hello, po 11. 7. 2022 v 14:47 odesílatel Pavel Stehule napsal: > Hi > > It's looks like memory leak > As we are collecting some data for Postgres processes, I've found processes using Rss Anonymous memory allocations: Some examples: 2022-07-08T14:50:24+0200 Name: postmaster Pid: 31522 RssAnon:

Re: PostgreSQL 14.4 ERROR: out of memory issues

Hi It's looks like memory leak ErrorContext: 8192 total in 1 blocks; 7928 free (5 chunks); 264 used > Grand total: 1456224 bytes in 195 blocks; 378824 free (165 chunks); > 1077400 used > 2022-07-02 14:48:07 CEST [3930]: [3-1] user=,db=,host=,app= ERROR: out of > memory > 2022-07-02 14:48:07 CE

Re: PostgreSQL 14.4 ERROR: out of memory issues

> On Jul 11, 2022, at 2:50 AM, Aleš Zelený wrote: > >  > Hello, > > we are experiencing out-of-memory issues after Postygres upgrade from 14.3 to > 14.4 on CentOS7. > > OS: CenotOS7 > > No OOM killer messages in the syslog. SWAP is disabled. > > -bash-4.2$ sysctl -q vm.overcommit_memory

PostgreSQL 14.4 ERROR: out of memory issues

Hello, we are experiencing out-of-memory issues after Postygres upgrade from 14.3 to 14.4 on CentOS7. OS: CenotOS7 No OOM killer messages in the syslog. SWAP is disabled. -bash-4.2$ sysctl -q vm.overcommit_memory vm.overcommit_memory = 2 [root@sts-uat-pgsql100 ~]# swapon -vs [root@sts-uat-pgsq