Can you add support for pg14?

[luss]

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]

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]

@luss Thanks! I will do some testing today.

[denishpatel]

@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]

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]

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

[olirice]

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:

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

[luss]

please resubmit this to the new pgsql-io/multicorn2 project

On Thu, Apr 14, 2022 at 6:22 PM Oliver Rice @.***> wrote:

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

DockerfileFROM 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-pipRUN python3 -m pip install -U pipRUN python3 -m pip install wheel setuptoolsRUN python3 -m pip install pytzRUN 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: [image: Screen Shot 2022-04-14 at 5 03 27 PM] https://user-images.githubusercontent.com/12958657/163486096-871b3563-79d7-43db-ac4b-1c29d8ed3387.png

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

— Reply to this email directly, view it on GitHub <pgsql-io/multicorn2#4>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHUG3XNBWQTCK7HHBDDVFCLBNANCNFSM5SQUARYA . You are receiving this because you were mentioned.Message ID: @.***>

[luss]

I'll figure it out.. Long live multicorn2. :-)

On Thu, Apr 14, 2022 at 7:28 PM Denis Lussier @.***> wrote:

please resubmit this to the new pgsql-io/multicorn2 project

On Thu, Apr 14, 2022 at 6:22 PM Oliver Rice @.***> wrote:

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

DockerfileFROM 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-pipRUN python3 -m pip install -U pipRUN python3 -m pip install wheel setuptoolsRUN python3 -m pip install pytzRUN 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: [image: Screen Shot 2022-04-14 at 5 03 27 PM] https://user-images.githubusercontent.com/12958657/163486096-871b3563-79d7-43db-ac4b-1c29d8ed3387.png

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

— Reply to this email directly, view it on GitHub <pgsql-io/multicorn2#4>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHUG3XNBWQTCK7HHBDDVFCLBNANCNFSM5SQUARYA . You are receiving this because you were mentioned.Message ID: @.***>

[olirice]

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]

Ahh, cool. I didn't know I could do that.

On Thu, Apr 14, 2022 at 8:30 PM Oliver Rice @.***> wrote:

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

— Reply to this email directly, view it on GitHub <pgsql-io/multicorn2#4>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAMWOHVUUIHNB3SNENFZLOLVFC2CVANCNFSM5SQUARYA . You are receiving this because you were mentioned.Message ID: @.***>

[luss]

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

[denishpatel]

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

[luss]

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

[denishpatel]

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

[olirice]

From explain plan:

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

working great! thanks @luss

[luss]

:-)