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

Hello,


For your reference I include a simple dump of a test case database, which executes the queries but does NOT reproduce the error.

This case seems much more complicated then I thought on first view.

The problem arose on the production database after it has been dumped/restore from pg15.4 to pg16.0 and was observed on failing queries from the application.

Many tables in production have an inherited table called xxxtable_archiv, which contains elder data and are not often updated by the application. So the error is seldom.

The normal access is only through the main table like:

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
;

xxxdb-# ;
FEHLER:  invalid perminfoindex 0 in RTE with relid 17034

(relid 17034=or_followup_archiv)

which failed repeatedly on production and a local copy (pg_dump/restore).


When you try to access the xxx_archiv table directly like :

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_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
;

this query succeeds and shows the execution plan!

BUT:

Once this query of the archiv table is run (and updated 1 record) the original query through the main table (without archiv) also succeeds!
So when one update is run successfully, the error is not reproducable any more!

This behavior is preserved through pg_dump/pg_restore of the whole databsase for both succes and failure case.

I have no clue what difference in the dump file would trigger this: On comparison of an sql dump only the updated row is moved at the end of the copy, nothing else changed.


Unfortunatly the provided errdb_noerr does not show the error (due to manually creation steps perhaps).

Hans Buschmann