Server goes to Recovery Mode when run a SQL

I´m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD)
and DB has 70GB

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2/3/19 7:53 AM, PegoraroF10 wrote:

First the below is unreadable, probably because it is being sent through
Nabble. Please subscribe to list directly and try again. In meantime
could not tell from below, but what relevant information is in the logs
before and after?

I have a complex query which puts my server in recovery mode every time
I run it. I don´t need to say that recovery mode is a situation you
don´t want your server goes to. *If I´m using some subselects I´ll get
that situation* 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, lkTelemarketing,
lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
Midia, Prioridade, DataHoraAlteracao, Fluxo) ... *but if I run those
subselects previously, get those values and put the results to my
statement, then it works. * with
StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$),
StatusDigitacaoReceptivoAgendados(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$),
StatusDigitacaoReceptivoTrabalhando(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) select
(select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select
IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from
StatusDigitacaoReceptivoTrabalhando); *Then i get these results (8, 14
and 17) and replace those subselects with these values and run, now it
runs fine.* with StatusTrabalhando(Intkey) as (select Intkey from
sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (*8*)),
StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusVisita$$ and Intkey in (*14*) and Intkey not in (*17*)),
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, lkTelemarketing,
lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
Midia, Prioridade, DataHoraAlteracao, Fluxo) *My original query, which
worked for some days and then went to recovery mode was ...* 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, lkTelemarketing,
lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
Midia, Prioridade, DataHoraAlteracao, Fluxo); as (select D.Digitacao_ID,
D.Fonte_ID, D.Curso_ID, Digitador_ID, D.Pesquisador_ID,
D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, D.Telefone,
D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro, D.Email,
D.Idade, D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio,
D.Serie, D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia,
D.Status, Status.Descricao lkStatus, Fonte.Fonte lkFonte,
DigitadorPessoa.Apelido lkDigitador, Pesquisador.Apelido lkPesquisador,
Telemarketing.Login lkTelemarketing, Escolaridade.Descricao
lkEscolaridade, D.Endereco, D.Bairro, D.Cidade, D.Estado, D.CEP,
D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp, PessoaNome.Nome
VisitaNome, VisitaStatus.Descricao VisitaStatus, D.Comercial,
Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case when
Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null
then $$Atribuido$$ when D.Status in (select IntKey from
StatusTrabalhando) then $$Trabalhando$$ when VisitaNome.Status in
(select IntKey from StatusAgendados) then $$Agendados$$ else
$$Descartados$$ end Fluxo from cad_Digitacao D inner join cad_Fonte
Fonte on Fonte.Fonte_ID = D.Fonte_ID inner join sys_lookup TipoFonte on
TipoFonte.intkey = Fonte.TipoFonte left join sys_Account Telemarketing
on Telemarketing.Pessoa_ID = D.Telemarketing_ID left join sys_Account
Digitador on Digitador.Account_ID = D.Digitador_ID left join cad_Pessoa
DigitadorPessoa on Digitador.Pessoa_ID = DigitadorPessoa.Pessoa_ID left
join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID = D.Pesquisador_ID
left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID left join
sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey =
Fonte.Midia left join sys_Lookup Escolaridade on Escolaridade.Grupo =
$$Escolaridade$$ and Escolaridade.IntKey = D.Escolaridade left join
sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and
Status.IntKey = D.Status left join cad_visita VisitaNome on
VisitaNome.Digitacao_ID = D.Digitacao_ID left join sys_Lookup
VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$ and
VisitaStatus.IntKey = VisitaNome.Status left join cad_Pessoa PessoaNome
on PessoaNome.Pessoa_ID = VisitaNome.Pessoa_ID where TipoFonte.descricao
= $$Campanhas$$ and Fonte.Ativo = 1 ), NaoAtribuidos as (select * from
Todos where Fluxo = $$Não Atribuido$$), Atribuidos as (select * from
Todos where Fluxo = $$Atribuido$$), Trabalhando as (select * from todos
where Fluxo = $$Trabalhando$$), Agendados as (select * from todos where
Fluxo = $$Agendados$$), Descartados as (select * from todos where Fluxo
= $$Descartados$$) select * from (select 1 as Ordem, Digitacao_ID,
cast(Null as I32) Fonte_ID, cast(Null as I32) Curso_ID, cast(Null as
I32) Digitador_ID, cast(Null as I32) Pesquisador_ID, Telemarketing_ID,
Nome, cast(Null as T100) Curso, Cast(Null as Telefone) Telefone,
Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null
as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as
T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null
as T50) Extra1, Cast(Null as T50) Extra2, Cast(Null as T50) Extra3,
Cast(Null as T50) Extra4, Cast(Null as T50) Colegio, Cast(Null as T50)
Serie, Cast(Null as T50) Turma, Cast(Null as C1) Turno, Cast(Null as
I16) AnoLetivo, Cast(Null as T50) Pai, Cast(Null as T50) Mae, Sequencia,
Cast(Null as I16) Status, lkStatus, lkFonte, Cast(Null as T50)
lkDigitador, Cast(Null as T50) lkPesquisador, lkTelemarketing, Cast(Null
as T50) lkEscolaridade, Cast(Null as T50) Endereco, Cast(Null as T30)
Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado) Estado, Cast(Null
as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as DataHora)
Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16) WhatsApp,
Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null
as Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem,
Prioridade desc, Cadastro) NA union all select 2 as Ordem, 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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Fluxo from
Atribuidos union all select 3 as Ordem, 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, lkTelemarketing, lkEscolaridade, Endereco,
Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union
all select 4 as Ordem, 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,
lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP,
Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus,
Comercial, Midia, Fluxo from Agendados union all select 5 as Ordem,
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, lkTelemarketing,
lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
Midia, Fluxo from Descartados order by Ordem; *And the explain of that
query was ...* QUERY PLAN Sort (cost=2640.68..2640.84 rows=65
width=1128) Sort Key: "*SELECT* 1".ordem CTE statustrabalhando -> Hash
Semi Join (cost=15.44..30.40 rows=7 width=4) Hash Cond:
((sys_lookup.intkey)::text =
(unnest(string_to_array("substring"((sys_var.varvalue)::text, 3),
','::text)))) -> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21
rows=14 width=4) Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Bitmap Index Scan on idxsyslookupgrupointkey (cost=0.00..4.38 rows=14
width=0) Index Cond: ((grupo)::text = 'StatusDigitacao'::text) -> Hash
(cost=9.81..9.81 rows=100 width=32) -> ProjectSet (cost=0.28..8.81
rows=100 width=32) -> Index Scan using idxsysvarname on sys_var
(cost=0.28..8.29 rows=1 width=9) Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoTrabalhando'::text) CTE
statusagendados -> Hash Semi Join (cost=24.51..39.66 rows=4 width=4)
Hash Cond: ((sys_lookup_1.intkey)::text =
(unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3),
','::text)))) -> Bitmap Heap Scan on sys_lookup sys_lookup_1
(cost=13.46..28.53 rows=8 width=4) Recheck Cond: ((grupo)::text =
'StatusVisita'::text) Filter: (NOT (hashed SubPlan 2)) -> Bitmap Index
Scan on idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0) Index
Cond: ((grupo)::text = 'StatusVisita'::text) SubPlan 2 -> ProjectSet
(cost=0.28..8.81 rows=100 width=32) -> Index Scan using idxsysvarname on
sys_var sys_var_1 (cost=0.28..8.29 rows=1 width=9) Index Cond:
((name)::text =
'/Company/StatusDigitacaoReceptivoDescartarAgendados'::text) -> Hash
(cost=9.81..9.81 rows=100 width=32) -> ProjectSet (cost=0.28..8.81
rows=100 width=32) -> Index Scan using idxsysvarname on sys_var
sys_var_2 (cost=0.28..8.29 rows=1 width=9) Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoAgendados'::text) CTE todos -> Merge
Right Join (cost=2152.17..2282.99 rows=2504 width=828) Merge Cond:
((visitanome.digitacao_id)::integer = (d.digitacao_id)::integer) ->
Nested Loop Left Join (cost=4.99..56111.59 rows=77463 width=46) ->
Nested Loop Left Join (cost=4.69..22162.21 rows=77463 width=27) Join
Filter: ((visitastatus.intkey)::integer = (visitanome.status)::smallint)
-> Index Scan using idxvisitadigitacao on cad_visita visitanome
(cost=0.29..3551.70 rows=77463 width=10) -> Materialize
(cost=4.40..19.43 rows=16 width=21) -> Bitmap Heap Scan on sys_lookup
visitastatus (cost=4.40..19.35 rows=16 width=21) Recheck Cond:
((grupo)::text = 'StatusVisita'::text) -> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0) Index Cond:
((grupo)::text = 'StatusVisita'::text) -> Index Scan using pkpessoa on
cad_pessoa pessoanome (cost=0.29..0.44 rows=1 width=27) Index Cond:
((pessoa_id)::integer = (visitanome.pessoa_id)::integer) -> Sort
(cost=2146.94..2148.64 rows=682 width=756) Sort Key: d.digitacao_id ->
Hash Left Join (cost=1301.36..2114.84 rows=682 width=756) Hash Cond:
((d.status)::smallint = (status.intkey)::integer) -> Hash Left Join
(cost=1281.98..2081.79 rows=682 width=739) Hash Cond:
((d.escolaridade)::smallint = (escolaridade.intkey)::integer) -> Hash
Left Join (cost=1263.51..2053.08 rows=682 width=722) Hash Cond:
((fonte.midia)::smallint = (midia.intkey)::integer) -> Hash Left Join
(cost=1233.51..1959.83 rows=682 width=705) Hash Cond:
((d.curso_id)::integer = (curso.curso_id)::integer) -> Nested Loop Left
Join (cost=1219.91..1944.43 rows=682 width=673) -> Hash Left Join
(cost=1219.62..1411.81 rows=682 width=650) Hash Cond:
((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer) ->
Hash Left Join (cost=1213.06..1401.72 rows=682 width=639) Hash Cond:
((d.digitador_id)::integer = (digitador.account_id)::integer) -> Nested
Loop (cost=29.52..215.51 rows=682 width=616) -> Hash Join
(cost=29.24..54.61 rows=7 width=17) Hash Cond:
((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer) -> Seq Scan
on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19) Filter:
((ativo)::smallint = 1) -> Hash (cost=29.21..29.21 rows=2 width=4) ->
Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4)
Filter: ((descricao)::text = 'Campanhas'::text) -> Index Scan using
idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227
width=603) Index Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer)
-> Hash (cost=1181.56..1181.56 rows=158 width=27) -> Nested Loop Left
Join (cost=0.29..1181.56 rows=158 width=27) -> Seq Scan on sys_account
digitador (cost=0.00..4.58 rows=158 width=8) -> Index Scan using
pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45 rows=1 width=27)
Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer) ->
Hash (cost=4.58..4.58 rows=158 width=15) -> Seq Scan on sys_account
telemarketing (cost=0.00..4.58 rows=158 width=15) -> Index Scan using
pkpessoa on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27)
Index Cond: ((pessoa_id)::integer = (d.pesquisador_id)::integer) -> Hash
(cost=11.60..11.60 rows=160 width=36) -> Seq Scan on col_curso curso
(cost=0.00..11.60 rows=160 width=36) -> Hash (cost=27.06..27.06 rows=236
width=23) -> Bitmap Heap Scan on sys_lookup midia (cost=10.11..27.06
rows=236 width=23) Recheck Cond: ((grupo)::text = 'Midia'::text) ->
Bitmap Index Scan on idxsyslookupgrupointkey (cost=0.00..10.05 rows=236
width=0) Index Cond: ((grupo)::text = 'Midia'::text) -> Hash
(cost=18.34..18.34 rows=10 width=21) -> Bitmap Heap Scan on sys_lookup
escolaridade (cost=4.36..18.34 rows=10 width=21) Recheck Cond:
((grupo)::text = 'Escolaridade'::text) -> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0) Index Cond:
((grupo)::text = 'Escolaridade'::text) -> Hash (cost=19.21..19.21
rows=14 width=21) -> Bitmap Heap Scan on sys_lookup status
(cost=4.39..19.21 rows=14 width=21) Recheck Cond: ((grupo)::text =
'StatusDigitacao'::text) -> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.38 rows=14 width=0) Index Cond: ((grupo)::text =
'StatusDigitacao'::text) SubPlan 4 -> CTE Scan on statustrabalhando
(cost=0.00..0.14 rows=7 width=4) SubPlan 5 -> CTE Scan on
statusagendados (cost=0.00..0.08 rows=4 width=4) CTE naoatribuidos ->
CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
'Não Atribuido'::text) CTE atribuidos -> CTE Scan on todos todos_1
(cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
'Atribuido'::text) CTE trabalhando -> CTE Scan on todos todos_2
(cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
'Trabalhando'::text) CTE agendados -> CTE Scan on todos todos_3
(cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
'Agendados'::text) CTE descartados -> CTE Scan on todos todos_4
(cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
'Descartados'::text) -> Append (cost=1.80..3.98 rows=65 width=1128) ->
Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13 width=1128) ->
Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128) -> Sort
(cost=1.80..1.83 rows=13 width=1160) Sort Key: naoatribuidos.prioridade
DESC, naoatribuidos.cadastro -> CTE Scan on naoatribuidos
(cost=0.00..1.56 rows=13 width=1160) -> Subquery Scan on "*SELECT* 2"
(cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on atribuidos
(cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 3"
(cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on trabalhando
(cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 4"
(cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on agendados
(cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 5"
(cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on descartados
(cost=0.00..0.26 rows=13 width=1128) *So, how to solve that and mainly
how to don´t get this recovery mode just running a query ?*
------------------------------------------------------------------------
Sent from the PostgreSQL - general mailing list archive
<http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html&gt; at
Nabble.com.

--
Adrian Klaver
adrian.klaver@aklaver.com

Just to be readable ...

And ... server log has only "the database system is in recovery mode" every
time I run that query.

I have a complex query which puts my server in recovery mode every time I
run it.
I don´t need to say that recovery mode is a situation you don´t want your
server goes to.
If I´m using some subselects I´ll get that situation

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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) ...

But if I run those subselects previously, get result values and put the
results to my statement, then it works.
with
StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$),
StatusDigitacaoReceptivoAgendados(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoAgendados$$),
StatusDigitacaoReceptivoTrabalhando(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)
select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados),
(select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from
StatusDigitacaoReceptivoTrabalhando);

Then i get these results (8, 14 and 17) and replace those subselects with
these values and run, now it runs fine.
with
StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusDigitacao$$ and Intkey in (8)),
StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusVisita$$ and Intkey in (14) and Intkey not in (17)),
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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo)

My original query, which worked for some days and then went to recovery mode
was ...
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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) as
(select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID,
D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso,
D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro,
D.Email, D.Idade,
D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie,
D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia, D.Status,
Status.Descricao lkStatus, Fonte.Fonte lkFonte, DigitadorPessoa.Apelido
lkDigitador,
Pesquisador.Apelido lkPesquisador, Telemarketing.Login lkTelemarketing,
Escolaridade.Descricao lkEscolaridade, D.Endereco, D.Bairro, D.Cidade,
D.Estado, D.CEP, D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp,
PessoaNome.Nome VisitaNome, VisitaStatus.Descricao VisitaStatus,
D.Comercial, Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case
when Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null
then $$Atribuido$$ when D.Status in (select IntKey from StatusTrabalhando)
then $$Trabalhando$$ when VisitaNome.Status in (select IntKey from
StatusAgendados) then $$Agendados$$ else $$Descartados$$ end Fluxo
from cad_Digitacao D
inner join cad_Fonte Fonte on Fonte.Fonte_ID = D.Fonte_ID
inner join sys_lookup TipoFonte on TipoFonte.intkey = Fonte.TipoFonte
left join sys_Account Telemarketing on Telemarketing.Pessoa_ID =
D.Telemarketing_ID
left join sys_Account Digitador on Digitador.Account_ID = D.Digitador_ID
left join cad_Pessoa DigitadorPessoa on Digitador.Pessoa_ID =
DigitadorPessoa.Pessoa_ID
left join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID =
D.Pesquisador_ID
left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID
left join sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey =
Fonte.Midia
left join sys_Lookup Escolaridade on Escolaridade.Grupo = $$Escolaridade$$
and Escolaridade.IntKey = D.Escolaridade
left join sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and
Status.IntKey = D.Status
left join cad_visita VisitaNome on VisitaNome.Digitacao_ID =
D.Digitacao_ID
left join sys_Lookup VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$
and VisitaStatus.IntKey = VisitaNome.Status
left join cad_Pessoa PessoaNome on PessoaNome.Pessoa_ID =
VisitaNome.Pessoa_ID where TipoFonte.descricao = $$Campanhas$$ and
Fonte.Ativo = 1 ),
NaoAtribuidos as (select * from Todos where Fluxo = $$Não Atribuido$$),
Atribuidos as (select * from Todos where Fluxo = $$Atribuido$$),
Trabalhando as (select * from todos where Fluxo = $$Trabalhando$$),
Agendados as (select * from todos where Fluxo = $$Agendados$$),
Descartados as (select * from todos where Fluxo = $$Descartados$$)
select * from (
select 1 as Ordem, Digitacao_ID, cast(Null as I32) Fonte_ID, cast(Null as
I32) Curso_ID, cast(Null as I32) Digitador_ID, cast(Null as I32)
Pesquisador_ID, Telemarketing_ID, Nome, cast(Null as T100) Curso, Cast(Null
as Telefone) Telefone,
Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null
as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as
T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null as
T50) Extra1,
Cast(Null as T50) Extra2, Cast(Null as T50) Extra3, Cast(Null as T50)
Extra4, Cast(Null as T50) Colegio, Cast(Null as T50) Serie, Cast(Null as
T50) Turma, Cast(Null as C1) Turno, Cast(Null as I16) AnoLetivo, Cast(Null
as T50) Pai,
Cast(Null as T50) Mae, Sequencia, Cast(Null as I16) Status, lkStatus,
lkFonte, Cast(Null as T50) lkDigitador, Cast(Null as T50) lkPesquisador,
lkTelemarketing, Cast(Null as T50) lkEscolaridade, Cast(Null as T50)
Endereco,
Cast(Null as T30) Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado)
Estado, Cast(Null as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as
DataHora) Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16)
WhatsApp,
Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null as
Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem,
Prioridade desc, Cadastro) NA union all
select 2 as Ordem, 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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Atribuidos union all
select 3 as Ordem, 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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union all
select 4 as Ordem, 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, lkTelemarketing, lkEscolaridade, Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Agendados union all
select 5 as Ordem, 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, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade,
Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo
from Descartados order by Ordem;

