Thread: Server goes to Recovery Mode when run a SQL
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.
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I´m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD) and DB has 70GB -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 2/3/19 7:53 AM, PegoraroF10 wrote:
First the below is unreadable, probably because it is being sent through
Nabble. Please subscribe to list directly and try again. In meantime
could not tell from below, but what relevant information is in the logs
before and after?
> I have a complex query which puts my server in recovery mode every time
> I run it. I don´t need to say that recovery mode is a situation you
> don´t want your server goes to. *If I´m using some subselects I´ll get
> that situation* with StatusTrabalhando(Intkey) as (select Intkey from
> sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
> StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
> $$StatusVisita$$ and Intkey in (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey
> not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
> from sys_Var where Name =
> $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
> Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
> Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
> Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3,
> Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia,
> Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing,
> lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
> Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
> Midia, Prioridade, DataHoraAlteracao, Fluxo) ... *but if I run those
> subselects previously, get those values and put the results to my
> statement, then it works. * with
> StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$),
> StatusDigitacaoReceptivoAgendados(IntKey) as (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$),
> StatusDigitacaoReceptivoTrabalhando(IntKey) as (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) select
> (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), (select
> IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from
> StatusDigitacaoReceptivoTrabalhando); *Then i get these results (8, 14
> and 17) and replace those subselects with these values and run, now it
> runs fine.* with StatusTrabalhando(Intkey) as (select Intkey from
> sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (*8*)),
> StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
> $$StatusVisita$$ and Intkey in (*14*) and Intkey not in (*17*)),
> Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
> Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
> Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3,
> Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia,
> Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing,
> lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
> Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
> Midia, Prioridade, DataHoraAlteracao, Fluxo) *My original query, which
> worked for some days and then went to recovery mode was ...* with
> StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo
> = $$StatusDigitacao$$ and Intkey in (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
> StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
> $$StatusVisita$$ and Intkey in (select
> unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var
> where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey
> not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
> from sys_Var where Name =
> $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
> Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
> Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
> Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3,
> Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia,
> Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing,
> lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
> Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
> Midia, Prioridade, DataHoraAlteracao, Fluxo); as (select D.Digitacao_ID,
> D.Fonte_ID, D.Curso_ID, Digitador_ID, D.Pesquisador_ID,
> D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, D.Telefone,
> D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro, D.Email,
> D.Idade, D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio,
> D.Serie, D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia,
> D.Status, Status.Descricao lkStatus, Fonte.Fonte lkFonte,
> DigitadorPessoa.Apelido lkDigitador, Pesquisador.Apelido lkPesquisador,
> Telemarketing.Login lkTelemarketing, Escolaridade.Descricao
> lkEscolaridade, D.Endereco, D.Bairro, D.Cidade, D.Estado, D.CEP,
> D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp, PessoaNome.Nome
> VisitaNome, VisitaStatus.Descricao VisitaStatus, D.Comercial,
> Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case when
> Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null
> then $$Atribuido$$ when D.Status in (select IntKey from
> StatusTrabalhando) then $$Trabalhando$$ when VisitaNome.Status in
> (select IntKey from StatusAgendados) then $$Agendados$$ else
> $$Descartados$$ end Fluxo from cad_Digitacao D inner join cad_Fonte
> Fonte on Fonte.Fonte_ID = D.Fonte_ID inner join sys_lookup TipoFonte on
> TipoFonte.intkey = Fonte.TipoFonte left join sys_Account Telemarketing
> on Telemarketing.Pessoa_ID = D.Telemarketing_ID left join sys_Account
> Digitador on Digitador.Account_ID = D.Digitador_ID left join cad_Pessoa
> DigitadorPessoa on Digitador.Pessoa_ID = DigitadorPessoa.Pessoa_ID left
> join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID = D.Pesquisador_ID
> left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID left join
> sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey =
> Fonte.Midia left join sys_Lookup Escolaridade on Escolaridade.Grupo =
> $$Escolaridade$$ and Escolaridade.IntKey = D.Escolaridade left join
> sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and
> Status.IntKey = D.Status left join cad_visita VisitaNome on
> VisitaNome.Digitacao_ID = D.Digitacao_ID left join sys_Lookup
> VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$ and
> VisitaStatus.IntKey = VisitaNome.Status left join cad_Pessoa PessoaNome
> on PessoaNome.Pessoa_ID = VisitaNome.Pessoa_ID where TipoFonte.descricao
> = $$Campanhas$$ and Fonte.Ativo = 1 ), NaoAtribuidos as (select * from
> Todos where Fluxo = $$Não Atribuido$$), Atribuidos as (select * from
> Todos where Fluxo = $$Atribuido$$), Trabalhando as (select * from todos
> where Fluxo = $$Trabalhando$$), Agendados as (select * from todos where
> Fluxo = $$Agendados$$), Descartados as (select * from todos where Fluxo
> = $$Descartados$$) select * from (select 1 as Ordem, Digitacao_ID,
> cast(Null as I32) Fonte_ID, cast(Null as I32) Curso_ID, cast(Null as
> I32) Digitador_ID, cast(Null as I32) Pesquisador_ID, Telemarketing_ID,
> Nome, cast(Null as T100) Curso, Cast(Null as Telefone) Telefone,
> Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null
> as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as
> T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null
> as T50) Extra1, Cast(Null as T50) Extra2, Cast(Null as T50) Extra3,
> Cast(Null as T50) Extra4, Cast(Null as T50) Colegio, Cast(Null as T50)
> Serie, Cast(Null as T50) Turma, Cast(Null as C1) Turno, Cast(Null as
> I16) AnoLetivo, Cast(Null as T50) Pai, Cast(Null as T50) Mae, Sequencia,
> Cast(Null as I16) Status, lkStatus, lkFonte, Cast(Null as T50)
> lkDigitador, Cast(Null as T50) lkPesquisador, lkTelemarketing, Cast(Null
> as T50) lkEscolaridade, Cast(Null as T50) Endereco, Cast(Null as T30)
> Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado) Estado, Cast(Null
> as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as DataHora)
> Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16) WhatsApp,
> Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null
> as Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem,
> Prioridade desc, Cadastro) NA union all select 2 as Ordem, Digitacao_ID,
> Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID,
> Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade,
> Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio,
> Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
> lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
> Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
> WhatsApp, VisitaNome, VisitaStatus, Comercial, Midia, Fluxo from
> Atribuidos union all select 3 as Ordem, Digitacao_ID, Fonte_ID,
> Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso,
> Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email,
> Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma,
> Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte,
> lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco,
> Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp,
> VisitaNome, VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union
> all select 4 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID,
> Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular,
> Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1,
> Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai,
> Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador,
> lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP,
> Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus,
> Comercial, Midia, Fluxo from Agendados union all select 5 as Ordem,
> Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
> Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
> Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3,
> Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia,
> Status, lkStatus, lkFonte, lkDigitador, lkPesquisador, lkTelemarketing,
> lkEscolaridade, Endereco, Bairro, Cidade, Estado, CEP, Repetido,
> Impresso, Etiqueta, WhatsApp, VisitaNome, VisitaStatus, Comercial,
> Midia, Fluxo from Descartados order by Ordem; *And the explain of that
> query was ...* QUERY PLAN Sort (cost=2640.68..2640.84 rows=65
> width=1128) Sort Key: "*SELECT* 1".ordem CTE statustrabalhando -> Hash
> Semi Join (cost=15.44..30.40 rows=7 width=4) Hash Cond:
> ((sys_lookup.intkey)::text =
> (unnest(string_to_array("substring"((sys_var.varvalue)::text, 3),
> ','::text)))) -> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21
> rows=14 width=4) Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text)
> -> Bitmap Index Scan on idxsyslookupgrupointkey (cost=0.00..4.38 rows=14
> width=0) Index Cond: ((grupo)::text = 'StatusDigitacao'::text) -> Hash
> (cost=9.81..9.81 rows=100 width=32) -> ProjectSet (cost=0.28..8.81
> rows=100 width=32) -> Index Scan using idxsysvarname on sys_var
> (cost=0.28..8.29 rows=1 width=9) Index Cond: ((name)::text =
> '/Company/StatusDigitacaoReceptivoTrabalhando'::text) CTE
> statusagendados -> Hash Semi Join (cost=24.51..39.66 rows=4 width=4)
> Hash Cond: ((sys_lookup_1.intkey)::text =
> (unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3),
> ','::text)))) -> Bitmap Heap Scan on sys_lookup sys_lookup_1
> (cost=13.46..28.53 rows=8 width=4) Recheck Cond: ((grupo)::text =
> 'StatusVisita'::text) Filter: (NOT (hashed SubPlan 2)) -> Bitmap Index
> Scan on idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0) Index
> Cond: ((grupo)::text = 'StatusVisita'::text) SubPlan 2 -> ProjectSet
> (cost=0.28..8.81 rows=100 width=32) -> Index Scan using idxsysvarname on
> sys_var sys_var_1 (cost=0.28..8.29 rows=1 width=9) Index Cond:
> ((name)::text =
> '/Company/StatusDigitacaoReceptivoDescartarAgendados'::text) -> Hash
> (cost=9.81..9.81 rows=100 width=32) -> ProjectSet (cost=0.28..8.81
> rows=100 width=32) -> Index Scan using idxsysvarname on sys_var
> sys_var_2 (cost=0.28..8.29 rows=1 width=9) Index Cond: ((name)::text =
> '/Company/StatusDigitacaoReceptivoAgendados'::text) CTE todos -> Merge
> Right Join (cost=2152.17..2282.99 rows=2504 width=828) Merge Cond:
> ((visitanome.digitacao_id)::integer = (d.digitacao_id)::integer) ->
> Nested Loop Left Join (cost=4.99..56111.59 rows=77463 width=46) ->
> Nested Loop Left Join (cost=4.69..22162.21 rows=77463 width=27) Join
> Filter: ((visitastatus.intkey)::integer = (visitanome.status)::smallint)
> -> Index Scan using idxvisitadigitacao on cad_visita visitanome
> (cost=0.29..3551.70 rows=77463 width=10) -> Materialize
> (cost=4.40..19.43 rows=16 width=21) -> Bitmap Heap Scan on sys_lookup
> visitastatus (cost=4.40..19.35 rows=16 width=21) Recheck Cond:
> ((grupo)::text = 'StatusVisita'::text) -> Bitmap Index Scan on
> idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0) Index Cond:
> ((grupo)::text = 'StatusVisita'::text) -> Index Scan using pkpessoa on
> cad_pessoa pessoanome (cost=0.29..0.44 rows=1 width=27) Index Cond:
> ((pessoa_id)::integer = (visitanome.pessoa_id)::integer) -> Sort
> (cost=2146.94..2148.64 rows=682 width=756) Sort Key: d.digitacao_id ->
> Hash Left Join (cost=1301.36..2114.84 rows=682 width=756) Hash Cond:
> ((d.status)::smallint = (status.intkey)::integer) -> Hash Left Join
> (cost=1281.98..2081.79 rows=682 width=739) Hash Cond:
> ((d.escolaridade)::smallint = (escolaridade.intkey)::integer) -> Hash
> Left Join (cost=1263.51..2053.08 rows=682 width=722) Hash Cond:
> ((fonte.midia)::smallint = (midia.intkey)::integer) -> Hash Left Join
> (cost=1233.51..1959.83 rows=682 width=705) Hash Cond:
> ((d.curso_id)::integer = (curso.curso_id)::integer) -> Nested Loop Left
> Join (cost=1219.91..1944.43 rows=682 width=673) -> Hash Left Join
> (cost=1219.62..1411.81 rows=682 width=650) Hash Cond:
> ((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer) ->
> Hash Left Join (cost=1213.06..1401.72 rows=682 width=639) Hash Cond:
> ((d.digitador_id)::integer = (digitador.account_id)::integer) -> Nested
> Loop (cost=29.52..215.51 rows=682 width=616) -> Hash Join
> (cost=29.24..54.61 rows=7 width=17) Hash Cond:
> ((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer) -> Seq Scan
> on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19) Filter:
> ((ativo)::smallint = 1) -> Hash (cost=29.21..29.21 rows=2 width=4) ->
> Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4)
> Filter: ((descricao)::text = 'Campanhas'::text) -> Index Scan using
> idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227
> width=603) Index Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer)
> -> Hash (cost=1181.56..1181.56 rows=158 width=27) -> Nested Loop Left
> Join (cost=0.29..1181.56 rows=158 width=27) -> Seq Scan on sys_account
> digitador (cost=0.00..4.58 rows=158 width=8) -> Index Scan using
> pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45 rows=1 width=27)
> Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer) ->
> Hash (cost=4.58..4.58 rows=158 width=15) -> Seq Scan on sys_account
> telemarketing (cost=0.00..4.58 rows=158 width=15) -> Index Scan using
> pkpessoa on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27)
> Index Cond: ((pessoa_id)::integer = (d.pesquisador_id)::integer) -> Hash
> (cost=11.60..11.60 rows=160 width=36) -> Seq Scan on col_curso curso
> (cost=0.00..11.60 rows=160 width=36) -> Hash (cost=27.06..27.06 rows=236
> width=23) -> Bitmap Heap Scan on sys_lookup midia (cost=10.11..27.06
> rows=236 width=23) Recheck Cond: ((grupo)::text = 'Midia'::text) ->
> Bitmap Index Scan on idxsyslookupgrupointkey (cost=0.00..10.05 rows=236
> width=0) Index Cond: ((grupo)::text = 'Midia'::text) -> Hash
> (cost=18.34..18.34 rows=10 width=21) -> Bitmap Heap Scan on sys_lookup
> escolaridade (cost=4.36..18.34 rows=10 width=21) Recheck Cond:
> ((grupo)::text = 'Escolaridade'::text) -> Bitmap Index Scan on
> idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0) Index Cond:
> ((grupo)::text = 'Escolaridade'::text) -> Hash (cost=19.21..19.21
> rows=14 width=21) -> Bitmap Heap Scan on sys_lookup status
> (cost=4.39..19.21 rows=14 width=21) Recheck Cond: ((grupo)::text =
> 'StatusDigitacao'::text) -> Bitmap Index Scan on idxsyslookupgrupointkey
> (cost=0.00..4.38 rows=14 width=0) Index Cond: ((grupo)::text =
> 'StatusDigitacao'::text) SubPlan 4 -> CTE Scan on statustrabalhando
> (cost=0.00..0.14 rows=7 width=4) SubPlan 5 -> CTE Scan on
> statusagendados (cost=0.00..0.08 rows=4 width=4) CTE naoatribuidos ->
> CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
> 'Não Atribuido'::text) CTE atribuidos -> CTE Scan on todos todos_1
> (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
> 'Atribuido'::text) CTE trabalhando -> CTE Scan on todos todos_2
> (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
> 'Trabalhando'::text) CTE agendados -> CTE Scan on todos todos_3
> (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
> 'Agendados'::text) CTE descartados -> CTE Scan on todos todos_4
> (cost=0.00..56.34 rows=13 width=1164) Filter: (fluxo =
> 'Descartados'::text) -> Append (cost=1.80..3.98 rows=65 width=1128) ->
> Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13 width=1128) ->
> Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128) -> Sort
> (cost=1.80..1.83 rows=13 width=1160) Sort Key: naoatribuidos.prioridade
> DESC, naoatribuidos.cadastro -> CTE Scan on naoatribuidos
> (cost=0.00..1.56 rows=13 width=1160) -> Subquery Scan on "*SELECT* 2"
> (cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on atribuidos
> (cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 3"
> (cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on trabalhando
> (cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 4"
> (cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on agendados
> (cost=0.00..0.26 rows=13 width=1128) -> Subquery Scan on "*SELECT* 5"
> (cost=0.00..0.39 rows=13 width=1128) -> CTE Scan on descartados
> (cost=0.00..0.26 rows=13 width=1128) *So, how to solve that and mainly
> how to don´t get this recovery mode just running a query ?*
> ------------------------------------------------------------------------
> Sent from the PostgreSQL - general mailing list archive
> <http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at
> Nabble.com.
--
Adrian Klaver
adrian.klaver@aklaver.com
Just to be readable ...
And ... server log has only "the database system is in recovery mode" every
time I run that query.
I have a complex query which puts my server in recovery mode every time I
run it.
I don´t need to say that recovery mode is a situation you don´t want your
server goes to.
If I´m using some subselects I´ll get that situation
with
StatusTrabalhando(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and
Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
StatusAgendados(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey
in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from
sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and
Intkey not in (select unnest(string_to_array(substring(VarValue from
3),$$,$$)) from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4,
Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) ...
But if I run those subselects previously, get result values and put the
results to my statement, then it works.
with
StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$),
StatusDigitacaoReceptivoAgendados(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoAgendados$$),
StatusDigitacaoReceptivoTrabalhando(IntKey) as (select
unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where
Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)
select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados),
(select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from
StatusDigitacaoReceptivoTrabalhando);
Then i get these results (8, 14 and 17) and replace those subselects with
these values and run, now it runs fine.
with
StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusDigitacao$$ and Intkey in (8)),
StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo =
$$StatusVisita$$ and Intkey in (14) and Intkey not in (17)),
Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4,
Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo)
My original query, which worked for some days and then went to recovery mode
was ...
with
StatusTrabalhando(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and
Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$))
from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoTrabalhando$$)),
StatusAgendados(Intkey) as
(select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey
in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from
sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and
Intkey not in (select unnest(string_to_array(substring(VarValue from
3),$$,$$)) from sys_Var where Name =
$$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)),
Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID,
Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo,
Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4,
Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp,
VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao,
Fluxo) as
(select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID,
D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso,
D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro,
D.Email, D.Idade,
D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie,
D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia, D.Status,
Status.Descricao lkStatus, Fonte.Fonte lkFonte, DigitadorPessoa.Apelido
lkDigitador,
Pesquisador.Apelido lkPesquisador, Telemarketing.Login lkTelemarketing,
Escolaridade.Descricao lkEscolaridade, D.Endereco, D.Bairro, D.Cidade,
D.Estado, D.CEP, D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp,
PessoaNome.Nome VisitaNome, VisitaStatus.Descricao VisitaStatus,
D.Comercial, Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case
when Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null
then $$Atribuido$$ when D.Status in (select IntKey from StatusTrabalhando)
then $$Trabalhando$$ when VisitaNome.Status in (select IntKey from
StatusAgendados) then $$Agendados$$ else $$Descartados$$ end Fluxo
from cad_Digitacao D
inner join cad_Fonte Fonte on Fonte.Fonte_ID = D.Fonte_ID
inner join sys_lookup TipoFonte on TipoFonte.intkey = Fonte.TipoFonte
left join sys_Account Telemarketing on Telemarketing.Pessoa_ID =
D.Telemarketing_ID
left join sys_Account Digitador on Digitador.Account_ID = D.Digitador_ID
left join cad_Pessoa DigitadorPessoa on Digitador.Pessoa_ID =
DigitadorPessoa.Pessoa_ID
left join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID =
D.Pesquisador_ID
left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID
left join sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey =
Fonte.Midia
left join sys_Lookup Escolaridade on Escolaridade.Grupo = $$Escolaridade$$
and Escolaridade.IntKey = D.Escolaridade
left join sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and
Status.IntKey = D.Status
left join cad_visita VisitaNome on VisitaNome.Digitacao_ID =
D.Digitacao_ID
left join sys_Lookup VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$
and VisitaStatus.IntKey = VisitaNome.Status
left join cad_Pessoa PessoaNome on PessoaNome.Pessoa_ID =
VisitaNome.Pessoa_ID where TipoFonte.descricao = $$Campanhas$$ and
Fonte.Ativo = 1 ),
NaoAtribuidos as (select * from Todos where Fluxo = $$Não Atribuido$$),
Atribuidos as (select * from Todos where Fluxo = $$Atribuido$$),
Trabalhando as (select * from todos where Fluxo = $$Trabalhando$$),
Agendados as (select * from todos where Fluxo = $$Agendados$$),
Descartados as (select * from todos where Fluxo = $$Descartados$$)
select * from (
select 1 as Ordem, Digitacao_ID, cast(Null as I32) Fonte_ID, cast(Null as
I32) Curso_ID, cast(Null as I32) Digitador_ID, cast(Null as I32)
Pesquisador_ID, Telemarketing_ID, Nome, cast(Null as T100) Curso, Cast(Null
as Telefone) Telefone,
Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null
as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as
T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null as
T50) Extra1,
Cast(Null as T50) Extra2, Cast(Null as T50) Extra3, Cast(Null as T50)
Extra4, Cast(Null as T50) Colegio, Cast(Null as T50) Serie, Cast(Null as
T50) Turma, Cast(Null as C1) Turno, Cast(Null as I16) AnoLetivo, Cast(Null
as T50) Pai,
Cast(Null as T50) Mae, Sequencia, Cast(Null as I16) Status, lkStatus,
lkFonte, Cast(Null as T50) lkDigitador, Cast(Null as T50) lkPesquisador,
lkTelemarketing, Cast(Null as T50) lkEscolaridade, Cast(Null as T50)
Endereco,
Cast(Null as T30) Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado)
Estado, Cast(Null as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as
DataHora) Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16)
WhatsApp,
Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null as
Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem,
Prioridade desc, Cadastro) NA union all
select 2 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID,
Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular,
Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2,
Extra3, Extra4, Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta,
WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Atribuidos union all
select 3 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID,
Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular,
Nascimento, Sexo, Escolaridade,
Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio,
Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus,
lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade,
Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union all
select 4 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID,
Pesquisador_ID, Telemarketing_ID,
Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade,
Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie,
Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte,
lkDigitador,
lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro,
Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo from Agendados union all
select 5 as Ordem, Digitacao_ID,
Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID,
Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro,
Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma,
Turno, AnoLetivo,
Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador,
lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade,
Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome,
VisitaStatus, Comercial, Midia, Fluxo
from Descartados order by Ordem;
And the explain of that query was ...
QUERY PLAN
Sort (cost=2640.68..2640.84 rows=65 width=1128)
Sort Key: "*SELECT* 1".ordem
CTE statustrabalhando
-> Hash Semi Join (cost=15.44..30.40 rows=7 width=4)
Hash Cond: ((sys_lookup.intkey)::text =
(unnest(string_to_array("substring"((sys_var.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21 rows=14
width=4)
Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text = 'StatusDigitacao'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
(cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoTrabalhando'::text)
CTE statusagendados
-> Hash Semi Join (cost=24.51..39.66 rows=4 width=4)
Hash Cond: ((sys_lookup_1.intkey)::text =
(unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3),
','::text))))
-> Bitmap Heap Scan on sys_lookup sys_lookup_1
(cost=13.46..28.53 rows=8 width=4)
Recheck Cond: ((grupo)::text = 'StatusVisita'::text)
Filter: (NOT (hashed SubPlan 2))
-> Bitmap Index Scan on idxsyslookupgrupointkey
(cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text = 'StatusVisita'::text)
SubPlan 2
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_1 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoDescartarAgendados'::text)
-> Hash (cost=9.81..9.81 rows=100 width=32)
-> ProjectSet (cost=0.28..8.81 rows=100 width=32)
-> Index Scan using idxsysvarname on sys_var
sys_var_2 (cost=0.28..8.29 rows=1 width=9)
Index Cond: ((name)::text =
'/Company/StatusDigitacaoReceptivoAgendados'::text)
CTE todos
-> Merge Right Join (cost=2152.17..2282.99 rows=2504 width=828)
Merge Cond: ((visitanome.digitacao_id)::integer =
(d.digitacao_id)::integer)
-> Nested Loop Left Join (cost=4.99..56111.59 rows=77463
width=46)
-> Nested Loop Left Join (cost=4.69..22162.21 rows=77463
width=27)
Join Filter: ((visitastatus.intkey)::integer =
(visitanome.status)::smallint)
-> Index Scan using idxvisitadigitacao on cad_visita
visitanome (cost=0.29..3551.70 rows=77463 width=10)
-> Materialize (cost=4.40..19.43 rows=16 width=21)
-> Bitmap Heap Scan on sys_lookup visitastatus
(cost=4.40..19.35 rows=16 width=21)
Recheck Cond: ((grupo)::text =
'StatusVisita'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0)
Index Cond: ((grupo)::text =
'StatusVisita'::text)
-> Index Scan using pkpessoa on cad_pessoa pessoanome
(cost=0.29..0.44 rows=1 width=27)
Index Cond: ((pessoa_id)::integer =
(visitanome.pessoa_id)::integer)
-> Sort (cost=2146.94..2148.64 rows=682 width=756)
Sort Key: d.digitacao_id
-> Hash Left Join (cost=1301.36..2114.84 rows=682
width=756)
Hash Cond: ((d.status)::smallint =
(status.intkey)::integer)
-> Hash Left Join (cost=1281.98..2081.79 rows=682
width=739)
Hash Cond: ((d.escolaridade)::smallint =
(escolaridade.intkey)::integer)
-> Hash Left Join (cost=1263.51..2053.08
rows=682 width=722)
Hash Cond: ((fonte.midia)::smallint =
(midia.intkey)::integer)
-> Hash Left Join (cost=1233.51..1959.83
rows=682 width=705)
Hash Cond: ((d.curso_id)::integer =
(curso.curso_id)::integer)
-> Nested Loop Left Join
(cost=1219.91..1944.43 rows=682 width=673)
-> Hash Left Join
(cost=1219.62..1411.81 rows=682 width=650)
Hash Cond:
((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer)
-> Hash Left Join
(cost=1213.06..1401.72 rows=682 width=639)
Hash Cond:
((d.digitador_id)::integer = (digitador.account_id)::integer)
-> Nested Loop
(cost=29.52..215.51 rows=682 width=616)
-> Hash
Join (cost=29.24..54.61 rows=7 width=17)
Hash
Cond: ((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer)
->
Seq Scan on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19)
Filter: ((ativo)::smallint = 1)
->
Hash (cost=29.21..29.21 rows=2 width=4)
-> Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4)
Filter: ((descricao)::text = 'Campanhas'::text)
-> Index
Scan using idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227
width=603)
Index
Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer)
-> Hash
(cost=1181.56..1181.56 rows=158 width=27)
-> Nested
Loop Left Join (cost=0.29..1181.56 rows=158 width=27)
->
Seq Scan on sys_account digitador (cost=0.00..4.58 rows=158 width=8)
->
Index Scan using pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45
rows=1 width=27)
Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer)
-> Hash
(cost=4.58..4.58 rows=158 width=15)
-> Seq Scan on
sys_account telemarketing (cost=0.00..4.58 rows=158 width=15)
-> Index Scan using pkpessoa
on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27)
Index Cond:
((pessoa_id)::integer = (d.pesquisador_id)::integer)
-> Hash (cost=11.60..11.60
rows=160 width=36)
-> Seq Scan on col_curso
curso (cost=0.00..11.60 rows=160 width=36)
-> Hash (cost=27.06..27.06 rows=236
width=23)
-> Bitmap Heap Scan on sys_lookup
midia (cost=10.11..27.06 rows=236 width=23)
Recheck Cond: ((grupo)::text =
'Midia'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..10.05 rows=236 width=0)
Index Cond:
((grupo)::text = 'Midia'::text)
-> Hash (cost=18.34..18.34 rows=10 width=21)
-> Bitmap Heap Scan on sys_lookup
escolaridade (cost=4.36..18.34 rows=10 width=21)
Recheck Cond: ((grupo)::text =
'Escolaridade'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0)
Index Cond: ((grupo)::text =
'Escolaridade'::text)
-> Hash (cost=19.21..19.21 rows=14 width=21)
-> Bitmap Heap Scan on sys_lookup status
(cost=4.39..19.21 rows=14 width=21)
Recheck Cond: ((grupo)::text =
'StatusDigitacao'::text)
-> Bitmap Index Scan on
idxsyslookupgrupointkey (cost=0.00..4.38 rows=14 width=0)
Index Cond: ((grupo)::text =
'StatusDigitacao'::text)
SubPlan 4
-> CTE Scan on statustrabalhando (cost=0.00..0.14 rows=7
width=4)
SubPlan 5
-> CTE Scan on statusagendados (cost=0.00..0.08 rows=4
width=4)
CTE naoatribuidos
-> CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Não Atribuido'::text)
CTE atribuidos
-> CTE Scan on todos todos_1 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Atribuido'::text)
CTE trabalhando
-> CTE Scan on todos todos_2 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Trabalhando'::text)
CTE agendados
-> CTE Scan on todos todos_3 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Agendados'::text)
CTE descartados
-> CTE Scan on todos todos_4 (cost=0.00..56.34 rows=13 width=1164)
Filter: (fluxo = 'Descartados'::text)
-> Append (cost=1.80..3.98 rows=65 width=1128)
-> Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13
width=1128)
-> Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128)
-> Sort (cost=1.80..1.83 rows=13 width=1160)
Sort Key: naoatribuidos.prioridade DESC,
naoatribuidos.cadastro
-> CTE Scan on naoatribuidos (cost=0.00..1.56
rows=13 width=1160)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on atribuidos (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on trabalhando (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on agendados (cost=0.00..0.26 rows=13
width=1128)
-> Subquery Scan on "*SELECT* 5" (cost=0.00..0.39 rows=13
width=1128)
-> CTE Scan on descartados (cost=0.00..0.26 rows=13
width=1128)
So, how to solve that and mainly how to don´t get this recovery mode just
running a query ?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 2/3/19 8:32 AM, PegoraroF10 wrote: > Just to be readable ... > > And ... server log has only "the database system is in recovery mode" every > time I run that query. I would assume that is in the log from the restart of the server after it crashed and then restarted in recovery mode. What is in the previous log, the one that was active when the query crashed the server? Also anything relevant from the OS system logs? > > I have a complex query which puts my server in recovery mode every time I > run it. > I don´t need to say that recovery mode is a situation you don´t want your > server goes to. Well actually that is how standby_servers run: https://www.postgresql.org/docs/11/warm-standby.html "... while each standby server operates in continuous recovery mode, reading the WAL files from the primary." Is this server a standby? > If I´m using some subselects I´ll get that situation > > with > StatusTrabalhando(Intkey) as > (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and > Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) > from sys_Var where Name = > $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), > StatusAgendados(Intkey) as > (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey > in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from > sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and > Intkey not in (select unnest(string_to_array(substring(VarValue from > 3),$$,$$)) from sys_Var where Name = > $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), > Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, > Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, > Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, > Colegio, > Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, > lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, > Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, > WhatsApp, > VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, > Fluxo) ... > > But if I run those subselects previously, get result values and put the > results to my statement, then it works. > with > StatusDigitacaoReceptivoDescartarAgendados(Intkey) as (select > unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where > Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$), > StatusDigitacaoReceptivoAgendados(IntKey) as (select > unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where > Name = $$/Company/StatusDigitacaoReceptivoAgendados$$), > StatusDigitacaoReceptivoTrabalhando(IntKey) as (select > unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where > Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$) > select (select IntKey from StatusDigitacaoReceptivoDescartarAgendados), > (select IntKey from StatusDigitacaoReceptivoAgendados), (select IntKey from > StatusDigitacaoReceptivoTrabalhando); > > Then i get these results (8, 14 and 17) and replace those subselects with > these values and run, now it runs fine. > with > StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = > $$StatusDigitacao$$ and Intkey in (8)), > StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = > $$StatusVisita$$ and Intkey in (14) and Intkey not in (17)), > Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, > Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, > Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, > Colegio, > Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, > lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, > Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, > WhatsApp, > VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, > Fluxo) > > My original query, which worked for some days and then went to recovery mode > was ... > with > StatusTrabalhando(Intkey) as > (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and > Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) > from sys_Var where Name = > $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), > StatusAgendados(Intkey) as > (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey > in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from > sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and > Intkey not in (select unnest(string_to_array(substring(VarValue from > 3),$$,$$)) from sys_Var where Name = > $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), > Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, > Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, > Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, > Colegio, > Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, > lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, > Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, > WhatsApp, > VisitaNome, VisitaStatus, Comercial, Midia, Prioridade, DataHoraAlteracao, > Fluxo) as > (select D.Digitacao_ID, D.Fonte_ID, D.Curso_ID, Digitador_ID, > D.Pesquisador_ID, D.Telemarketing_ID, D.Nome, Curso.Descricao Curso, > D.Telefone, D.Celular, D.Nascimento, D.Sexo, D.Escolaridade, D.Cadastro, > D.Email, D.Idade, > D.Obs, D.Extra1, D.Extra2, D.Extra3, D.Extra4, D.Colegio, D.Serie, > D.Turma, D.Turno, D.AnoLetivo, D.Pai, D.Mae, D.Sequencia, D.Status, > Status.Descricao lkStatus, Fonte.Fonte lkFonte, DigitadorPessoa.Apelido > lkDigitador, > Pesquisador.Apelido lkPesquisador, Telemarketing.Login lkTelemarketing, > Escolaridade.Descricao lkEscolaridade, D.Endereco, D.Bairro, D.Cidade, > D.Estado, D.CEP, D.Repetido, D.Impresso, D.Etiqueta, D.WhatsApp, > PessoaNome.Nome VisitaNome, VisitaStatus.Descricao VisitaStatus, > D.Comercial, Midia.Descricao, Midia.LetterKey, D.DataHoraAlteracao, case > when Telemarketing_ID is Null then $$Não Atribuido$$ when D.Status is null > then $$Atribuido$$ when D.Status in (select IntKey from StatusTrabalhando) > then $$Trabalhando$$ when VisitaNome.Status in (select IntKey from > StatusAgendados) then $$Agendados$$ else $$Descartados$$ end Fluxo > from cad_Digitacao D > inner join cad_Fonte Fonte on Fonte.Fonte_ID = D.Fonte_ID > inner join sys_lookup TipoFonte on TipoFonte.intkey = Fonte.TipoFonte > left join sys_Account Telemarketing on Telemarketing.Pessoa_ID = > D.Telemarketing_ID > left join sys_Account Digitador on Digitador.Account_ID = D.Digitador_ID > left join cad_Pessoa DigitadorPessoa on Digitador.Pessoa_ID = > DigitadorPessoa.Pessoa_ID > left join cad_Pessoa Pesquisador on Pesquisador.Pessoa_ID = > D.Pesquisador_ID > left join col_Curso Curso on Curso.Curso_ID = D.Curso_ID > left join sys_Lookup Midia on Midia.Grupo = $$Midia$$ and Midia.IntKey = > Fonte.Midia > left join sys_Lookup Escolaridade on Escolaridade.Grupo = $$Escolaridade$$ > and Escolaridade.IntKey = D.Escolaridade > left join sys_Lookup Status on Status.Grupo = $$StatusDigitacao$$ and > Status.IntKey = D.Status > left join cad_visita VisitaNome on VisitaNome.Digitacao_ID = > D.Digitacao_ID > left join sys_Lookup VisitaStatus on VisitaStatus.Grupo = $$StatusVisita$$ > and VisitaStatus.IntKey = VisitaNome.Status > left join cad_Pessoa PessoaNome on PessoaNome.Pessoa_ID = > VisitaNome.Pessoa_ID where TipoFonte.descricao = $$Campanhas$$ and > Fonte.Ativo = 1 ), > NaoAtribuidos as (select * from Todos where Fluxo = $$Não Atribuido$$), > Atribuidos as (select * from Todos where Fluxo = $$Atribuido$$), > Trabalhando as (select * from todos where Fluxo = $$Trabalhando$$), > Agendados as (select * from todos where Fluxo = $$Agendados$$), > Descartados as (select * from todos where Fluxo = $$Descartados$$) > select * from ( > select 1 as Ordem, Digitacao_ID, cast(Null as I32) Fonte_ID, cast(Null as > I32) Curso_ID, cast(Null as I32) Digitador_ID, cast(Null as I32) > Pesquisador_ID, Telemarketing_ID, Nome, cast(Null as T100) Curso, Cast(Null > as Telefone) Telefone, > Cast(Null as Telefone) Celular, Cast(Null as Data) Nascimento, cast(Null > as Sexo) as Sexo, Cast(Null as I16) Escolaridade, Cadastro, Cast(Null as > T50) Email, Cast(Null as I16) Idade, Cast(Null as Memo) Obs, Cast(Null as > T50) Extra1, > Cast(Null as T50) Extra2, Cast(Null as T50) Extra3, Cast(Null as T50) > Extra4, Cast(Null as T50) Colegio, Cast(Null as T50) Serie, Cast(Null as > T50) Turma, Cast(Null as C1) Turno, Cast(Null as I16) AnoLetivo, Cast(Null > as T50) Pai, > Cast(Null as T50) Mae, Sequencia, Cast(Null as I16) Status, lkStatus, > lkFonte, Cast(Null as T50) lkDigitador, Cast(Null as T50) lkPesquisador, > lkTelemarketing, Cast(Null as T50) lkEscolaridade, Cast(Null as T50) > Endereco, > Cast(Null as T30) Bairro, Cast(Null as T30) Cidade, Cast(Null as Estado) > Estado, Cast(Null as T10) CEP, Cast(Null as I16) Repetido, Cast(Null as > DataHora) Impresso, Cast(Null as DataHora) Etiqueta, Cast(Null as I16) > WhatsApp, > Cast(Null as T250) VisitaNome, Cast(Null as T50) VisitaStatus, Cast(Null as > Telefone) Comercial, Midia, Fluxo from NaoAtribuidos order by Ordem, > Prioridade desc, Cadastro) NA union all > select 2 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, > Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, > Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, > Extra3, Extra4, Colegio, > Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, > lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, > Endereco, Bairro, Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, > WhatsApp, VisitaNome, > VisitaStatus, Comercial, Midia, Fluxo from Atribuidos union all > select 3 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, > Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, > Nascimento, Sexo, Escolaridade, > Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, > Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, > lkFonte, lkDigitador, lkPesquisador, lkTelemarketing, lkEscolaridade, > Endereco, Bairro, > Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, > VisitaStatus, Comercial, Midia, Fluxo from Trabalhando union all > select 4 as Ordem, Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, > Pesquisador_ID, Telemarketing_ID, > Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, > Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, > Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, > lkDigitador, > lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, > Cidade, Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, > VisitaStatus, Comercial, Midia, Fluxo from Agendados union all > select 5 as Ordem, Digitacao_ID, > Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, > Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, > Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, > Turno, AnoLetivo, > Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, > lkPesquisador, lkTelemarketing, lkEscolaridade, Endereco, Bairro, Cidade, > Estado, CEP, Repetido, Impresso, Etiqueta, WhatsApp, VisitaNome, > VisitaStatus, Comercial, Midia, Fluxo > from Descartados order by Ordem; > > And the explain of that query was ... > QUERY PLAN > Sort (cost=2640.68..2640.84 rows=65 width=1128) > Sort Key: "*SELECT* 1".ordem > CTE statustrabalhando > -> Hash Semi Join (cost=15.44..30.40 rows=7 width=4) > Hash Cond: ((sys_lookup.intkey)::text = > (unnest(string_to_array("substring"((sys_var.varvalue)::text, 3), > ','::text)))) > -> Bitmap Heap Scan on sys_lookup (cost=4.39..19.21 rows=14 > width=4) > Recheck Cond: ((grupo)::text = 'StatusDigitacao'::text) > -> Bitmap Index Scan on idxsyslookupgrupointkey > (cost=0.00..4.38 rows=14 width=0) > Index Cond: ((grupo)::text = 'StatusDigitacao'::text) > -> Hash (cost=9.81..9.81 rows=100 width=32) > -> ProjectSet (cost=0.28..8.81 rows=100 width=32) > -> Index Scan using idxsysvarname on sys_var > (cost=0.28..8.29 rows=1 width=9) > Index Cond: ((name)::text = > '/Company/StatusDigitacaoReceptivoTrabalhando'::text) > CTE statusagendados > -> Hash Semi Join (cost=24.51..39.66 rows=4 width=4) > Hash Cond: ((sys_lookup_1.intkey)::text = > (unnest(string_to_array("substring"((sys_var_2.varvalue)::text, 3), > ','::text)))) > -> Bitmap Heap Scan on sys_lookup sys_lookup_1 > (cost=13.46..28.53 rows=8 width=4) > Recheck Cond: ((grupo)::text = 'StatusVisita'::text) > Filter: (NOT (hashed SubPlan 2)) > -> Bitmap Index Scan on idxsyslookupgrupointkey > (cost=0.00..4.40 rows=16 width=0) > Index Cond: ((grupo)::text = 'StatusVisita'::text) > SubPlan 2 > -> ProjectSet (cost=0.28..8.81 rows=100 width=32) > -> Index Scan using idxsysvarname on sys_var > sys_var_1 (cost=0.28..8.29 rows=1 width=9) > Index Cond: ((name)::text = > '/Company/StatusDigitacaoReceptivoDescartarAgendados'::text) > -> Hash (cost=9.81..9.81 rows=100 width=32) > -> ProjectSet (cost=0.28..8.81 rows=100 width=32) > -> Index Scan using idxsysvarname on sys_var > sys_var_2 (cost=0.28..8.29 rows=1 width=9) > Index Cond: ((name)::text = > '/Company/StatusDigitacaoReceptivoAgendados'::text) > CTE todos > -> Merge Right Join (cost=2152.17..2282.99 rows=2504 width=828) > Merge Cond: ((visitanome.digitacao_id)::integer = > (d.digitacao_id)::integer) > -> Nested Loop Left Join (cost=4.99..56111.59 rows=77463 > width=46) > -> Nested Loop Left Join (cost=4.69..22162.21 rows=77463 > width=27) > Join Filter: ((visitastatus.intkey)::integer = > (visitanome.status)::smallint) > -> Index Scan using idxvisitadigitacao on cad_visita > visitanome (cost=0.29..3551.70 rows=77463 width=10) > -> Materialize (cost=4.40..19.43 rows=16 width=21) > -> Bitmap Heap Scan on sys_lookup visitastatus > (cost=4.40..19.35 rows=16 width=21) > Recheck Cond: ((grupo)::text = > 'StatusVisita'::text) > -> Bitmap Index Scan on > idxsyslookupgrupointkey (cost=0.00..4.40 rows=16 width=0) > Index Cond: ((grupo)::text = > 'StatusVisita'::text) > -> Index Scan using pkpessoa on cad_pessoa pessoanome > (cost=0.29..0.44 rows=1 width=27) > Index Cond: ((pessoa_id)::integer = > (visitanome.pessoa_id)::integer) > -> Sort (cost=2146.94..2148.64 rows=682 width=756) > Sort Key: d.digitacao_id > -> Hash Left Join (cost=1301.36..2114.84 rows=682 > width=756) > Hash Cond: ((d.status)::smallint = > (status.intkey)::integer) > -> Hash Left Join (cost=1281.98..2081.79 rows=682 > width=739) > Hash Cond: ((d.escolaridade)::smallint = > (escolaridade.intkey)::integer) > -> Hash Left Join (cost=1263.51..2053.08 > rows=682 width=722) > Hash Cond: ((fonte.midia)::smallint = > (midia.intkey)::integer) > -> Hash Left Join (cost=1233.51..1959.83 > rows=682 width=705) > Hash Cond: ((d.curso_id)::integer = > (curso.curso_id)::integer) > -> Nested Loop Left Join > (cost=1219.91..1944.43 rows=682 width=673) > -> Hash Left Join > (cost=1219.62..1411.81 rows=682 width=650) > Hash Cond: > ((d.telemarketing_id)::integer = (telemarketing.pessoa_id)::integer) > -> Hash Left Join > (cost=1213.06..1401.72 rows=682 width=639) > Hash Cond: > ((d.digitador_id)::integer = (digitador.account_id)::integer) > -> Nested Loop > (cost=29.52..215.51 rows=682 width=616) > -> Hash > Join (cost=29.24..54.61 rows=7 width=17) > Hash > Cond: ((fonte.tipofonte)::smallint = (tipofonte.intkey)::integer) > -> > Seq Scan on cad_fonte fonte (cost=0.00..24.62 rows=136 width=19) > > Filter: ((ativo)::smallint = 1) > -> > Hash (cost=29.21..29.21 rows=2 width=4) > > -> Seq Scan on sys_lookup tipofonte (cost=0.00..29.21 rows=2 width=4) > > Filter: ((descricao)::text = 'Campanhas'::text) > -> Index > Scan using idxdigitacaofonte on cad_digitacao d (cost=0.29..20.72 rows=227 > width=603) > Index > Cond: ((fonte_id)::integer = (fonte.fonte_id)::integer) > -> Hash > (cost=1181.56..1181.56 rows=158 width=27) > -> Nested > Loop Left Join (cost=0.29..1181.56 rows=158 width=27) > -> > Seq Scan on sys_account digitador (cost=0.00..4.58 rows=158 width=8) > -> > Index Scan using pkpessoa on cad_pessoa digitadorpessoa (cost=0.29..7.45 > rows=1 width=27) > > Index Cond: ((digitador.pessoa_id)::integer = (pessoa_id)::integer) > -> Hash > (cost=4.58..4.58 rows=158 width=15) > -> Seq Scan on > sys_account telemarketing (cost=0.00..4.58 rows=158 width=15) > -> Index Scan using pkpessoa > on cad_pessoa pesquisador (cost=0.29..0.78 rows=1 width=27) > Index Cond: > ((pessoa_id)::integer = (d.pesquisador_id)::integer) > -> Hash (cost=11.60..11.60 > rows=160 width=36) > -> Seq Scan on col_curso > curso (cost=0.00..11.60 rows=160 width=36) > -> Hash (cost=27.06..27.06 rows=236 > width=23) > -> Bitmap Heap Scan on sys_lookup > midia (cost=10.11..27.06 rows=236 width=23) > Recheck Cond: ((grupo)::text = > 'Midia'::text) > -> Bitmap Index Scan on > idxsyslookupgrupointkey (cost=0.00..10.05 rows=236 width=0) > Index Cond: > ((grupo)::text = 'Midia'::text) > -> Hash (cost=18.34..18.34 rows=10 width=21) > -> Bitmap Heap Scan on sys_lookup > escolaridade (cost=4.36..18.34 rows=10 width=21) > Recheck Cond: ((grupo)::text = > 'Escolaridade'::text) > -> Bitmap Index Scan on > idxsyslookupgrupointkey (cost=0.00..4.35 rows=10 width=0) > Index Cond: ((grupo)::text = > 'Escolaridade'::text) > -> Hash (cost=19.21..19.21 rows=14 width=21) > -> Bitmap Heap Scan on sys_lookup status > (cost=4.39..19.21 rows=14 width=21) > Recheck Cond: ((grupo)::text = > 'StatusDigitacao'::text) > -> Bitmap Index Scan on > idxsyslookupgrupointkey (cost=0.00..4.38 rows=14 width=0) > Index Cond: ((grupo)::text = > 'StatusDigitacao'::text) > SubPlan 4 > -> CTE Scan on statustrabalhando (cost=0.00..0.14 rows=7 > width=4) > SubPlan 5 > -> CTE Scan on statusagendados (cost=0.00..0.08 rows=4 > width=4) > CTE naoatribuidos > -> CTE Scan on todos (cost=0.00..56.34 rows=13 width=1164) > Filter: (fluxo = 'Não Atribuido'::text) > CTE atribuidos > -> CTE Scan on todos todos_1 (cost=0.00..56.34 rows=13 width=1164) > Filter: (fluxo = 'Atribuido'::text) > CTE trabalhando > -> CTE Scan on todos todos_2 (cost=0.00..56.34 rows=13 width=1164) > Filter: (fluxo = 'Trabalhando'::text) > CTE agendados > -> CTE Scan on todos todos_3 (cost=0.00..56.34 rows=13 width=1164) > Filter: (fluxo = 'Agendados'::text) > CTE descartados > -> CTE Scan on todos todos_4 (cost=0.00..56.34 rows=13 width=1164) > Filter: (fluxo = 'Descartados'::text) > -> Append (cost=1.80..3.98 rows=65 width=1128) > -> Subquery Scan on "*SELECT* 1" (cost=1.80..2.09 rows=13 > width=1128) > -> Subquery Scan on na (cost=1.80..1.96 rows=13 width=1128) > -> Sort (cost=1.80..1.83 rows=13 width=1160) > Sort Key: naoatribuidos.prioridade DESC, > naoatribuidos.cadastro > -> CTE Scan on naoatribuidos (cost=0.00..1.56 > rows=13 width=1160) > -> Subquery Scan on "*SELECT* 2" (cost=0.00..0.39 rows=13 > width=1128) > -> CTE Scan on atribuidos (cost=0.00..0.26 rows=13 > width=1128) > -> Subquery Scan on "*SELECT* 3" (cost=0.00..0.39 rows=13 > width=1128) > -> CTE Scan on trabalhando (cost=0.00..0.26 rows=13 > width=1128) > -> Subquery Scan on "*SELECT* 4" (cost=0.00..0.39 rows=13 > width=1128) > -> CTE Scan on agendados (cost=0.00..0.26 rows=13 > width=1128) > -> Subquery Scan on "*SELECT* 5" (cost=0.00..0.39 rows=13 > width=1128) > -> CTE Scan on descartados (cost=0.00..0.26 rows=13 > width=1128) > > So, how to solve that and mainly how to don´t get this recovery mode just > running a query ? > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
On Sun, Feb 03, 2019 at 10:05:46AM -0800, Adrian Klaver wrote: > On 2/3/19 8:32 AM, PegoraroF10 wrote: >> I have a complex query which puts my server in recovery mode every time I >> run it. >> I don´t need to say that recovery mode is a situation you don´t want your >> server goes to. Do you mean that your server crashes after running a SQL query? That could be a bug. > Well actually that is how standby_servers run: > > https://www.postgresql.org/docs/11/warm-standby.html > > "... while each standby server operates in continuous recovery mode, reading > the WAL files from the primary." > > Is this server a standby? If you could post a self-contained test case, that would be really helpful to see if there is an actual bug. You can obfuscate the schema if need be, as long as the problem can be reproduced that's fine. -- Michael
Attachment
Olá Marcos, > My original query, which worked for some days and then went to > recovery mode > was ... If it was working and then ceased to function, did any of the following occur:- 1) Postgres version changed? 2) OS version changed? 3) Schema changes affecting the tables/views used in your query? I assume that you have a development data base. Can you bump up the log level on that, run the query and see any errors in the log. Cheers, Robert
Nothing was change, Postgres 10, Ubuntu 16.04 and Schema was the same, before and after that problem. Well, that database is replicated and on replicated server that problem doesn´t occur. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 2/4/19 3:13 AM, PegoraroF10 wrote: > Nothing was change, Postgres 10, Ubuntu 16.04 and Schema was the same, before > and after that problem. Well something changed or you would not be having a problem. > Well, that database is replicated and on replicated server that problem > doesn´t occur. Define what you mean by replicated? > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
About replication ... Logical Replication with CREATE PUBLICATION/SUBSCRIPTION. Yes, some DDL commands were ran on that server but none of them were related with that select. Let me explain better. We have a single server with a single database on it. Each customer has its own schema and connects to it to work exclusively on that schema. So, sometimes we add some customer or change something on an old one. But this DDL change we could ran is not related with that schema we are talking. Schemas can have different structures but that schema which puts my server on recovery mode was not changed. Maybe something related happened some days ago. When we start a new customer we add a schema, put all their tables on it and it´s ok. Our server has today 90 schemas and each schema has 100 tables, resulting in 9000 tables. Some days ago we added 5 new customers on same day, so we added 500 tables. Then, when we did that some selects on system tables were very very slow and that was only solved when we did a REINDEX DATABASE. Even REINDEX SYSTEM did not solve. Is this problem related with recovery mode of my server ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 2/4/19 7:33 AM, PegoraroF10 wrote: > About replication ... Logical Replication with CREATE > PUBLICATION/SUBSCRIPTION. > > Yes, some DDL commands were ran on that server but none of them were related > with that select. > Let me explain better. We have a single server with a single database on it. > Each customer has its own schema and connects to it to work exclusively on > that schema. So, sometimes we add some customer or change something on an > old one. But this DDL change we could ran is not related with that schema we > are talking. Schemas can have different structures but that schema which > puts my server on recovery mode was not changed. > > Maybe something related happened some days ago. When we start a new customer > we add a schema, put all their tables on it and it´s ok. Our server has > today 90 schemas and each schema has 100 tables, resulting in 9000 tables. > Some days ago we added 5 new customers on same day, so we added 500 tables. > Then, when we did that some selects on system tables were very very slow and > that was only solved when we did a REINDEX DATABASE. Even REINDEX SYSTEM did > not solve. Is this problem related with recovery mode of my server ? Unsure at the moment as there is not enough information to come to any conclusions. Questions: 1) Do you still have the logs from when you added the 5 new schema and do they show any warnings, errors, etc at or after that time? 2) Are you seeing any warnings, errors, etc in the logs currently? 3) Is the replication for all tables? 4) The exact same query works without a problem on the replicated server, correct? 5) In the Postgres server that is being replicated to are there any log entries that might be of concern? 6) Is it possible to get a stack trace of the crash?: https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > -- Adrian Klaver adrian.klaver@aklaver.com
Olá Marcos, On Mon, 2019-02-04 at 08:33 -0700, PegoraroF10 wrote: > About replication ... Logical Replication with CREATE > PUBLICATION/SUBSCRIPTION. > > Yes, some DDL commands were ran on that server but none of them were > related > with that select. > Let me explain better. We have a single server with a single database > on it. > Each customer has its own schema and connects to it to work > exclusively on > that schema. So, sometimes we add some customer or change something > on an > old one. But this DDL change we could ran is not related with that > schema we > are talking. Schemas can have different structures but that schema > which > puts my server on recovery mode was not changed. > > Maybe something related happened some days ago. When we start a new > customer > we add a schema, put all their tables on it and it´s ok. Our server > has > today 90 schemas and each schema has 100 tables, resulting in 9000 > tables. > Some days ago we added 5 new customers on same day, so we added 500 > tables. > Then, when we did that some selects on system tables were very very > slow and > that was only solved when we did a REINDEX DATABASE. Even REINDEX > SYSTEM did > not solve. Is this problem related with recovery mode of my server ? > > I trust that you saved the output from your "create" scripts. I would find those five log files and see if any errors occurred. Also, you would have to run them on the "subscriber" server first. So, if you "diff'd" the log files for "escola-53" (or whatever) from the publication and the subscriber servers, they should be similar in all respects? I don't know the answer to that question. Chapter 31.4 in the doco is interesting reading. Cheers, Robert
Well, now we have two queries which stops completelly our postgres server.
That problem occurs on 10.6 and 11.1 versions.
On both server the problem is the same.
Linux logs of old crash are:
Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 callbacks 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.
You are running afoul of the Linux OOM killer which is what the kernel uses when it experiences memory pressure.
You could exclude PostgreSQL from the OOM killer, you'll need to look up on how to do that for your particular Linux distro.
We've experienced this with queries that consume more RAM and swap than the server has available.
--
Jeremiah
From: PegoraroF10 <marcos@f10.com.br>
Sent: Friday, February 8, 2019 4:11 PM
To: pgsql-general@postgresql.org
Subject: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL
Sent: Friday, February 8, 2019 4:11 PM
To: pgsql-general@postgresql.org
Subject: [EXTERNAL]Re: Server goes to Recovery Mode when run a SQL
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Well, now we have two queries which stops completelly our postgres server.
That problem occurs on 10.6 and 11.1 versions.
On both server the problem is the same.
Linux logs of old crash are:
Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 callbacks suppressed Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405791] postgres[10195]: segfault at 24 ip 0000555dc6a71cb0 sp 00007ffc5f91db38 error 4 in postgres[555dc69b4000+6db000]
Postgres log of old crash:
2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,3,,2019-02-01 18:31:37 -02,,0,LOG,00000,"server process (PID 10195) was terminated by signal 11: Segmentation fault","Failed process was running: WITH StatusTrabalhando(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusDigitacao$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) AS (SELECT Intkey FROM sys_Lookup WHERE Grupo = $$StatusVisita$$ AND Intkey in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) AND Intkey NOT in (SELECT unnest(string_to_array(substring(VarValue FROM 3),$$,$$)) FROM sys_Var WHERE Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status",,,,,,,,"" 2019-02-01 18:39:53.229 -02,,,1523,,5c54aca9.5f3,4,,2019-02-01 18:31:37 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
Postgres logs of new crash:
2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25625,,2019-01-28 15:19:52 -02,,0,LOG,00000,"server process (PID 10321) was terminated by signal 11: Segmentation fault","Failed process was running: with StatusTrabalhando(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusDigitacao$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoTrabalhando$$)), StatusAgendados(Intkey) as (select Intkey from sys_Lookup where Grupo = $$StatusVisita$$ and Intkey in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoAgendados$$) and Intkey not in (select unnest(string_to_array(substring(VarValue from 3),$$,$$)) from sys_Var where Name = $$/Company/StatusDigitacaoReceptivoDescartarAgendados$$)), Todos(Digitacao_ID, Fonte_ID, Curso_ID, Digitador_ID, Pesquisador_ID, Telemarketing_ID, Nome, Curso, Telefone, Celular, Nascimento, Sexo, Escolaridade, Cadastro, Email, Idade, Obs, Extra1, Extra2, Extra3, Extra4, Colegio, Serie, Turma, Turno, AnoLetivo, Pai, Mae, Sequencia, Status, lkStatus, lkFonte, lkDigitador, lkPesquisador",,,,,,,,"" 2019-02-08 17:21:16.650 -02,,,16321,,5c4f39b8.3fc1,25626,,2019-01-28 15:19:52 -02,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,"" 2019-02-08 17:21:16.650 -02,"postgres","f10db",10138,"74.125.92.65:44342",5c5dcd7d.279a,2,"idle",2019-02-08 16:42:05 -02,107/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.650 -02,"postgres","f10db",9667,"173.194.101.228:63516",5c5dbdee.25c3,2,"idle",2019-02-08 15:35:42 -02,20/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.650 -02,"postgres","f10db",10096,"74.125.92.68:50186",5c5dcd77.2770,2,"idle",2019-02-08 16:41:59 -02,48/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.651 -02,"postgres","f10db",9696,"74.125.115.163:48542",5c5dbe07.25e0,2,"idle",2019-02-08 15:36:07 -02,49/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.652 -02,"postgres","f10db",9597,"192.168.1.111:57271",5c5dbae2.257d,1,"idle",2019-02-08 15:22:42 -02,4/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver" 2019-02-08 17:21:16.652 -02,,,9567,,5c5db777.255f,1,,2019-02-08 15:08:07 -02,1/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.652 -02,"postgres","f10db",9670,"74.125.45.164:58116",5c5dbdf0.25c6,2,"idle",2019-02-08 15:35:44 -02,23/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"" 2019-02-08 17:21:16.652 -02,"postgres","f10db",9611,"177.92.53.2:61645",5c5dbc25.258b,1,"idle",2019-02-08 15:28:05 -02,5/0,0,WARNING,57P02,"terminating connection because of crash of another server process","The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"PostgreSQL JDBC Driver" 2019-02-08 17:21:16.655 -02,,,16321,,5c4f39b8.3fc1,25627,,2019-01-28 15:19:52 -02,,0,LOG,00000,"archiver process (PID 9568) exited with exit code 1",,,,,,,,,"" 2019-02-08 17:21:16.705 -02,,,16321,,5c4f39b8.3fc1,25628,,2019-01-28 15:19:52 -02,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,"" 2019-02-08 17:21:16.934 -02,,,10329,,5c5dd6ac.2859,1,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was interrupted; last known up at 2019-02-08 17:06:37 -02",,,,,,,,,"" 2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,2,,2019-02-08 17:21:16 -02,,0,LOG,00000,"recovered replication state of node 4 to 8F/2913B4C0",,,,,,,,,"" 2019-02-08 17:21:46.478 -02,,,10329,,5c5dd6ac.2859,3,,2019-02-08 17:21:16 -02,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,"" 2019-02-08 17:21:46.650 -02,,,10329,,5c5dd6ac.2859,4,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo starts at EF/3DCF0C10",,,,,,,,,"" 2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,5,,2019-02-08 17:21:16 -02,,0,LOG,00000,"redo done at EF/41B6A618",,,,,,,,,"" 2019-02-08 17:21:48.129 -02,,,10329,,5c5dd6ac.2859,6,,2019-02-08 17:21:16 -02,,0,LOG,00000,"last completed transaction was at log time 2019-02-08 17:21:14.151441-02",,,,,,,,,"" 2019-02-08 17:22:36.479 -02,,,16321,,5c4f39b8.3fc1,25629,,2019-01-28 15:19:52 -02,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,"" 2019-02-08 17:22:36.533 -02,,,10340,,5c5dd6fc.2864,1,,2019-02-08 17:22:36 -02,3/2,0,LOG,00000,"logical replication apply worker for subscription ""sub_google_all"" has started",,,,,,,,,""
Linux Log of new crash, which takes several minutes to stop:
Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres invoked oom-killer: gfp_mask=0x24280ca, order=0, oom_score_adj=0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643123] postgres cpuset=/ mems_allowed=0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643127] CPU: 0 PID: 9399 Comm: postgres Not tainted 4.4.0-138-generic #164-Ubuntu Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643128] Hardware name: Dell Inc. OptiPlex 7010/0773VG, BIOS A25 05/10/2017 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643129] 0000000000000286 02ce01de1fa33dca ffff8800de3cfaf8 ffffffff81404fe3 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643131] ffff8800de3cfcb0 ffff880408d80000 ffff8800de3cfb68 ffffffff8121425e Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643133] 0000000000000015 0000000000000000 ffff8804083f49c0 ffff880408cbd400 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643134] Call Trace: Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643140] [] dump_stack+0x63/0x90 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643144] [] dump_header+0x5a/0x1c5 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643147] [] ? apparmor_capable+0xc4/0x1b0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643149] [] oom_kill_process+0x202/0x3c0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643151] [] out_of_memory+0x219/0x460 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643154] [] __alloc_pages_slowpath.constprop.88+0x943/0xaf0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643156] [] __alloc_pages_nodemask+0x288/0x2a0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643158] [] alloc_pages_vma+0xad/0x250 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643161] [] handle_mm_fault+0x1420/0x1b70 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643164] [] __do_page_fault+0x1a4/0x410 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643165] [] do_page_fault+0x22/0x30 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643169] [] page_fault+0x28/0x30 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643170] Mem-Info: Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] active_anon:3535478 inactive_anon:424526 isolated_anon:32 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] active_file:679 inactive_file:594 isolated_file:0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] unevictable:0 dirty:0 writeback:45 unstable:0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] slab_reclaimable:43397 slab_unreclaimable:8228 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] mapped:529649 shmem:851835 pagetables:26133 bounce:0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643173] free:33778 free_pcp:35 free_cma:0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643175] Node 0 DMA free:15880kB min:64kB low:80kB high:96kB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:15984kB managed:15896kB mlocked:0kB dirty:0kB writeback:0kB mapped:0kB shmem:0kB slab_reclaimable:0kB slab_unreclaimable:16kB kernel_stack:0kB pagetables:0kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:0 all_unreclaimable? yes Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643179] lowmem_reserve[]: 0 3437 15947 15947 15947 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643181] Node 0 DMA32 free:64520kB min:14552kB low:18188kB high:21828kB active_anon:2842836kB inactive_anon:568288kB active_file:640kB inactive_file:504kB unevictable:0kB isolated(anon):0kB isolated(file):0kB present:3640260kB managed:3559444kB mlocked:0kB dirty:0kB writeback:64kB mapped:628652kB shmem:682620kB slab_reclaimable:41856kB slab_unreclaimable:7976kB kernel_stack:736kB pagetables:21384kB unstable:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB writeback_tmp:0kB pages_scanned:7160 all_unreclaimable? yes Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643185] lowmem_reserve[]: 0 0 12510 12510 12510 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643186] Node 0 Normal free:54712kB min:52964kB low:66204kB high:79444kB active_anon:11299076kB inactive_anon:1129816kB active_file:2076kB inactive_file:1872kB unevictable:0kB isolated(anon):128kB isolated(file):0kB present:13074432kB managed:12810364kB mlocked:0kB dirty:0kB writeback:116kB mapped:1489944kB shmem:2724720kB slab_reclaimable:131732kB slab_unreclaimable:24920kB kernel_stack:2480kB pagetables:83148kB unstable:0kB bounce:0kB free_pcp:140kB local_pcp:20kB free_cma:0kB writeback_tmp:0kB pages_scanned:33152 all_unreclaimable? yes Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643190] lowmem_reserve[]: 0 0 0 0 0 Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643192] Node 0 DMA: 0*4kB 1*8kB (U) 0*16kB 0*32kB 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15880kB Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643199] Node 0 DMA32: 2099*4kB (UME) 1396*8kB (UME) 1396*16kB (UME) 469*32kB (UME) 106*64kB (UME) 17*128kB (E) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 65868kB Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643205] Node 0 Normal: 13326*4kB (UEH) 0*8kB 14*16kB (H) 2*32kB (H) 6*64kB (H) 5*128kB (H) 0*256kB 0*512kB 0*1024kB 0*2048kB 0*4096kB = 54616kB Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643211] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643212] 853390 total pagecache pages Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643213] 330 pages in swap cache Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643214] Swap cache stats: add 580602, delete 580272, find 30643927/30685696 Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643215] Free swap = 0kB Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643216] Total swap = 999420kB Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643216] 4182669 pages RAM Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643217] 0 pages HighMem/MovableOnly Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643218] 86243 pages reserved Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643218] 0 pages cma reserved Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643219] 0 pages hwpoisoned Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643220] [ pid ] uid tgid total_vm rss nr_ptes nr_pmds swapents oom_score_adj name Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643223] [ 309] 0 309 10866 2186 25 3 49 0 systemd-journal Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643225] [ 356] 0 356 25742 0 18 3 65 0 lvmetad Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643227] [ 377] 0 377 11394 11 24 3 497 -1000 systemd-udevd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643228] [ 634] 100 634 25081 0 20 3 71 0 systemd-timesyn Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643230] [ 735] 104 735 64098 38 28 3 168 0 rsyslogd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643231] [ 745] 0 745 68967 74 38 3 112 0 accounts-daemon Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643233] [ 749] 0 749 7253 19 20 3 50 0 cron Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643234] [ 751] 0 751 7155 37 18 3 43 0 systemd-logind Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643236] [ 752] 106 752 10722 51 26 3 59 -900 dbus-daemon Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643237] [ 818] 0 818 4892 27 15 3 36 0 irqbalance Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643239] [ 877] 0 877 4030 51 11 3 164 0 dhclient Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643240] [ 922] 0 922 3985 0 13 3 38 0 agetty Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643242] [ 926] 0 926 16377 29 36 4 150 -1000 sshd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643244] [16321] 109 16321 1127906 21773 138 5 306 -900 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643245] [16322] 109 16322 41437 162 63 4 321 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643247] [ 5603] 109 5603 11320 0 26 3 207 0 systemd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643248] [ 5605] 109 5605 15315 18 31 3 455 0 (sd-pam) Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643250] [22681] 109 22681 1130447 520116 2165 8 860 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643251] [22682] 109 22682 1128404 516014 2156 8 362 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643253] [22683] 109 22683 1127906 2732 75 5 316 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643254] [22684] 109 22684 1128158 345 79 5 366 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643256] [22685] 109 22685 41967 171 64 5 313 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643257] [22686] 109 22686 47349 3760 73 5 334 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643259] [22687] 109 22687 1128120 426 79 5 381 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643260] [ 2507] 0 2507 23207 46 52 3 185 0 sshd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643262] [ 2528] 109 2528 23207 60 49 3 178 0 sshd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643264] [ 9079] 109 9079 1193045 562876 2298 8 6844 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643265] [ 9081] 109 9081 1128224 1614 98 6 457 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643267] [26521] 109 26521 1128285 5423 111 8 513 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643268] [26522] 109 26522 1128285 4679 135 8 487 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643270] [ 5194] 109 5194 1129913 16312 287 8 828 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643271] [ 7101] 109 7101 1157957 286907 2223 8 324 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643273] [ 8936] 109 8936 1129795 27817 506 8 248 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643274] [ 8979] 109 8979 1128946 16452 376 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643276] [ 9008] 109 9008 1128850 16359 375 8 266 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643277] [ 9101] 109 9101 1129373 12880 244 8 245 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643279] [ 9328] 0 9328 23207 234 50 3 0 0 sshd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643280] [ 9349] 109 9349 23207 240 49 3 0 0 sshd Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643282] [ 9351] 109 9351 5555 362 16 3 0 0 bash Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643283] [ 9362] 109 9362 6649 320 19 3 0 0 htop Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643285] [ 9365] 109 9365 5554 365 16 3 0 0 bash Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643286] [ 9399] 109 9399 4129624 3008886 6087 21 267 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643288] [ 9438] 109 9438 1128844 16432 381 8 229 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643289] [ 9441] 109 9441 1128801 16306 375 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643291] [ 9443] 109 9443 1129663 21678 433 8 247 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643292] [ 9449] 109 9449 1128833 16359 378 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643294] [ 9452] 109 9452 1128945 16437 377 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643295] [ 9484] 109 9484 1128850 16401 378 8 266 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643296] [ 9485] 109 9485 1128827 16380 377 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643298] [ 9486] 109 9486 1128801 16381 378 8 231 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643299] [ 9487] 109 9487 1128841 16342 376 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643301] [ 9488] 109 9488 1128801 16336 375 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643302] [ 9489] 109 9489 1129799 28325 506 8 248 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643304] [ 9490] 109 9490 1129662 21623 432 8 247 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643305] [ 9491] 109 9491 1128785 16300 373 8 267 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643307] [ 9492] 109 9492 1128945 16445 377 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643308] [ 9493] 109 9493 1128833 16388 377 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643309] [ 9494] 109 9494 1128830 16370 375 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643311] [ 9495] 109 9495 1129018 16529 378 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643312] [ 9496] 109 9496 1128832 16385 376 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643314] [ 9498] 109 9498 1128866 16371 376 8 265 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643315] [ 9547] 109 9547 1132672 63812 490 8 283 0 postgres Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643316] Out of memory: Kill process 9399 (postgres) score 693 or sacrifice child Feb 8 15:06:41 fxReplicationServer kernel: [1363901.643368] Killed process 9399 (postgres) total-vm:16518496kB, anon-rss:11997448kB, file-rss:38096kB Feb 8 17:21:16 fxReplicationServer kernel: [1371977.845728] postgres[10321]: segfault at 10 ip 00005567a6069752 sp 00007ffed70be970 error 4 in postgres[5567a5e1a000+727000] PostgreSQL 10.6 (Ubuntu 10.6-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit PostgreSQL 11.1 (Ubuntu 11.1-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
And this new query which stops the server too but differently because the old one stops immediatelly instead of this, as you can see images.
This query runs for aproximately 5 minutes. See link above with images and logs and you´ll see how memory will grow. Memory use starts with 8gb e grows until use them all. When all memory is in use then it starts to swap. When all swap is allocated then it gets the "out of memory" and stops completelly. You'll see that this query has generate_series, with, recursive and lateral statements. It was bad written because that generate_series generates a enormous quantity of records, probably millions of them. But a wrong query couldn´t stop the entire server, could ?
images and logs are available on: https://drive.google.com/open?id=18zIvkV3ew4aZ1_cxI-EmIPVql7ydvEwi
EXPLAIN ANALYSE
WITH feriados as ( select dia, (sum(repete) > 0) repete from ( select data dia, repete from sys_feriado FeriadoPeriodo where FeriadoPeriodo.repete = 0 and FeriadoPeriodo.tipo in (2, 3) UNION all select to_date(to_char(current_date, 'yyyy-')|| to_char(data, 'mm-dd'), 'yyyy-mm-dd') dia, repete from sys_feriado FeriadoAnual where FeriadoAnual.repete = 1 and FeriadoAnual.tipo in (2, 3) ) feriados group by 1 order by 1
), materias (turma_id, materia_id, materia, sequencia, previsto, dataini, datafim, tempoatividade, minutosaula, minutosrestantes) AS ( select 593, 11091, 'AAC - Ética e Segurança Digital', 9, 120, cast('2019/01/30' as data), cast('2019/01/30' as data), 60, 120, 0 union select 593, 11085, 'AAC - Computação nas Nuvens', 12, 60, cast(null as data), cast(null as data), 60, 120, 60
), aulasporsemana (turma_id, quantidade) as ( select turma_id, count(*) from col_diasaula WHERE turma_id = (select turma_id from materias limit 1) group by 1
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista, aularealizada, tempoatividade, aulasNoDia, dia, minutoaula, minutosassistidos, cargaconteudo, cargarestante) as ( SELECT materias.turma_id, materias.sequencia, materias.materia_id, materias.materia, coalesce(realizada.prevista, 1), realizada.aularealizada, materias.tempoatividade, (realizada.minutosassistidos / materias.tempoatividade), realizada.dia, materias.minutosaula, realizada.minutosassistidos, materias.previsto, coalesce(materias.previsto - (row_number() OVER AulaDaMateria * realizada.minutosassistidos), materias.previsto) restante
FROM materias LEFT JOIN LATERAL ( SELECT true aularealizada, tsrange(col_aula.data, (col_aula.data + (col_aula.tempo|| ' minute')::interval)) dia, 0 prevista, (extract(EPOCH FROM col_aula.tempo) / 60) minutosassistidos FROM col_aula WHERE col_aula.materia_id = materias.materia_id AND col_aula.turma_id = materias.turma_id order by col_aula.data, sequencia ) realizada ON TRUE WINDOW AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST, materias.sequencia, materias.materia_id), AulaDia as (PARTITION BY materias.materia_id, realizada.dia) ORDER BY turma_id, datafim NULLS LAST, dataini NULLS LAST, sequencia, materia_id
)
SELECT
*
FROM ( with recursive aulas as ( SELECT turma_id, aularealizada, coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo irregular, coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) assistido_ate_agora, CASE WHEN prevista = 0 AND NOT (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) OVER aulas_realizar, 0) > cargaconteudo) THEN (cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) OVER aulas_realizar + aulasNoDia) WHEN prevista = 1 THEN (cargaconteudo / tempoatividade) ELSE 0 END aulas, case when prevista = 0 and not (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo) then (cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) over aulas_realizar + aulasNoDia) else 1 END proxima, prevista, upper(dia) ultimadata, conteudo_id, conteudo, cargaconteudo, cargarestante, tempoatividade, dia, minutosassistidos, minutoaula, sequencia FROM assistidas JOIN aulasporsemana USING (turma_id) WINDOW aulas_realizar AS (PARTITION BY conteudo_id) UNION select turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas, proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo, datas.cargarestante, tempoatividade, dia, datas.minutosassistidos, minutoaula, sequencia from aulas c JOIN LATERAL ( select Format('%s week', coalesce(c.aulas, 0)) semanas, false aularealizada, c.conteudo_id, tsrange(generate_series, generate_series + (minutoaula|| ' minute')::interval) diacalculado, cargarestante - (row_number() OVER () * (extract(epoch from col_diasaula.tempoaula) / 60)) cargarestante, (case c.prevista when 1 then row_number() OVER () else 1 end * (extract(epoch from col_diasaula.tempoaula) / c.tempoatividade)) minutosassistidos from generate_series(c.ultimadata - interval '1 day', (c.ultimadata + Format('%s week', coalesce(proxima, 0))::INTERVAL), '1 day') join col_diasaula on col_diasaula.dia = (extract(dow from generate_series) +1) and col_diasaula.turma_id = c.turma_id ) datas on TRUE where datas.conteudo_id = c.conteudo_id and c.aulas is not null and coalesce(c.proxima, -1) >= 0
) select * from aulas
) valores;
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 2/8/19 1:11 PM, PegoraroF10 wrote: > *Well, now we have two queries which stops completelly our postgres server. > That problem occurs on 10.6 and 11.1 versions. > On both server the problem is the same. * > *Linux Log of new crash, which takes several minutes to stop:* > > Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres invoked oom-killer: gfp_mask=0x24280ca, order=0,oom_score_adj=0 So the oom-killer kicked in. See below for some ways to deal with this: https://www.postgresql.org/docs/10/kernel-resources.html > *And this new query which stops the server too but differently because > the old one stops immediatelly instead of this, as you can see images.* > > *This query runs for aproximately 5 minutes. See link above with images > and logs and you´ll see how memory will grow. Memory use starts with 8gb > e grows until use them all. When all memory is in use then it starts to > swap. When all swap is allocated then it gets the "out of memory" and > stops completelly. You'll see that this query has generate_series, with, > recursive and lateral statements. It was bad written because that > generate_series generates a enormous quantity of records, probably > millions of them. But a wrong query couldn´t stop the entire server, > could ?* You run anything(computer related or otherwise) out of resources and bad things are going to happen. -- Adrian Klaver adrian.klaver@aklaver.com
Hi, On Fri, Feb 08, 2019 at 02:11:33PM -0700, PegoraroF10 wrote: > *Well, now we have two queries which stops completelly our postgres server. > That problem occurs on 10.6 and 11.1 versions. > On both server the problem is the same. > Linux logs of old crash are:* > Feb 1 18:39:53 fx-cloudserver kernel: [ 502.405788] show_signal_msg: 5 > callbacks suppressedFeb 1 18:39:53 fx-cloudserver kernel: [ 502.405791] > postgres[10195]: segfault at 24 ip 0000555dc6a71cb0 sp 00007ffc5f91db38 > error 4 in postgres[555dc69b4000+6db000] "segfault" seems to mean you hit a bug, which we'll want more information to diagnose. Could you install debugging symbols ? Ubuntu calls their package postgresql-10-dbg or similar. And start server with coredumps enabled, using pg_ctl -c -D /var/lib/postgresql/10/main (or similar). Then trigger the query and hope to find a core dump in the data directory. Or possibly it'll be processed into /var/crash by apport daemon, depending if that's running and enabled (check /proc/sys/kernel/core_pattern). https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Getting_a_trace_from_a_randomly_crashing_backend > *Linux Log of new crash, which takes several minutes to stop:* > Feb 8 15:06:40 fxReplicationServer kernel: [1363901.643121] postgres > invoked oom-killer: gfp_mask=0x24280ca, order=0, oom_score_adj=0Feb 8 > fxReplicationServer kernel: [1363901.643368] Killed process 9399 (postgres) > total-vm:16518496kB, anon-rss:11997448kB, file-rss:38096kBFeb 8 17:21:16 > fxReplicationServer kernel: [1371977.845728] postgres[10321]: segfault at 10 > ip 00005567a6069752 sp 00007ffed70be970 error 4 in In this case, you ran out of RAM, as you noticed. You should make sure ANALYZE statistics are up to date (including manually ANALYZEing any parent tables). On Sun, Feb 03, 2019 at 09:05:42AM -0700, PegoraroF10 wrote: > I´m using Postgres 10 on ubuntu in a Google VM (8 cores, 32Gb RAM, 250Gb SSD) > and DB has 70GB What is work_mem setting ? You could try running the query with lower work_mem, or check EXPLAIN ANALYZE and try to resolve any bad rowcount estimates. > *This query runs for aproximately 5 minutes. See link above with images and > logs and you´ll see how memory will grow. Memory use starts with 8gb e grows > until use them all. When all memory is in use then it starts to swap. When > all swap is allocated then it gets the "out of memory" and stops > completelly. > on: https://drive.google.com/open?id=18zIvkV3ew4aZ1_cxI-EmIPVql7ydvEwi* It says "Access Denied", perhaps you could send a link to https://explain.depesz.com/ ? Justin
Hi,
On Sat, 2019-02-09 at 15:46 -0600, Justin Pryzby wrote:
> Hi,
>
>
>
> "segfault" seems to mean you hit a bug, which we'll want more
> information to
> diagnose. Could you install debugging symbols ? Ubuntu calls their
> package
> postgresql-10-dbg or similar. And start server with coredumps
> enabled, using
> pg_ctl -c -D /var/lib/postgresql/10/main (or similar). Then trigger
> the query
> and hope to find a core dump in the data directory. Or possibly
> it'll be
> processed into /var/crash by apport daemon, depending if that's
> running and
> enabled (check /proc/sys/kernel/core_pattern).
>
I believe there is a bug. I've examined the query Marcos sent with his
first post and I think the parser should have listed some errors and
not tried to run the query.
I'm probably wrong but consider the following.
Around line 33 of the query:-
tsrange(col_aula.data, (col_aula.data + (col_aula.tempo|| '
minute')::interval)) dia, 0 prevista,
(extract(EPOCH FROM col_aula.tempo) / 60) minutosassistidos
Assuming column col_aula.tempo is of type INTEGER, is NOT NULL and
let's say contains a value of 60, then it parses as
tsrange(col_aula.data, (col_aula.data + (60' minute')::interval))
which would pull a syntax error.
You cannot extract EPOCH from a column that is of type INTEGER. Another
syntax error.
Down around line 87 onwards there are generate_series without any
parameters, and further dubious usage of EPOCH, as well as DOW.
Not having the table definitions is obviously clouding the analysis.
If there is a bug in the parser, then one of the experts will have to
opine about that.
HTH,
Robert
On Sun, Feb 10, 2019 at 03:15:38PM +1100, rob stone wrote: > Down around line 87 onwards there are generate_series without any > parameters, and further dubious usage of EPOCH, as well as DOW. > > Not having the table definitions is obviously clouding the analysis. That seems like the root issue for now. > If there is a bug in the parser, then one of the experts will have to > opine about that. If you actually extract the SQL query and the schema which are used to make the problem reproducible? That would help a bit instead of having to guess based on the sparse information on this thread. -- Michael
Attachment
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 ...
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
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
is a sql FROM VALUES, why do you need tables ? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Wed, 2019-02-13 at 05:32 -0700, PegoraroF10 wrote: > is a sql FROM VALUES, why do you need tables ? > > I can see that, however:- ERROR: relation "col_diasaula" does not exist Position: 7477 [SQL State: 42P01] Is that a table, view or a missing CTE?
sorry, it´s a missing part of the CTE
that constant should be on beginning part of it.
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)
)
, col_diasaula(turma_id, dia, tempoaula) as(
select * from (values(593, 3, time '02:00')
) as x(turma_id, dia, tempoaula)),
), aulasporsemana as (
select * from (values (593,1)) x (turma_id, quantidade)
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista,
aularealizada, tempoatividade, aulasNoDia, dia, minutoaula,
minutosassistidos, cargaconteudo, cargarestante) as (
SELECT
materias.turma_id,
materias.sequencia,
materias.materia_id,
materias.materia,
coalesce(realizada.prevista, 1),
realizada.aularealizada,
materias.tempoatividade,
(realizada.minutosassistidos / materias.tempoatividade),
realizada.dia,
materias.minutosaula,
realizada.minutosassistidos,
materias.previsto,
coalesce(materias.previsto - (row_number() OVER AulaDaMateria *
realizada.minutosassistidos), materias.previsto)
FROM materias
LEFT JOIN LATERAL (
SELECT TRUE, tsrange(col_aula.data, (col_aula.data + ( col_aula.tempo ||
' minute')::INTERVAL)) dia, 0 prevista, (extract(EPOCH FROM col_aula.tempo)
/ 60) minutosassistidos
FROM (VALUES
(593, 11091, TIMESTAMP '2019-01-30 19:00:00', TIME '02:00'),
(593, 11091, '2019-02-06 19:00:00', '01:00')
) col_aula (turma_id, materia_id, data, tempo)
WHERE col_aula.materia_id = materias.materia_id AND col_aula.turma_id =
materias.turma_id
ORDER BY col_aula.data, sequencia
) AS realizada(aularealizada, dia, prevista, minutosassistidos) ON TRUE
WINDOW
AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY
materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST,
materias.sequencia, materias.materia_id),
AulaDia as (PARTITION BY materias.materia_id, realizada.dia)
ORDER BY turma_id, datafim NULLS LAST, dataini NULLS LAST, sequencia,
materia_id
) SELECT * FROM (
with recursive aulas as (
SELECT
turma_id,
aularealizada,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1)
over aulas_realizar, 0) > cargaconteudo irregular,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1)
over aulas_realizar, 0) assistido_ate_agora,
CASE
WHEN prevista = 0 AND NOT (coalesce(coalesce(minutosassistidos, 0) +
lag(minutosassistidos, 1) OVER aulas_realizar, 0) > cargaconteudo) THEN
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) OVER
aulas_realizar + aulasNoDia)
WHEN prevista = 1 THEN
(cargaconteudo / tempoatividade)
ELSE 0
END aulas,
case
when prevista = 0 and not (coalesce(coalesce(minutosassistidos, 0) +
lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo) then
(cargaconteudo / tempoatividade) - (lag(aulasNoDia, 1) over
aulas_realizar + aulasNoDia)
else 1
END proxima,
prevista,
upper(dia) ultimadata,
conteudo_id,
conteudo,
cargaconteudo,
cargarestante,
tempoatividade,
dia,
minutosassistidos,
minutoaula,
sequencia
FROM assistidas
JOIN aulasporsemana USING (turma_id)
WINDOW aulas_realizar AS (PARTITION BY conteudo_id)
UNION
select
turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas,
proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo,
datas.cargarestante, tempoatividade, dia, datas.minutosassistidos,
minutoaula, sequencia
from aulas c
JOIN LATERAL (
select
Format('%s week', coalesce(c.aulas, 0)) semanas,
false aularealizada,
c.conteudo_id,
tsrange(generate_series, generate_series + (minutoaula|| '
minute')::interval) diacalculado,
cargarestante - (row_number() OVER () * (extract(epoch from
col_diasaula.tempoaula) / 60)) cargarestante,
(case c.prevista when 1 then row_number() OVER () else 1 end *
(extract(epoch from col_diasaula.tempoaula) / c.tempoatividade))
minutosassistidos
from
generate_series(c.ultimadata - interval '1 day', (c.ultimadata +
Format('%s week', coalesce(proxima, 0))::INTERVAL), '1 day')
join col_diasaula on col_diasaula.dia = (extract(dow from
generate_series) +1) and col_diasaula.turma_id = c.turma_id
) datas on TRUE
where datas.conteudo_id = c.conteudo_id and c.aulas is not null and
coalesce(c.proxima, -1) >= 0
) select * from aulas
) valores;
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Hi Michael, so sorry for the delay. I did never had that problem again, so
tougth it was solved, but seems not. We have two different SQL which pushs
server to recovery mode. This SQL is the hardest one, because you´ll have to
wait some time to get the error. Just runs this SQL, opens your HTOP and
wait, you´ll see your memory growing and growing until ...
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
But same problem occurs on version 10.
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)
), col_diasaula(turma_id, dia, tempoaula) as(select * from (values(593, 3,
time '02:00')) as x(turma_id, dia, tempoaula)
), 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: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
PegoraroF10 <marcos@f10.com.br> writes: > Hi Michael, so sorry for the delay. I did never had that problem again, so > tougth it was solved, but seems not. We have two different SQL which pushs > server to recovery mode. This SQL is the hardest one, because you´ll have to > wait some time to get the error. Just runs this SQL, opens your HTOP and > wait, you´ll see your memory growing and growing until ... Are you sure this query ever terminates? It looks to me like it just keeps producing tuples indefinitely until you kill it or something runs out of memory. If the response to "out of memory" is a server crash rather than something nicer, you probably need to corral your OOM killer. See https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT regards, tom lane
This is the way you can test to push your server immediatelly to recovery
mode. This one you´ll get the problem instantaneously, differently from the
last one which you have to wait a long time to happen.
A master detail relation with an additional table of configurations. Usually
this master has 20 or 40 thousand records and detail has 10 or 20 times
more. Configuration table is a key-value records, both text which I store
values of any type, just prefixing it with S: for string B: for boolean and
so on.
Tests were done on ...
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
drop table if exists ctr;create table ctr(Contrato_ID integer primary key,
Matricula Date, Status smallint);
drop table if exists rec;create table rec(Receber_ID serial primary key,
Contrato_ID integer, Parcela smallint, TipoRecebimento smallint, Vencimento
Date, Quitacao Date, Valor Numeric, Calculo Numeric, ValorPago Numeric);
drop table if exists var;create table var(Var_ID serial primary key, Name
text, VarValue text);
Populate master detail tables with some random values ...
with Contratos(Contrato_ID, Matricula, Status, Parcelas, ValorParcela,
Quitadas) as (select g, (Current_Date - (trunc(random()*2000)||'
day')::interval)::Date, trunc(random()*9)+1,
greatest(trunc(random()*20)::integer,11), (random()*200+100)::numeric(15,2),
least(trunc(random()*20)::integer,5) from generate_series(1,20000,1) g),
Receber(Contrato_ID, Parcela, TipoRecebimento, Vencimento, Valor) as
(select Contrato_ID, Linha, trunc(random()*10), Matricula + ('1
month'::interval * linha), ValorParcela from Contratos join lateral (select
Linha from generate_series(1,Parcelas,1) Linha) g on true),
insContratos as (insert into CTR select Contrato_ID, Matricula, Status
from Contratos returning *),
insReceber as (insert into REC(Contrato_ID, Parcela, TipoRecebimento,
Vencimento, Quitacao, Valor, Calculo, ValorPago) select R.Contrato_ID,
Parcela, TipoRecebimento, Vencimento, case when Parcela<=Quitadas then
Vencimento else null end, Valor, Valor*.9, case when Parcela<=Quitadas then
Valor else null end from Receber R inner join Contratos using(Contrato_ID)
returning *)
select (select count(*) from insContratos), count(*) from insReceber;
populate configuration table ... (this table usually has 2 or 3 thousand
records but with these ones the problem occurred already)
insert into var(Name, VarValue) select * from (Values
('/Config/StatusVisitaObrigaRetorno','S:2,17'),
('/Config/TemWhatsApp','B:True'),
('/Config/TempoRetornarAtriuido','S:03:00'),
('/Config/TempoRetornarTrabalhando','S:25'),
('/Config/Timezone','S:America/Sao_Paulo'),
('/Config/TipoFonteFunilVendas','I:7'),
('/Config/TipoRecebimentoCancelamento','S:6,7,10'),
('/Config/TipoRecebimentoPadraoCobranca','S:4'),
('/Config/TitularIdadeMinima','F:18'),
('/Config/TreinamentoCaixa','B:True'),
('/Config/TreinamentoNaoAlunos','S:Palestra'),
('/Config/TurmaContrato','B:False')) x;
This one works because I used a CTE
explain (analyze, buffers, verbose, costs) with Vars(VarValue) as (select
string_to_array(substring(VarValue from 3),',')::smallint[] from Var where
Name = '/Config/TipoRecebimentoCancelamento')
select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(min(Vencimento::date) filter (where ((status <> 6) or
(TipoRecebimento = any(VarValue))) and (Quitacao is null)), null),
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(Valor),0),
NullIf(sum(ValorPago),0)
from ctr C left join rec using(Contrato_ID) cross join Vars group by
C.Contrato_ID;
This is the one my server goes to recovery mode.
explain (analyze, buffers, verbose, costs) select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(min(Vencimento::date) filter (where ((status <> 6) or
(TipoRecebimento in (select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)), null),
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)),0),
NullIf(sum(Valor),0),
NullIf(sum(ValorPago),0)
from ctr C left join rec using(Contrato_ID) group by C.Contrato_ID;
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
PegoraroF10 <marcos@f10.com.br> writes:
> This is the way you can test to push your server immediatelly to recovery
> mode. This one you´ll get the problem instantaneously, differently from the
> last one which you have to wait a long time to happen.
When I try this I get
ERROR: operator does not exist: smallint = text
LINE 2: ...eber_ID) filter (where ((status <> 6) or (TipoRecebimento in
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
which makes me wonder whether you've got a buggy custom operator.
regards, tom lane
Ok, it works if I cast TipoRecebimento to text. (TipoRecebimento::text in (select substring(VarValue from 3) from Var where Name = '/Config/TipoRecebimentoCancelamento')) But now, how can I know what custom operator it´s trying to use, because some day I can get in troubles again because that. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Correct, on yours it gives an error message, but on mine it tries to execute and goes to recovery. So, how can I know which custom operator is being used on that comparison ? -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
PegoraroF10 <marcos@f10.com.br> writes:
> So, how can I know which custom operator is being used on that comparison ?
You tell us. I don't know of any common extension that would create
a "smallint = text" operator. (A variant theory is that you didn't
make a new operator, but an implicit cast from smallint to text.
Either way, it almost certainly is a homebrew thing. Or maybe what
you are running is not stock Postgres?)
It might be interesting to try some direct test, like
SELECT 22::smallint = 'foo'::text;
For me that gives the same "operator does not exist: smallint = text"
error, but I bet your database tries to execute it.
regards, tom lane
It works, shouldn´t but works. Results False
And I have created these two operators a long time ago.
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = text,
RIGHTARG = public.i32
);
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = public.i32,
RIGHTARG = text
);
Droped them and works as expected, giving me same message like yours.
DROP OPERATOR public.= (text,public.i32);
DROP OPERATOR public.= (public.i32,text);
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
PegoraroF10 <marcos@f10.com.br> writes:
> And I have created these two operators a long time ago.
> CREATE OPERATOR public.= (
> FUNCTION = public.fntextonumero,
> LEFTARG = text,
> RIGHTARG = public.i32
> );
> CREATE OPERATOR public.= (
> FUNCTION = public.fntextonumero,
> LEFTARG = public.i32,
> RIGHTARG = text
> );
... so, what do those functions look like? And, since there's evidently a
custom type or domain involved, what is i32 exactly?
In short: you have completely failed to provide a self-contained test
case.
regards, tom lane
And the operator function, just to you know, is only a conversion to text
CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
SELECT ftext = finteger::text;
$$;
CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
SELECT ftext = finteger::text;
$$;
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Well, I didn´t know where was the problem exactly, then ...
The entire script to see that problem is
create domain public.i32 as integer;
CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
SELECT ftext = finteger::text;
$$;
CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
RETURNS boolean
LANGUAGE sql STABLE
AS $$
SELECT ftext = finteger::text;
$$;
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = text,
RIGHTARG = public.i32
);
CREATE OPERATOR public.= (
FUNCTION = public.fntextonumero,
LEFTARG = public.i32,
RIGHTARG = text
);
Then that script I´ve sent you this morning.
This way I think you can get the problem. Is this a Postgres problem ? Well,
I created these operators because Firebird accepts comparisons between
numeric and text values, and as we converted from that ... I found a way to
continue comparing them. The problem ocurred now because the IN operator.
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On 2020-Aug-14, PegoraroF10 wrote: > CREATE FUNCTION public.fntextonumero(ftext text, finteger public.i32) How is public.i32 defined? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
PegoraroF10 <marcos@f10.com.br> writes:
> CREATE FUNCTION public.fntextonumero(finteger public.i32, ftext text)
> RETURNS boolean
> LANGUAGE sql STABLE
> AS $$
> SELECT ftext = finteger::text;
> $$;
Huh. The crash goes away if you change that to
SELECT finteger::text = ftext;
It looks like ExecInitSubPlan is just assuming that the outer side
of the hashable comparison condition is on the left, and this
function is confusing it by swapping that around to the right.
Kinda surprising that we never identified that problem before ---
this code's been like this for years.
regards, tom lane