fdw crashes when making use of partitioning and the partitions have sorted option set

[cabecada]

i think i came across a bug where when we make use of sorted field in the foreign table, we see a crash when = operator is used in predicates.
could not find pathkey item to sort

its reproducible from the repo example in tests as below.
when we drop the sorted option from parititions query returns fine.

vijay=# CREATE FOREIGN TABLE example1 (
    one     INT8,
    two     INT8[],
    three   TEXT,
    four    TIMESTAMP,
    five    DATE,
    six     BOOL,
    seven   FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example1.parquet', sorted 'one');

CREATE FOREIGN TABLE example2 (
    one     INT8,
    two     INT8[],
    three   TEXT,
    four    TIMESTAMP,
    five    DATE,
    six     BOOL,
    seven   FLOAT8)
SERVER parquet_srv
OPTIONS (filename '/tmp/parquet_fdw/data/simple/example2.parquet', sorted 'one');
CREATE FOREIGN TABLE
CREATE FOREIGN TABLE
vijay=# select * from example1;
 one |    two     | three |        four         |    five    | six | seven 
-----+------------+-------+---------------------+------------+-----+-------
   1 | {1,2,3}    | foo   | 2018-01-01 00:00:00 | 2018-01-01 | t   |   0.5
   2 | {NULL,5,6} | bar   | 2018-01-02 00:00:00 | 2018-01-02 | f   |      
   3 | {7,8,9}    | baz   | 2018-01-03 00:00:00 | 2018-01-03 | t   |     1
   4 | {10,11,12} | uno   | 2018-01-04 00:00:10 | 2018-01-04 | f   |   1.5
   5 | {13,14,15} | dos   | 2018-01-05 00:00:00 | 2018-01-05 | f   |      
   6 | {16,17,18} | tres  | 2018-01-06 00:00:00 | 2018-01-06 | f   |     2
(6 rows)

vijay=# select * from example2;
 one |   two   | three |        four         |    five    | six | seven 
-----+---------+-------+---------------------+------------+-----+-------
   1 | {19,20} | eins  | 2018-01-01 00:00:00 | 2018-01-01 | t   |      
   3 | {21,22} | zwei  | 2018-01-03 00:00:00 | 2018-01-03 | f   |      
   5 | {23,24} | drei  | 2018-01-05 00:00:00 | 2018-01-05 | t   |      
   7 | {25,26} | vier  | 2018-01-07 00:00:00 | 2018-01-07 | f   |      
   9 | {27,28} | fünf  | 2018-01-09 00:00:00 | 2018-01-09 | t   |      
(5 rows)

vijay=# create table example (
vijay(#     one     INT8,
vijay(#     two     INT8[],
vijay(#     three   TEXT,
vijay(#     four    TIMESTAMP,
vijay(#     five    DATE,
vijay(#     six     BOOL,
vijay(#     seven   FLOAT8)
vijay-# partition by list(three);
CREATE TABLE
vijay=# alter table example attach partition example1 for values in ('foo', 'bar', 'baz', 'uno', 'dos', 'tres');
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
ERROR:  could not find pathkey item to sort
vijay=# alter table example1 options (drop sorted);
ALTER TABLE
vijay=# alter table example2 options (drop sorted);
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Foreign Scan on example1  (cost=0.00..0.06 rows=1 width=93) (actual time=0.537..0.912 rows=1 loops=1)
   Filter: (three = 'foo'::text)
   Rows Removed by Filter: 5
   Reader: Single File
   Row groups: 1, 2
 Planning Time: 0.888 ms
 Execution Time: 1.399 ms
(7 rows)

[cabecada]

(gdb) b 5857
Breakpoint 3 at 0x56081fdf9195: file createplan.c, line 5857.
(gdb) c
Continuing.

Breakpoint 3, prepare_sort_from_pathkeys (lefttree=lefttree@entry=0x560821ed93a0, pathkeys=pathkeys@entry=0x560821f4c750, 
    relids=0x560821e1b538, reqColIdx=reqColIdx@entry=0x0, adjust_tlist_in_place=<optimized out>, 
    adjust_tlist_in_place@entry=true, p_numsortkeys=p_numsortkeys@entry=0x7ffc4712cb20, p_sortColIdx=0x7ffc4712cb28, 
    p_sortOperators=0x7ffc4712cb30, p_collations=0x7ffc4712cb38, p_nullsFirst=0x7ffc4712cb40) at createplan.c:5857
5857	in createplan.c
(gdb) bt
#0  prepare_sort_from_pathkeys (lefttree=lefttree@entry=0x560821ed93a0, pathkeys=pathkeys@entry=0x560821f4c750, 
    relids=0x560821e1b538, reqColIdx=reqColIdx@entry=0x0, adjust_tlist_in_place=<optimized out>, 
    adjust_tlist_in_place@entry=true, p_numsortkeys=p_numsortkeys@entry=0x7ffc4712cb20, p_sortColIdx=0x7ffc4712cb28, 
    p_sortOperators=0x7ffc4712cb30, p_collations=0x7ffc4712cb38, p_nullsFirst=0x7ffc4712cb40) at createplan.c:5857
#1  0x000056081fdfdb7c in create_append_plan (root=root@entry=0x560821f420e8, best_path=best_path@entry=0x560821ed8400, 
    flags=flags@entry=1) at createplan.c:1130
#2  0x000056081fdfb591 in create_plan_recurse (root=0x560821f420e8, best_path=0x560821ed8400, flags=1) at createplan.c:397
#3  0x000056081fdfd9ea in create_plan (root=root@entry=0x560821f420e8, best_path=<optimized out>) at createplan.c:325
#4  0x000056081fe0989c in standard_planner (parse=0x560821f6a198, cursorOptions=256, boundParams=<optimized out>)
    at planner.c:413
#5  0x000056081feb34ec in pg_plan_query (querytree=querytree@entry=0x560821f6a198, cursorOptions=<optimized out>, 
    boundParams=boundParams@entry=0x0) at postgres.c:878
#6  0x000056081fd11767 in ExplainOneQuery (queryEnv=0x0, params=0x0, 
    queryString=0x560821deb2a0 "explain analyze select * from example where three = 'foo';", es=0x560821f69ef8, into=0x0, 
    cursorOptions=<optimized out>, query=0x560821f6a198) at explain.c:368
#7  ExplainOneQuery (query=0x560821f6a198, cursorOptions=<optimized out>, into=0x0, es=0x560821f69ef8, 
    queryString=0x560821deb2a0 "explain analyze select * from example where three = 'foo';", params=0x0, queryEnv=0x0)
    at explain.c:342
#8  0x000056081fd11d0d in ExplainQuery (pstate=pstate@entry=0x560821f654f8, stmt=stmt@entry=0x560821dec148, 
    queryString=queryString@entry=0x560821deb2a0 "explain analyze select * from example where three = 'foo';", 
    params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, dest=dest@entry=0x560821f65468) at explain.c:256
#9  0x000056081feb93ac in standard_ProcessUtility (pstmt=0x560821f45080, 
    queryString=0x560821deb2a0 "explain analyze select * from example where three = 'foo';", 
    context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x560821f65468, completionTag=0x7ffc4712cf30 "")
    at utility.c:675