And the explain of that query was ...
QUERY PLAN
Sort (cost=2640.68..2640.84 rows=65 width=1128)
Sort Key: "*SELECT* 1".ordem
CTE statustrabalhando
-> Hash Semi Join (cost=15.44..30.40 rows=7 width=4)
Hash Cond: ((sys_lookup.intkey)::text =
(unnest(string_to_array("substring"((sys_var.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21 rows=14
width=4)
Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
(cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoTrabalhando'::text)
CTE statusagendados
-> Hash Semi Join (cost=24.51..39.66 rows=4 width=4)
Hash Cond: ((sys_lookup_1.intkey)::text =
(unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup sys_lookup_1
(cost=13.46..28.53 rows=8 width=4)
Recheck Cond: ((grupo)::text = 'StatusVisita'::text)
Filter: (NOT (hashed SubPlan 2))
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text = 'StatusVisita'::text)
SubPlan 2
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_1 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoDescartarAgendados'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_2 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoAgendados'::text)
CTE todos
-> Merge Right Join (cost=2152.17..2282.99 rows=2504 width=828)
Merge Cond: ((visitanome.digitacao_id)::integer =
(d.digitacao_id)::integer)
-> Nested Loop Left Join (cost=4.99..56111.59 rows=77463
width=46)
-> Nested Loop Left Join (cost=4.69..22162.21 rows=77463
width=27)
Join Filter: ((visitastatus.intkey)::integer =
(visitanome.status)::smallint)
-> Index Scan using idxvisitadigitacao on cad_visita
visitanome (cost=0.29..3551.70 rows=77463 width=10)
-> Materialize (cost=4.40..19.43 rows=16 width=21)
-> Bitmap Heap Scan on sys_lookup visitastatus
(cost=4.40..19.35 rows=16 width=21)
Recheck Cond: ((grupo)::text =
'StatusVisita'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text =
'StatusVisita'::text)
-> Index Scan using pkpessoa on cad_pessoa pessoanome
(cost=0.29..0.44 rows=1 width=27)
Index Cond: ((pessoa_id)::integer =
(visitanome.pessoa_id)::integer)
-> Sort (cost=2146.94..2148.64 rows=682 width=756)
Sort Key: d.digitacao_id
-> Hash Left Join (cost=1301.36..2114.84 rows=682
width=756)
Hash Cond: ((d.status)::smallint =
(status.intkey)::integer)
-> Hash Left Join (cost=1281.98..2081.79 rows=682
width=739)
Hash Cond: ((d.escolaridade)::smallint =
(escolaridade.intkey)::integer)
-> Hash Left Join (cost=1263.51..2053.08
rows=682 width=722)
Hash Cond: ((fonte.midia)::smallint =
(midia.intkey)::integer)
-> Hash Left Join (cost=1233.51..1959.83
rows=682 width=705)
Hash Cond: ((d.curso_id)::integer =
(curso.curso_id)::integer)
-> Nested Loop Left Join
(cost=1219.91..1944.43 rows=682 width=673)
-> Hash Left Join
(cost=1219.62..1411.81 rows=682 width=650)
Hash Cond:
((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer)
-> Hash Left Join
(cost=1213.06..1401.72 rows=682 width=639)
Hash Cond:
((d.digitador_id)::integer = (digitador.account_id)::integer)
-> Nested Loop
(cost=29.52..215.51 rows=682 width=616)
-> Hash
Join (cost=29.24..54.61 rows=7 width=17)
Hash
Cond: ((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer)
->
Seq Scan on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19)

Filter: ((ativo)::smallint = 1)
->
Hash (cost=29.21..29.21 rows=2 width=4)

-> Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4)

Filter: ((descricao)::text = 'Campanhas'::text)
-> Index
Scan using idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227
width=603)
Index
Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer)
-> Hash
(cost=1181.56..1181.56 rows=158 width=27)
-> Nested
Loop Left Join (cost=0.29..1181.56 rows=158 width=27)
->
Seq Scan on sys_account digitador (cost=0.00..4.58 rows=158 width=8)
->
Index Scan using pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45
rows=1 width=27)

Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer)
-> Hash
(cost=4.58..4.58 rows=158 width=15)
-> Seq Scan on
sys_account telemarketing (cost=0.00..4.58 rows=158 width=15)
-> Index Scan using pkpessoa
on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27)
Index Cond:
((pessoa_id)::integer = (d.pesquisador_id)::integer)
-> Hash (cost=11.60..11.60
rows=160 width=36)
-> Seq Scan on col_curso
curso (cost=0.00..11.60 rows=160 width=36)
-> Hash (cost=27.06..27.06 rows=236
width=23)
-> Bitmap Heap Scan on sys_lookup
midia (cost=10.11..27.06 rows=236 width=23)
Recheck Cond: ((grupo)::text =
'Midia'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..10.05 rows=236 width=0)
Index Cond:
((grupo)::text = 'Midia'::text)
-> Hash (cost=18.34..18.34 rows=10 width=21)
-> Bitmap Heap Scan on sys_lookup
escolaridade (cost=4.36..18.34 rows=10 width=21)
Recheck Cond: ((grupo)::text =
'Escolaridade'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0)
Index Cond: ((grupo)::text =
'Escolaridade'::text)
-> Hash (cost=19.21..19.21 rows=14 width=21)
-> Bitmap Heap Scan on sys_lookup status
(cost=4.39..19.21 rows=14 width=21)
Recheck Cond: ((grupo)::text =
'StatusDigitacao'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text =
'StatusDigitacao'::text)
SubPlan 4
-> CTE Scan on statustrabalhando (cost=0.00..0.14 rows=7
width=4)
SubPlan 5
-> CTE Scan on statusagendados (cost=0.00..0.08 rows=4
width=4)
CTE naoatribuidos
-> CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Não Atribuido'::text)
CTE atribuidos
-> CTE Scan on todos todos_1 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Atribuido'::text)
CTE trabalhando
-> CTE Scan on todos todos_2 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Trabalhando'::text)
CTE agendados
-> CTE Scan on todos todos_3 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Agendados'::text)
CTE descartados
-> CTE Scan on todos todos_4 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Descartados'::text)
-> Append (cost=1.80..3.98 rows=65 width=1128)
-> Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13
width=1128)
-> Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128)
-> Sort (cost=1.80..1.83 rows=13 width=1160)
Sort Key: naoatribuidos.prioridade DESC,
naoatribuidos.cadastro
-> CTE Scan on naoatribuidos (cost=0.00..1.56
rows=13 width=1160)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on atribuidos (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on trabalhando (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on agendados (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 5" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on descartados (cost=0.00..0.26 rows=13
width=1128)

So, how to solve that and mainly how to don´t get this recovery mode just
running a query ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2/3/19 8:32 AM, PegoraroF10 wrote:

Just to be readable ...

And ... server log has only "the database system is in recovery mode" every
time I run that query.

I would assume that is in the log from the restart of the server after
it crashed and then restarted in recovery mode.

What is in the previous log, the one that was active when the query
crashed the server?

Also anything relevant from the OS system logs?

I have a complex query which puts my server in recovery mode every time I
run it.
I don´t need to say that recovery mode is a situation you don´t want your
server goes to.

Well actually that is how standby_servers run:

https://www.postgresql.org/docs/11/warm-standby.html

"... while each standby server operates in continuous recovery mode,
reading the WAL files from the primary."

Is this server a standby?

If I´m using some subselects I´ll get that situation

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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) ...

But if I run those subselects previously, get result values and put the
results to my statement, then it works.
with
StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$),
StatusDigitacaoReceptivoAgendados(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoAgendados$$),
StatusDigitacaoReceptivoTrabalhando(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)
select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados),
(select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from
StatusDigitacaoReceptivoTrabalhando);

