Re: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL

You are running afoul of the Linux OOM killer which is what the kernel uses when it experiences memory pressure.

You could exclude PostgreSQL from the OOM killer, you'll need to look up on how to do that for your particular Linux distro.

We've experienced this with queries that consume more RAM and swap than the server has available.

--

Jeremiah

________________________________
From: PegoraroF10 <marcos(at)f10(dot)com(dot)br>
Sent: Friday, February 8, 2019 4:11 PM
To: pgsql-general(at)postgresql(dot)org
Subject: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Well, now we have two queries which stops completelly our postgres server.
That problem occurs on 10.6 and 11.1 versions.
On both server the problem is the same.

Linux logs of old crash are:

Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 callbacks suppressed
Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405791] postgres[10195]: segfault at 24 ip 0000555dc6a71cb0 sp 00007ffc5f91db38 error 4 in postgres[555dc69b4000+6db000]

Postgres log of old crash:

2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,3,,2019-02-01 18:31:37 -02,,0,LOG,00000,"server process (PID 10195) was terminated by signal 11: Segmentation fault","Failed process was running: WITH StatusTrabalhando(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusDigitacao$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusVisita$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) AND Intkey NOT in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status",,,,,,,,""
2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,4,,2019-02-01 18:31:37 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""

Postgres logs of new crash:

