pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
2+ messages / 2 participants
[nested] [flat]
* AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-08 13:16 Hans Buschmann <buschmann@nidsa.net>
2023-10-08 15:24 ` Re: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Hans Buschmann @ 2023-10-08 13:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
2023-10-08 13:16 AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Hans Buschmann <buschmann@nidsa.net>
@ 2023-10-08 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-10-08 15:24 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hans Buschmann <buschmann@nidsa.net> writes:
> 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.
Hmm. The most likely explanation for the error sometimes appearing
and sometimes not is that it depends on the shape of the selected
query plan, which in turn would depend on current pg_statistics
contents.
Perhaps you could devise some dummy data that would repro the issue
and you could share with us?
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-10-08 15:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-08 13:16 AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Hans Buschmann <buschmann@nidsa.net>
2023-10-08 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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