From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | buschmann(at)nidsa(dot)net |
Subject: | BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx |
Date: | 2023-10-05 14:01:06 |
Message-ID: | 18147-6fc796538913ee88@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 18147
Logged by: Hans Buschmann
Email address: buschmann(at)nidsa(dot)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
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
From | Date | Subject | |
---|---|---|---|
Next Message | Given, Robert A | 2023-10-05 14:19:52 | RE: REFRESH MATERIALIZED VIEW error |
Previous Message | vignesh C | 2023-10-05 10:49:37 | Re: [16+] subscription can end up in inconsistent state |