From: Hans Buschmann <buschmann@nidsa.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
Date: Sun, 8 Oct 2023 13:16:52 +0000
Message-ID: <9fdfed847859435e8641e613198be9e3@nidsa.net> (raw)
In-Reply-To: <2796464.1696525626@sss.pgh.pa.us>
References: <18147-6fc796538913ee88@postgresql.org>
<CAKFQuwYFq-Q2rxbXXBmdHCAEFm-Pu_qhPAEi-PO+LmjtYN2hhg@mail.gmail.com>
<2796464.1696525626@sss.pgh.pa.us>
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
Attachments:
errdb_noerr_231008.sql (application/octet-stream, 4.3K) download
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, david.g.johnston@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
In-Reply-To: <9fdfed847859435e8641e613198be9e3@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