From: Hans Buschmann <buschmann@nidsa.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Amit Langote <amitlangote09@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
Date: Tue, 24 Oct 2023 06:16:13 +0000
Message-ID: <b134494499a7481f987955247b65a63b@nidsa.net> (raw)
In-Reply-To: <408285.1698085850@sss.pgh.pa.us>
References: <18147-6fc796538913ee88@postgresql.org>
<CAKFQuwYFq-Q2rxbXXBmdHCAEFm-Pu_qhPAEi-PO+LmjtYN2hhg@mail.gmail.com>
<2796464.1696525626@sss.pgh.pa.us>
<9fdfed847859435e8641e613198be9e3@nidsa.net>
<3319880.1696778678@sss.pgh.pa.us>
<3c9fe737f6aa4ae9b0dc19bffd97e106@nidsa.net>
<408285.1698085850@sss.pgh.pa.us>
________________________________
>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: buschmann@nidsa.net, tgl@sss.pgh.pa.us, amitlangote09@gmail.com, pg@bowt.ie, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
In-Reply-To: <b134494499a7481f987955247b65a63b@nidsa.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox