Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvAiM-003e76-3x for pgsql-bugs@arkaria.postgresql.org; Tue, 24 Oct 2023 06:16:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qvAiJ-003rQH-44 for pgsql-bugs@arkaria.postgresql.org; Tue, 24 Oct 2023 06:16:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvAiI-003rQ2-OT for pgsql-bugs@lists.postgresql.org; Tue, 24 Oct 2023 06:16:27 +0000 Received: from ma03-relay.lansolnet.com ([176.95.46.36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qvAiC-002PVc-17 for pgsql-bugs@lists.postgresql.org; Tue, 24 Oct 2023 06:16:26 +0000 Received: from unknown ([192.168.8.230]) by ma03-relay.lansolnet.com; Tue, 24 Oct 2023 08:16:18 +0200 Received: from MBX200D.cloud4partner.com (192.168.8.233) by MBX200A.cloud4partner.com (192.168.8.230) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.32; Tue, 24 Oct 2023 08:16:14 +0200 Received: from MBX200E.cloud4partner.com (192.168.8.234) by MBX200D.cloud4partner.com (192.168.8.233) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.32; Tue, 24 Oct 2023 08:16:13 +0200 Received: from MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634]) by MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634%13]) with mapi id 15.01.0669.032; Tue, 24 Oct 2023 08:16:13 +0200 From: Hans Buschmann To: Tom Lane CC: Amit Langote , Peter Geoghegan , "David G. Johnston" , "pgsql-bugs@lists.postgresql.org" Subject: AW: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Thread-Topic: AW: AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx Thread-Index: AQHZ95SMlAcyyDepIUuM64bIjWMVCrA7LBuAgAAf8QCABJEsgIAACTEAgBfMsBX///pPAIAA5Ja6 Date: Tue, 24 Oct 2023 06:16:13 +0000 Message-ID: References: <18147-6fc796538913ee88@postgresql.org> ,<2796464.1696525626@sss.pgh.pa.us> <9fdfed847859435e8641e613198be9e3@nidsa.net>,<3319880.1696778678@sss.pgh.pa.us> <3c9fe737f6aa4ae9b0dc19bffd97e106@nidsa.net>,<408285.1698085850@sss.pgh.pa.us> In-Reply-To: <408285.1698085850@sss.pgh.pa.us> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.8.240] Content-Type: multipart/alternative; boundary="_000_b134494499a7481f987955247b65a63bnidsanet_" MIME-Version: 1.0 X-cloud-security-sender: buschmann@nidsa.net X-cloud-security-recipient: pgsql-bugs@lists.postgresql.org X-cloud-security-Virusscan: CLEAN X-cloud-security-disclaimer: This E-Mail was scanned by E-Mailservice on ma03-relay.lansolnet.com with 83882198615A X-cloud-security-connect: unknown[192.168.8.230], TLS=1, IP=192.168.8.230 X-cloud-security: scantime:.8999 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_b134494499a7481f987955247b65a63bnidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ________________________________ >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=3D# err_demo=3D# explain analyze -- explain analyze verbose -- explain -- selec= t * 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=3Dcoalesce(prfac_netto_1,0.0) err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0) err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0) err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0) err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0) err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0) err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0) err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0) err_demo-# from qp_netto err_demo-# where err_demo-# or_followup.id_of=3Dqp_netto.id_of err_demo-# and or_followup.of_season=3D35 err_demo-# ; FEHLER: invalid perminfoindex 0 in RTE with relid 30512 err_demo=3D# explain analyze -- explain analyze verbose -- explain -- selec= t * 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=3Dcoalesce(prfac_netto_1,0.0) err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0) err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0) err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0) err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0) err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0) err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0) err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0) err_demo-# from qp_netto err_demo-# where err_demo-# or_followup_archiv.id_of=3Dqp_netto.id_of err_demo-# and or_followup_archiv.of_season=3D35 err_demo-# ; QUERY= PLAN ---------------------------------------------------------------------------= --------------------------------------------------------------------------- Update on or_followup_archiv (cost=3D3.00..373.13 rows=3D0 width=3D0) (ac= tual time=3D1.680..1.680 rows=3D0 loops=3D1) -> Bitmap Heap Scan on or_followup_archiv (cost=3D3.00..373.13 rows=3D= 1 width=3D118) (actual time=3D0.152..0.161 rows=3D1 loops=3D1) Recheck Cond: ((of_season =3D 35) AND (id_of =3D 72812)) Rows Removed by Index Recheck: 543 Heap Blocks: lossy=3D4 -> Bitmap Index Scan on brin_or_followup_archiv_season_id_of (co= st=3D0.00..3.00 rows=3D542 width=3D0) (actual time=3D0.083..0.083 rows=3D40= loops=3D1) Index Cond: ((of_season =3D 35) AND (id_of =3D 72812)) Planning Time: 1.456 ms Execution Time: 1.733 ms (9 Zeilen) err_demo=3D# err_demo=3D# explain analyze -- explain analyze verbose -- explain -- selec= t * 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=3Dcoalesce(prfac_netto_1,0.0) err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0) err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0) err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0) err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0) err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0) err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0) err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0) err_demo-# from qp_netto err_demo-# where err_demo-# or_followup.id_of=3Dqp_netto.id_of err_demo-# and or_followup.of_season=3D35 err_demo-# ; QU= ERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ Update on or_followup (cost=3D3.00..373.14 rows=3D0 width=3D0) (actual ti= me=3D0.169..0.169 rows=3D0 loops=3D1) Update on or_followup_archiv or_followup_1 -> Result (cost=3D3.00..373.14 rows=3D1 width=3D122) (actual time=3D0.= 147..0.148 rows=3D1 loops=3D1) -> Bitmap Heap Scan on or_followup_archiv or_followup_1 (cost=3D= 3.00..373.13 rows=3D1 width=3D10) (actual time=3D0.147..0.147 rows=3D1 loop= s=3D1) Recheck Cond: ((of_season =3D 35) AND (id_of =3D 72812)) Rows Removed by Index Recheck: 831 Heap Blocks: lossy=3D7 -> Bitmap Index Scan on brin_or_followup_archiv_season_id_o= f (cost=3D0.00..3.00 rows=3D542 width=3D0) (actual time=3D0.042..0.042 row= s=3D70 loops=3D1) Index Cond: ((of_season =3D 35) AND (id_of =3D 72812)) Planning Time: 0.671 ms Execution Time: 0.201 ms (11 Zeilen) err_demo=3D# 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 --_000_b134494499a7481f987955247b65a63bnidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable





