pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
3+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2023-10-24 387f9ed0a0 b1444a09dc 1268e73781 8f4a6b9e4f Fix problems when a plain-inheritance parent table is excluded.
* BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-05 14:01 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-10-05 14:01 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: buschmann@nidsa.net
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%40mai...
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-05 15:12 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2023-10-05 15:12 UTC (permalink / raw)
To: buschmann@nidsa.net; pgsql-bugs@lists.postgresql.org
On Thu, Oct 5, 2023 at 8:05 AM PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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:
>
> 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.
>
>
The simplified query is preferred so long as it produces the error - but
you really should be providing the create table commands to make the entire
script self-contained.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-05 17:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-10-05 17:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: buschmann@nidsa.net; pgsql-bugs@lists.postgresql.org
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The simplified query is preferred so long as it produces the error - but
> you really should be providing the create table commands to make the entire
> script self-contained.
Yeah, it's impossible to reproduce or investigate this without
table definitions ... and I for one don't feel like guessing at
the table details.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-05 17:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-05 14:01 BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx PG Bug reporting form <noreply@postgresql.org>
2023-10-05 15:12 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-05 17:07 ` 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