Server goes to Recovery Mode when run a SQL

classic Classic list List threaded Threaded
23 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Server goes to Recovery Mode when run a SQL

PegoraroF10
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 at Nabble.com.
Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Adrian Klaver-4
In reply to this post by PegoraroF10
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> at
> Nabble.com.


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

rob stone-2
In reply to this post by PegoraroF10
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



Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

rob stone-2
In reply to this post by PegoraroF10
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




Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
In reply to this post by PegoraroF10

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 at Nabble.com.
Reply | Threaded
Open this post in threaded view
|

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

Jeremiah Bauer
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 <[hidden email]>
Sent: Friday, February 8, 2019 4:11 PM
To: [hidden email]
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 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.
Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Adrian Klaver-4
In reply to this post by PegoraroF10
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Justin Pryzby
In reply to this post by PegoraroF10
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

Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

rob stone-2
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




Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

PegoraroF10
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 the PostgreSQL - general mailing list archive at Nabble.com.
Reply | Threaded
Open this post in threaded view
|

Re: Server goes to Recovery Mode when run a SQL

rob stone-2
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


12