AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx

________________________________

>Thanks! Reproduced it as described on HEAD (although for me, repeating
>the query repeats the error, there's no need to do anything fancy to
>re-arm it). Backtrace looks like

To clear the error (as it is now on production system) you have to execute the update against or_followup_archiv, directly (bypassing the parent table) as shown in

err_demo=#
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=qp_netto.id_of
err_demo-# and or_followup.of_season=35
err_demo-# ;
FEHLER: invalid perminfoindex 0 in RTE with relid 30512
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup_archiv set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup_archiv.id_of=qp_netto.id_of
err_demo-# and or_followup_archiv.of_season=35
err_demo-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Update on or_followup_archiv (cost=3.00..373.13 rows=0 width=0) (actual time=1.680..1.680 rows=0 loops=1)
-> Bitmap Heap Scan on or_followup_archiv (cost=3.00..373.13 rows=1 width=118) (actual time=0.152..0.161 rows=1 loops=1)
Recheck Cond: ((of_season = 35) AND (id_of = 72812))
Rows Removed by Index Recheck: 543
Heap Blocks: lossy=4
-> Bitmap Index Scan on brin_or_followup_archiv_season_id_of (cost=0.00..3.00 rows=542 width=0) (actual time=0.083..0.083 rows=40 loops=1)
Index Cond: ((of_season = 35) AND (id_of = 72812))
Planning Time: 1.456 ms
Execution Time: 1.733 ms
(9 Zeilen)

err_demo=#
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=qp_netto.id_of
err_demo-# and or_followup.of_season=35
err_demo-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on or_followup (cost=3.00..373.14 rows=0 width=0) (actual time=0.169..0.169 rows=0 loops=1)
Update on or_followup_archiv or_followup_1
-> Result (cost=3.00..373.14 rows=1 width=122) (actual time=0.147..0.148 rows=1 loops=1)
-> Bitmap Heap Scan on or_followup_archiv or_followup_1 (cost=3.00..373.13 rows=1 width=10) (actual time=0.147..0.147 rows=1 loops=1)
Recheck Cond: ((of_season = 35) AND (id_of = 72812))
Rows Removed by Index Recheck: 831
Heap Blocks: lossy=7
-> Bitmap Index Scan on brin_or_followup_archiv_season_id_of (cost=0.00..3.00 rows=542 width=0) (actual time=0.042..0.042 rows=70 loops=1)
Index Cond: ((of_season = 35) AND (id_of = 72812))
Planning Time: 0.671 ms
Execution Time: 0.201 ms
(11 Zeilen)

err_demo=#

1st query: ERROR
2nd query to or_followup_archiv: Succeeds
then 1st query repeated: SUCCEEDS!!

With this direct update to or_followup_archiv (or an unclustered table) the error disappears.

Hans Buschmann