在duckdb_fdw中调用'postgres_scan'时遇到一个libpq报错, 但是在duckdb cli中调用正常.

[digoal]

在duckdb_fdw中调用'postgres_scan'时遇到一个错误:

postgres=# 
SELECT duckdb_execute('duckdb_server',   
$$  
COPY (
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
) 
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet'; 
$$);  
ERROR:  HV00L: SQL error during prepare: IO Error: Unable to connect to Postgres at dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456: libpq is incorrectly linked to backend functions
   
COPY (
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
) 
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet'; 

LOCATION:  sqlite_prepare_wrapper, duckdb_fdw.c:504

这条调用在duckdb cli中直接调用是正常的.

[digoal]

COPY (
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
) 
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/abc.parquet'; 

100% ▕████████████████████████████████████████████████████████████▏ 

[digoal]

可能和这个issue类似:
https://github.com/greenplum-db/gpdb/issues/11400

https://github.com/greenplum-db/gpdb/commit/667f0c37bc6d7bce7be8b758652ef95ddb823e19

* Fix postgres_fdw's libpq issue

When using posgres_fdw, it reports the following error:
unsupported frontend protocol 28675.0: server supports 2.0 to 3.0

root cause: Even if postgres_fdw.so is dynamic linked to libpq.so
which is compiled with the option -DFRONTEND, but when it's loaded
in gpdb and run, it will use the backend libpq which is compiled together
with postgres program and reports the error.

We statically link libpq into postgres_fdw and hide all the symbols
of libpq.a with --exclude-libs=libpq.a to make it uses the frontend
libpq.

As postgres_fdw is compiled as a backend without -DFRONTEND, and linked
to libpq which is a frontend, but _PQconninfoOption's length is
different between backend and frontend as there is a macro in it.
The backend's _PQconninfoOption has field connofs, but the frontend
doesn't. This leads to the crash of postgres_fdw. So we delete the
frontend macro in _PQconninfoOption.

* Add FRONTEND macro on including libpq header files

postgres_fdw is compiled as a backend, it needs the server's
header files such as executor/tuptable.h. It also needs libpq
to connect to a remote postgres database, so it's staticly linked
to libpq.a which is compiled as a frontend using -DFRONTEND.

But the struct PQconninfoOption's length is different between
backend and frontend, there is no "connofs" field in frontend.
When postgres_fdw calls the function "PQconndefaults" implemented
in libpq.a and uses the returned PQconninfoOption variable, it crashes,
because the PQconninfoOption variable returned by libpq.a doesn't contain
the "connofs" value, but the postgres_fdw thinks it has, so it crashes.

In last commit, we remove the FRONTEND macro in struct PQconninfoOption
to make PQconninfoOption is same in backend and frontend, but that brings
an ABI change. To avoid that, we revert that, and instead, we add
the FRONTEND macro on including libpq header files, so that
postgres_fdw can process the libpq's variables returned by libpq.a's
functions as frontend.

* Report error if the libpq-fe.h is included before postgres_fdw.h
postgres_fdw needs to include frontend mode libpq-fe.h, so if
the libpq-fe.h is included before the postgres_fdw.h, and we
don't know if it is frontend mode, so we just report the error here.

[alitrack]

请分别告知DuckDB、PostgreSQL和postgres_scan的版本

[digoal]

请分别告知DuckDB、PostgreSQL和postgres_scan的版本

DuckDB: v0.7.1
PostgreSQL: 15.2
postgres_scan: v0.7.1

[digoal]

不需要依赖oss环境, 也能复现这个问题:

SELECT duckdb_execute('duckdb_server',   
$$  
CREATE OR REPLACE TEMP macro v1() as table 
select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres host=127.0.0.1 port=1921', 'public', 't1');
$$);  
-- 以上正常

-- 以下报错
SELECT duckdb_execute('duckdb_server',   
$$  
create table t as select * from v1();
$$);  

SELECT duckdb_execute('duckdb_server',   
$$  
select * from v1();
$$);  

[alitrack]

宏不会执行里面的代码，所以不会报错
另外参照gpdb的方案依然不行， 尝试在里面加了段测试代码，一样的报错
这个函数检测触发的报错
pg_link_canary_is_frontend

[digoal]

是因为duckdb_fdw向duckdb请求调用postgres_scanner是 postgres server(使用backend protocol) 发起, 但是postgres_scanner是client使用frontend protocol?

[digoal]

这点有点像 postgres_fdw 即是 client又是server

[alitrack]

暂时没有解决办法，不过建议查询和archive分开（独立的进程或者程序来做数据库的归档）

[alitrack]

@digoal
好消息，
最新版本duckdb_fdw 测试postgres_scan 成功了