2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25625,,2019-01-28 15:19:52 -02,,0,LOG,00000,"server process (PID 10321) was terminated by signal 11: Segmentation fault","Failed process was running: with
StatusTrabalhando(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and
Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
StatusAgendados(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey
in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from
sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and
Intkey not in (select unnest(string_to_array(substring(VarValue from
3),$$,$$)) from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4,
Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador",,,,,,,,""
2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25626,,2019-01-28 15:19:52 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",10138,"74.125.92.65:44342",5c5dcd7d.279a,2,"idle",2019-02-08 16:42:05 -02,107/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",9667,"173.194.101.228:63516",5c5dbdee.25c3,2,"idle",2019-02-08 15:35:42 -02,20/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.650 -02,"postgres","f10db",10096,"74.125.92.68:50186",5c5dcd77.2770,2,"idle",2019-02-08 16:41:59 -02,48/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.651 -02,"postgres","f10db",9696,"74.125.115.163:48542",5c5dbe07.25e0,2,"idle",2019-02-08 15:36:07 -02,49/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9597,"192.168.1.111:57271",5c5dbae2.257d,1,"idle",2019-02-08 15:22:42 -02,4/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver"
2019-02-08 17:21:16.652 -02,,,9567,,5c5db777.255f,1,,2019-02-08 15:08:07 -02,1/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9670,"74.125.45.164:58116",5c5dbdf0.25c6,2,"idle",2019-02-08 15:35:44 -02,23/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,""
2019-02-08 17:21:16.652 -02,"postgres","f10db",9611,"177.92.53.2:61645",5c5dbc25.258b,1,"idle",2019-02-08 15:28:05 -02,5/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver"
2019-02-08 17:21:16.655 -02,,,16321,,5c4f39b8.3fc1,25627,,2019-01-28 15:19:52 -02,,0,LOG,00000,"archiver process (PID 9568) exited with exit code 1",,,,,,,,,""
2019-02-08 17:21:16.705 -02,,,16321,,5c4f39b8.3fc1,25628,,2019-01-28 15:19:52 -02,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,""
2019-02-08 17:21:16.934 -02,,,10329,,5c5dd6ac.2859,1,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was interrupted; last known up at 2019-02-08 17:06:37 -02",,,,,,,,,""
2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,2,,2019-02-08 17:21:16 -02,,0,LOG,00000,"recovered replication state of node 4 to 8F/2913B4C0",,,,,,,,,""
2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,3,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,""
2019-02-08 17:21:46.650 -02,,,10329,,5c5dd6ac.2859,4,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo starts at EF/3DCF0C10",,,,,,,,,""
2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,5,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo done at EF/41B6A618",,,,,,,,,""
2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,6,,2019-02-08 17:21:16 -02,,0,LOG,00000,"last completed transaction was at log time 2019-02-08 17:21:14.151441-02",,,,,,,,,""
2019-02-08 17:22:36.479 -02,,,16321,,5c4f39b8.3fc1,25629,,2019-01-28 15:19:52 -02,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2019-02-08 17:22:36.533 -02,,,10340,,5c5dd6fc.2864,1,,2019-02-08 17:22:36 -02,3/2,0,LOG,00000,"logical replication apply worker for subscription ""sub_google_all"" has started",,,,,,,,,""

Linux Log of new crash, which takes several minutes to stop:

Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres invoked oom-killer: gfp_mask=0x24280ca, order=0, oom_score_adj=0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643123] postgres cpuset=/ mems_allowed=0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643127] CPU: 0 PID: 9399 Comm: postgres Not tainted 4.4.0-138-generic #164-Ubuntu
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643128] Hardware name: Dell Inc. OptiPlex 7010/0773VG, BIOS A25 05/10/2017
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643129] 0000000000000286 02ce01de1fa33dca ffff8800de3cfaf8 ffffffff81404fe3
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643131] ffff8800de3cfcb0 ffff880408d80000 ffff8800de3cfb68 ffffffff8121425e
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643133] 0000000000000015 0000000000000000 ffff8804083f49c0 ffff880408cbd400
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643134] Call Trace:
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643140] [] dump_stack+0x63/0x90
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643144] [] dump_header+0x5a/0x1c5
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643147] [] ? apparmor_capable+0xc4/0x1b0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643149] [] oom_kill_process+0x202/0x3c0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643151] [] out_of_memory+0x219/0x460
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643154] [] __alloc_pages_slowpath.constprop.88+0x943/0xaf0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643156] [] __alloc_pages_nodemask+0x288/0x2a0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643158] [] alloc_pages_vma+0xad/0x250
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643161] [] handle_mm_fault+0x1420/0x1b70
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643164] [] __do_page_fault+0x1a4/0x410
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643165] [] do_page_fault+0x22/0x30
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643169] [] page_fault+0x28/0x30
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643170] Mem-Info:
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] active_anon:3535478 inactive_anon:424526 isolated_anon:32
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] active_file:679 inactive_file:594 isolated_file:0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] unevictable:0 dirty:0 writeback:45 unstable:0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] slab_reclaimable:43397 slab_unreclaimable:8228
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] mapped:529649 shmem:851835 pagetables:26133 bounce:0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] free:33778 free_pcp:35 free_cma:0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643175] Node 0 DMA free:15880kB min:64kB low:80kB high:96kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15984kB managed:15896kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:16kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643179] lowmem_reserve[]: 0 3437 15947 15947 15947
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643181] Node 0 DMA32 free:64520kB min:14552kB low:18188kB high:21828kB active_anon:2842836kB inactive_anon:568288kB active_file:640kB inactive_file:504kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:3640260kB managed:3559444kB mlocked:0kB dirty:0kB writeback:64kB mapped:628652kB shmem:682620kB slab_reclaimable:41856kB slab_unreclaimable:7976kB kernel_stack:736kB pagetables:21384kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:7160 all_unreclaimable? yes
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643185] lowmem_reserve[]: 0 0 12510 12510 12510
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643186] Node 0 Normal free:54712kB min:52964kB low:66204kB high:79444kB active_anon:11299076kB inactive_anon:1129816kB active_file:2076kB inactive_file:1872kB unevictable:0kB isolated(anon):128kB isolated(file):0kB present:13074432kB managed:12810364kB mlocked:0kB dirty:0kB writeback:116kB mapped:1489944kB shmem:2724720kB slab_reclaimable:131732kB slab_unreclaimable:24920kB kernel_stack:2480kB pagetables:83148kB unstable:0kB bounce:0kB free_pcp:140kB local_pcp:20kB free_cma:0kB writeback_tmp:0kB pages_scanned:33152 all_unreclaimable? yes
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643190] lowmem_reserve[]: 0 0 0 0 0
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643192] Node 0 DMA: 0*4kB 1*8kB (U) 0*16kB 0*32kB 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15880kB
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643199] Node 0 DMA32: 2099*4kB (UME) 1396*8kB (UME) 1396*16kB (UME) 469*32kB (UME) 106*64kB (UME) 17*128kB (E) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 65868kB
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643205] Node 0 Normal: 13326*4kB (UEH) 0*8kB 14*16kB (H) 2*32kB (H) 6*64kB (H) 5*128kB (H) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 54616kB
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643211] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643212] 853390 total pagecache pages
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643213] 330 pages in swap cache
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643214] Swap cache stats: add 580602, delete 580272, find 30643927/30685696
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643215] Free swap = 0kB
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643216] Total swap = 999420kB
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643216] 4182669 pages RAM
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643217] 0 pages HighMem/MovableOnly
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643218] 86243 pages reserved
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643218] 0 pages cma reserved
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643219] 0 pages hwpoisoned
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643220] [ pid ] uid tgid total_vm rss nr_ptes nr_pmds swapents oom_score_adj name
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643223] [ 309] 0 309 10866 2186 25 3 49 0 systemd-journal
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643225] [ 356] 0 356 25742 0 18 3 65 0 lvmetad
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643227] [ 377] 0 377 11394 11 24 3 497 -1000 systemd-udevd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643228] [ 634] 100 634 25081 0 20 3 71 0 systemd-timesyn
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643230] [ 735] 104 735 64098 38 28 3 168 0 rsyslogd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643231] [ 745] 0 745 68967 74 38 3 112 0 accounts-daemon
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643233] [ 749] 0 749 7253 19 20 3 50 0 cron
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643234] [ 751] 0 751 7155 37 18 3 43 0 systemd-logind
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643236] [ 752] 106 752 10722 51 26 3 59 -900 dbus-daemon
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643237] [ 818] 0 818 4892 27 15 3 36 0 irqbalance
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643239] [ 877] 0 877 4030 51 11 3 164 0 dhclient
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643240] [ 922] 0 922 3985 0 13 3 38 0 agetty
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643242] [ 926] 0 926 16377 29 36 4 150 -1000 sshd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643244] [16321] 109 16321 1127906 21773 138 5 306 -900 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643245] [16322] 109 16322 41437 162 63 4 321 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643247] [ 5603] 109 5603 11320 0 26 3 207 0 systemd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643248] [ 5605] 109 5605 15315 18 31 3 455 0 (sd-pam)
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643250] [22681] 109 22681 1130447 520116 2165 8 860 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643251] [22682] 109 22682 1128404 516014 2156 8 362 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643253] [22683] 109 22683 1127906 2732 75 5 316 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643254] [22684] 109 22684 1128158 345 79 5 366 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643256] [22685] 109 22685 41967 171 64 5 313 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643257] [22686] 109 22686 47349 3760 73 5 334 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643259] [22687] 109 22687 1128120 426 79 5 381 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643260] [ 2507] 0 2507 23207 46 52 3 185 0 sshd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643262] [ 2528] 109 2528 23207 60 49 3 178 0 sshd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643264] [ 9079] 109 9079 1193045 562876 2298 8 6844 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643265] [ 9081] 109 9081 1128224 1614 98 6 457 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643267] [26521] 109 26521 1128285 5423 111 8 513 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643268] [26522] 109 26522 1128285 4679 135 8 487 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643270] [ 5194] 109 5194 1129913 16312 287 8 828 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643271] [ 7101] 109 7101 1157957 286907 2223 8 324 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643273] [ 8936] 109 8936 1129795 27817 506 8 248 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643274] [ 8979] 109 8979 1128946 16452 376 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643276] [ 9008] 109 9008 1128850 16359 375 8 266 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643277] [ 9101] 109 9101 1129373 12880 244 8 245 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643279] [ 9328] 0 9328 23207 234 50 3 0 0 sshd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643280] [ 9349] 109 9349 23207 240 49 3 0 0 sshd
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643282] [ 9351] 109 9351 5555 362 16 3 0 0 bash
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643283] [ 9362] 109 9362 6649 320 19 3 0 0 htop
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643285] [ 9365] 109 9365 5554 365 16 3 0 0 bash
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643286] [ 9399] 109 9399 4129624 3008886 6087 21 267 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643288] [ 9438] 109 9438 1128844 16432 381 8 229 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643289] [ 9441] 109 9441 1128801 16306 375 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643291] [ 9443] 109 9443 1129663 21678 433 8 247 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643292] [ 9449] 109 9449 1128833 16359 378 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643294] [ 9452] 109 9452 1128945 16437 377 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643295] [ 9484] 109 9484 1128850 16401 378 8 266 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643296] [ 9485] 109 9485 1128827 16380 377 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643298] [ 9486] 109 9486 1128801 16381 378 8 231 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643299] [ 9487] 109 9487 1128841 16342 376 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643301] [ 9488] 109 9488 1128801 16336 375 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643302] [ 9489] 109 9489 1129799 28325 506 8 248 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643304] [ 9490] 109 9490 1129662 21623 432 8 247 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643305] [ 9491] 109 9491 1128785 16300 373 8 267 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643307] [ 9492] 109 9492 1128945 16445 377 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643308] [ 9493] 109 9493 1128833 16388 377 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643309] [ 9494] 109 9494 1128830 16370 375 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643311] [ 9495] 109 9495 1129018 16529 378 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643312] [ 9496] 109 9496 1128832 16385 376 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643314] [ 9498] 109 9498 1128866 16371 376 8 265 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643315] [ 9547] 109 9547 1132672 63812 490 8 283 0 postgres
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643316] Out of memory: Kill process 9399 (postgres) score 693 or sacrifice child
Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643368] Killed process 9399 (postgres) total-vm:16518496kB, anon-rss:11997448kB, file-rss:38096kB
Feb 8 17:21:16 fxReplicationServer kernel: [1371977.845728] postgres[10321]: segfault at 10 ip 00005567a6069752 sp 00007ffed70be970 error 4 in postgres[5567a5e1a000+727000]

PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
PostgreSQL 11.1 (Ubuntu 11.1-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

And this new query which stops the server too but differently because the old one stops immediatelly instead of this, as you can see images.

This query runs for aproximately 5 minutes. See link above with images and logs and you´ll see how memory will grow. Memory use starts with 8gb e grows until use them all. When all memory is in use then it starts to swap. When all swap is allocated then it gets the "out of memory" and stops completelly. You'll see that this query has generate_series, with, recursive and lateral statements. It was bad written because that generate_series generates a enormous quantity of records, probably millions of them. But a wrong query couldn´t stop the entire server, could ?

images and logs are available on: https://drive.google.com/open?id=18zIvkV3ew4aZ1_cxI-EmIPVql7ydvEwi

EXPLAIN ANALYSE
WITH feriados as (
select dia, (sum(repete) > 0) repete from (
select data dia, repete from sys_feriado FeriadoPeriodo where FeriadoPeriodo.repete = 0 and FeriadoPeriodo.tipo in (2, 3) UNION all
select to_date(to_char(current_date, 'yyyy-')|| to_char(data, 'mm-dd'), 'yyyy-mm-dd') dia, repete
from sys_feriado FeriadoAnual where FeriadoAnual.repete = 1 and FeriadoAnual.tipo in (2, 3)
) feriados group by 1 order by 1
), materias (turma_id, materia_id, materia, sequencia, previsto, dataini, datafim, tempoatividade, minutosaula, minutosrestantes) AS (
select 593, 11091, 'AAC - Ética e Segurança Digital', 9, 120, cast('2019/01/30' as data), cast('2019/01/30' as data), 60, 120, 0 union
select 593, 11085, 'AAC - Computação nas Nuvens', 12, 60, cast(null as data), cast(null as data), 60, 120, 60
), aulasporsemana (turma_id, quantidade) as (
select turma_id, count(*) from col_diasaula WHERE turma_id = (select turma_id from materias limit 1) group by 1
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista, aularealizada, tempoatividade, aulasNoDia, dia, minutoaula, minutosassistidos, cargaconteudo, cargarestante) as (
SELECT
materias.turma_id,
materias.sequencia,
materias.materia_id,
materias.materia,
coalesce(realizada.prevista, 1),
realizada.aularealizada,
materias.tempoatividade,
(realizada.minutosassistidos / materias.tempoatividade),
realizada.dia,
materias.minutosaula,
realizada.minutosassistidos,
materias.previsto,
coalesce(materias.previsto - (row_number() OVER AulaDaMateria * realizada.minutosassistidos), materias.previsto) restante
FROM
materias
LEFT JOIN LATERAL (
SELECT
true aularealizada,
tsrange(col_aula.data, (col_aula.data + (col_aula.tempo|| ' minute')::interval)) dia, 0 prevista,
(extract(EPOCH FROM col_aula.tempo) / 60) minutosassistidos
FROM col_aula
WHERE col_aula.materia_id = materias.materia_id AND col_aula.turma_id = materias.turma_id
order by col_aula.data, sequencia
) realizada ON TRUE
WINDOW AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST, materias.sequencia, materias.materia_id),
AulaDia as (PARTITION BY materias.materia_id, realizada.dia)
ORDER BY turma_id, datafim NULLS LAST, dataini NULLS LAST, sequencia, materia_id
)
SELECT
*
FROM (
with recursive aulas as (
SELECT
turma_id,
aularealizada,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo irregular,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) assistido_ate_agora,
CASE
WHEN prevista = 0 AND NOT (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) OVER aulas_realizar, 0) > cargaconteudo) THEN
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) OVER aulas_realizar + aulasNoDia)
WHEN prevista = 1 THEN
(cargaconteudo / tempoatividade)
ELSE 0
END aulas,
case
when prevista = 0 and not (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo) then
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) over aulas_realizar + aulasNoDia)
else 1
END proxima,
prevista,
upper(dia) ultimadata,
conteudo_id,
conteudo,
cargaconteudo,
cargarestante,
tempoatividade,
dia,
minutosassistidos,
minutoaula,
sequencia
FROM assistidas
JOIN aulasporsemana USING (turma_id)
WINDOW aulas_realizar AS (PARTITION BY conteudo_id)
UNION
select
turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas, proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo, datas.cargarestante, tempoatividade, dia, datas.minutosassistidos, minutoaula, sequencia
from aulas c
JOIN LATERAL (
select
Format('%s week', coalesce(c.aulas, 0)) semanas,
false aularealizada,
c.conteudo_id,
tsrange(generate_series, generate_series + (minutoaula|| ' minute')::interval) diacalculado,
cargarestante - (row_number() OVER () * (extract(epoch from col_diasaula.tempoaula) / 60)) cargarestante,
(case c.prevista when 1 then row_number() OVER () else 1 end * (extract(epoch from col_diasaula.tempoaula) / c.tempoatividade)) minutosassistidos
from
generate_series(c.ultimadata - interval '1 day', (c.ultimadata + Format('%s week', coalesce(proxima, 0))::INTERVAL), '1 day')
join col_diasaula on col_diasaula.dia = (extract(dow from generate_series) +1) and col_diasaula.turma_id = c.turma_id
) datas on TRUE
where datas.conteudo_id = c.conteudo_id and c.aulas is not null and coalesce(c.proxima, -1) >= 0
) select * from aulas
) valores;

________________________________
Sent from the PostgreSQL - general mailing list archive<http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at Nabble.com.
CONFIDENTIALITY NOTICE: The information contained in this email (and any attachments) is privileged and confidential and protected from disclosure. If you are not the intended recipient of this email or the attachments, be aware that any disclosure, copying, distribution or use of this email or any attachment is strictly prohibited and you should not read the message or read or open any attachment. If you have received this email by mistake, please immediately notify the sender and delete it permanently from your system. Agri Stats, Inc. and its subsidiaries will not be held liable to any person or entity resulting from the unintended or unauthorized use of any information contained in this email.