Thread: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16807 Logged by: Alexander Lakhin Email address: exclusion@gmail.com PostgreSQL version: 13.1 Operating system: Ubuntu 20.04 Description: When executing the following query: CREATE EXTENSION postgres_fdw; DO $d$ BEGIN EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname '$$||current_database()||$$', port '$$||current_setting('port')||$$' )$$; END; $d$; CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; CREATE TABLE t1 (c1 int NOT NULL); CREATE FOREIGN TABLE ft1 (c1 int NOT NULL) SERVER loopback OPTIONS (schema_name 'public', table_name 't1'); INSERT INTO t1 SELECT id FROM generate_series(1, 100) id; DELETE FROM t1; ANALYZE ft1; EXPLAIN SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; The assert-enabled server crashes with the stacktrace: Core was generated by `postgres: law contrib_regression [local] EXPLAIN '. Program terminated with signal SIGABRT, Aborted. #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 50 ../sysdeps/unix/sysv/linux/raise.c: No such file or directory. (gdb) bt #0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50 #1 0x00007efc3d7fa859 in __GI_abort () at abort.c:79 #2 0x000055d6c447c083 in ExceptionalCondition ( conditionName=conditionName@entry=0x7efc3dd795eb "fpinfo->retrieved_rows >= 1", errorType=errorType@entry=0x7efc3dd784e5 "FailedAssertion", fileName=fileName@entry=0x7efc3dd7959c "postgres_fdw.c", lineNumber=lineNumber@entry=2810) at assert.c:67 #3 0x00007efc3dd6e5e5 in estimate_path_cost_size (root=root@entry=0x55d6c5e9b250, foreignrel=foreignrel@entry=0x55d6c5e8b760, param_join_conds=param_join_conds@entry=0x0, pathkeys=pathkeys@entry=0x55d6c5e9e258, fpextra=fpextra@entry=0x0, p_rows=p_rows@entry=0x7ffcc2b6d320, p_width=0x7ffcc2b6d31c, p_startup_cost=0x7ffcc2b6d328, p_total_cost=0x7ffcc2b6d330) at postgres_fdw.c:2810 #4 0x00007efc3dd6fdda in add_paths_with_pathkeys_for_rel (root=root@entry=0x55d6c5e9b250, rel=rel@entry=0x55d6c5e8b760, epq_path=epq_path@entry=0x0) at postgres_fdw.c:5278 #5 0x00007efc3dd72c8e in postgresGetForeignPaths (root=0x55d6c5e9b250, baserel=0x55d6c5e8b760, foreigntableid=<optimized out>) at postgres_fdw.c:994 #6 0x000055d6c4247d1f in set_foreign_pathlist (root=root@entry=0x55d6c5e9b250, rel=rel@entry=0x55d6c5e8b760, rte=rte@entry=0x55d6c5d97bf0) at allpaths.c:927 #7 0x000055d6c424b78e in set_rel_pathlist (root=root@entry=0x55d6c5e9b250, rel=0x55d6c5e8b760, rti=rti@entry=1, rte=0x55d6c5d97bf0) at allpaths.c:490 #8 0x000055d6c424b8ad in set_base_rel_pathlists (root=root@entry=0x55d6c5e9b250) at allpaths.c:352 #9 0x000055d6c424bf95 in make_one_rel (root=root@entry=0x55d6c5e9b250, joinlist=joinlist@entry=0x55d6c5e9cbd0) at allpaths.c:222 #10 0x000055d6c4272976 in query_planner (root=root@entry=0x55d6c5e9b250, qp_callback=qp_callback@entry=0x55d6c42768fe <standard_qp_callback>, qp_extra=qp_extra@entry=0x7ffcc2b6d5a0) at planmain.c:269 #11 0x000055d6c4279aa4 in grouping_planner (root=root@entry=0x55d6c5e9b250, inheritance_update=inheritance_update@entry=false, tuple_fraction=<optimized out>, tuple_fraction@entry=0) at planner.c:2058 #12 0x000055d6c427c041 in subquery_planner (glob=glob@entry=0x55d6c5e8b648, parse=parse@entry=0x55d6c5d97ad8, parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=false, tuple_fraction=tuple_fraction@entry=0) at planner.c:1015 #13 0x000055d6c427c6d7 in standard_planner (parse=0x55d6c5d97ad8, query_string=<optimized out>, cursorOptions=256, boundParams=<optimized out>) at planner.c:405 #14 0x000055d6c427cccb in planner (parse=parse@entry=0x55d6c5d97ad8, query_string=query_string@entry=0x55d6c5d743d0 "EXPLAIN\nSELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;", cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at planner.c:275 #15 0x000055d6c435685d in pg_plan_query (querytree=querytree@entry=0x55d6c5d97ad8, query_string=query_string@entry=0x55d6c5d743d0 "EXPLAIN\nSELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;", cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:875 #16 0x000055d6c4145ed5 in ExplainOneQuery (query=0x55d6c5d97ad8, cursorOptions=cursorOptions@entry=256, into=into@entry=0x0, es=es@entry=0x55d6c5e681b0, queryString=0x55d6c5d743d0 "EXPLAIN\nSELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;", params=params@entry=0x0, queryEnv=0x0) at explain.c:391 #17 0x000055d6c41468d1 in ExplainQuery (pstate=pstate@entry=0x55d6c5e59f70, stmt=stmt@entry=0x55d6c5d75b38, params=params@entry=0x0, dest=dest@entry=0x55d6c5e59ed8) at ../../../src/include/nodes/nodes.h:594 #18 0x000055d6c435c84b in standard_ProcessUtility (pstmt=0x55d6c5d75be0, queryString=0x55d6c5d743d0 "EXPLAIN\nSELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x55d6c5e59ed8, qc=0x7ffcc2b6da30) at utility.c:829 #19 0x000055d6c435cdea in ProcessUtility (pstmt=pstmt@entry=0x55d6c5d75be0, queryString=<optimized out>, context=context@entry=PROCESS_UTILITY_TOPLEVEL, params=<optimized out>, queryEnv=<optimized out>, dest=dest@entry=0x55d6c5e59ed8, qc=0x7ffcc2b6da30) at utility.c:524 #20 0x000055d6c4359255 in PortalRunUtility (portal=portal@entry=0x55d6c5dd7870, pstmt=0x55d6c5d75be0, isTopLevel=<optimized out>, setHoldSnapshot=setHoldSnapshot@entry=true, dest=dest@entry=0x55d6c5e59ed8, qc=qc@entry=0x7ffcc2b6da30) at pquery.c:1157 #21 0x000055d6c435a069 in FillPortalStore (portal=portal@entry=0x55d6c5dd7870, isTopLevel=isTopLevel@entry=true) at ../../../src/include/nodes/nodes.h:594 #22 0x000055d6c435ac0f in PortalRun (portal=portal@entry=0x55d6c5dd7870, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x55d6c5e71c50, altdest=altdest@entry=0x55d6c5e71c50, qc=0x7ffcc2b6dc20) at pquery.c:751 #23 0x000055d6c4356ed5 in exec_simple_query ( query_string=query_string@entry=0x55d6c5d743d0 "EXPLAIN\nSELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1;") at postgres.c:1239 #24 0x000055d6c4358df1 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x55d6c5d9fb18, dbname=<optimized out>, username=<optimized out>) at postgres.c:4315 #25 0x000055d6c42c4726 in BackendRun (port=port@entry=0x55d6c5d98400) at postmaster.c:4526 #26 0x000055d6c42c7881 in BackendStartup (port=port@entry=0x55d6c5d98400) at postmaster.c:4210 #27 0x000055d6c42c7ac8 in ServerLoop () at postmaster.c:1739 #28 0x000055d6c42c8ff1 in PostmasterMain (argc=8, argv=<optimized out>) at postmaster.c:1412 #29 0x000055d6c4212224 in main (argc=8, argv=0x55d6c5d6ea40) at main.c:210 This can be reproduced also by `make check -C contrib/postgres_fdw` with the following change in contrib/postgres_fdw/sql/postgres_fdw.sql: -DELETE FROM "S 1"."T 4" WHERE c1 % 3 != 0; -- delete for outer join tests +DELETE FROM "S 1"."T 4"; -- delete for outer join tests The first bad commit is 08d2d58a. Without the Assert() that query executes successfully.
Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
Etsuro Fujita
Date:
On Wed, Jan 6, 2021 at 3:29 AM PG Bug reporting form <noreply@postgresql.org> wrote: > > The following bug has been logged on the website: > > Bug reference: 16807 > Logged by: Alexander Lakhin > Email address: exclusion@gmail.com > PostgreSQL version: 13.1 > Operating system: Ubuntu 20.04 > Description: > > When executing the following query: > CREATE EXTENSION postgres_fdw; > > DO $d$ > BEGIN > EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw > OPTIONS (dbname '$$||current_database()||$$', > port '$$||current_setting('port')||$$' > )$$; > END; > $d$; > > CREATE USER MAPPING FOR CURRENT_USER SERVER loopback; > CREATE TABLE t1 (c1 int NOT NULL); > CREATE FOREIGN TABLE ft1 (c1 int NOT NULL) SERVER loopback OPTIONS > (schema_name 'public', table_name 't1'); > > INSERT INTO t1 SELECT id FROM generate_series(1, 100) id; > DELETE FROM t1; > > ANALYZE ft1; > > EXPLAIN > SELECT t1.c1, t2.c1 FROM ft1 t1 JOIN ft1 t2 ON (t1.c1 = t2.c1) ORDER BY > t1.c1, t2.c1; > > The assert-enabled server crashes with the stacktrace: Reproduced. Will look into this closely. Thanks for the report, Alexander! Best regards, Etsuro Fujita
Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
Etsuro Fujita
Date:
On Wed, Jan 6, 2021 at 12:15 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > Reproduced. Will look into this closely. If foreignrel->tuples is set to zero, the retrieved_rows estimate will also be set to zero, so the assertion pointed out upthread is not correct. I think I mistakenly assumed that foreignrel->tuples would have been forced to be at least one row, like foreignrel->rows, before we get to estimate_path_cost_size(), which is not correct. To fix, I think it would be better to adjust the assertion accordingly than removing it, since it was added to make sure that the retrieved_rows estimate is set to a sensible value. Attached is a patch for that. I added a simpler test case as well. Best regards, Etsuro Fujita
Attachment
Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
Kyotaro Horiguchi
Date:
At Thu, 14 Jan 2021 11:56:08 +0900, Etsuro Fujita <etsuro.fujita@gmail.com> wrote in > On Wed, Jan 6, 2021 at 12:15 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > > Reproduced. Will look into this closely. > > If foreignrel->tuples is set to zero, the retrieved_rows estimate will > also be set to zero, so the assertion pointed out upthread is not > correct. I think I mistakenly assumed that foreignrel->tuples would > have been forced to be at least one row, like foreignrel->rows, before > we get to estimate_path_cost_size(), which is not correct. > > To fix, I think it would be better to adjust the assertion accordingly > than removing it, since it was added to make sure that the > retrieved_rows estimate is set to a sensible value. Attached is a > patch for that. I added a simpler test case as well. FWIW I drew the same conclusion. regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
Etsuro Fujita
Date:
On Thu, Jan 14, 2021 at 1:30 PM Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote: > At Thu, 14 Jan 2021 11:56:08 +0900, Etsuro Fujita <etsuro.fujita@gmail.com> wrote in > > If foreignrel->tuples is set to zero, the retrieved_rows estimate will > > also be set to zero, so the assertion pointed out upthread is not > > correct. I think I mistakenly assumed that foreignrel->tuples would > > have been forced to be at least one row, like foreignrel->rows, before > > we get to estimate_path_cost_size(), which is not correct. > > > > To fix, I think it would be better to adjust the assertion accordingly > > than removing it, since it was added to make sure that the > > retrieved_rows estimate is set to a sensible value. Attached is a > > patch for that. I added a simpler test case as well. > > FWIW I drew the same conclusion. Ok, thanks for reviewing! I tweaked a regression test comment a little bit and added the commit message. Attached is an updated patch. I’ll commit the patch. Best regards, Etsuro Fujita
Attachment
Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table
From
Etsuro Fujita
Date:
On Thu, Feb 4, 2021 at 6:56 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote: > I tweaked a regression test comment a little bit and added the commit > message. Attached is an updated patch. I’ll commit the patch. Pushed. Best regards, Etsuro Fujita