This is the way you can test to push your server immediatelly to recovery
mode. This one you´ll get the problem instantaneously, differently from the
last one which you have to wait a long time to happen.
A master detail relation with an additional table of configurations. Usually
this master has 20 or 40 thousand records and detail has 10 or 20 times
more. Configuration table is a key-value records, both text which I store
values of any type, just prefixing it with S: for string B: for boolean and
so on.
Tests were done on ...
PostgreSQL 11.5 (Ubuntu 11.5-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
drop table if exists ctr;create table ctr(Contrato_ID integer primary key,
Matricula Date, Status smallint);
drop table if exists rec;create table rec(Receber_ID serial primary key,
Contrato_ID integer, Parcela smallint, TipoRecebimento smallint, Vencimento
Date, Quitacao Date, Valor Numeric, Calculo Numeric, ValorPago Numeric);
drop table if exists var;create table var(Var_ID serial primary key, Name
text, VarValue text);
Populate master detail tables with some random values ...
with Contratos(Contrato_ID, Matricula, Status, Parcelas, ValorParcela,
Quitadas) as (select g, (Current_Date - (trunc(random()*2000)||'
day')::interval)::Date, trunc(random()*9)+1,
greatest(trunc(random()*20)::integer,11), (random()*200+100)::numeric(15,2),
least(trunc(random()*20)::integer,5) from generate_series(1,20000,1) g),
Receber(Contrato_ID, Parcela, TipoRecebimento, Vencimento, Valor) as
(select Contrato_ID, Linha, trunc(random()*10), Matricula + ('1
month'::interval * linha), ValorParcela from Contratos join lateral (select
Linha from generate_series(1,Parcelas,1) Linha) g on true),
insContratos as (insert into CTR select Contrato_ID, Matricula, Status
from Contratos returning *),
insReceber as (insert into REC(Contrato_ID, Parcela, TipoRecebimento,
Vencimento, Quitacao, Valor, Calculo, ValorPago) select R.Contrato_ID,
Parcela, TipoRecebimento, Vencimento, case when Parcela<=Quitadas then
Vencimento else null end, Valor, Valor*.9, case when Parcela<=Quitadas then
Valor else null end from Receber R inner join Contratos using(Contrato_ID)
returning *)
select (select count(*) from insContratos), count(*) from insReceber;
populate configuration table ... (this table usually has 2 or 3 thousand
records but with these ones the problem occurred already)
insert into var(Name, VarValue) select * from (Values
('/Config/StatusVisitaObrigaRetorno','S:2,17'),
('/Config/TemWhatsApp','B:True'),
('/Config/TempoRetornarAtriuido','S:03:00'),
('/Config/TempoRetornarTrabalhando','S:25'),
('/Config/Timezone','S:America/Sao_Paulo'),
('/Config/TipoFonteFunilVendas','I:7'),
('/Config/TipoRecebimentoCancelamento','S:6,7,10'),
('/Config/TipoRecebimentoPadraoCobranca','S:4'),
('/Config/TitularIdadeMinima','F:18'),
('/Config/TreinamentoCaixa','B:True'),
('/Config/TreinamentoNaoAlunos','S:Palestra'),
('/Config/TurmaContrato','B:False')) x;
This one works because I used a CTE
explain (analyze, buffers, verbose, costs) with Vars(VarValue) as (select
string_to_array(substring(VarValue from 3),',')::smallint[] from Var where
Name = '/Config/TipoRecebimentoCancelamento')
select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Vencimento < current_date) and (Quitacao is null)),0),
NullIf(min(Vencimento::date) filter (where ((status <> 6) or
(TipoRecebimento = any(VarValue))) and (Quitacao is null)), null),
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento =
any(VarValue))) and (Quitacao is null)),0),
NullIf(sum(Valor),0),
NullIf(sum(ValorPago),0)
from ctr C left join rec using(Contrato_ID) cross join Vars group by
C.Contrato_ID;
This is the one my server goes to recovery mode.
explain (analyze, buffers, verbose, costs) select C.contrato_id,
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(sum(calculo) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Vencimento < current_date) and
(Quitacao is null)),0),
NullIf(min(Vencimento::date) filter (where ((status <> 6) or
(TipoRecebimento in (select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)), null),
NullIf(count(Receber_ID) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)),0),
NullIf(sum(valor) filter (where ((status <> 6) or (TipoRecebimento in
(select substring(VarValue from 3) from Var where Name =
'/Config/TipoRecebimentoCancelamento'))) and (Quitacao is null)),0),
NullIf(sum(Valor),0),
NullIf(sum(ValorPago),0)
from ctr C left join rec using(Contrato_ID) group by C.Contrato_ID;
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