Then i get these results (8, 14 and 17) and replace those subselects with
these values and run, now it runs fine.
with
StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusDigitacao$$ and Intkey in (8)),
StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusVisita$$ and Intkey in (14) and Intkey not in (17)),
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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo)

My original query, which worked for some days and then went to recovery mode
was ...
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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) as
(select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID,
D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso,
D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro,
D.Email, D.Idade,
D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie,
D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia, D.Status,
Status.Descricao lkStatus, Fonte.Fonte lkFonte, DigitadorPessoa.Apelido
lkDigitador,
Pesquisador.Apelido lkPesquisador, Telemarketing.Login lkTelemarketing,
Escolaridade.Descricao lkEscolaridade, D.Endereco, D.Bairro, D.Cidade,
D.Estado, D.CEP, D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp,
PessoaNome.Nome VisitaNome, VisitaStatus.Descricao VisitaStatus,
D.Comercial, Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case
when Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null
then $$Atribuido$$ when D.Status in (select IntKey from StatusTrabalhando)
then $$Trabalhando$$ when VisitaNome.Status in (select IntKey from
StatusAgendados) then $$Agendados$$ else $$Descartados$$ end Fluxo
from cad_Digitacao D
inner join cad_Fonte Fonte on Fonte.Fonte_ID = D.Fonte_ID
inner join sys_lookup TipoFonte on TipoFonte.intkey = Fonte.TipoFonte
left join sys_Account Telemarketing on Telemarketing.Pessoa_ID =
D.Telemarketing_ID
left join sys_Account Digitador on Digitador.Account_ID = D.Digitador_ID
left join cad_Pessoa DigitadorPessoa on Digitador.Pessoa_ID =
DigitadorPessoa.Pessoa_ID
left join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID =
D.Pesquisador_ID
left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID
left join sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey =
Fonte.Midia
left join sys_Lookup Escolaridade on Escolaridade.Grupo = $$Escolaridade$$
and Escolaridade.IntKey = D.Escolaridade
left join sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and
Status.IntKey = D.Status
left join cad_visita VisitaNome on VisitaNome.Digitacao_ID =
D.Digitacao_ID
left join sys_Lookup VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$
and VisitaStatus.IntKey = VisitaNome.Status
left join cad_Pessoa PessoaNome on PessoaNome.Pessoa_ID =
VisitaNome.Pessoa_ID where TipoFonte.descricao = $$Campanhas$$ and
Fonte.Ativo = 1 ),
NaoAtribuidos as (select * from Todos where Fluxo = $$Não Atribuido$$),
Atribuidos as (select * from Todos where Fluxo = $$Atribuido$$),
Trabalhando as (select * from todos where Fluxo = $$Trabalhando$$),
Agendados as (select * from todos where Fluxo = $$Agendados$$),
Descartados as (select * from todos where Fluxo = $$Descartados$$)
select * from (
select 1 as Ordem, Digitacao_ID, cast(Null as I32) Fonte_ID, cast(Null as
I32) Curso_ID, cast(Null as I32) Digitador_ID, cast(Null as I32)
Pesquisador_ID, Telemarketing_ID, Nome, cast(Null as T100) Curso, Cast(Null
as Telefone) Telefone,
Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null
as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as
T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null as
T50) Extra1,
Cast(Null as T50) Extra2, Cast(Null as T50) Extra3, Cast(Null as T50)
Extra4, Cast(Null as T50) Colegio, Cast(Null as T50) Serie, Cast(Null as
T50) Turma, Cast(Null as C1) Turno, Cast(Null as I16) AnoLetivo, Cast(Null
as T50) Pai,
Cast(Null as T50) Mae, Sequencia, Cast(Null as I16) Status, lkStatus,
lkFonte, Cast(Null as T50) lkDigitador, Cast(Null as T50) lkPesquisador,
lkTelemarketing, Cast(Null as T50) lkEscolaridade, Cast(Null as T50)
Endereco,
Cast(Null as T30) Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado)
Estado, Cast(Null as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as
DataHora) Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16)
WhatsApp,
Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null as
Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem,
Prioridade desc, Cadastro) NA union all
select 2 as Ordem, 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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Atribuidos union all
select 3 as Ordem, 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, lkTelemarketing, lkEscolaridade,
Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union all
select 4 as Ordem, 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, lkTelemarketing, lkEscolaridade, Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Agendados union all
select 5 as Ordem, 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, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade,
Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo
from Descartados order by Ordem;

