*Here is a SQL which will get that recovery mode. You can run it on any
database because we created it with FROM VALUES, so ...*
But this one is that one which grows and grows memory use until all memory
and swap space are gone, so problem occurs. That other SQL which gives us
the same problem but immediatelly we couldn´t replay it without our entire
database. Even if we extract just that schema to a new DB it doesn´t go to
recovery mode. We will think a little bit more to create something you can
test.
with feriados as ( SELECT dia, repete FROM ( VALUES
('2014-10-11'::DATE, FALSE), ('2014-10-13', FALSE), ('2014-10-14', FALSE),
('2014-10-15', FALSE), ('2014-10-16', FALSE), ('2014-10-17',
FALSE), ('2014-12-19', FALSE), ('2014-12-20', FALSE), ('2014-12-22', FALSE),
('2014-12-23', FALSE), ('2014-12-24', FALSE), ('2014-12-26',
FALSE), ('2014-12-27', FALSE), ('2014-12-29', FALSE), ('2014-12-30', FALSE),
('2014-12-31', FALSE), ('2015-01-02', FALSE), ('2015-01-03', FALSE),
('2015-01-04', FALSE), ('2015-02-16', FALSE), ('2015-02-17',
FALSE), ('2015-04-03', FALSE), ('2015-04-04', FALSE), ('2015-06-04', FALSE),
('2015-12-18', FALSE), ('2015-12-19', FALSE), ('2015-12-21',
FALSE), ('2015-12-22', FALSE), ('2015-12-23', FALSE), ('2015-12-24', FALSE),
('2015-12-26', FALSE), ('2015-12-28', FALSE), ('2015-12-29', FALSE),
('2015-12-30', FALSE), ('2015-12-31', FALSE), ('2016-01-02',
FALSE), ('2016-01-04', FALSE), ('2016-01-05', FALSE), ('2016-01-06', FALSE),
('2016-01-07', FALSE), ('2016-01-08', FALSE), ('2016-01-09',
FALSE), ('2016-02-08', FALSE), ('2016-03-09', FALSE), ('2016-03-25', FALSE),
('2016-03-26', FALSE), ('2016-05-26', FALSE), ('2016-12-24', FALSE),
('2016-12-28', FALSE), ('2016-12-29', FALSE), ('2016-12-30',
FALSE), ('2016-12-31', FALSE), ('2017-01-02', FALSE), ('2017-01-03', FALSE),
('2017-01-04', FALSE), ('2017-01-05', FALSE), ('2017-01-06',
FALSE), ('2017-01-07', FALSE), ('2017-02-25', FALSE), ('2017-02-27', FALSE),
('2017-03-09', FALSE), ('2017-04-14', FALSE), ('2017-04-15', FALSE),
('2017-06-15', FALSE), ('2017-09-30', FALSE), ('2017-12-18',
FALSE), ('2017-12-19', FALSE), ('2017-12-20', FALSE), ('2017-12-21', FALSE),
('2017-12-22', FALSE), ('2017-12-23', FALSE), ('2017-12-26',
FALSE), ('2017-12-27', FALSE), ('2017-12-28', FALSE), ('2017-12-29', FALSE),
('2017-12-30', FALSE), ('2018-01-02', FALSE), ('2018-01-03', FALSE),
('2018-01-04', FALSE), ('2018-01-05', FALSE), ('2018-01-06',
FALSE), ('2018-01-07', FALSE), ('2018-02-12', FALSE), ('2018-02-13', FALSE),
('2018-03-30', FALSE), ('2018-03-31', FALSE), ('2018-04-30',
FALSE), ('2018-05-31', FALSE), ('2018-10-15', FALSE), ('2018-12-18', FALSE),
('2018-12-19', FALSE), ('2018-12-20', FALSE), ('2018-12-21', FALSE),
('2018-12-22', FALSE), ('2018-12-24', FALSE), ('2018-12-26',
FALSE), ('2018-12-27', FALSE), ('2018-12-28', FALSE), ('2018-12-29', FALSE),
('2018-12-31', FALSE), ('2019-01-01', TRUE), ('2019-01-02',
FALSE), ('2019-01-03', FALSE), ('2019-01-04', FALSE), ('2019-01-05', FALSE),
('2019-01-07', FALSE), ('2019-02-09', TRUE), ('2019-03-09', TRUE),
('2019-04-21', TRUE), ('2019-05-01', TRUE), ('2019-09-07', TRUE),
('2019-10-12', TRUE), ('2019-11-02', TRUE), ('2019-11-15', TRUE),
('2019-12-19', TRUE), ('2019-12-20', TRUE), ('2019-12-21', TRUE),
('2019-12-22', TRUE), ('2019-12-23', TRUE), ('2019-12-25', TRUE),
('2019-12-26', TRUE), ('2019-12-27', TRUE) ) x (dia, repete)),
materias as ( SELECT * from (VALUES (593, 11091, 'AAC - Ética e
Segurança Digital', 9, 120, '2019/01/30'::DATE, '2019/01/30'::DATE, 60, 120,
0), (593, 11085, 'AAC - Computação nas Nuvens', 12, 60, NULL, NULL, 60,
120, 60) ) x (turma_id, materia_id, materia, sequencia, previsto, dataini,
datafim, tempoatividade, minutosaula, minutosrestantes)), aulasporsemana as
( select * from (values (593,1)) x (turma_id, quantidade)), assistidas
(turma_id, sequencia, conteudo_id, conteudo, prevista, aularealizada,
tempoatividade, aulasNoDia, dia, minutoaula, minutosassistidos,
cargaconteudo, cargarestante) as ( SELECT materias.turma_id,
materias.sequencia, materias.materia_id, materias.materia,
coalesce(realizada.prevista, 1), realizada.aularealizada,
materias.tempoatividade, (realizada.minutosassistidos /
materias.tempoatividade), realizada.dia, materias.minutosaula,
realizada.minutosassistidos, materias.previsto,
coalesce(materias.previsto - (row_number() OVER AulaDaMateria *
realizada.minutosassistidos), materias.previsto) FROM materias LEFT JOIN
LATERAL ( SELECT TRUE, tsrange(col_aula.data, (col_aula.data + (
col_aula.tempo || ' minute')::INTERVAL)) dia, 0 prevista, (extract(EPOCH
FROM col_aula.tempo) / 60) minutosassistidos FROM (VALUES (593,
11091, TIMESTAMP '2019-01-30 19:00:00', TIME '02:00'), (593, 11091,
'2019-02-06 19:00:00', '01:00') ) col_aula (turma_id, materia_id, data,
tempo) WHERE col_aula.materia_id = materias.materia_id AND
col_aula.turma_id = materias.turma_id ORDER BY col_aula.data, sequencia
) AS realizada(aularealizada, dia, prevista, minutosassistidos) ON TRUE
WINDOW AulaDaMateria as (PARTITION BY materias.materia_id ORDER BY
materias.turma_id, materias.datafim NULLS LAST, materias.dataini NULLS LAST,
materias.sequencia, materias.materia_id), AulaDia as (PARTITION BY
materias.materia_id, realizada.dia) ORDER BY turma_id, datafim NULLS LAST,
dataini NULLS LAST, sequencia, materia_id) SELECT * FROM ( with recursive
aulas as ( SELECT turma_id, aularealizada,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over
aulas_realizar, 0) > cargaconteudo irregular,
coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over
aulas_realizar, 0) assistido_ate_agora, CASE WHEN prevista = 0
AND NOT (coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1)
OVER aulas_realizar, 0) > cargaconteudo) THEN (cargaconteudo /
tempoatividade) - (lag(aulasNoDia, 1) OVER aulas_realizar + aulasNoDia)
WHEN prevista = 1 THEN (cargaconteudo / tempoatividade) ELSE
0 END aulas, case when prevista = 0 and not
(coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over
aulas_realizar, 0) > cargaconteudo) then (cargaconteudo /
tempoatividade) - (lag(aulasNoDia, 1) over aulas_realizar + aulasNoDia)
else 1 END proxima, prevista, upper(dia) ultimadata,
conteudo_id, conteudo, cargaconteudo, cargarestante,
tempoatividade, dia, minutosassistidos, minutoaula,
sequencia FROM assistidas JOIN aulasporsemana USING (turma_id)
WINDOW aulas_realizar AS (PARTITION BY conteudo_id) UNION select
turma_id, datas.aularealizada, irregular, assistido_ate_agora, aulas,
proxima, prevista, ultimadata, datas.conteudo_id, conteudo, cargaconteudo,
datas.cargarestante, tempoatividade, dia, datas.minutosassistidos,
minutoaula, sequencia from aulas c JOIN LATERAL ( select
Format('%s week', coalesce(c.aulas, 0)) semanas, false aularealizada,
c.conteudo_id, tsrange(generate_series, generate_series +
(minutoaula|| ' minute')::interval) diacalculado, cargarestante -
(row_number() OVER () * (extract(epoch from col_diasaula.tempoaula) / 60))
cargarestante, (case c.prevista when 1 then row_number() OVER () else
1 end * (extract(epoch from col_diasaula.tempoaula) / c.tempoatividade))
minutosassistidos from generate_series(c.ultimadata - interval
'1 day', (c.ultimadata + Format('%s week', coalesce(proxima, 0))::INTERVAL),
'1 day') join col_diasaula on col_diasaula.dia = (extract(dow from
generate_series) +1) and col_diasaula.turma_id = c.turma_id ) datas on
TRUE where datas.conteudo_id = c.conteudo_id and c.aulas is not null and
coalesce(c.proxima, -1) >= 0) select * from aulas) valores;
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
