pg_duckdb
pg_duckdb copied to clipboard
duckdb
pgq interop not fully there - even for raw_query
What happens?
Prepare the data
SET duckdb.force_execution = true;
SELECT duckdb.install_extension('duckpgq', 'community');
CALL duckdb.recycle_ddb();
SET duckdb.allow_unsigned_extensions = true;
SELECT * FROM duckdb.raw_query($$
CREATE TABLE Person2 AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv'
; $$);
SELECT * FROM duckdb.raw_query($$
CREATE TABLE Person_knows_person2 AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/81c32c9002d4059c2c3073dbca155275/raw/8b440e810a48dcaa08c07086e493ec0e2ec6b3cb/person_knows_person-sf0.003.csv'
; $$);
SELECT * FROM duckdb.raw_query($$ CREATE PROPERTY GRAPH snb2
VERTEX TABLES (
Person2
)
EDGE TABLES (
Person_knows_person2 SOURCE KEY (Person1Id) REFERENCES Person2 (id)
DESTINATION KEY (Person2Id) REFERENCES Person2 (id)
LABEL knows
); $$);
SELECT * FROM duckdb.raw_query($$
FROM GRAPH_TABLE (snb2
MATCH (a:Person2)-[k:knows]->(b:Person2)
COLUMNS (a.id, b.id)
)
; $$);
Problem 1
SELECT * FROM duckdb.raw_query($$
FROM GRAPH_TABLE (snb2
MATCH (a:Person2)-[k:knows]->(b:Person2)
COLUMNS (a.id, b.id)
)
LIMIT 10
; $$);
correctly results in
[2025-08-31 11:50:54] [01000] (PGDuckDB/CreatePlan) Prepared query returned an error: 'Catalog Error: Table Function with name raw_query does not exist!
[2025-08-31 11:50:54] Did you mean "main.pragma_user_agent"?
[2025-08-31 11:50:54] LINE 1: SELECT raw_query FROM duckdb.raw_query('
[2025-08-31 11:50:54] ^
result: id id_1
BIGINT BIGINT
[ Rows: 10]
14 10995116277782
14 24189255811081
14 26388279066668
16 2199023255594
16 26388279066655
16 28587302322180
16 28587302322204
32 2199023255594
32 13194139533352
32 17592186044461
[2025-08-31 11:50:54] 1 row retrieved starting from 1 in 323 ms (execution: 10 ms, fetching: 313 ms)
but this is only in the log output - not in the real output where normally the SQL tables are displayed.
How can this be outputted in the normal tabular output?
Problem 2
SELECT * FROM duckdb.raw_query($$
FROM local_clustering_coefficient(snb2, person2, knows);
; $$);
crashes with
database-1 | 2025-08-31 09:49:43.226 UTC [191] WARNING: (PGDuckDB/CreatePlan) Prepared query returned an error: 'Catalog Error: Table Function with name raw_query does not exist!
database-1 | Did you mean "main.pragma_user_agent"?
database-1 |
database-1 | LINE 1: SELECT raw_query FROM duckdb.raw_query('
database-1 | ^
database-1 | 2025-08-31 09:49:43.256 UTC [191] ERROR: (PGDuckDB/pgduckdb_raw_query_cpp) INTERNAL Error: Attempted to dereference unique_ptr that is NULL!
database-1 |
database-1 | Stack Trace:
database-1 |
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Exception::ToJSON(duckdb::ExceptionType, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)+0x3c) [0xffff84baa6cc]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Exception::Exception(duckdb::ExceptionType, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)+0x28) [0xffff84baa79c]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::InternalException::InternalException(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)+0x1c) [0xffff84bad71c]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::AddCTEMap(duckdb::CommonTableExpressionMap&)+0x84) [0xffff8441f4d4]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::BindNode(duckdb::QueryNode&)+0x24) [0xffff8441f558]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::SubqueryRef&, duckdb::optional_ptr<duckdb::CommonTableExpressionInfo, true>)+0x110) [0xffff843bb790]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::TableRef&)+0x160) [0xffff8441ec10]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::BindTableFunctionInternal(duckdb::TableFunction&, duckdb::TableFunctionRef const&, duckdb::vector<duckdb::Value, true>, std::unordered_map<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, duckdb::Value, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::allocator<std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const, duckdb::Value> > >, duckdb::vector<duckdb::LogicalType, true>, duckdb::vector<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, true>)+0x284) [0xffff843b37d8]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::TableFunctionRef&)+0x5e4) [0xffff843bf1c4]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::TableRef&)+0x114) [0xffff8441ebc4]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::BindNode(duckdb::SelectNode&)+0x34) [0xffff8435faf4]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::BindNode(duckdb::QueryNode&)+0xd4) [0xffff8441f608]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::QueryNode&)+0x60) [0xffff84420370]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::SelectStatement&)+0x40) [0xffff8436b460]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Binder::Bind(duckdb::SQLStatement&)+0x298) [0xffff844202b8]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::Planner::CreatePlan(duckdb::SQLStatement&)+0x9c) [0xffff8442cf8c]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::CreatePreparedStatementInternal(duckdb::ClientContextLock&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::optional_ptr<std::unordered_map<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, duckdb::BoundParameterData, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::allocator<std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const, duckdb::BoundParameterData> > >, true>)+0x248) [0xffff84fd2e9c]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::CreatePreparedStatement(duckdb::ClientContextLock&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::optional_ptr<std::unordered_map<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> >, duckdb::BoundParameterData, duckdb::CaseInsensitiveStringHashFunction, duckdb::CaseInsensitiveStringEquality, std::allocator<std::pair<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const, duckdb::BoundParameterData> > >, true>, duckdb::PreparedStatementMode)+0x2a4) [0xffff84fd4004]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::PendingStatementInternal(duckdb::ClientContextLock&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::PendingQueryParameters const&)+0x90) [0xffff84fdb120]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::PendingStatementOrPreparedStatement(duckdb::ClientContextLock&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::shared_ptr<duckdb::PreparedStatementData, true>&, duckdb::PendingQueryParameters const&)+0xcc) [0xffff84fe10c0]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::PendingStatementOrPreparedStatementInternal(duckdb::ClientContextLock&, std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::shared_ptr<duckdb::PreparedStatementData, true>&, duckdb::PendingQueryParameters const&)+0x100) [0xffff84fe24c0]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::PendingQueryInternal(duckdb::ClientContextLock&, duckdb::unique_ptr<duckdb::SQLStatement, std::default_delete<duckdb::SQLStatement>, true>, duckdb::PendingQueryParameters const&, bool)+0x94) [0xffff84fe1514]
database-1 | /usr/lib/postgresql/17/lib/libduckdb.so(duckdb::ClientContext::Query(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&, bool)+0x13c) [0xffff84fe395c]
database-1 | /usr/lib/postgresql/17/lib/pg_duckdb.so(+0x4cee4) [0xffff86decee4]
database-1 | /usr/lib/postgresql/17/lib/pg_duckdb.so(+0x4d134) [0xffff86ded134]
database-1 | /usr/lib/postgresql/17/lib/pg_duckdb.so(+0x33f1c) [0xffff86dd3f1c]
database-1 | /usr/lib/postgresql/17/lib/pg_duckdb.so(+0x3413c) [0xffff86dd413c]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(fmgr_security_definer+0x150) [0xaaaabae1fce0]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(ExecMakeTableFunctionResult+0x214) [0xaaaabab07ae4]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(+0x2e8090) [0xaaaabab18090]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(standard_ExecutorRun+0x134) [0xaaaabaafe6d4]
database-1 | /usr/lib/postgresql/17/lib/pg_stat_statements.so(+0x380c) [0xffff86e7380c]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(+0x4926e4) [0xaaaabacc26e4]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(PortalRun+0x288) [0xaaaabacc3d38]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(PostgresMain+0x1228) [0xaaaabacc10bc]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(+0x48b6d4) [0xaaaabacbb6d4]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(postmaster_child_launch+0xd8) [0xaaaabac1d738]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(+0x3f11c4) [0xaaaabac211c4]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(PostmasterMain+0xdd0) [0xaaaabac23060]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(main+0x21c) [0xaaaaba91662c]
database-1 | /lib/aarch64-linux-gnu/libc.so.6(+0x27740) [0xffff89a77740]
database-1 | /lib/aarch64-linux-gnu/libc.so.6(__libc_start_main+0x98) [0xffff89a77818]
database-1 | postgres: admin_user duckpond 172.18.0.3(33096) SELECT(_start+0x30) [0xaaaaba916b70]
database-1 |
database-1 | This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
database-1 | For more information, see https://duckdb.org/docs/stable/dev/internal_errors
database-1 | 2025-08-31 09:49:43.256 UTC [191] STATEMENT: SELECT * FROM duckdb.raw_query($$
database-1 | FROM local_clustering_coefficient(snb2, person2, knows);
database-1 | ; $$)
v View in Docker Desktop o View Config w Enable Watch
To Reproduce
Use the example above
OS:
aarch64 osx
pg_duckdb Version (if built from source use commit hash):
52ba7aa798a6c4339428b0eb63975ea03cc69954
Postgres Version (if built from source use commit hash):
postgres:17.5-bookworm
Hardware:
M2 macbook
Full Name:
Georg Heiler
Affiliation:
Complexity Science Hub
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a source build
Did you include all relevant data sets for reproducing the issue?
Yes
Did you include all code required to reproduce the issue?
- [x] Yes, I have
Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
- [x] Yes, I have
duckpgq is currently not supported by pg_duckdb. The first problem requires duckpgq to fix this issue: https://github.com/cwida/duckpgq-extension/issues/210 Then you should be able to use duckdb.query instead of duckdb.raw_query.
The second issue I'm not sure about what's going on exactly. It's clearly a bug, but I'm not sure whether it's caused by pg_duckdb, DuckDB or duckpgq. Does it reproduce in plain duckdb too with the queries you ran?
