| From: | Tender Wang <tndrwang(at)gmail(dot)com> |
|---|---|
| To: | n(dot)kalinin(at)postgrespro(dot)ru, pgsql-bugs(at)lists(dot)postgresql(dot)org, Richard Guo <guofenglinux(at)gmail(dot)com> |
| Subject: | Re: BUG #18902: TRAP:: failed Assert("!is_sorted") in File: "createplan.c" |
| Date: | 2025-04-23 11:03:28 |
| Message-ID: | CAHewXNnGMemnERhK=DNKjc8pqYoowJz4KN0hi14Pk0-rs8y-sg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)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(dot)kalinin(at)postgrespro(dot)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(at)postgresql(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tender Wang | 2025-04-23 11:47:42 | Re: BUG #18902: TRAP:: failed Assert("!is_sorted") in File: "createplan.c" |
| Previous Message | Amit Kapila | 2025-04-23 09:01:18 | Re: Disabled logical replication origin session causes primary key errors |