Replies: 21 comments 2 replies
-
|
It looks like plugins must be manually enabled - see https://github.com/duckdb/pg_duckdb/pull/403/files for |
Beta Was this translation helpful? Give feedback.
-
|
Try |
Beta Was this translation helpful? Give feedback.
-
|
fails with the smae error |
Beta Was this translation helpful? Give feedback.
-
|
but this link from the log http://extensions.duckdb.org/v1.2.0/linux_arm64/duckpgq.duckdb_extension.gz indeed refers to a 403 URL |
Beta Was this translation helpful? Give feedback.
-
|
Ah right, that's the wrong URL for a community extension. |
Beta Was this translation helpful? Give feedback.
-
|
this function was not defined for me in 0.3.1 re-trying now with there: SELECT duckdb.install_extension('duckpgq', 'community');However: CREATE TABLE Person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv';the example code now fails: even fails if: |
Beta Was this translation helpful? Give feedback.
-
|
This is expected, queries have to be valid Postgres queries first, and you're also in extension land which is "at your own risk". To directly access DuckDB, use |
Beta Was this translation helpful? Give feedback.
-
|
intersting: select * from duckdb.query('SELECT * FROM ''https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv''');
[2025-03-25 14:12:38] [XX000] ERROR: (PGDuckDB/CreatePlan) IO Error: Extension "/var/lib/postgresql/data/duckdb_extensions/v1.2.1/linux_arm64/duckpgq.duckdb_extension" could not be loaded because its signature is either missing or invalid and unsigned extensions are disabled by configuration (allow_unsigned_extensions)
but still keeps failing if it looks like these extensiosn are there however how can I validate if they are correct? detailsDockerfile FROM postgres:17.4-bookworm AS base
FROM base AS builder
RUN set -ex \
&& apt-get update \
&& apt-get upgrade -y \
&& apt-get install -y \
wget \
build-essential \
cmake \
git \
ninja-build \
pkg-config \
liblz4-dev \
postgresql-server-dev-${PG_MAJOR}
### pg_duckdb
WORKDIR /plugins
# 2065ff1427da2e9ff3f1eddee1d6ebb1b1941885
# RUN git clone --branch v0.3.1 --single-branch https://github.com/duckdb/pg_duckdb.git && \
RUN mkdir pg_duckdb && cd pg_duckdb && git init && \
git remote add origin https://github.com/duckdb/pg_duckdb.git && \
git fetch --depth 1 origin 2065ff1427da2e9ff3f1eddee1d6ebb1b1941885 && \
git checkout FETCH_HEAD && \
#cd pg_duckdb && \
make && \
make install
FROM base AS db
RUN set -ex \
&& apt-get update \
&& apt-get upgrade -y \
&& apt-get install -y \
wget \
llvm-19 llvm-19-dev
### pg_duckdb
COPY --from=builder /plugins/pg_duckdb /plugins/pg_duckdb
COPY --from=builder /usr/lib/postgresql/17/lib/libduckdb.so /usr/lib/postgresql/17/lib/libduckdb.so
# Copy the installation script into the image
COPY pg_duckdb_copy.sh /plugins/pg_duckdb/pg_duckdb_copy.sh
RUN chmod +x /plugins/pg_duckdb/pg_duckdb_copy.sh && \
/plugins/pg_duckdb/pg_duckdb_copy.sh
RUN apt purge -y --autoremove \
wget \
llvm-19 llvm-19-dev && \
rm -rf /var/lib/apt/lists/*
# /var/lib/postgresql/data
WORKDIR /plugins
installation script for the multi-stage container #!/usr/bin/env bash
set -euo pipefail
# Use PG_MAJOR from environment (default to 17 if not set)
PG_MAJOR=${PG_MAJOR:-17}
# Directories where PostgreSQL expects extension files:
LIB_DIR="/usr/lib/postgresql/${PG_MAJOR}/lib"
EXT_DIR="/usr/share/postgresql/${PG_MAJOR}/extension"
BITCODE_DIR="${LIB_DIR}/bitcode/pg_duckdb"
# Create required directories
mkdir -p "${LIB_DIR}"
mkdir -p "${EXT_DIR}"
mkdir -p "${BITCODE_DIR}/src"
mkdir -p "${BITCODE_DIR}/src/catalog"
mkdir -p "${BITCODE_DIR}/src/pg"
mkdir -p "${BITCODE_DIR}/src/scan"
mkdir -p "${BITCODE_DIR}/src/utility"
mkdir -p "${BITCODE_DIR}/src/vendor"
# Copy shared libraries
install -c -m 755 /plugins/pg_duckdb/pg_duckdb.so "${LIB_DIR}/pg_duckdb.so"
# Copy the extension control file and SQL scripts.
# Note: The control file’s default_version should match the available SQL files.
install -c -m 644 /plugins/pg_duckdb/pg_duckdb.control "${EXT_DIR}/"
cp -a /plugins/pg_duckdb/sql/. "${EXT_DIR}/"
find /plugins/pg_duckdb/sql -type f -name "*.sql" -exec install -c -m 644 {} "${EXT_DIR}/" \;
# Copy bitcode files from the source tree into the corresponding bitcode directories
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_background_worker.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_ddl.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_detoast.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_duckdb.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_hooks.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_metadata_cache.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_node.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_options.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_planner.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_ruleutils.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_table_am.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_types.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_utils.bc "${BITCODE_DIR}/src/"
install -c -m 644 /plugins/pg_duckdb/src/pgduckdb_xact.bc "${BITCODE_DIR}/src/"
# Copy catalog bitcode files
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_catalog.bc "${BITCODE_DIR}/src/catalog/"
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_schema.bc "${BITCODE_DIR}/src/catalog/"
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_storage.bc "${BITCODE_DIR}/src/catalog/"
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_table.bc "${BITCODE_DIR}/src/catalog/"
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_transaction.bc "${BITCODE_DIR}/src/catalog/"
install -c -m 644 /plugins/pg_duckdb/src/catalog/pgduckdb_transaction_manager.bc "${BITCODE_DIR}/src/catalog/"
# Copy pg bitcode files
install -c -m 644 /plugins/pg_duckdb/src/pg/error_data.bc "${BITCODE_DIR}/src/pg/"
install -c -m 644 /plugins/pg_duckdb/src/pg/relations.bc "${BITCODE_DIR}/src/pg/"
install -c -m 644 /plugins/pg_duckdb/src/pg/transactions.bc "${BITCODE_DIR}/src/pg/"
# Copy scan bitcode files
install -c -m 644 /plugins/pg_duckdb/src/scan/postgres_scan.bc "${BITCODE_DIR}/src/scan/"
install -c -m 644 /plugins/pg_duckdb/src/scan/postgres_table_reader.bc "${BITCODE_DIR}/src/scan/"
# Copy utility bitcode file
install -c -m 644 /plugins/pg_duckdb/src/utility/copy.bc "${BITCODE_DIR}/src/utility/"
# Copy vendor bitcode files
install -c -m 644 /plugins/pg_duckdb/src/vendor/pg_explain.bc "${BITCODE_DIR}/src/vendor/"
install -c -m 644 /plugins/pg_duckdb/src/vendor/pg_ruleutils_14.bc "${BITCODE_DIR}/src/vendor/"
install -c -m 644 /plugins/pg_duckdb/src/vendor/pg_ruleutils_15.bc "${BITCODE_DIR}/src/vendor/"
install -c -m 644 /plugins/pg_duckdb/src/vendor/pg_ruleutils_16.bc "${BITCODE_DIR}/src/vendor/"
install -c -m 644 /plugins/pg_duckdb/src/vendor/pg_ruleutils_17.bc "${BITCODE_DIR}/src/vendor/"
# Now run LLVM LTO thinlink to produce the index file.
# We use `find` to list all .bc files under pg_duckdb/src
cd "${LIB_DIR}/bitcode"
llvm_lto_bin="/usr/lib/llvm-19/bin/llvm-lto"
if [ ! -x "${llvm_lto_bin}" ]; then
echo "Error: ${llvm_lto_bin} not found or not executable"
exit 1
fi
${llvm_lto_bin} -thinlto -thinlto-action=thinlink -o pg_duckdb.index.bc $(find pg_duckdb/src -type f -name "*.bc")
echo "pg_duckdb extension installed successfully."
|
Beta Was this translation helpful? Give feedback.
-
|
|
Beta Was this translation helpful? Give feedback.
-
|
indeed - confirmed call duckdb.recycle_ddb();
SET duckdb.allow_unsigned_extensions = true;
select * from duckdb.query('SELECT * FROM ''https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv''');
this is executing fine however: select * from duckdb.query('CREATE TABLE Person AS SELECT * FROM ''https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv''');fails with: |
Beta Was this translation helpful? Give feedback.
-
CREATE TABLE Person AS from duckdb.query('SELECT * FROM ''https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv''');seems to work though (nesting) is a bit tricky to get used to |
Beta Was this translation helpful? Give feedback.
-
|
What is a bit unclear to me is: For a SQL of CREATE PROPERTY GRAPH snb
VERTEX TABLES (
Person
)
EDGE TABLES (
Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
DESTINATION KEY (Person2Id) REFERENCES Person (id)
LABEL knows
);
FROM GRAPH_TABLE (snb
MATCH (a:Person)-[k:knows]->(b:Person)
COLUMNS (a.id, b.id)
)
LIMIT 1;
FROM GRAPH_TABLE (snb
MATCH p = ANY SHORTEST (a:person)-[k:knows]->{1,3}(b:Person)
COLUMNS (a.id, b.id, path_length(p))
)
LIMIT 1;
FROM local_clustering_coefficient(snb, person, knows);
(from the duckpgq example) how to port this over for non-simple SQLs. In particular I wonder if there is a multi-line way of feeding this in to the nested duckdb.query? For example: select * from duckdb.query('
CREATE PROPERTY GRAPH snb
VERTEX TABLES (
Person
)
EDGE TABLES (
Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
DESTINATION KEY (Person2Id) REFERENCES Person (id)
LABEL knows
)');fails with: unlike before - here the graph types are duckdb / pgq only and not available in PG. How should this be handled? |
Beta Was this translation helpful? Give feedback.
-
|
When you don't want or need to get the results, use duckdb.raw_query. This should solve the "single select statement" error. In Postgres, you can use $$ as your quote identifier to pass multiple queries and avoid double quoting.
Yes, I think you're reaching the end of what's possible. I believe there's a discussion about this topic. |
Beta Was this translation helpful? Give feedback.
-
select *
from duckdb.raw_query($$
CREATE PROPERTY GRAPH snb
VERTEX TABLES (
Person
)
EDGE TABLES (
Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
DESTINATION KEY (Person2Id) REFERENCES Person (id)
LABEL knows
)$$);fails with is this still a misunderstanding on my side? or reaching the limits. Can you link the other discussion? the example of seems to work though SELECT * FROM duckdb.raw_query($$ INSTALL duckpgq FROM community; $$);let me double-check the query |
Beta Was this translation helpful? Give feedback.
-
|
Try |
Beta Was this translation helpful? Give feedback.
-
|
Indeed: SELECT * FROM duckdb.raw_query($$ CREATE PROPERTY GRAPH snb
VERTEX TABLES (
Person
)
EDGE TABLES (
Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
DESTINATION KEY (Person2Id) REFERENCES Person (id)
LABEL knows
); $$);somewhat works but fails with: this means I would need to create the persons table not in postgres but differently or read it in a way that duckdb can read it? (from postgres i.e. inception style)? |
Beta Was this translation helpful? Give feedback.
-
|
let me add this also here - perhaps this helps cwida/duckpgq-extension#226 |
Beta Was this translation helpful? Give feedback.
-
|
You can use read_csv to create that table, and then DuckDB should be able to read it. Otherwise you'll have to create it on the DuckDB side, where it would only exist temporarily in memory, which is basically the same behavior as |
Beta Was this translation helpful? Give feedback.
-
|
but could I also refernece it from postgres? But this would be less efficient right? so you mean using the execute raw with create table in duckdb (in-memory)? |
Beta Was this translation helpful? Give feedback.
-
|
Try using |
Beta Was this translation helpful? Give feedback.
-
|
one workaround is: SELECT * FROM duckdb.raw_query($$
CREATE TABLE Person AS SELECT * FROM 'https://gist.githubusercontent.com/Dtenwolde/2b02aebbed3c9638a06fda8ee0088a36/raw/8c4dc551f7344b12eaff2d1438c9da08649d00ec/person-sf0.003.csv'
; $$);
SELECT * FROM duckdb.raw_query($$
CREATE TABLE Person_knows_person 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 snb
VERTEX TABLES (
Person
)
EDGE TABLES (
Person_knows_person SOURCE KEY (Person1Id) REFERENCES Person (id)
DESTINATION KEY (Person2Id) REFERENCES Person (id)
LABEL knows
); $$);lowercasing still fails with table person does not exist |
Beta Was this translation helpful? Give feedback.
-
|
But: SELECT * FROM duckdb.raw_query($$
FROM GRAPH_TABLE (snb
MATCH (a:Person)-[k:knows]->(b:Person)
COLUMNS (a.id, b.id)
)
LIMIT 10;
; $$);
esult: id id_1 but does not output the values as a table (in the table output) how can I change this @wuputah ? |
Beta Was this translation helpful? Give feedback.
-
|
plus: SELECT * FROM duckdb.raw_query($$
FROM local_clustering_coefficient(snb, person, knows);
; $$);fails with: |
Beta Was this translation helpful? Give feedback.
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
-
What happens?
from postgres with pg_duckdb:
fails with:
but inside a normal duckdb:
INSTALL duckpgq FROM community; LOAD duckpgq;works just fine.
To Reproduce
How can I make use of other extensions from
pg_duckdb?OS:
linux aarch64
pg_duckdb Version (if built from source use commit hash):
0.3.0
Postgres Version (if built from source use commit hash):
17
Hardware:
macbook m2 + docker
Full Name:
Georg Heiler
Affiliation:
CSH
What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.
I have tested with a stable release
Did you include all relevant data sets for reproducing the issue?
No - Other reason (please specify in the issue body)
Did you include all code required to reproduce the issue?
Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?
further details
See https://duckdb.org/community_extensions/extensions/duckpgq.html
it would be great if arbitrary plugins can be executed easily for example
duckpgqBeta Was this translation helpful? Give feedback.
All reactions