err_demo.sql

application/octet-stream

Download

------------------------------------

-- how to reproduce the error:

create database err_demo template=template0 encoding 'UTF8' lc_collate='C';
ALTER DATABASE err_demo SET search_path TO public,archiv;

\c err_demo

\i errdb_noerr_231008.sql

begin;

insert into or_followup
select 
 inum
,inum>>11
from generate_series (1::int,100000) as t (inum)
;

commit;


CREATE INDEX brin_or_followup_archiv_season_id_of ON archiv.or_followup_archiv USING brin (of_season, id_of) WITH (pages_per_range='4');



create index of_archiv_recluster on or_followup_archiv (of_season,id_of);

cluster verbose or_followup_archiv using of_archiv_recluster;

drop index if exists of_archiv_recluster;



explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
with qp_netto as (
select 
72812::int                              as id_of        ,
1.000000::numeric(8,6)                  as fac_to_us    ,
6.9318647425014148::numeric(8,3)        as prfac_netto_1,
0.0::numeric(8,3)                       as prfac_netto_2,
1.000000::numeric(8,6)                  as our_to_us    ,
6.88795000000000000000::numeric(8,3)    as prour_netto_1,
0.0::numeric(8,3)                       as prour_netto_2
)
-- select * from qp_netto;
update  or_followup set
 of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
from qp_netto
where
or_followup.id_of=qp_netto.id_of
and or_followup.of_season=35
;


-- here the error occurs:
-- FEHLER:  invalid perminfoindex 0 in RTE with relid 30512

/*
-- this clears the error.
-- to make it reproducable, you have to recluster or_followup_archiv (the 3 lines above with or_followup_archiv)

-- explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
with qp_netto as (
select 
72812::int                              as id_of        ,
1.000000::numeric(8,6)                  as fac_to_us    ,
6.9318647425014148::numeric(8,3)        as prfac_netto_1,
0.0::numeric(8,3)                       as prfac_netto_2,
1.000000::numeric(8,6)                  as our_to_us    ,
6.88795000000000000000::numeric(8,3)    as prour_netto_1,
0.0::numeric(8,3)                       as prour_netto_2
)
-- select * from qp_netto;
update  or_followup_archiv set
 of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
from qp_netto
where
or_followup_archiv.id_of=qp_netto.id_of
and or_followup_archiv.of_season=35
;

*/