Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GetForeignUpperPaths & aggregate pushdown #22

Open
jyc opened this issue Dec 5, 2022 · 4 comments
Open

GetForeignUpperPaths & aggregate pushdown #22

jyc opened this issue Dec 5, 2022 · 4 comments
Assignees
Labels
enhancement New feature or request

Comments

@jyc
Copy link

jyc commented Dec 5, 2022

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

@jyc jyc added the enhancement New feature or request label Dec 5, 2022
@jyc jyc changed the title GetForeignUpperPaths & aggregation pushdown GetForeignUpperPaths & aggregate pushdown Dec 5, 2022
@burmecia
Copy link
Member

burmecia commented Dec 6, 2022

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

@jyc
Copy link
Author

jyc commented Dec 6, 2022

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
Copy link
Member

burmecia commented Dec 6, 2022

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

@ibotty
Copy link
Contributor

ibotty commented Jul 14, 2023

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

3 participants