And the explain of that query was ...
QUERY PLAN
Sort (cost=2640.68..2640.84 rows=65 width=1128)
Sort Key: "*SELECT* 1".ordem
CTE statustrabalhando
-> Hash Semi Join (cost=15.44..30.40 rows=7 width=4)
Hash Cond: ((sys_lookup.intkey)::text =
(unnest(string_to_array("substring"((sys_var.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21 rows=14
width=4)
Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
(cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoTrabalhando'::text)
CTE statusagendados
-> Hash Semi Join (cost=24.51..39.66 rows=4 width=4)
Hash Cond: ((sys_lookup_1.intkey)::text =
(unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup sys_lookup_1
(cost=13.46..28.53 rows=8 width=4)
Recheck Cond: ((grupo)::text = 'StatusVisita'::text)
Filter: (NOT (hashed SubPlan 2))
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text = 'StatusVisita'::text)
SubPlan 2
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_1 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoDescartarAgendados'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_2 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoAgendados'::text)
CTE todos
-> Merge Right Join (cost=2152.17..2282.99 rows=2504 width=828)
Merge Cond: ((visitanome.digitacao_id)::integer =
(d.digitacao_id)::integer)
-> Nested Loop Left Join (cost=4.99..56111.59 rows=77463
width=46)
-> Nested Loop Left Join (cost=4.69..22162.21 rows=77463
width=27)
Join Filter: ((visitastatus.intkey)::integer =
(visitanome.status)::smallint)
-> Index Scan using idxvisitadigitacao on cad_visita
visitanome (cost=0.29..3551.70 rows=77463 width=10)
-> Materialize (cost=4.40..19.43 rows=16 width=21)
-> Bitmap Heap Scan on sys_lookup visitastatus
(cost=4.40..19.35 rows=16 width=21)
Recheck Cond: ((grupo)::text =
'StatusVisita'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text =
'StatusVisita'::text)
-> Index Scan using pkpessoa on cad_pessoa pessoanome
(cost=0.29..0.44 rows=1 width=27)
Index Cond: ((pessoa_id)::integer =
(visitanome.pessoa_id)::integer)
-> Sort (cost=2146.94..2148.64 rows=682 width=756)
Sort Key: d.digitacao_id
-> Hash Left Join (cost=1301.36..2114.84 rows=682
width=756)
Hash Cond: ((d.status)::smallint =
(status.intkey)::integer)
-> Hash Left Join (cost=1281.98..2081.79 rows=682
width=739)
Hash Cond: ((d.escolaridade)::smallint =
(escolaridade.intkey)::integer)
-> Hash Left Join (cost=1263.51..2053.08
rows=682 width=722)
Hash Cond: ((fonte.midia)::smallint =
(midia.intkey)::integer)
-> Hash Left Join (cost=1233.51..1959.83
rows=682 width=705)
Hash Cond: ((d.curso_id)::integer =
(curso.curso_id)::integer)
-> Nested Loop Left Join
(cost=1219.91..1944.43 rows=682 width=673)
-> Hash Left Join
(cost=1219.62..1411.81 rows=682 width=650)
Hash Cond:
((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer)
-> Hash Left Join
(cost=1213.06..1401.72 rows=682 width=639)
Hash Cond:
((d.digitador_id)::integer = (digitador.account_id)::integer)
-> Nested Loop
(cost=29.52..215.51 rows=682 width=616)
-> Hash
Join (cost=29.24..54.61 rows=7 width=17)
Hash
Cond: ((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer)
->
Seq Scan on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19)

Filter: ((ativo)::smallint = 1)
->
Hash (cost=29.21..29.21 rows=2 width=4)

-> Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4)

Filter: ((descricao)::text = 'Campanhas'::text)
-> Index
Scan using idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227
width=603)
Index
Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer)
-> Hash
(cost=1181.56..1181.56 rows=158 width=27)
-> Nested
Loop Left Join (cost=0.29..1181.56 rows=158 width=27)
->
Seq Scan on sys_account digitador (cost=0.00..4.58 rows=158 width=8)
->
Index Scan using pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45
rows=1 width=27)

Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer)
-> Hash
(cost=4.58..4.58 rows=158 width=15)
-> Seq Scan on
sys_account telemarketing (cost=0.00..4.58 rows=158 width=15)
-> Index Scan using pkpessoa
on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27)
Index Cond:
((pessoa_id)::integer = (d.pesquisador_id)::integer)
-> Hash (cost=11.60..11.60
rows=160 width=36)
-> Seq Scan on col_curso
curso (cost=0.00..11.60 rows=160 width=36)
-> Hash (cost=27.06..27.06 rows=236
width=23)
-> Bitmap Heap Scan on sys_lookup
midia (cost=10.11..27.06 rows=236 width=23)
Recheck Cond: ((grupo)::text =
'Midia'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..10.05 rows=236 width=0)
Index Cond:
((grupo)::text = 'Midia'::text)
-> Hash (cost=18.34..18.34 rows=10 width=21)
-> Bitmap Heap Scan on sys_lookup
escolaridade (cost=4.36..18.34 rows=10 width=21)
Recheck Cond: ((grupo)::text =
'Escolaridade'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0)
Index Cond: ((grupo)::text =
'Escolaridade'::text)
-> Hash (cost=19.21..19.21 rows=14 width=21)
-> Bitmap Heap Scan on sys_lookup status
(cost=4.39..19.21 rows=14 width=21)
Recheck Cond: ((grupo)::text =
'StatusDigitacao'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text =
'StatusDigitacao'::text)
SubPlan 4
-> CTE Scan on statustrabalhando (cost=0.00..0.14 rows=7
width=4)
SubPlan 5
-> CTE Scan on statusagendados (cost=0.00..0.08 rows=4
width=4)
CTE naoatribuidos
-> CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Não Atribuido'::text)
CTE atribuidos
-> CTE Scan on todos todos_1 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Atribuido'::text)
CTE trabalhando
-> CTE Scan on todos todos_2 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Trabalhando'::text)
CTE agendados
-> CTE Scan on todos todos_3 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Agendados'::text)
CTE descartados
-> CTE Scan on todos todos_4 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Descartados'::text)
-> Append (cost=1.80..3.98 rows=65 width=1128)
-> Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13
width=1128)
-> Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128)
-> Sort (cost=1.80..1.83 rows=13 width=1160)
Sort Key: naoatribuidos.prioridade DESC,
naoatribuidos.cadastro
-> CTE Scan on naoatribuidos (cost=0.00..1.56
rows=13 width=1160)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on atribuidos (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on trabalhando (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on agendados (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 5" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on descartados (cost=0.00..0.26 rows=13
width=1128)

So, how to solve that and mainly how to don´t get this recovery mode just
running a query ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, Feb 03, 2019 at 10:05:46AM -0800, Adrian Klaver wrote:

On 2/3/19 8:32 AM, PegoraroF10 wrote:

I have a complex query which puts my server in recovery mode every time I
run it.
I don´t need to say that recovery mode is a situation you don´t want your
server goes to.

Do you mean that your server crashes after running a SQL query? That
could be a bug.

Well actually that is how standby_servers run:

https://www.postgresql.org/docs/11/warm-standby.html

"... while each standby server operates in continuous recovery mode, reading
the WAL files from the primary."

Is this server a standby?

If you could post a self-contained test case, that would be really
helpful to see if there is an actual bug. You can obfuscate the
schema if need be, as long as the problem can be reproduced that's
fine.
--
Michael

Olá Marcos,

My original query, which worked for some days and then went to
recovery mode
was ...

If it was working and then ceased to function, did any of the following
occur:-

1) Postgres version changed?
2) OS version changed?
3) Schema changes affecting the tables/views used in your query?

