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 1gtvJn-0004yk-1c for pgsql-general@arkaria.postgresql.org; Wed, 13 Feb 2019 14:15:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtvJl-00046d-Ld for pgsql-general@arkaria.postgresql.org; Wed, 13 Feb 2019 14:15:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtvJl-00046W-9y for pgsql-general@lists.postgresql.org; Wed, 13 Feb 2019 14:15:17 +0000 Received: from n3.nabble.com ([162.255.23.22]) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtvJi-0000fw-3A for pgsql-general@postgresql.org; Wed, 13 Feb 2019 14:15:16 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id 46B1A11FD2335 for ; Wed, 13 Feb 2019 07:15:13 -0700 (MST) Date: Wed, 13 Feb 2019 07:15:13 -0700 (MST) From: PegoraroF10 To: pgsql-general@postgresql.org Message-ID: <1550067313287-0.post@n3.nabble.com> In-Reply-To: <2a2c39fa777dba8bdf59dbefd7e5b7582f67b012.camel@gmail.com> 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> <1550058083109-0.post@n3.nabble.com> <1550061147378-0.post@n3.nabble.com> <2a2c39fa777dba8bdf59dbefd7e5b7582f67b012.camel@gmail.com> 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 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk sorry, it=C2=B4s 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 - =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)),=20 ), 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: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.ht= ml