>Thanks!  Reproduced it as described on HE= AD (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=3D#
err_demo=3D# 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  &nbs= p;     ,
err_demo(# 1.000000::numeric(8,6)          &n= bsp;       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)          &n= bsp;       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=3Dcoalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0)<= /div>
err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0)<= /div>
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=3Dqp_netto.id_of
err_demo-# and or_followup.of_season=3D35
err_demo-# ;
FEHLER:  invalid perminfoindex 0 in RTE with relid 30512
err_demo=3D# 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  &nbs= p;     ,
err_demo(# 1.000000::numeric(8,6)          &n= bsp;       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)          &n= bsp;       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=3Dcoalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0)<= /div>
err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0)<= /div>
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup_archiv.id_of=3Dqp_netto.id_of
err_demo-# and or_followup_archiv.of_season=3D35
err_demo-# ;
                    =                      = ;                     &nb= sp;       QUERY PLAN           = ;                     &nb= sp;                     &= nbsp;             
----------------------------------------------------------------------= ---------------------------------------------------------------------------= -----
 Update on or_followup_archiv  (cost=3D3.00..373.13 rows=3D0= width=3D0) (actual time=3D1.680..1.680 rows=3D0 loops=3D1)
   ->  Bitmap Heap Scan on or_followup_archiv  = (cost=3D3.00..373.13 rows=3D1 width=3D118) (actual time=3D0.152..0.161 rows= =3D1 loops=3D1)
         Recheck Cond: ((of_season =3D 35) AN= D (id_of =3D 72812))
         Rows Removed by Index Recheck: 543
         Heap Blocks: lossy=3D4
         ->  Bitmap Index Scan on bri= n_or_followup_archiv_season_id_of  (cost=3D0.00..3.00 rows=3D542 width= =3D0) (actual time=3D0.083..0.083 rows=3D40 loops=3D1)
               Index Cond: ((o= f_season =3D 35) AND (id_of =3D 72812))
 Planning Time: 1.456 ms
 Execution Time: 1.733 ms
(9 Zeilen)


err_demo=3D#
err_demo=3D# 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  &nbs= p;     ,
err_demo(# 1.000000::numeric(8,6)          &n= bsp;       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)          &n= bsp;       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=3Dcoalesce(prfac_netto_1,0.0)
err_demo-# ,of_pr1_fac_netusd=3Dcoalesce(prfac_netto_1*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr2_fac_netto=3Dcoalesce(prfac_netto_2,0.0)
err_demo-# ,of_pr2_fac_netusd=3Dcoalesce(prfac_netto_2*fac_to_us,0.0)<= /div>
err_demo-# ,of_pr1_our_netto=3Dcoalesce(prour_netto_1,0.0)
err_demo-# ,of_pr1_our_netusd=3Dcoalesce(prour_netto_1*our_to_us,0.0)<= /div>
err_demo-# ,of_pr2_our_netto=3Dcoalesce(prour_netto_2,0.0)
err_demo-# ,of_pr2_our_netusd=3Dcoalesce(prour_netto_2*our_to_us,0.0)<= /div>
err_demo-# from qp_netto
err_demo-# where
err_demo-# or_followup.id_of=3Dqp_netto.id_of
err_demo-# and or_followup.of_season=3D35
err_demo-# ;
                    =                      = ;                     &nb= sp;          QUERY PLAN       =                     &nbs= p;                     &n= bsp;              
----------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------
 Update on or_followup  (cost=3D3.00..373.14 rows=3D0 width= =3D0) (actual time=3D0.169..0.169 rows=3D0 loops=3D1)
   Update on or_followup_archiv or_followup_1
   ->  Result  (cost=3D3.00..373.14 rows=3D1 wi= dth=3D122) (actual time=3D0.147..0.148 rows=3D1 loops=3D1)
         ->  Bitmap Heap Scan on or_f= ollowup_archiv or_followup_1  (cost=3D3.00..373.13 rows=3D1 width=3D10= ) (actual time=3D0.147..0.147 rows=3D1 loops=3D1)
               Recheck Cond: (= (of_season =3D 35) AND (id_of =3D 72812))
               Rows Removed by= Index Recheck: 831
               Heap Blocks: lo= ssy=3D7
               ->  Bit= map Index Scan on brin_or_followup_archiv_season_id_of  (cost=3D0.00..= 3.00 rows=3D542 width=3D0) (actual time=3D0.042..0.042 rows=3D70 loops=3D1)=
                    =  Index Cond: ((of_season =3D 35) AND (id_of =3D 72812))
 Planning Time: 0.671 ms
 Execution Time: 0.201 ms
(11 Zeilen)


err_demo=3D#

1st query:                &nbs= p;                 ERROR
2nd query to or_followup_archiv: Succeeds
then 1st query repeated:       SUCCEEDS!!

With this direct update to or_followup_a= rchiv (or an unclustered table) the error disappears.

Hans Buschmann
--_000_b134494499a7481f987955247b65a63bnidsanet_--