GetForeignUpperPaths & aggregate pushdown

[jyc]

Is there any ongoing work on supporting GetForeignUpperPaths? I was thinking of taking a stab at it, but I don't want to duplicate effort.

This would let us push down aggregates for evaluation by the foreign server. Here's a blog post about implementing this in Multicorn, a FDW framework for Python: https://www.splitgraph.com/blog/postgresql-fdw-aggregation-pushdown-multicorn-part-1

[burmecia]

Thanks @jyc ! That looks great, we have no plan yet but if you can contribute that will be much appreciated.

[jyc]

Thanks! FYI I'm not sure when I'll be able to get to it, so please don't wait on me.

Do you have a preferred way for people to send in questions? I tried to follow the "hello world" instructions in the README right now and ran into an error:

$ pwd
/Users/jyc/projects/supabase-wrappers/wrappers
$ cargo pgx run --features helloworld_fdw
    Building extension with features helloworld_fdw
     Running command "cargo" "build" "--features" "helloworld_fdw" "--message-format=json-render-diagnostics"
   Compiling proc-macro2 v1.0.47
   Compiling unicode-ident v1.0.5
...
   Compiling wrappers v0.1.0 (/Users/jyc/projects/supabase-wrappers/wrappers)
    Finished dev [unoptimized + debuginfo] target(s) in 48.51s
  Installing extension
     Copying control file to /Users/jyc/.pgx/15.1/pgx-install/share/postgresql/extension/wrappers.control
     Copying shared library to /Users/jyc/.pgx/15.1/pgx-install/lib/postgresql/wrappers-0.1.0.so
 Discovering SQL entities
  Discovered 1 SQL entities: 0 schemas (0 unique), 1 functions, 0 types, 0 enums, 0 sqls, 0 ords, 0 hashes, 0 aggregates, 0 triggers
     Writing SQL entities to /Users/jyc/.pgx/15.1/pgx-install/share/postgresql/extension/wrappers--0.1.0.sql
    Finished installing wrappers
    Starting Postgres v15 on port 28815
     Creating database wrappers
psql (15.1)
Type "help" for help.

wrappers=# drop extension if exists wrappers cascade;
NOTICE:  extension "wrappers" does not exist, skipping
DROP EXTENSION
wrappers=# create extension wrappers;
CREATE EXTENSION
wrappers=#
wrappers=# -- create foreign data wrapper and enable 'HelloWorldFdw'
wrappers=# drop foreign data wrapper if exists helloworld_wrapper cascade;
NOTICE:  foreign-data wrapper "helloworld_wrapper" does not exist, skipping
DROP FOREIGN DATA WRAPPER
wrappers=# create foreign data wrapper helloworld_wrapper
wrappers-#   handler wrappers_handler
wrappers-#   validator wrappers_validator
wrappers-#   options (
wrappers(#     wrapper 'HelloWorldFdw'
wrappers(#   );
ERROR:  function wrappers_handler() does not exist

It looks like the wrappers extension has loaded properly:

wrappers=# \dx
                         List of installed extensions
   Name   | Version |   Schema   |                 Description
----------+---------+------------+---------------------------------------------
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
 wrappers | 0.1.0   | public     | Foreign data wrappers developed by Supabase
(2 rows)

... but the wrappers_handler function isn't defined. Did I do something wrong?

[burmecia]

We just did a major refactoring for the code, docs aren't updated yet, will update it soon.

[ibotty]

I am also interested in supporting this pushdown. What do you think of an API akin to the one by Multicorn?

In the ForeignDataWrapper trait add a function each

• that returns the aggregations that can be pushed down,

• that returns whether GROUP BY can be pushed down.

  Also modify the functions that take the query, i.e begin_scan but and get_rel_size to add a group_by and a aggregates parameter.

  What do you think?