postgres-fdw 集成cratedb 的尝试的几个问题

尽管cratedb 支持pg 协议但是不是100%兼容的（不像yugabyte db，基于pg）
所以进行了一些尝试以及修改pg fdw 源码，支持（目前还是有问题）

start transaction 问题

cratedb 对于transction 是不支持的，但是做了协议兼容（兼容的begin语法）所以有问题了
因为pg fdw 对于transction 使用了start 处理，解决方法（修改pg fdw 源码为begin）

参考patch https://github.com/rongfengliang/pg-fdw-patch

 

 

docker 构建

 

FROM postgres:12.3 as build

​

LABEL AUTHOR="dalongrong"

​

ENV TZ=Asia/Shanghai \

    LANG=zh_CN.UTF-8 

​

RUN set -x \

    && ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone \

    && localedef -i zh_CN -c -f UTF-8 -A /usr/share/locale/locale.alias $LANG \

    && echo 'LANG="$LANG"' > /etc/default/locale \

    && apt-get update \

    && apt-get install -y --no-install-recommends patch ca-certificates wget unzip make postgresql-server-dev-"${PG_MAJOR}" gcc libc6-dev libssl-dev libkrb5-dev libaio1  gnupg \

    && wget -O /tmp/postgresql-12.3.tar.gz  https://ftp.postgresql.org/pub/source/v12.3/postgresql-12.3.tar.gz \

    && wget -O /tmp/pg-fdw.patch   https://raw.githubusercontent.com/rongfengliang/pg-fdw-patch/main/pg-fdw.patch \

    && cd /tmp && tar xvzf  postgresql-12.3.tar.gz \

    && cp /tmp/pg-fdw.patch /tmp/postgresql-12.3/contrib/postgres_fdw \

    && cd /tmp/postgresql-12.3/contrib/postgres_fdw \

    && patch  -p0  connection.c pg-fdw.patch \

    && make USE_PGXS=1 install \

    && apt-get purge -y --auto-remove wget unzip make gnupg gcc postgresql-server-dev-"${PG_MAJOR}" libc6-dev libssl-dev libkrb5-dev \

    && apt-get autoremove \

    && apt-get autoclean \

    && rm -rf /var/lib/apt/lists/* \

    && rm -rf /tmp/*

​

FROM postgres:12.3

COPY --from=build /usr/lib/postgresql/12/lib/postgres_fdw.so /usr/lib/postgresql/12/lib/postgres_fdw.so

COPY --from=build /usr/lib/postgresql/12/lib/bitcode/postgres_fdw  /usr/lib/postgresql/12/lib/bitcode/postgres_fdw

COPY --from=build /usr/share/postgresql/12/extension/postgres_fdw.control  /usr/share/postgresql/12/extension/postgres_fdw.control

COPY --from=build /usr/share/postgresql/12/extension/postgres_fdw--1.0.sql  /usr/share/postgresql/12/extension/postgres_fdw--1.0.sql

• 游标问题

此问题暂时无解，需要等待cratedb 对于游标的支持，主要的命令为DECLARE liahona CURSOR FOR SELECT * FROM films;
fdw 参考代码

 

 

参考资料

https://github.com/crate/crate/issues/10212
https://www.postgresql.org/docs/12/sql-start-transaction.html
https://github.com/rongfengliang/pg-fdw-patch
https://github.com/crate/crate/projects/5#card-48254180
https://sourcegraph.com/github.com/postgres/postgres/-/blob/contrib/postgres_fdw/postgres_fdw.c#L3342
https://sourcegraph.com/github.com/postgres/postgres/-/blob/contrib/postgres_fdw/connection.c#L561