#10 0x000056081feb7277 in PortalRunUtility (portal=portal@entry=0x560821e69ec0, pstmt=0x560821f45080, 
    isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=true, dest=dest@entry=0x560821f65468, 
    completionTag=completionTag@entry=0x7ffc4712cf30 "") at pquery.c:1163
#11 0x000056081feb76d3 in FillPortalStore (portal=0x560821e69ec0, isTopLevel=<optimized out>) at pquery.c:1042
#12 0x000056081feb7a95 in PortalRun (portal=portal@entry=0x560821e69ec0, count=count@entry=9223372036854775807, 
    isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x560821f45160, 
    altdest=altdest@entry=0x560821f45160, completionTag=0x7ffc4712d160 "") at pquery.c:772
#13 0x000056081feb3938 in exec_simple_query (
    query_string=0x560821deb2a0 "explain analyze select * from example where three = 'foo';") at postgres.c:1215
#14 0x000056081feb5331 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x560821e15428, dbname=<optimized out>, 
    username=<optimized out>) at postgres.c:4271
#15 0x000056081fe451eb in BackendRun (port=0x560821e12330, port=0x560821e12330) at postmaster.c:4484
--Type <RET> for more, q to quit, c to continue without paging--
#16 BackendStartup (port=0x560821e12330) at postmaster.c:4167
#17 ServerLoop () at postmaster.c:1725
#18 0x000056081fe46134 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x560821de4b30) at postmaster.c:1398
#19 0x000056081fbe92ad in main (argc=5, argv=0x560821de4b30) at main.c:228

[cabecada]

querying the foreign table directly works fine.
but querying the parent table causes crash

vijay=# select * from example1;
 one |    two     | three |        four         |    five    | six | seven 
-----+------------+-------+---------------------+------------+-----+-------
   1 | {1,2,3}    | foo   | 2018-01-01 00:00:00 | 2018-01-01 | t   |   0.5
   2 | {NULL,5,6} | bar   | 2018-01-02 00:00:00 | 2018-01-02 | f   |      
   3 | {7,8,9}    | baz   | 2018-01-03 00:00:00 | 2018-01-03 | t   |     1
   4 | {10,11,12} | uno   | 2018-01-04 00:00:10 | 2018-01-04 | f   |   1.5
   5 | {13,14,15} | dos   | 2018-01-05 00:00:00 | 2018-01-05 | f   |      
   6 | {16,17,18} | tres  | 2018-01-06 00:00:00 | 2018-01-06 | f   |     2
(6 rows)

vijay=# explain analyze select * from example1 where three = 'foo';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Foreign Scan on example1  (cost=0.00..0.06 rows=1 width=93) (actual time=0.512..0.915 rows=1 loops=1)
   Filter: (three = 'foo'::text)
   Rows Removed by Filter: 5
   Reader: Single File
   Row groups: 1, 2
 Planning Time: 0.790 ms
 Execution Time: 1.372 ms
(7 rows)

vijay=# explain analyze select * from example where three = 'foo';
ERROR:  could not find pathkey item to sort

[cabecada]

postgres 12.8

[imasli]

@cabecada When you created the foreign table you included a sorted column. If the sorted column is not included would it still show the same error?

[cabecada]

no, without sorted. no errors atleast in the test setup up above. (i drop the sorted option) and it worked fine.

vijay=# alter table example1 options (drop sorted);
ALTER TABLE
vijay=# alter table example2 options (drop sorted);
ALTER TABLE
vijay=# explain analyze select * from example where three = 'foo';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Foreign Scan on example1  (cost=0.00..0.06 rows=1 width=93) (actual time=0.537..0.912 rows=1 loops=1)
   Filter: (three = 'foo'::text)
   Rows Removed by Filter: 5
   Reader: Single File
   Row groups: 1, 2
 Planning Time: 0.888 ms
 Execution Time: 1.399 ms
(7 rows)

[za-arthur]

This was fixed by #60