I assume that you have a development data base. Can you bump up the log
level on that, run the query and see any errors in the log.

Cheers,
Robert

Nothing was change, Postgres 10, Ubuntu 16.04 and Schema was the same, before
and after that problem.
Well, that database is replicated and on replicated server that problem
doesn´t occur.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2/4/19 3:13 AM, PegoraroF10 wrote:

Nothing was change, Postgres 10, Ubuntu 16.04 and Schema was the same, before
and after that problem.

Well something changed or you would not be having a problem.

Well, that database is replicated and on replicated server that problem
doesn´t occur.

Define what you mean by replicated?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

About replication ... Logical Replication with CREATE
PUBLICATION/SUBSCRIPTION.

Yes, some DDL commands were ran on that server but none of them were related
with that select.
Let me explain better. We have a single server with a single database on it.
Each customer has its own schema and connects to it to work exclusively on
that schema. So, sometimes we add some customer or change something on an
old one. But this DDL change we could ran is not related with that schema we
are talking. Schemas can have different structures but that schema which
puts my server on recovery mode was not changed.

Maybe something related happened some days ago. When we start a new customer
we add a schema, put all their tables on it and it´s ok. Our server has
today 90 schemas and each schema has 100 tables, resulting in 9000 tables.
Some days ago we added 5 new customers on same day, so we added 500 tables.
Then, when we did that some selects on system tables were very very slow and
that was only solved when we did a REINDEX DATABASE. Even REINDEX SYSTEM did
not solve. Is this problem related with recovery mode of my server ?

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 2/4/19 7:33 AM, PegoraroF10 wrote:

