Horton

Thread

Commits

  1. Revert "Move PartitionPruneInfo out of plan nodes into PlannedStmt"

  2. Fix setrefs.c code for adjusting partPruneInfos

  3. Fix corner-case planner failure for MERGE.

  1. "variable not found in subplan target list"

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-03-28T11:22:48Z

    I have to run now so can't dissect it, but while running sqlsmith on the
SQL/JSON patch after Justin's report, I got $SUBJECT in this query:

MERGE INTO public.target_parted as target_0
    USING (select  
          subq_0.c5 as c0, 
          subq_0.c0 as c1, 
          ref_0.a as c2, 
          subq_0.c1 as c3, 
          subq_0.c9 as c4, 
          (select c from public.prt2_m_p3 limit 1 offset 1)
             as c5, 
          subq_0.c8 as c6, 
          ref_0.a as c7, 
          subq_0.c7 as c8, 
          subq_0.c1 as c9, 
          pg_catalog.system_user() as c10
        from 
          public.itest1 as ref_0
            left join (select  
                  ref_1.matches as c0, 
                  ref_1.typ as c1, 
                  ref_1.colname as c2, 
                  (select slotname from public.iface limit 1 offset 44)
                     as c3, 
                  ref_1.matches as c4, 
                  ref_1.op as c5, 
                  ref_1.matches as c6, 
                  ref_1.value as c7, 
                  ref_1.op as c8, 
                  ref_1.op as c9, 
                  ref_1.typ as c10
                from 
                  public.brinopers_multi as ref_1
                where cast(null as polygon) <@ (select polygon from public.tab_core_types limit 1 offset 22)
                    ) as subq_0
            on (cast(null as macaddr8) >= cast(null as macaddr8))
        where subq_0.c10 > subq_0.c2
        limit 49) as subq_1
    ON target_0.b = subq_1.c2 
    WHEN MATCHED 
      AND (cast(null as box) |>> cast(null as box)) 
        or (cast(null as lseg) ?-| (select s from public.lseg_tbl limit 1 offset 6)
            )
       THEN DELETE
    WHEN NOT MATCHED AND (EXISTS (
          select  
              21 as c0, 
              subq_2.c0 as c1
            from 
              public.itest14 as sample_0 tablesample system (3.6) 
                inner join public.num_exp_sqrt as sample_1 tablesample bernoulli (0.3) 
                on (cast(null as "char") <= cast(null as "char")),
              lateral (select  
                    sample_1.id as c0
                  from 
                    public.a as ref_2
                  where (cast(null as lseg) <@ cast(null as line)) 
                    or ((select b3 from public.bit_defaults limit 1 offset 80)
                         <> (select b3 from public.bit_defaults limit 1 offset 4)
                        )
                  limit 158) as subq_2
            where (cast(null as name) !~ (select t from public.test_tsvector limit 1 offset 5)
                  ) 
              and ((select bool from public.tab_core_types limit 1 offset 61)
                   < (select pg_catalog.bool_or(v) from public.rtest_view1)
                  ))) 
        or (18 is NULL)
       THEN INSERT VALUES ( pg_catalog.int4um(
        cast(public.func_with_bad_set() as int4)), 13)
    WHEN MATCHED AND ((24 is not NULL) 
          or (true)) 
        or (cast(null as "timestamp") <= cast(null as timestamptz))
       THEN UPDATE  set 
        b = target_0.b


Ugh.

I got no more SQL/JSON related crashes so far.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"No hay ausente sin culpa ni presente sin disculpa" (Prov. francés)

  2. Re: "variable not found in subplan target list"

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-28T11:38:25Z

    Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I have to run now so can't dissect it, but while running sqlsmith on the
> SQL/JSON patch after Justin's report, I got $SUBJECT in this query:

