Re: Server goes to Recovery Mode when run a SQL

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