*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 suppressedFeb 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:
withStatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo
= $$StatusDigitacao$$ andIntkey 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
Intkeyin (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
fromsys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and
Intkey not in (select unnest(string_to_array(substring(VarValue
from3),$$,$$)) 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=0Feb 8
15:06:40 fxReplicationServer kernel: [1363901.643123] postgres cpuset=/
mems_allowed=0Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643127]
CPU: 0 PID: 9399 Comm: postgres Not tainted 4.4.0-138-generic #164-UbuntuFeb
8 15:06:40 fxReplicationServer kernel: [1363901.643128] Hardware name: Dell
Inc. OptiPlex 7010/0773VG, BIOS A25 05/10/2017Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643129] 0000000000000286
02ce01de1fa33dca ffff8800de3cfaf8 ffffffff81404fe3Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643131] ffff8800de3cfcb0
ffff880408d80000 ffff8800de3cfb68 ffffffff8121425eFeb 8 15:06:40
fxReplicationServer kernel: [1363901.643133] 0000000000000015
0000000000000000 ffff8804083f49c0 ffff880408cbd400Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643134] Call Trace:Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643140] [] dump_stack+0x63/0x90Feb 8
15:06:40 fxReplicationServer kernel: [1363901.643144] []
dump_header+0x5a/0x1c5Feb 8 15:06:40 fxReplicationServer kernel:
[1363901.643147] [] ? apparmor_capable+0xc4/0x1b0Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643149] []
oom_kill_process+0x202/0x3c0Feb 8 15:06:40 fxReplicationServer kernel:
[1363901.643151] [] out_of_memory+0x219/0x460Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643154] []
__alloc_pages_slowpath.constprop.88+0x943/0xaf0Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643156] []
__alloc_pages_nodemask+0x288/0x2a0Feb 8 15:06:40 fxReplicationServer
kernel: [1363901.643158] [] alloc_pages_vma+0xad/0x250Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643161] []
handle_mm_fault+0x1420/0x1b70Feb 8 15:06:40 fxReplicationServer kernel:
[1363901.643164] [] __do_page_fault+0x1a4/0x410Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643165] [] do_page_fault+0x22/0x30Feb
8 15:06:40 fxReplicationServer kernel: [1363901.643169] []
page_fault+0x28/0x30Feb 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:32Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173]
active_file:679 inactive_file:594 isolated_file:0Feb 8 15:06:40
fxReplicationServer kernel: [1363901.643173] unevictable:0 dirty:0
writeback:45 unstable:0Feb 8 15:06:40 fxReplicationServer kernel:
[1363901.643173] slab_reclaimable:43397 slab_unreclaimable:8228Feb 8
15:06:40 fxReplicationServer kernel: [1363901.643173] mapped:529649
shmem:851835 pagetables:26133 bounce:0Feb 8 15:06:40 fxReplicationServer
kernel: [1363901.643173] free:33778 free_pcp:35 free_cma:0Feb 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? yesFeb 8 15:06:40 fxReplicationServer
kernel: [1363901.643179] lowmem_reserve[]: 0 3437 15947 15947 15947Feb 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? yesFeb
8 15:06:40 fxReplicationServer kernel: [1363901.643185] lowmem_reserve[]: 0
0 12510 12510 12510Feb 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? yesFeb
8 15:06:40 fxReplicationServer kernel: [1363901.643190] lowmem_reserve[]: 0
0 0 0 0Feb 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) = 15880kBFeb 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 = 65868kBFeb 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 = 54616kBFeb 8 15:06:40 fxReplicationServer kernel:
[1363901.643211] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0
hugepages_size=2048kBFeb 8 15:06:41 fxReplicationServer kernel:
[1363901.643212] 853390 total pagecache pagesFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643213] 330 pages in swap cacheFeb 8
15:06:41 fxReplicationServer kernel: [1363901.643214] Swap cache stats: add
580602, delete 580272, find 30643927/30685696Feb 8 15:06:41
fxReplicationServer kernel: [1363901.643215] Free swap = 0kBFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643216] Total swap = 999420kBFeb 8
15:06:41 fxReplicationServer kernel: [1363901.643216] 4182669 pages RAMFeb
8 15:06:41 fxReplicationServer kernel: [1363901.643217] 0 pages
HighMem/MovableOnlyFeb 8 15:06:41 fxReplicationServer kernel:
[1363901.643218] 86243 pages reservedFeb 8 15:06:41 fxReplicationServer
kernel: [1363901.643218] 0 pages cma reservedFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643219] 0 pages hwpoisonedFeb 8
15:06:41 fxReplicationServer kernel: [1363901.643220] [ pid ] uid tgid
total_vm rss nr_ptes nr_pmds swapents oom_score_adj nameFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643223] [ 309] 0 309 10866
2186 25 3 49 0 systemd-journalFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643225] [ 356] 0 356 25742
0 18 3 65 0 lvmetadFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643227] [ 377] 0 377 11394
11 24 3 497 -1000 systemd-udevdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643228] [ 634] 100 634 25081
0 20 3 71 0 systemd-timesynFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643230] [ 735] 104 735 64098
38 28 3 168 0 rsyslogdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643231] [ 745] 0 745 68967
74 38 3 112 0 accounts-daemonFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643233] [ 749] 0 749 7253
19 20 3 50 0 cronFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643234] [ 751] 0 751 7155
37 18 3 43 0 systemd-logindFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643236] [ 752] 106 752 10722
51 26 3 59 -900 dbus-daemonFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643237] [ 818] 0 818 4892
27 15 3 36 0 irqbalanceFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643239] [ 877] 0 877 4030
51 11 3 164 0 dhclientFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643240] [ 922] 0 922 3985
0 13 3 38 0 agettyFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643242] [ 926] 0 926 16377
29 36 4 150 -1000 sshdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643244] [16321] 109 16321 1127906
21773 138 5 306 -900 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643245] [16322] 109 16322 41437
162 63 4 321 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643247] [ 5603] 109 5603 11320
0 26 3 207 0 systemdFeb 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 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643251] [22682] 109 22682 1128404
516014 2156 8 362 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643253] [22683] 109 22683 1127906
2732 75 5 316 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643254] [22684] 109 22684 1128158
345 79 5 366 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643256] [22685] 109 22685 41967
171 64 5 313 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643257] [22686] 109 22686 47349
3760 73 5 334 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643259] [22687] 109 22687 1128120
426 79 5 381 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643260] [ 2507] 0 2507 23207
46 52 3 185 0 sshdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643262] [ 2528] 109 2528 23207
60 49 3 178 0 sshdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643264] [ 9079] 109 9079 1193045
562876 2298 8 6844 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643265] [ 9081] 109 9081 1128224
1614 98 6 457 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643267] [26521] 109 26521 1128285
5423 111 8 513 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643268] [26522] 109 26522 1128285
4679 135 8 487 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643270] [ 5194] 109 5194 1129913
16312 287 8 828 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643271] [ 7101] 109 7101 1157957
286907 2223 8 324 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643273] [ 8936] 109 8936 1129795
27817 506 8 248 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643274] [ 8979] 109 8979 1128946
16452 376 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643276] [ 9008] 109 9008 1128850
16359 375 8 266 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643277] [ 9101] 109 9101 1129373
12880 244 8 245 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643279] [ 9328] 0 9328 23207
234 50 3 0 0 sshdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643280] [ 9349] 109 9349 23207
240 49 3 0 0 sshdFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643282] [ 9351] 109 9351 5555
362 16 3 0 0 bashFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643283] [ 9362] 109 9362 6649
320 19 3 0 0 htopFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643285] [ 9365] 109 9365 5554
365 16 3 0 0 bashFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643286] [ 9399] 109 9399 4129624
3008886 6087 21 267 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643288] [ 9438] 109 9438 1128844
16432 381 8 229 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643289] [ 9441] 109 9441 1128801
16306 375 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643291] [ 9443] 109 9443 1129663
21678 433 8 247 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643292] [ 9449] 109 9449 1128833
16359 378 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643294] [ 9452] 109 9452 1128945
16437 377 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643295] [ 9484] 109 9484 1128850
16401 378 8 266 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643296] [ 9485] 109 9485 1128827
16380 377 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643298] [ 9486] 109 9486 1128801
16381 378 8 231 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643299] [ 9487] 109 9487 1128841
16342 376 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643301] [ 9488] 109 9488 1128801
16336 375 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643302] [ 9489] 109 9489 1129799
28325 506 8 248 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643304] [ 9490] 109 9490 1129662
21623 432 8 247 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643305] [ 9491] 109 9491 1128785
16300 373 8 267 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643307] [ 9492] 109 9492 1128945
16445 377 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643308] [ 9493] 109 9493 1128833
16388 377 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643309] [ 9494] 109 9494 1128830
16370 375 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643311] [ 9495] 109 9495 1129018
16529 378 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643312] [ 9496] 109 9496 1128832
16385 376 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643314] [ 9498] 109 9498 1128866
16371 376 8 265 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643315] [ 9547] 109 9547 1132672
63812 490 8 283 0 postgresFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643316] Out of memory: Kill process
9399 (postgres) score 693 or sacrifice childFeb 8 15:06:41
fxReplicationServer kernel: [1363901.643368] Killed process 9399 (postgres)
total-vm:16518496kB, anon-rss:11997448kB, file-rss:38096kBFeb 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-bitPostgreSQL 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 ANALYSEWITH 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) restanteFROM 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: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
