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

From: Hans Buschmann <buschmann(at)nidsa(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Amit Langote <amitlangote09(at)gmail(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
Date: 2023-10-24 06:16:13
Message-ID: b134494499a7481f987955247b65a63b@nidsa.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

________________________________

>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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2023-10-24 09:42:28 BUG #18167: cannot create partitioned tables when default_tablespace is set
Previous Message Andrei Lepikhov 2023-10-24 04:25:12 Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower