Re: BUG #18902: TRAP:: failed Assert("!is_sorted") in File: "createplan.c"

PG Bug reporting form <noreply@postgresql.org> 于2025年4月23日周三 17:37写道：

> The following bug has been logged on the website:
>
> Bug reference:      18902
> Logged by:          Nikita Kalinin
> Email address:      n.kalinin@postgrespro.ru
> PostgreSQL version: 17.4
> Operating system:   ubuntu 22.04
> Description:
>
> Hello!
> After I built PostgreSQL like this:
> `./configure --enable-tap-tests --enable-debug --with-openssl
> --enable-cassert --prefix=/tmp/pg && make -j8 && make install`
> And executed the following script:
> ```
> CREATE EXTENSION postgres_fdw;
>
> CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
> DO $d$
>     BEGIN
>         EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
>             OPTIONS (dbname '$$||current_database()||$$',
>                      port '$$||current_setting('port')||$$'
>             )$$;
>         EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER
> postgres_fdw
>             OPTIONS (dbname '$$||current_database()||$$',
>                      port '$$||current_setting('port')||$$'
>             )$$;
>         EXECUTE $$CREATE SERVER loopback3 FOREIGN DATA WRAPPER
> postgres_fdw
>             OPTIONS (dbname '$$||current_database()||$$',
>                      port '$$||current_setting('port')||$$'
>             )$$;
>     END;
> $d$;
>
> CREATE USER MAPPING FOR public SERVER testserver1
>         OPTIONS (user 'value', password 'value');
> CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
> CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;
> CREATE USER MAPPING FOR public SERVER loopback3;
>
> CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
> CREATE FOREIGN TABLE ft1 (
>         c0 int,
>         c1 int NOT NULL,
>         c2 int NOT NULL,
>         c3 text,
>         c4 timestamptz,
>         c5 timestamp,
>         c6 varchar(10),
>         c7 char(10) default 'ft1',
>         c8 user_enum
> ) SERVER loopback;
> ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
>
> CREATE FOREIGN TABLE ft2 (
>         c1 int NOT NULL,
>         c2 int NOT NULL,
>         cx int,
>         c3 text,
>         c4 timestamptz,
>         c5 timestamp,
>         c6 varchar(10),
>         c7 char(10) default 'ft2',
>         c8 user_enum
> ) SERVER loopback;
> ALTER FOREIGN TABLE ft2 DROP COLUMN cx;
>
> CREATE FOREIGN TABLE ft4 (
>         c1 int NOT NULL,
>         c2 int NOT NULL,
>         c3 text
> ) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 3');
>
> CREATE FOREIGN TABLE ft5 (
>         c1 int NOT NULL,
>         c2 int NOT NULL,
>         c3 text
> ) SERVER loopback OPTIONS (schema_name 'S 1', table_name 'T 4');
>
> CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text,
> CONSTRAINT local_tbl_pkey PRIMARY KEY (c1));
> INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM
> generate_series(1, 1000) id;
> ANALYZE local_tbl;
> SET enable_nestloop TO false;
> SET enable_hashjoin TO false;
> EXPLAIN (VERBOSE, COSTS OFF)
> SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND
> ft1.c2
> = ft4.c1
>     AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND
> ft2.c1 < 100 FOR UPDATE;
> ```
>

Yes, I can reproduce this crash on HEAD. This crash is related with below
commit:
commit 828e94c9d2fd87c06a75354361543119d9937068
Author: Richard Guo <rguo@postgresql.org>
Date:   Wed Oct 9 17:14:42 2024 +0900

    Consider explicit incremental sort for mergejoins

I remove the Assert(!is_sorted), its plan looks like as below after adding
below codes:
if (presorted_keys > 0 && presorted_keys <
list_length(best_path->outersortkeys))
       use_incremental_sort = true;

                                         QUERY PLAN

---------------------------------------------------------------------------------------------
 LockRows
   ->  Merge Join
         Merge Cond: (local_tbl.c1 = ft1.c2)
         ->  Index Scan using local_tbl_pkey on local_tbl
         ->  Sort
               Sort Key: ft1.c2
               ->  Foreign Scan
                     Relations: (((ft1) INNER JOIN (ft2)) INNER JOIN (ft4))
INNER JOIN (ft5)
                     ->  Merge Join
                           Merge Cond: (ft1.c2 = ft5.c1)
                           ->  Sort
                                 Sort Key: ft1.c2
                                 ->  Merge Join
                                       Merge Cond: (ft1.c2 = ft4.c1)
                                       ->  Sort
                                             Sort Key: ft1.c2
                                             ->  Merge Join
                                                   Merge Cond: (ft1.c1 =
ft2.c1)
                                                   ->  Sort
                                                         Sort Key: ft1.c1
                                                         ->  Foreign Scan
on ft1
                                                   ->  Sort
                                                         Sort Key: ft2.c1
                                                         ->  Foreign Scan
on ft2
                                       ->  Sort
                                             Sort Key: ft4.c1
                                             ->  Foreign Scan on ft4
                           ->  Sort
                                 Sort Key: ft5.c1
                                 ->  Foreign Scan on ft5


 The crash happened in the second mergejoin, the outerpath is already
sorted by ft1.c2, and the best_path->outersortkeys contains ft1.c2,
so the pathkeys_count_contained_in() return true.  How about below fix:
if (is_sorted)
      use_incremental_sort = false;
else if (presorted_keys > 0)
      use_incremental_sort = true;


-- 
Thanks,
Tender Wang