AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx

Поиск
Список
Период
Сортировка
От Hans Buschmann
Тема AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx
Дата
Msg-id b134494499a7481f987955247b65a63b@nidsa.net
обсуждение исходный текст
Ответ на Re: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
Дерево обсуждения





>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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Andrei Lepikhov
Дата:
Сообщение: Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower
Следующее
От: PG Bug reporting form
Дата:
Сообщение: BUG #18167: cannot create partitioned tables when default_tablespace is set