pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedAW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
2+ messages / 2 participants
[nested] [flat]
* AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-24 06:16 Hans Buschmann <buschmann@nidsa.net>
0 siblings, 1 reply; 2+ messages in thread
From: Hans Buschmann @ 2023-10-24 06:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Amit Langote <amitlangote09@gmail.com>; Peter Geoghegan <pg@bowt.ie>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
________________________________
>Thanks! Reproduced it as described on HEAD (although for me, repeating
>the query repeats the error, there's no need to do anything fancy to
>re-arm it). Backtrace looks like
To clear the error (as it is now on production system) you have to execute the update against or_followup_archiv, directly (bypassing the parent table) as shown in
err_demo=#
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=qp_netto.id_of
err_demo-# and or_followup.of_season=35
err_demo-# ;
FEHLER: invalid perminfoindex 0 in RTE with relid 30512
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup_archiv set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup_archiv.id_of=qp_netto.id_of
err_demo-# and or_followup_archiv.of_season=35
err_demo-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Update on or_followup_archiv (cost=3.00..373.13 rows=0 width=0) (actual time=1.680..1.680 rows=0 loops=1)
-> Bitmap Heap Scan on or_followup_archiv (cost=3.00..373.13 rows=1 width=118) (actual time=0.152..0.161 rows=1 loops=1)
Recheck Cond: ((of_season = 35) AND (id_of = 72812))
Rows Removed by Index Recheck: 543
Heap Blocks: lossy=4
-> Bitmap Index Scan on brin_or_followup_archiv_season_id_of (cost=0.00..3.00 rows=542 width=0) (actual time=0.083..0.083 rows=40 loops=1)
Index Cond: ((of_season = 35) AND (id_of = 72812))
Planning Time: 1.456 ms
Execution Time: 1.733 ms
(9 Zeilen)
err_demo=#
err_demo=# explain analyze -- explain analyze verbose -- explain -- select * from ( -- select count(*) from ( -- select length(sel) from (
err_demo-# with qp_netto as (
err_demo(# select
err_demo(# 72812::int as id_of ,
err_demo(# 1.000000::numeric(8,6) as fac_to_us ,
err_demo(# 6.9318647425014148::numeric(8,3) as prfac_netto_1,
err_demo(# 0.0::numeric(8,3) as prfac_netto_2,
err_demo(# 1.000000::numeric(8,6) as our_to_us ,
err_demo(# 6.88795000000000000000::numeric(8,3) as prour_netto_1,
err_demo(# 0.0::numeric(8,3) as prour_netto_2
err_demo(# )
err_demo-# -- select * from qp_netto;
err_demo-# update or_followup set
err_demo-# of_pr1_fac_netto=coalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=coalesce(prfac_netto_1*fac_to_us,0.0)
err_demo-# ,of_pr2_fac_netto=coalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=coalesce(prfac_netto_2*fac_to_us,0.0)
err_demo-# ,of_pr1_our_netto=coalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=coalesce(prour_netto_1*our_to_us,0.0)
err_demo-# ,of_pr2_our_netto=coalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=coalesce(prour_netto_2*our_to_us,0.0)
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=qp_netto.id_of
err_demo-# and or_followup.of_season=35
err_demo-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on or_followup (cost=3.00..373.14 rows=0 width=0) (actual time=0.169..0.169 rows=0 loops=1)
Update on or_followup_archiv or_followup_1
-> Result (cost=3.00..373.14 rows=1 width=122) (actual time=0.147..0.148 rows=1 loops=1)
-> Bitmap Heap Scan on or_followup_archiv or_followup_1 (cost=3.00..373.13 rows=1 width=10) (actual time=0.147..0.147 rows=1 loops=1)
Recheck Cond: ((of_season = 35) AND (id_of = 72812))
Rows Removed by Index Recheck: 831
Heap Blocks: lossy=7
-> Bitmap Index Scan on brin_or_followup_archiv_season_id_of (cost=0.00..3.00 rows=542 width=0) (actual time=0.042..0.042 rows=70 loops=1)
Index Cond: ((of_season = 35) AND (id_of = 72812))
Planning Time: 0.671 ms
Execution Time: 0.201 ms
(11 Zeilen)
err_demo=#
1st query: ERROR
2nd query to or_followup_archiv: Succeeds
then 1st query repeated: SUCCEEDS!!
With this direct update to or_followup_archiv (or an unclustered table) the error disappears.
Hans Buschmann
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
@ 2023-10-24 15:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hans Buschmann <buschmann@nidsa.net>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2023-10-24 15:56 UTC (permalink / raw)
To: Hans Buschmann <buschmann@nidsa.net>; +Cc: Amit Langote <amitlangote09@gmail.com>; Peter Geoghegan <pg@bowt.ie>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hans Buschmann <buschmann@nidsa.net> writes:
> 1st query: ERROR
> 2nd query to or_followup_archiv: Succeeds
> then 1st query repeated: SUCCEEDS!!
> With this direct update to or_followup_archiv (or an unclustered table) the error disappears.
Yeah. After more study I think there's less there than meets the
eye. If the row update manages to be HOT then we don't need to make
any new index entries so we never reach the troublesome code.
In your original reproducer, the CLUSTER step was important because
it left the target tuple in a fully-packed page with no room for a
HOT update on the same page. When playing around with variants or
even re-executing the same query, it matters how much free space
there is on the page containing the target tuple, and that is
affected by all sorts of seemingly-irrelevant actions.
Anyway, we do now have a simple and reliable reproducer, so we can
work on fixing this. Thanks again for the report!
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-10-24 15:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-24 06:16 AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Hans Buschmann <buschmann@nidsa.net>
2023-10-24 15:56 ` 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