Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtsv3-0005wX-1d for pgsql-general@arkaria.postgresql.org; Wed, 13 Feb 2019 11:41:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtsuz-0004Qo-Nl for pgsql-general@arkaria.postgresql.org; Wed, 13 Feb 2019 11:41:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtsuz-0004QB-EL for pgsql-general@lists.postgresql.org; Wed, 13 Feb 2019 11:41:33 +0000 Received: from n3.nabble.com ([162.255.23.22]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtsus-0000FK-0X for pgsql-general@postgresql.org; Wed, 13 Feb 2019 11:41:33 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id 1B47211FC80BC for ; Wed, 13 Feb 2019 04:41:23 -0700 (MST) Date: Wed, 13 Feb 2019 04:41:23 -0700 (MST) From: PegoraroF10 To: pgsql-general@postgresql.org Message-ID: <1550058083109-0.post@n3.nabble.com> In-Reply-To: <20190212032432.GH1475@paquier.xyz> References: <1549209182255-0.post@n3.nabble.com> <1549209942200-0.post@n3.nabble.com> <1549660293178-0.post@n3.nabble.com> <20190209214651.GP31721@telsasoft.com> <20190212032432.GH1475@paquier.xyz> Subject: Re: Server goes to Recovery Mode when run a SQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_256648_247246891.1550058083109" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_256648_247246891.1550058083109 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable *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=C2=B4t replay it without our en= tire database. Even if we extract just that schema to a new DB it doesn=C2=B4t g= o 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)= , =20 ('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)= , =20 ('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)= , =20 ('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)= , =20 ('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)= , =20 ('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)= , =20 ('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)= , =20 ('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), = =20 ('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, = 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, =20 materias.sequencia, materias.materia_id, materias.materia, =20 coalesce(realizada.prevista, 1), realizada.aularealizada, =20 materias.tempoatividade, (realizada.minutosassistidos / materias.tempoatividade), realizada.dia, materias.minutosaula, =20 realizada.minutosassistidos, materias.previsto, =20 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, sequenci= a=20 ) AS realizada(aularealizada, dia, prevista, minutosassistidos) ON TRUE=20 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, =20 coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) over aulas_realizar, 0) > cargaconteudo irregular, =20 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) = =20 WHEN prevista =3D 1 THEN (cargaconteudo / tempoatividade) E= LSE 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) = =20 else 1 END proxima, prevista, upper(dia) ultimadata, =20 conteudo_id, conteudo, cargaconteudo, cargarestante, =20 tempoatividade, dia, minutosassistidos, minutoaula, =20 sequencia FROM assistidas JOIN aulasporsemana USING (turma_id) = =20 WINDOW aulas_realizar AS (PARTITION BY conteudo_id) UNION select = =20 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 = =20 Format('%s week', coalesce(c.aulas, 0)) semanas, false aularealizada= , =20 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 () els= e 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 a= nd coalesce(c.proxima, -1) >=3D 0) select * from aulas) valores; -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.ht= ml ------=_Part_256648_247246891.1550058083109 Content-Type: text/html; charset=UTF8 Content-Transfer-Encoding: quoted-printable Here is a SQL which will get that recovery mode. You can run it on any d= atabase 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 t= he same problem but immediatelly we couldn=C2=B4t replay it without our ent= ire database. Even if we extract just that schema to a new DB it doesn=C2= =B4t go to recovery mode. We will think a little bit more to create somethi= ng 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', FAL=
SE), ('2014-12-22', FALSE), ('2014-12-23', FALSE),
           ('2014-12-24', FALSE), ('2014-12-26', FALSE), ('2014-12-27', FAL=
SE), ('2014-12-29', FALSE), ('2014-12-30', FALSE),
           ('2014-12-31', FALSE), ('2015-01-02', FALSE), ('2015-01-03', FAL=
SE), ('2015-01-04', FALSE), ('2015-02-16', FALSE),
           ('2015-02-17', FALSE), ('2015-04-03', FALSE), ('2015-04-04', FAL=
SE), ('2015-06-04', FALSE), ('2015-12-18', FALSE),
           ('2015-12-19', FALSE), ('2015-12-21', FALSE), ('2015-12-22', FAL=
SE), ('2015-12-23', FALSE), ('2015-12-24', FALSE),
           ('2015-12-26', FALSE), ('2015-12-28', FALSE), ('2015-12-29', FAL=
SE), ('2015-12-30', FALSE), ('2015-12-31', FALSE),
           ('2016-01-02', FALSE), ('2016-01-04', FALSE), ('2016-01-05', FAL=
SE), ('2016-01-06', FALSE), ('2016-01-07', FALSE),
           ('2016-01-08', FALSE), ('2016-01-09', FALSE), ('2016-02-08', FAL=
SE), ('2016-03-09', FALSE), ('2016-03-25', FALSE),
           ('2016-03-26', FALSE), ('2016-05-26', FALSE), ('2016-12-24', FAL=
SE), ('2016-12-28', FALSE), ('2016-12-29', FALSE),
           ('2016-12-30', FALSE), ('2016-12-31', FALSE), ('2017-01-02', FAL=
SE), ('2017-01-03', FALSE), ('2017-01-04', FALSE),
           ('2017-01-05', FALSE), ('2017-01-06', FALSE), ('2017-01-07', FAL=
SE), ('2017-02-25', FALSE), ('2017-02-27', FALSE),
           ('2017-03-09', FALSE), ('2017-04-14', FALSE), ('2017-04-15', FAL=
SE), ('2017-06-15', FALSE), ('2017-09-30', FALSE),
           ('2017-12-18', FALSE), ('2017-12-19', FALSE), ('2017-12-20', FAL=
SE), ('2017-12-21', FALSE), ('2017-12-22', FALSE),
           ('2017-12-23', FALSE), ('2017-12-26', FALSE), ('2017-12-27', FAL=
SE), ('2017-12-28', FALSE), ('2017-12-29', FALSE),
           ('2017-12-30', FALSE), ('2018-01-02', FALSE), ('2018-01-03', FAL=
SE), ('2018-01-04', FALSE), ('2018-01-05', FALSE),
           ('2018-01-06', FALSE), ('2018-01-07', FALSE), ('2018-02-12', FAL=
SE), ('2018-02-13', FALSE), ('2018-03-30', FALSE),
           ('2018-03-31', FALSE), ('2018-04-30', FALSE), ('2018-05-31', FAL=
SE), ('2018-10-15', FALSE), ('2018-12-18', FALSE),
           ('2018-12-19', FALSE), ('2018-12-20', FALSE), ('2018-12-21', FAL=
SE), ('2018-12-22', FALSE), ('2018-12-24', FALSE),
           ('2018-12-26', FALSE), ('2018-12-27', FALSE), ('2018-12-28', FAL=
SE), ('2018-12-29', FALSE), ('2018-12-31', FALSE),
           ('2019-01-01', TRUE), ('2019-01-02', FALSE), ('2019-01-03', FALS=
E), ('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)
), aulasporsemana as (
   select * from (values (593,1)) x (turma_id, quantidade)
), assistidas (turma_id, sequencia, conteudo_id, conteudo, prevista, aulare=
alizada, tempoatividade, aulasNoDia, dia, minutoaula, minutosassistidos, ca=
rgaconteudo, 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 * realiza=
da.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.temp=
o) / 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.tu=
rma_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, mat=
eria_id
) SELECT * FROM (
  with recursive aulas as (
    SELECT
      turma_id,
      aularealizada,
      coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) o=
ver aulas_realizar, 0) > cargaconteudo irregular,
      coalesce(coalesce(minutosassistidos, 0) + lag(minutosassistidos, 1) o=
ver 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, minutoa=
ula, 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_dia=
saula.tempoaula) / 60)) cargarestante,
        (case c.prevista when 1 then row_number() OVER () else 1 end * (ext=
ract(epoch from col_diasaula.tempoaula) / c.tempoatividade)) minutosassisti=
dos
      from
        generate_series(c.ultimadata - interval '1 day', (c.ultimadata + Fo=
rmat('%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 c=
oalesce(c.proxima, -1) >=3D 0
) select * from aulas
) valores;
=09 =09 =09
Sent from the PostgreSQL - general mailing list archive at Nabble.c= om.
------=_Part_256648_247246891.1550058083109--