Re: Server goes to Recovery Mode when run a SQL - Mailing list pgsql-general

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;