BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18147
Logged by:          Hans Buschmann
Email address:      buschmann@nidsa.net
PostgreSQL version: 16.0
Operating system:   Fedora 38 x86-64 64bit, also on Win64
Description:

We have recently moved our production cluster from pg15.4 to pg16.0

In a long lasting correct case (since about pg 9.6) an update statement now
fails with $subject.

I have simplified the case and the error remains (here shown on windows)

------------------
the query:

-- explain analyze -- explain analyze verbose -- explain -- select * from (
-- select count(*) from ( -- select length(sel) from (
with qp_netto as (
select 
77812::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
;
------------------------
result:


xxxdb=# select version ();
-[ RECORD 1 ]-------------------------------------------------------
version | PostgreSQL 16.0, compiled by Visual C++ build 1935, 64-bit


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

I have found a relating discussion under

https://www.postgresql.org/message-id/flat/CANQ0oxfxBKKTReQgSh_KbL99DqdjfBZTastC0XT2ZZMBkAhTQw%40mail.gmail.com

but could not resolve the problem.

The query is quite simplified.. Perhaps it is good to now, that the table
or_followup has an inherited table or_followup_archiv (= relid 17034) which
is chosen by of_season and has not the same index definitions as
or_followup.

Thank you for looking!

Hans Buschmann