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

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