Reproduces in HEAD and v15 too (once you replace pg_catalog.system_user
with some function that exists in v15).  So it's not the fault of the
JSON patch, nor of my outer-join hacking which had been my first thought.

			regards, tom lane

  3. Re: "variable not found in subplan target list"

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-28T12:56:41Z

    Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I have to run now so can't dissect it, but while running sqlsmith on the
> SQL/JSON patch after Justin's report, I got $SUBJECT in this query:

I reduced this down to

MERGE INTO public.target_parted as target_0
    USING public.itest1 as ref_0
    ON target_0.b = ref_0.a
    WHEN NOT MATCHED
       THEN INSERT VALUES (42, 13);

The critical moving part seems to just be that the MERGE target
is a partitioned table ... but surely somebody tested that before?

			regards, tom lane

  4. Re: "variable not found in subplan target list"

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-28T13:17:00Z

    I wrote:
> I reduced this down to

> MERGE INTO public.target_parted as target_0
>     USING public.itest1 as ref_0
>     ON target_0.b = ref_0.a
>     WHEN NOT MATCHED
>        THEN INSERT VALUES (42, 13);

> The critical moving part seems to just be that the MERGE target
> is a partitioned table ... but surely somebody tested that before?

Oh, it's not just any partitioned table:

regression=# \d+ target_parted
                                Partitioned table "public.target_parted"
 Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
 a      | integer |           |          |         | plain   |             |              | 
 b      | integer |           |          |         | plain   |             |              | 
Partition key: LIST (a)
Number of partitions: 0

The planner is reducing the scan of target_parted to
a dummy scan, as is reasonable, but it forgets to
provide ctid as an output from that scan; then the
parent join node is unhappy because it does have
a ctid output.  So it looks like the problem is some
shortcut we take while creating the dummy scan.

I suppose that without the planner bug, this'd fail at
runtime for lack of a partition to put (42,13) into.
Because of that, the case isn't really interesting
for production, which may explain the lack of reports.

			regards, tom lane

  5. Re: "variable not found in subplan target list"

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-28T14:46:29Z

    I wrote:
> The planner is reducing the scan of target_parted to
> a dummy scan, as is reasonable, but it forgets to
> provide ctid as an output from that scan; then the
> parent join node is unhappy because it does have
> a ctid output.  So it looks like the problem is some
> shortcut we take while creating the dummy scan.

Oh, actually the problem is in distribute_row_identity_vars,
which is supposed to handle this case, but it thinks it doesn't
have to back-fill the rel's reltarget.  Wrong.  Now that I see
the problem, I wonder if we can't reproduce a similar symptom
without MERGE, which would mean that v14 has the issue too.

The attached seems to fix it, but I'm going to look for a
non-MERGE test case before pushing.

			regards, tom lane

  6. Re: "variable not found in subplan target list"

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-03-28T18:19:30Z

    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

  7. Re: "variable not found in subplan target list"

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-28T18:39:21Z

    Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> So I'm back home and found a couple more weird errors in the log:

> ERROR:  mismatching PartitionPruneInfo found at part_prune_index 0
> DETALLE:  plan node relids (b 1), pruneinfo relids (b 36)

This one reproduces for me.

> 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

Hmph, I couldn't reproduce that, not even with other settings of
debug_parallel_query.  Are you running it with non-default
planner parameters?

> select 1 as c0
> ...
> ERROR:  could not find commutator for operator 53286

I got a slightly different error:

ERROR:  missing support function 1(195306,195306) in opfamily 1976

where

regression=# select 195306::regtype;               
  regtype   
------------
 int8alias1
(1 row)

So that one is related to the intentionally-somewhat-broken
int8 opclass configuration that equivclass.sql leaves behind.
I've always had mixed emotions about whether leaving that
set up that way was a good idea or not.  In principle nothing
really bad should happen, but it can lead to confusing errors
like this one.  Maybe it'd be better to roll that back?

			regards, tom lane

  8. Re: "variable not found in subplan target list"

    Amit Langote <amitlangote09@gmail.com> — 2023-03-29T08:28:22Z

    On Wed, Mar 29, 2023 at 3:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > So I'm back home and found a couple more weird errors in the log:
>
> > ERROR:  mismatching PartitionPruneInfo found at part_prune_index 0
> > DETALLE:  plan node relids (b 1), pruneinfo relids (b 36)
>
> This one reproduces for me.

I've looked into this one and the attached patch fixes it for me.
Turns out set_plan_refs()'s idea of when the entries from
PlannerInfo.partPruneInfos are transferred into
PlannerGlobal.partPruneInfo was wrong.

Though, I wonder if we need to keep ec386948948 that introduced the
notion of part_prune_index around if the project that needed it [1]
has moved on to an entirely different approach altogether, one that
doesn't require hacking up the pruning code.

--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com
 [1] https://commitfest.postgresql.org/42/3478/

  9. Re: "variable not found in subplan target list"

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-03-30T10:53:25Z

    On 2023-Mar-29, Amit Langote wrote:

> On Wed, Mar 29, 2023 at 3:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > > So I'm back home and found a couple more weird errors in the log:
> >
> > > ERROR:  mismatching PartitionPruneInfo found at part_prune_index 0
> > > DETALLE:  plan node relids (b 1), pruneinfo relids (b 36)
> >
> > This one reproduces for me.
> 
> I've looked into this one and the attached patch fixes it for me.
> Turns out set_plan_refs()'s idea of when the entries from
> PlannerInfo.partPruneInfos are transferred into
> PlannerGlobal.partPruneInfo was wrong.

Thanks for the patch. I've pushed it to github for CI testing, and if
there are no problems I'll put it in.

> Though, I wonder if we need to keep ec386948948 that introduced the
> notion of part_prune_index around if the project that needed it [1]
> has moved on to an entirely different approach altogether, one that
> doesn't require hacking up the pruning code.

Hmm, that's indeed tempting.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

  10. Re: "variable not found in subplan target list"

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-05-02T17:54:09Z

    Hi Amit,

On 2023-Mar-30, Alvaro Herrera wrote:

> On 2023-Mar-29, Amit Langote wrote:

> > Though, I wonder if we need to keep ec386948948 that introduced the
> > notion of part_prune_index around if the project that needed it [1]
> > has moved on to an entirely different approach altogether, one that
> > doesn't require hacking up the pruning code.
> 
> Hmm, that's indeed tempting.

We have an open item about this, and I see no reason not to do it.  I
checked, and putting things back is just a matter of reverting
589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
conflicts, and bumping catversion once more.  Would you like to do that
yourself, or do you prefer that I do it?  Ideally, we'd do it before
beta1.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/

  11. Re: "variable not found in subplan target list"

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2023-05-04T10:44:01Z

    On 2023-May-02, Alvaro Herrera wrote:

> We have an open item about this, and I see no reason not to do it.  I
> checked, and putting things back is just a matter of reverting
> 589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
> conflicts, and bumping catversion once more.  Would you like to do that
> yourself, or do you prefer that I do it?  Ideally, we'd do it before
> beta1.

I have pushed the revert now.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

  12. Re: "variable not found in subplan target list"

    Amit Langote <amitlangote09@gmail.com> — 2023-05-04T13:55:39Z

    Hi Alvaro,

On Thu, May 4, 2023 at 19:44 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2023-May-02, Alvaro Herrera wrote:
>
> > We have an open item about this, and I see no reason not to do it.  I
> > checked, and putting things back is just a matter of reverting
> > 589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
> > conflicts, and bumping catversion once more.  Would you like to do that
> > yourself, or do you prefer that I do it?  Ideally, we'd do it before
> > beta1.
>
> I have pushed the revert now.


Thanks for taking care of it.

(Wouldn’t have been able to get to it till Monday myself.)

> --
Thanks, Amit Langote
EDB: http://www.enterprisedb.com