About replication ... Logical Replication with CREATE
PUBLICATION/SUBSCRIPTION.

Yes, some DDL commands were ran on that server but none of them were related
with that select.
Let me explain better. We have a single server with a single database on it.
Each customer has its own schema and connects to it to work exclusively on
that schema. So, sometimes we add some customer or change something on an
old one. But this DDL change we could ran is not related with that schema we
are talking. Schemas can have different structures but that schema which
puts my server on recovery mode was not changed.

Maybe something related happened some days ago. When we start a new customer
we add a schema, put all their tables on it and it´s ok. Our server has
today 90 schemas and each schema has 100 tables, resulting in 9000 tables.
Some days ago we added 5 new customers on same day, so we added 500 tables.
Then, when we did that some selects on system tables were very very slow and
that was only solved when we did a REINDEX DATABASE. Even REINDEX SYSTEM did
not solve. Is this problem related with recovery mode of my server ?

Unsure at the moment as there is not enough information to come to any
conclusions.

Questions:

1) Do you still have the logs from when you added the 5 new schema and
do they show any warnings, errors, etc at or after that time?

2) Are you seeing any warnings, errors, etc in the logs currently?

3) Is the replication for all tables?

4) The exact same query works without a problem on the replicated
server, correct?

5) In the Postgres server that is being replicated to are there any log
entries that might be of concern?

