BUG #17713: Assert with postgres_fdw in v12

The following bug has been logged on the website:

Bug reference:      17713
Logged by:          Robins Tharakan
Email address:      tharakan@gmail.com
PostgreSQL version: 12.13
Operating system:   Ubuntu 20.04
Description:

Hi,

This is a repro that triggers an assert (only) in REL_12_STABLE. In
particular the issue seems to have begun after this commit -
08d2d58a2a1c8ef8d39e8132d39ee14a1d029500. The foreign table
is empty so am unsure if Assert()'s expectations are valid.

Since all versions v13+ seem to work okay, it is possible that a future
bug-fix was not backpatched far enough to v12.


Backtrace
=========
Program terminated with signal SIGABRT, Aborted.
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007fda51fc9859 in __GI_abort () at abort.c:79
#2  0x00005591731936b6 in ExceptionalCondition (
    conditionName=0x7fda39d454d0 "!(fpinfo->retrieved_rows >= 1)",
    errorType=0x7fda39d45020 "FailedAssertion",
    fileName=0x7fda39d4515b "postgres_fdw.c", lineNumber=2752) at
assert.c:54
#3  0x00007fda39d33e1f in estimate_path_cost_size (root=0x5591745e8580,
    foreignrel=0x559174629660, param_join_conds=0x0, pathkeys=0x0,
    fpextra=0x55917462c870, p_rows=0x7fff6c788538, p_width=0x7fff6c788534,
    p_startup_cost=0x7fff6c788540, p_total_cost=0x7fff6c788548)
    at postgres_fdw.c:2752
#4  0x00007fda39d3b1c3 in add_foreign_final_paths (root=0x5591745e8580,
    input_rel=0x559174629660, final_rel=0x55917462c290,
extra=0x7fff6c788750)
    at postgres_fdw.c:6321
#5  0x00007fda39d3a612 in postgresGetForeignUpperPaths
(root=0x5591745e8580,
    stage=UPPERREL_FINAL, input_rel=0x559174629660,
    output_rel=0x55917462c290, extra=0x7fff6c788750) at
postgres_fdw.c:5883
#6  0x0000559172ee0074 in grouping_planner (root=0x5591745e8580,
    inheritance_update=false, tuple_fraction=136) at planner.c:2412
#7  0x0000559172edd757 in subquery_planner (glob=0x5591745e84e8,
    parse=0x5591746d2890, parent_root=0x0, hasRecursion=false,
    tuple_fraction=0) at planner.c:1012


Full Backtrace excerpt
======================
#2  0x00005591731936b6 in ExceptionalCondition (
    conditionName=0x7fda39d454d0 "!(fpinfo->retrieved_rows >= 1)",
    errorType=0x7fda39d45020 "FailedAssertion",
    fileName=0x7fda39d4515b "postgres_fdw.c", lineNumber=2752) at
assert.c:54
No locals.
#3  0x00007fda39d33e1f in estimate_path_cost_size (root=0x5591745e8580,
    foreignrel=0x559174629660, param_join_conds=0x0, pathkeys=0x0,
    fpextra=0x55917462c870, p_rows=0x7fff6c788538, p_width=0x7fff6c788534,
    p_startup_cost=0x7fff6c788540, p_total_cost=0x7fff6c788548)
    at postgres_fdw.c:2752
        run_cost = 0
        fpinfo = 0x55917462a338
        rows = 3.9525251667299724e-323
        retrieved_rows = 0
        width = 32767
        startup_cost = 6.9532335201825916e-310
        total_cost = 6.4042594576845104e-315



Repro SQL
=========

-- drop database t;
create database t;
\c t
create table loct3 (f1 text collate "C" unique, f2 text, f3 varchar(10)
unique);
begin;

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')||$$'
            )$$;
    END;
$d$;
CREATE USER MAPPING FOR public SERVER testserver1
 OPTIONS (user 'postgres12', password 'postgres12');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback
 OPTIONS (user 'postgres12', password 'postgres12');

create foreign table ft3 (f1 text collate "C", f2 text, f3 varchar(10))
  server loopback options (table_name 'loct3', use_remote_estimate
'true');

SELECT FROM (SELECT FROM public.ft3 AS ref_3 WHERE NULL) AS subq_1 WHERE
NULL LIMIT 1;

rollback;

Thanks to SQLSmith / SQLReduce for helping with the find.
-
Robins Tharakan
Amazon Web Services