BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      16807
Logged by:          Alexander Lakhin
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table

Etsuro Fujita-2
On Wed, Jan 6, 2021 at 3:29 AM PG Bug reporting form
<[hidden email]> wrote:

>
> The following bug has been logged on the website:
>
> Bug reference:      16807
> Logged by:          Alexander Lakhin
> Email address:      [hidden email]
> 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


Reply | Threaded
Open this post in threaded view
|

Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table

Etsuro Fujita-2
On Wed, Jan 6, 2021 at 12:15 PM Etsuro Fujita <[hidden email]> 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

fix-postgres_fdw-assertion.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: BUG #16807: Assert failed in postgres_fdw/estimate_path_cost_size with an empty foreign table

Kyotaro Horiguchi-4
At Thu, 14 Jan 2021 11:56:08 +0900, Etsuro Fujita <[hidden email]> wrote in

> On Wed, Jan 6, 2021 at 12:15 PM Etsuro Fujita <[hidden email]> 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