6) Is it possible to get a stack trace of the crash?:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

Olá Marcos,

On Mon, 2019-02-04 at 08:33 -0700, PegoraroF10 wrote:

About replication ... Logical Replication with CREATE
PUBLICATION/SUBSCRIPTION.

Yes, some DDL commands were ran on that server but none of them were
related
with that select.
Let me explain better. We have a single server with a single database
on it.
Each customer has its own schema and connects to it to work
exclusively on
that schema. So, sometimes we add some customer or change something
on an
old one. But this DDL change we could ran is not related with that
schema we
are talking. Schemas can have different structures but that schema
which
puts my server on recovery mode was not changed.

Maybe something related happened some days ago. When we start a new
customer
we add a schema, put all their tables on it and it´s ok. Our server
has
today 90 schemas and each schema has 100 tables, resulting in 9000
tables.
Some days ago we added 5 new customers on same day, so we added 500
tables.
Then, when we did that some selects on system tables were very very
slow and
that was only solved when we did a REINDEX DATABASE. Even REINDEX
SYSTEM did
not solve. Is this problem related with recovery mode of my server ?

I trust that you saved the output from your "create" scripts. I would
find those five log files and see if any errors occurred.

Also, you would have to run them on the "subscriber" server first. So,
if you "diff'd" the log files for "escola-53" (or whatever) from the
publication and the subscriber servers, they should be similar in all
respects? I don't know the answer to that question.

Chapter 31.4 in the doco is interesting reading.

Cheers,
Robert

*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

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@f10.com.br>
Sent: Friday, February 8, 2019 4:11 PM
To: pgsql-general@postgresql.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&gt; 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.

On 2/8/19 1:11 PM, PegoraroF10 wrote:

*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 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

So the oom-killer kicked in.

See below for some ways to deal with this:

https://www.postgresql.org/docs/10/kernel-resources.html

*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 ?*

You run anything(computer related or otherwise) out of resources and bad
things are going to happen.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

On Fri, Feb 08, 2019 at 02:11:33PM -0700, PegoraroF10 wrote:

*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]

"segfault" seems to mean you hit a bug, which we'll want more information to
diagnose. Could you install debugging symbols ? Ubuntu calls their package
postgresql-10-dbg or similar. And start server with coredumps enabled, using
pg_ctl -c -D /var/lib/postgresql/10/main (or similar). Then trigger the query
and hope to find a core dump in the data directory. Or possibly it'll be
processed into /var/crash by apport daemon, depending if that's running and
enabled (check /proc/sys/kernel/core_pattern).

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Getting_a_trace_from_a_randomly_crashing_backend

*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
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

In this case, you ran out of RAM, as you noticed. You should make sure ANALYZE
statistics are up to date (including manually ANALYZEing any parent tables).

On Sun, Feb 03, 2019 at 09:05:42AM -0700, PegoraroF10 wrote:

I�m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD)
and DB has 70GB

What is work_mem setting ?

You could try running the query with lower work_mem, or check EXPLAIN ANALYZE
and try to resolve any bad rowcount estimates.

*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.

on: https://drive.google.com/open?id=18zIvkV3ew4aZ1_cxI-EmIPVql7ydvEwi*

It says "Access Denied", perhaps you could send a link to
https://explain.depesz.com/ ?

Justin

Hi,

On Sat, 2019-02-09 at 15:46 -0600, Justin Pryzby wrote:

Hi,

"segfault" seems to mean you hit a bug, which we'll want more
information to
diagnose. Could you install debugging symbols ? Ubuntu calls their
package
postgresql-10-dbg or similar. And start server with coredumps
enabled, using
pg_ctl -c -D /var/lib/postgresql/10/main (or similar). Then trigger
the query
and hope to find a core dump in the data directory. Or possibly
it'll be
processed into /var/crash by apport daemon, depending if that's
running and
enabled (check /proc/sys/kernel/core_pattern).

I believe there is a bug. I've examined the query Marcos sent with his
first post and I think the parser should have listed some errors and
not tried to run the query.
I'm probably wrong but consider the following.

Around line 33 of the query:-

