Date: Wed, 12 Aug 2020 13:47:38 -0700 (MST)
From: PegoraroF10 <marcos@f10.com.br>
To: pgsql-general@postgresql.org
Message-ID: <1597265258319-0.post@n3.nabble.com>
In-Reply-To: <20190204010709.GC1881@paquier.xyz>
References: <1549209182255-0.post@n3.nabble.com>
 <37bc715f-12e2-84ac-6492-88d7b6306777@aklaver.com>
 <1549211574297-0.post@n3.nabble.com>
 <cfcb3858-d169-652c-71a7-5d77f295085a@aklaver.com>
 <20190204010709.GC1881@paquier.xyz>
Subject: Re: Server goes to Recovery Mode when run a SQL
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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=C2=B4ll h=
ave to
wait some time to get the error. Just runs this SQL, opens your HTOP and
wait, you=C2=B4ll see your memory growing and growing until ...=20

PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compile=
d
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 - =C3=89tica e Seguran=C3=A7a Digital', 9, 120,
'2019/01/30'::DATE, '2019/01/30'::DATE, 60, 120, 0),
    (593, 11085, 'AAC - Computa=C3=A7=C3=A3o nas Nuvens', 12, 60, NULL, NUL=
L, 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 =3D materias.materia_id AND col_aula.turma_id=
 =3D
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 =3D 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 =3D 1 THEN
          (cargaconteudo / tempoatividade)
        ELSE 0
      END aulas,
      case
        when prevista =3D 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 =3D (extract(dow from
generate_series) +1) and col_diasaula.turma_id =3D c.turma_id
      ) datas on TRUE
    where datas.conteudo_id =3D c.conteudo_id and c.aulas is not null and
coalesce(c.proxima, -1) >=3D 0
) select * from aulas
) valores;


--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.h=
tml