pgsql-io/multicorn2

Can you add support for pg14?

denishpatel opened this issue · 17 comments

denishpatel commented
Can you add support for pg14?
luss commented

yes. we are actively working towards this goal. PG14 introduced some tricky breaking changes to the internal PG API's that Multicorn uses, hence the delays.

luss commented

Tonight I worked on the code a bit. The code in the master branch now compiles (and probably runs) on PG14 (and still compile and runs on PG10 thru PG13. Later this week I'll do some testing. Who wants get involved?

denishpatel commented

@luss Thanks! I will do some testing today.

denishpatel commented

@luss I tested Multicorn and it compiles with pg14. However, when I try to use Multicorn with Bigquery FDW(which uses Multicorn), the "WHERE clause in query is not working. do you think recent change could have caused WHERE clause pushdown to stop working?

PS: The WHERE pushdown is working on PG13 so it should be related to recent changes.

luss commented

Hmmmm. Nothing I did should affect that, but I haven't tested yet.

Could you please make sure that building for PG13 from the same source code works for pushdown in BigqueryFDW. I will also test with BigQueryFDW later this week and/or this weekend. We should be able to figure this out...

denishpatel commented

@luss yeah. The push down works on pg13 build on same source code.

olirice commented

Here is a reproduction case showing to show that filter push down in pg14 is not occurring

Postgres 14

# Dockerfile
FROM supabase/postgres:latest

RUN apt-get update \
    && apt-get install -y --no-install-recommends \
      build-essential \
      git \
      postgresql-server-dev-14

RUN apt-get install -y python3-pip
RUN python3 -m pip install -U pip
RUN python3 -m pip install wheel setuptools
RUN python3 -m pip install pytz
RUN python3 -m pip install sqlalchemy


RUN git clone https://github.com/pgsql-io/Multicorn.git \ 
    && cd Multicorn \
    && make \
    && make install \
    && ldconfig

RUN python3 -m pip install -e /Multicorn

RUN python3 -c 'from sqlalchemy import create_engine; eng = create_engine("sqlite:////app.db"); eng.execute("create table foo(id integer primary key);"); eng.execute("insert into foo(id) values (1)")'

Create the docker image

docker build -t 'postgres_multicorn' .

Run the docker image

docker run --rm --name postgres_multicorn -p 5407:5432 -d -e POSTGRES_DB=multidb -e POSTGRES_PASSWORD=password -e POSTGRES_USER=postgres -d postgres_multicorn

You can connect to the database multidb, port 5407, password password, and user postgres.

Then in SQL

create extension multicorn;

create server alchemy_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

create foreign table foo (
    id int
) server alchemy_srv options (
  db_url 'sqlite:////app.db',
  tablename 'foo'
);

explain select * from foo where id = 1

Which shows the explain plan:
Screen Shot 2022-04-14 at 5 03 27 PM

Hope that helps. We're interested in using multicorn on 14 too

  • 🚀1
luss commented
  • 👍1
luss commented
  • 🎉2
olirice commented

Could you transfer the issue to the other repo instead to keep the context from previous messages?

It's on the right hand sidebar "Transfer Issue"

If not, I'll copy of over tomorrow

Thanks

luss commented
luss commented

Now this issue is properly with the multicorn2 project and I am working on debugging the pushdown iussue in pg14 today.

  • 👍1
  • 🚀1
denishpatel commented

@luss Let me know if you need help with testing.

luss commented

Hey Guys, please test some more with v2.2. It now supports pushdown and is tested in pg13 & pg14 with python3.6

denishpatel commented

@luss I just tested Google BigQuery FDW with pg14 and WHERE pushdown is working as expected. Thanks for the fix!

olirice commented

From explain plan:

Multicorn: SELECT id, message, FROM some_table WHERE project = :project_1"

working great! thanks @luss

luss commented

:-)