tsrange(col_aula.data, (col_aula.data + (col_aula.tempo|| '
minute')::interval)) dia, 0 prevista,
(extract(EPOCH FROM col_aula.tempo) / 60) minutosassistidos

Assuming column col_aula.tempo is of type INTEGER, is NOT NULL and
let's say contains a value of 60, then it parses as

tsrange(col_aula.data, (col_aula.data + (60' minute')::interval))

which would pull a syntax error.

You cannot extract EPOCH from a column that is of type INTEGER. Another
syntax error.

Down around line 87 onwards there are generate_series without any
parameters, and further dubious usage of EPOCH, as well as DOW.

Not having the table definitions is obviously clouding the analysis.

If there is a bug in the parser, then one of the experts will have to
opine about that.

HTH,
Robert

On Sun, Feb 10, 2019 at 03:15:38PM +1100, rob stone wrote:

Down around line 87 onwards there are generate_series without any
parameters, and further dubious usage of EPOCH, as well as DOW.

Not having the table definitions is obviously clouding the analysis.

That seems like the root issue for now.

If there is a bug in the parser, then one of the experts will have to
opine about that.

If you actually extract the SQL query and the schema which are used to
make the problem reproducible? That would help a bit instead of
having to guess based on the sparse information on this thread.
--
Michael

*Here is a SQL which will get that recovery mode. You can run it on any
database because we created it with FROM VALUES, so ...*
But this one is that one which grows and grows memory use until all memory
and swap space are gone, so problem occurs. That other SQL which gives us
the same problem but immediatelly we couldn´t replay it without our entire
database. Even if we extract just that schema to a new DB it doesn´t go to
recovery mode. We will think a little bit more to create something you can
test.
with feriados as ( SELECT dia, repete FROM ( VALUES
('2014-10-11'::DATE, FALSE), ('2014-10-13', FALSE), ('2014-10-14', FALSE),
('2014-10-15', FALSE), ('2014-10-16', FALSE), ('2014-10-17',
FALSE), ('2014-12-19', FALSE), ('2014-12-20', FALSE), ('2014-12-22', FALSE),
('2014-12-23', FALSE), ('2014-12-24', FALSE), ('2014-12-26',
FALSE), ('2014-12-27', FALSE), ('2014-12-29', FALSE), ('2014-12-30', FALSE),
('2014-12-31', FALSE), ('2015-01-02', FALSE), ('2015-01-03', FALSE),
('2015-01-04', FALSE), ('2015-02-16', FALSE), ('2015-02-17',
FALSE), ('2015-04-03', FALSE), ('2015-04-04', FALSE), ('2015-06-04', FALSE),
('2015-12-18', FALSE), ('2015-12-19', FALSE), ('2015-12-21',
FALSE), ('2015-12-22', FALSE), ('2015-12-23', FALSE), ('2015-12-24', FALSE),
('2015-12-26', FALSE), ('2015-12-28', FALSE), ('2015-12-29', FALSE),
('2015-12-30', FALSE), ('2015-12-31', FALSE), ('2016-01-02',
FALSE), ('2016-01-04', FALSE), ('2016-01-05', FALSE), ('2016-01-06', FALSE),
('2016-01-07', FALSE), ('2016-01-08', FALSE), ('2016-01-09',
FALSE), ('2016-02-08', FALSE), ('2016-03-09', FALSE), ('2016-03-25', FALSE),
('2016-03-26', FALSE), ('2016-05-26', FALSE), ('2016-12-24', FALSE),
('2016-12-28', FALSE), ('2016-12-29', FALSE), ('2016-12-30',
FALSE), ('2016-12-31', FALSE), ('2017-01-02', FALSE), ('2017-01-03', FALSE),
('2017-01-04', FALSE), ('2017-01-05', FALSE), ('2017-01-06',
FALSE), ('2017-01-07', FALSE), ('2017-02-25', FALSE), ('2017-02-27', FALSE),
('2017-03-09', FALSE), ('2017-04-14', FALSE), ('2017-04-15', FALSE),
('2017-06-15', FALSE), ('2017-09-30', FALSE), ('2017-12-18',
FALSE), ('2017-12-19', FALSE), ('2017-12-20', FALSE), ('2017-12-21', FALSE),
('2017-12-22', FALSE), ('2017-12-23', FALSE), ('2017-12-26',
FALSE), ('2017-12-27', FALSE), ('2017-12-28', FALSE), ('2017-12-29', FALSE),
('2017-12-30', FALSE), ('2018-01-02', FALSE), ('2018-01-03', FALSE),
('2018-01-04', FALSE), ('2018-01-05', FALSE), ('2018-01-06',
FALSE), ('2018-01-07', FALSE), ('2018-02-12', FALSE), ('2018-02-13', FALSE),
('2018-03-30', FALSE), ('2018-03-31', FALSE), ('2018-04-30',
FALSE), ('2018-05-31', FALSE), ('2018-10-15', FALSE), ('2018-12-18', FALSE),
('2018-12-19', FALSE), ('2018-12-20', FALSE), ('2018-12-21', FALSE),
('2018-12-22', FALSE), ('2018-12-24', FALSE), ('2018-12-26',
FALSE), ('2018-12-27', FALSE), ('2018-12-28', FALSE), ('2018-12-29', FALSE),
('2018-12-31', FALSE), ('2019-01-01', TRUE), ('2019-01-02',
FALSE), ('2019-01-03', FALSE), ('2019-01-04', FALSE), ('2019-01-05', FALSE),
('2019-01-07', FALSE), ('2019-02-09', TRUE), ('2019-03-09', TRUE),
('2019-04-21', TRUE), ('2019-05-01', TRUE), ('2019-09-07', TRUE),
('2019-10-12', TRUE), ('2019-11-02', TRUE), ('2019-11-15', TRUE),
('2019-12-19', TRUE), ('2019-12-20', TRUE), ('2019-12-21', TRUE),
('2019-12-22', TRUE), ('2019-12-23', TRUE), ('2019-12-25', TRUE),
('2019-12-26', TRUE), ('2019-12-27', TRUE) ) x (dia, repete)),
materias as ( SELECT * from (VALUES (593, 11091, 'AAC - Ética e
Segurança Digital', 9, 120, '2019/01/30'::DATE, '2019/01/30'::DATE, 60, 120,
0), (593, 11085, 'AAC - Computação nas Nuvens', 12, 60, NULL, NULL, 60,
120, 60) ) x (turma_id, materia_id, materia, sequencia, previsto, dataini,
datafim, tempoatividade, minutosaula, minutosrestantes)), aulasporsemana as
( select * from (values (593,1)) x (turma_id, quantidade)), 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) FROM materias LEFT JOIN
LATERAL ( SELECT TRUE, tsrange(col_aula.data, (col_aula.data + (
col_aula.tempo || ' minute')::INTERVAL)) dia, 0 prevista, (extract(EPOCH
FROM col_aula.tempo) / 60) minutosassistidos FROM (VALUES (593,
11091, TIMESTAMP '2019-01-30 19:00:00', TIME '02:00'), (593, 11091,
'2019-02-06 19:00:00', '01:00') ) col_aula (turma_id, materia_id, data,
tempo) WHERE col_aula.materia_id = materias.materia_id AND
col_aula.turma_id = materias.turma_id ORDER BY col_aula.data, sequencia
) AS realizada(aularealizada, dia, prevista, minutosassistidos) 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

Hello Marcos,

On Wed, 2019-02-13 at 04:41 -0700, PegoraroF10 wrote:

Here is a SQL which will get that recovery mode. You can run it on
any database because we created it with FROM VALUES, so ...
But this one is that one which grows and grows memory use until all
memory and swap space are gone, so problem occurs. That other SQL
which gives us the same problem but immediatelly we couldn´t replay
it without our entire database. Even if we extract just that schema
to a new DB it doesn´t go to recovery mode. We will think a little
bit more to create something you can test.

Thanks for the revised query.

However, the create table/view scripts are needed as well.

Cheers,
Robert