Content-Type: multipart/alternative;
 boundary="------------OO4XZ7yHuLsFh00oDOPQ2w7G"
Message-ID: <600a153e-191b-952d-7090-cad5097b798f@gmail.com>
Date: Sat, 4 Nov 2023 13:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Support run-time partition pruning for hash join
Content-Language: en-US
To: Richard Guo <guofenglinux@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Cc: Andy Fan <zhihui.fan1213@gmail.com>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
References: <CAMbWs49atE64_JjqrF2+BBZvG-h7afL89wCghg_GmvOHPB_LbQ@mail.gmail.com>
 <CAKU4AWpGo7wXEQqvsv13s=Cto3k9QbucW5DJtjwD1a0dF9iTgA@mail.gmail.com>
 <CAApHDvp+O0PtZMWomyYg=RE68d1wBnc-yxjJsDK3hmYBnP=-qw@mail.gmail.com>
 <CAMbWs49+p6hBxXJHFiSwOtPCSkAHwhJj3hTpCR_pmMiUUVLZ1Q@mail.gmail.com>
 <CAApHDvrTwpgYG9UGjBJpMrey36LMpwtEx_2RWrByxSq7NnKqdA@mail.gmail.com>
 <CAMbWs488_uMqmyQo0Cq-XrDSiEqiXCnfJtCyhXrWn_HdBen3Tw@mail.gmail.com>
 <CAMbWs4_GhDa7+qRbdhLzMTXKZr2naiJc7C+tE1BcK5UVjxqncA@mail.gmail.com>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <CAMbWs4_GhDa7+qRbdhLzMTXKZr2naiJc7C+tE1BcK5UVjxqncA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/600a153e-191b-952d-7090-cad5097b798f%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------OO4XZ7yHuLsFh00oDOPQ2w7G
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Richard,

02.11.2023 14:19, Richard Guo wrote:
>
> However, the cfbot indicates that there are test cases that fail on
> FreeBSD [1] (no failure on other platforms).  So I set up a FreeBSD-13
> locally but just cannot reproduce the failure.  I must be doing
> something wrong.  Can anyone give me some hints or suggestions?
>
> FYI. The failure looks like:
>
>  explain (costs off)
>    select p2.a, p1.c from permtest_parent p1 inner join permtest_parent p2
>    on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';
> -                     QUERY PLAN
> -----------------------------------------------------
> - Hash Join
> -   Hash Cond: (p2.a = p1.a)
> -   ->  Seq Scan on permtest_grandchild p2
> -   ->  Hash
> -         ->  Seq Scan on permtest_grandchild p1
> -               Filter: ("left"(c, 3) ~ 'a1$'::text)
> -(6 rows)
> -
> +ERROR:  unrecognized node type: 1130127496

I've managed to reproduce that failure on my Ubuntu with:
CPPFLAGS="-Og -DWRITE_READ_PARSE_PLAN_TREES -DCOPY_PARSE_PLAN_TREES" ./configure ... make check
...
  SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
-                    QUERY PLAN
---------------------------------------------------
- Sort
-   Sort Key: t1.a, t2.b
-   ->  Hash Right Join
-         Hash Cond: (t2.b = t1.a)
-         ->  Append
-               ->  Seq Scan on prt2_p1 t2_1
-               ->  Seq Scan on prt2_p2 t2_2
-               ->  Seq Scan on prt2_p3 t2_3
-         ->  Hash
-               ->  Append
-                     ->  Seq Scan on prt1_p1 t1_1
-                           Filter: (b = 0)
-                     ->  Seq Scan on prt1_p2 t1_2
-                           Filter: (b = 0)
-                     ->  Seq Scan on prt1_p3 t1_3
-                           Filter: (b = 0)
-(16 rows)
-
+ERROR:  unrecognized node type: -1465804424
...

As far as I can see from https://cirrus-ci.com/task/6642692659085312,
the FreeBSD host has the following CPPFLAGS specified:
-DRELCACHE_FORCE_RELEASE
-DCOPY_PARSE_PLAN_TREES
-DWRITE_READ_PARSE_PLAN_TREES
-DRAW_EXPRESSION_COVERAGE_TEST
-DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS

Best regards,
Alexander
--------------OO4XZ7yHuLsFh00oDOPQ2w7G
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hello Richard,<br>
      <br>
      02.11.2023 14:19, Richard Guo wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAMbWs4_GhDa7+qRbdhLzMTXKZr2naiJc7C+tE1BcK5UVjxqncA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr"><br>
        </div>
        <div class="gmail_quote">However, the cfbot indicates that there
          are test cases that fail on<br>
          <div>FreeBSD [1] (no failure on other platforms).  So I set up
            a FreeBSD-13<br>
            locally but just cannot reproduce the failure.  I must be
            doing<br>
            something wrong.  Can anyone give me some hints or
            suggestions?<br>
            <br>
            FYI. The failure looks like:<br>
            <br>
             explain (costs off)<br>
               select p2.a, p1.c from permtest_parent p1 inner join
            permtest_parent p2<br>
               on p1.a = p2.a and left(p1.c, 3) ~ 'a1$';<br>
            -                     QUERY PLAN<br>
            -----------------------------------------------------<br>
            - Hash Join<br>
            -   Hash Cond: (p2.a = p1.a)<br>
            -   -&gt;  Seq Scan on permtest_grandchild p2<br>
            -   -&gt;  Hash<br>
            -         -&gt;  Seq Scan on permtest_grandchild p1<br>
            -               Filter: ("left"(c, 3) ~ 'a1$'::text)<br>
            -(6 rows)<br>
            -<br>
            +ERROR:  unrecognized node type: 1130127496<br>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    I've managed to reproduce that failure on my Ubuntu with:<br>
    CPPFLAGS="-Og -DWRITE_READ_PARSE_PLAN_TREES -DCOPY_PARSE_PLAN_TREES"
    ./configure ... make check<br>
    ...<br>
     SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE
    t1.b = 0 ORDER BY t1.a, t2.b;<br>
    -                    QUERY PLAN                    <br>
    ---------------------------------------------------<br>
    - Sort<br>
    -   Sort Key: t1.a, t2.b<br>
    -   -&gt;  Hash Right Join<br>
    -         Hash Cond: (t2.b = t1.a)<br>
    -         -&gt;  Append<br>
    -               -&gt;  Seq Scan on prt2_p1 t2_1<br>
    -               -&gt;  Seq Scan on prt2_p2 t2_2<br>
    -               -&gt;  Seq Scan on prt2_p3 t2_3<br>
    -         -&gt;  Hash<br>
    -               -&gt;  Append<br>
    -                     -&gt;  Seq Scan on prt1_p1 t1_1<br>
    -                           Filter: (b = 0)<br>
    -                     -&gt;  Seq Scan on prt1_p2 t1_2<br>
    -                           Filter: (b = 0)<br>
    -                     -&gt;  Seq Scan on prt1_p3 t1_3<br>
    -                           Filter: (b = 0)<br>
    -(16 rows)<br>
    -<br>
    +ERROR:  unrecognized node type: -1465804424<br>
    ...<br>
    <br>
    As far as I can see from
    <a class="moz-txt-link-freetext" href="https://cirrus-ci.com/task/6642692659085312">https://cirrus-ci.com/task/6642692659085312</a>,<br>
    the FreeBSD host has the following CPPFLAGS specified:<br>
    -DRELCACHE_FORCE_RELEASE<br>
    -DCOPY_PARSE_PLAN_TREES<br>
    -DWRITE_READ_PARSE_PLAN_TREES<br>
    -DRAW_EXPRESSION_COVERAGE_TEST<br>
    -DENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS<br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------OO4XZ7yHuLsFh00oDOPQ2w7G--