Feedback-ID: ia2694551:Fastmail
Date: Tue, 28 Mar 2023 20:19:30 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: "variable not found in subplan target list"
Message-ID: <20230328181930.s7xbr326635y4sfc@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <20230328112248.6as34mlx5sr4kltg@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/20230328181930.s7xbr326635y4sfc%40alvherre.pgsql>
Precedence: bulk

So I'm back home and found a couple more weird errors in the log:

MERGE INTO public.idxpart2 as target_0
        USING (select  1 
                from 
                public.xmltest2 as ref_0
                inner join public.prt1_l_p1 as sample_0
                inner join fkpart4.droppk as ref_1
                on (sample_0.a = ref_1.a )
                on (true)
                limit 50) as subq_0
        left join information_schema.transforms as ref_2
        left join public.transition_table_status as sample_1
        on (ref_2.transform_type is not NULL)
        on (true)
        ON target_0.a = sample_1.level 
WHEN MATCHED
        THEN UPDATE set a = target_0.a;
ERROR:  mismatching PartitionPruneInfo found at part_prune_index 0
DETALLE:  plan node relids (b 1), pruneinfo relids (b 36)

This one is probably my fault, will look later.


select  
      pg_catalog.pg_stat_get_buf_fsync_backend() as c9 
    from 
      public.tenk2 as ref_0
    where (ref_0.stringu2 is NULL) 
      and (EXISTS (
        select  1 from fkpart5.fk1 as ref_1
          where pg_catalog.current_date() < (select pg_catalog.max(filler3) from public.mcv_lists))) ;

ERROR:  subplan "InitPlan 1 (returns $1)" was not initialized
CONTEXTO:  parallel worker


select 1 as c0
    from 
      (select  
              subq_0.c9 as c5, 
              subq_0.c8 as c9
            from 
              public.iso8859_5_inputs as ref_0,
              lateral (select  
                    ref_1.ident as c2, 
                    ref_0.description as c8, 
                    ref_1.used_bytes as c9
                  from 
                    pg_catalog.pg_backend_memory_contexts as ref_1
                  where true
                      ) as subq_0
            where subq_0.c2 is not NULL) as subq_1
        inner join pg_catalog.pg_class as sample_0
        on (subq_1.c5 = public.int8alias1in(
              cast(case when subq_1.c9 is not NULL then null end
                 as cstring)))
    where true;
ERROR:  could not find commutator for operator 53286


There were quite a few of those "variable not found" ones, both
mentioning singular "targetlist" and others that said "targetlists".  I
reran them with your patch and they no longer error out, so I guess it's
all the same bug.


-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I must say, I am absolutely impressed with what pgsql's implementation of
VALUES allows me to do. It's kind of ridiculous how much "work" goes away in
my code.  Too bad I can't do this at work (Oracle 8/9)."       (Tom Allison)
           http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php