Postgres ATTACH ignores explicit alias and fails for database names containing hyphens

[steinarts]

When attaching a PostgreSQL database whose database name contains a hyphen (-), ATTACH fails even when an explicit alias is provided.

Example:

ATTACH
'host=xxx.xxx.xxx.xxx port=5432 dbname=xy_vip-handball_db user=... password=...'
AS vip_pg
(DBTYPE postgres, SCHEMA='public');

Expected:
internal DuckDB catalog uses vip_pg

Actual:
The extension extracts the PostgreSQL database name and uses that as the internal DuckDB catalog name.

Generated statement:

ATTACH '...'
AS xy_vip-handball_db
(TYPE postgres, SCHEMA public, read_only);

DuckDB then reports
Parser Error: syntax error at or near "-"

Root cause
postgres/src/storage/postgres_storage.cpp

auto catalogName = extractDBName(dbPath);

if (dbName == "") {
dbName = catalogName;
}

connector->connect(dbPath, catalogName, schemaName, clientContext);

Even when an explicit alias (dbName) is supplied, catalogName is always derived from the PostgreSQL connection string.

postgres/src/connector/postgres_connector.cpp

executeQuery(std::format(
"attach '{}' as {} (TYPE postgres, SCHEMA {}, read_only);",
dbPath,
catalogName,
schemaName));

catalogName is interpolated as an unquoted DuckDB identifier.

If the PostgreSQL database is named xy_vip-handball_db
the generated SQL becomes

ATTACH '...'
AS xy_vip-handball_db
(TYPE postgres, SCHEMA public, read_only);

which DuckDB parses as subtraction.

Suggested fix

Either:
use the explicit Ladybug alias (dbName) as the DuckDB catalog name, or
quote and escape the generated DuckDB identifier.

Or both.

[adsharma]

Could you test if the pg_client extension in 0.19.1 has the same problem? That's the extension we want to invest in. The duckdb based postgres extension will be deprecated in a couple of cycles.

[steinarts]

Thanks!
I checked the current Explorer image (latest), and pg_client is not available in the official extension list.
I also tried pulling:
ghcr.io/ladybugdb/explorer:0.19.1
failed to resolve reference "ghcr.io/ladybugdb/explorer:0.19.1": ghcr.io/ladybugdb/explorer:0.19.1: not found

Could you point me to the correct Docker image/tag (or build) that contains the pg_client extension? I'd be happy to test the issue there.

[adsharma]

@steinarts just pushed

    "image.name": "ghcr.io/ladybugdb/explorer:latest,ghcr.io/ladybugdb/explorer:0.19.1"

https://github.com/LadybugDB/explorer/actions/runs/31202384881/job/92946058061

[steinarts]

I managed to run the Explorer image from the build by pulling its digest.
POSTGRES is listed in SHOW_OFFICIAL_EXTENSIONS(), but INSTALL postgres currently fails before I can test ATTACH:

IO exception: Failed to download extension: postgres at URL
https://extension.ladybugdb.com/v0.19.0/linux_amd64/postgres/libpostgres.lbug_extension
(ERROR: SSL server verification failed)

One thing I noticed is that the Explorer build is 0.19.1, while the extension downloader is requesting the v0.19.0 extension.

Is POSTGRES here the new pg_client implementation you wanted me to test, and is the v0.19.0 extension URL expected for the 0.19.1 Explorer build?

[adsharma]

pg_client is the new client implementation to test.
The error is related to the docker image not including openssl3 and/or certificates. Needs a respin.

[adsharma]

Please retry after this build completes:

https://github.com/LadybugDB/explorer/actions/runs/31265493124

[steinarts]

INSTALL pg_client; works now, so the SSL issue appears to be fixed.
However, LOAD pg_client; fails with:

IO exception: Failed to load library:
/home/node/.lbdb/extension/0.19.0/linux_amd64/pg_client/libpg_client.lbug_extension
which is needed by extension: pg_client.
Error: libpq.so.5: cannot open shared object file: No such file or directory.

Looks like the Explorer image is also missing the PostgreSQL client library (libpq.so.5).

[adsharma]

https://github.com/LadybugDB/explorer/actions/runs/31266601483/job/93125642604

