How to make duckpgq work on musl/Alpine compiled pg_postgres image

[veikkoeeva]

Hi!

I would like to use this extension in pg_duckdb that is compiled with musl in Alpine instead of the usual glibc.

The case I have is that I used pg_duckdb to work on Alpine Linux Postgresql with the goal to enable SQL/PGQ and other DuckDb amenities in PostgreSQL.

The concrete instructions are (currently) at Alpine Linux ([here])(https://github.com/veikkoeeva/AITest/blob/main/src/AITest.Postgresql/Postgresql.Dockerfile#L45)). I can indeed run this Docker image and with a quick check pgduckdb works. So far so good, things seem to work! However, then I tried

select duckdb.raw_query($$ install duckpgq from community; $$);
select duckdb.raw_query($$ load duckpgq; $$);

and received an error.

Question:
If one wanted to enable a support for duckpgq to support mus, how would this support be concretely implemented so that for instance the given piece of sql would work also in musl based images?

Maybe also, are there are instructions on how to add duckpgq to DuckDb as precompiled binary?

I am thinking this is similar in nature to #170. Also maybe I should open a similar question or discussion in pg_duckdb and use this as a concrete driver if there needs to be some things to happen also there.

[Dtenwolde]

Hi there! Could you tell me what error you are getting when you are loading duckpgq?

It could very well be related to #170, I am not familiar enough with Alpine but happy to look into it.

Currently, DuckPGQ won't work with pg_duckdb due to an issue with the query() function (#210).

Maybe also, are there are instructions on how to add duckpgq to DuckDb as precompiled binary?

If I understand correctly, using the install and load commands are already attempting to do this. And if the install command works, that means that DuckPGQ is available as a precompiled version for your DuckDB & architecture combination :)

[veikkoeeva]

Sure!

1. select duckdb.raw_query($$ install duckpgq from community; $$);

Empty result (success).

2. select duckdb.raw_query($$ load duckpgq; $$);

SQL Error [XX000]: ERROR: (PGDuckDB/pgduckdb_raw_query) IO Error: Extension "/var/lib/postgresql/data/duckdb_extensions/v1.1.3/linux_amd64/duckpgq.duckdb_extension" could not be loaded: Error loading shared library ld-linux-x86-64.so.2: No such file or directory (needed by /var/lib/postgresql/data/duckdb_extensions/v1.1.3/linux_amd64/duckpgq.duckdb_extension)

I note that I actually had to rebuild the image with git clone --depth 1 --recurse-submodules --branch v0.2.0 https://github.com/duckdb/pg_duckdb.git – that is, with v.0.2.0 tag (in the link it uses v0.3.1).

With v0.3.1 tag already the first query select duckdb.raw_query($$ install duckpgq from community; $$); causes

SQL Error [XX000]: ERROR: (PGDuckDB/pgduckdb_raw_query) IO Error: Failed to install 'duckpgq'
The file was built specifically for DuckDB version 'v0.0.1' and can only be loaded with that version of DuckDB. (this version of DuckDB is > 'v1.2.0')

(I don't remember if there was a change in this installation query, but I also assume the error would state it explicitly.)

[Dtenwolde]

Thank you!

SQL Error [XX000]: ERROR: (PGDuckDB/pgduckdb_raw_query) IO Error: Extension "/var/lib/postgresql/data/duckdb_extensions/v1.1.3/linux_amd64/duckpgq.duckdb_extension" could not be loaded: Error loading shared library ld-linux-x86-64.so.2: No such file or directory (needed by /var/lib/postgresql/data/duckdb_extensions/v1.1.3/linux_amd64/duckpgq.duckdb_extension)

This indeed seems like a dynamic linking issue, and duckpgq is a bit special in this regard, so this might be tricky to get working. I'll try to reproduce it when I find the time.

SQL Error [XX000]: ERROR: (PGDuckDB/pgduckdb_raw_query) IO Error: Failed to install 'duckpgq'
The file was built specifically for DuckDB version 'v0.0.1' and can only be loaded with that version of DuckDB. (this version of DuckDB is > 'v1.2.0') (I don't remember if there was a change in this installation query).

This seems like a versioning issue (the v0.0.1 is odd) and from the runner I can see that indeed the version seems to be incorrect for the musl version: https://github.com/cwida/duckpgq-extension/actions/runs/13459597964/job/37611492197#step:22:88 (This should read v1.2.0. I'll ping @carlopi because he is familiar with this.

Though there is the change this might still not work even if the version is correct because of the dynamic linking issue. But let's fix one issue at a time :)

[veikkoeeva]

Looks like a plan! No worries either. I would explore further if I could the equation of my skills and time to work. But I'm poking a bit around these DuckDb and extension codebases currently to see how things work here. :)