Should be ready in a few to test.

[steinarts]

pg_client now installs and loads successfully, and ATTACH to vip-handball_db succeeds, so the hyphen issue appears fixed.

However:
CALL show_tables() RETURN *;

returns no rows, and
LOAD FROM vip_pg.league
RETURN *
LIMIT 5;

fails with:
Catalog exception: league does not exist in catalog.

I also tried SQL_QUERY, but that function does not exist in this build.
What is the intended syntax for scanning a PostgreSQL table through pg_client after ATTACH?

[adsharma]

extensions/pg_client/test/test_files/pg_client.test

Line 4 in 61d6963

-SKIP_IN_MEM

has some examples

[adsharma]

There is a bit of a convention for table registration:

node_*: node tables
fkrel_*: foreign key based REL tables
rel_*: CSR based REL tables

you can probably use other table names, but will have to do the registration yourself. The tables which follow the convention will be autoregistered.

[steinarts]

Thanks — pg_client is now installing/loading successfully, and attaching directly to the PostgreSQL database with the hyphenated database name works as well.

I've moved on to testing fkrel_* auto-registration. I created a minimal PostgreSQL schema with:

CREATE TABLE node_reltest_user (
id BIGINT PRIMARY KEY
);

CREATE TABLE node_reltest_ticket (
id BIGINT PRIMARY KEY
);

CREATE TABLE fkrel_reltest_user_holds_ticket (
id BIGINT PRIMARY KEY,
from_id BIGINT NOT NULL REFERENCES node_reltest_user(id),
to_id BIGINT NOT NULL REFERENCES node_reltest_ticket(id)
);

SHOW_TABLES() looks correct:

node_reltest_ticket NODE shadow(graph)
node_reltest_user NODE shadow(graph)
fkrel_reltest_user_holds_ticket REL reltest

node_reltest_ticket ATTACHED reltest(PG_CLIENT)
node_reltest_user ATTACHED reltest(PG_CLIENT)
fkrel_reltest_user_holds_ticket ATTACHED reltest(PG_CLIENT)

Scanning the relationship table directly also works:

LOAD FROM reltest.fkrel_reltest_user_holds_ticket
RETURN *
ORDER BY id;

Result:
id | from_id | to_id
1 | 1 | 100
2 | 2 | 101

However, traversing the auto-registered relationship:

MATCH (u:reltest.node_reltest_user)
-[r:fkrel_reltest_user_holds_ticket]->
(t:reltest.node_reltest_ticket)
RETURN u.id, t.id
ORDER BY u.id;

fails with:
Runtime exception: Table with ID 1 not found in database reltest.

Is there any additional registration/schema requirement for traversing an fkrel_* relationship with pg_client, or does this look like a bug in the relationship table resolution?

[adsharma]

It looks like a bug in the table resolution. Can you share:

CALL show_tables() return *;

[steinarts]

Here is the output of CALL SHOW_TABLES() RETURN *; immediately after a successful ATTACH:

0 | node_reltest_ticket | NODE | shadow(graph)
1 | node_reltest_user | NODE | shadow(graph)
2 | fkrel_reltest_user_holds_ticket | REL | reltest

0 | node_reltest_ticket | ATTACHED | reltest(PG_CLIENT)
1 | node_reltest_user | ATTACHED | reltest(PG_CLIENT)
2 | fkrel_reltest_user_holds_ticket | ATTACHED | reltest(PG_CLIENT)

The relationship traversal still fails with:
Runtime exception: Table with ID 1 not found in database reltest.

So table ID 1 does appear to exist in the catalog as node_reltest_user, both as NODE and ATTACHED.

[adsharma]

The way fkrel tables are supposed to work is to convert a cypher query into SQL joins and execute using the foreign db's planner/executor. The error message seems to indicate that the cypher -> SQL converter couldn't do it, so it fell back to executing using the normal REL table path which doesn't know how to deal with foreign keys.

Perhaps tweak the query to match the simpler ones in pg_client.test that are passing? The correct answer of course is to implement a hybrid planner where parts of the query are executed by SQL and other parts by ladybug's physical operators. We haven't implemented it yet. So for now, you have to break it up until cypher -> sql rewriter works.