DuckDBPyRelation.query(): is there a way to scope or release the registered virtual table?

[FBruzzesi]

Hi there 👋🏼 I am a Narwhals maintainer; Narwhals is a compatibility layer that lets library authors write dataframe-agnostic code, DuckDB included.

We wrap a DuckDBPyRelation handed to us by the user, and I'd like to check we're not missing something
before accepting a limitation.

What I am trying to achieve

Run SQL against a user-supplied relation without leaving anything behind in their catalog.

A few operations we expose have no relational-API equivalent yet (unpivot, ASOF joins, and union all by name), hence we implement them with rel.query(virtual_table_name, sql). We moved to rel.query from duckdb.sql deliberately, since the latter runs on the process-global default connection, which isn't usable from multiple threads and I have been working on thread-safety recently.

What I'd like is for the virtual table that rel.query registers to be released once the relation derived from it is gone, or to be scoped in a way that lets us release it ourselves.

Why I would like to achieve it

We are a library: the connection belongs to the user and typically outlives any individual call, so anything we register on it is effectively permanent from their point of view. Three things compound:

• The registered view can't be dropped once the returned relation exists. Binding happens at execution time, so dropping it turns any later fetchall() into a CatalogException.
• Reusing a name silently rebinds an already-constructed relation to the new one, and this holds even after the first relation has been executed once. So a fixed name isn't an option for us - it would return wrong data rather than raise - and we generate a unique random name per call.
• Unique names plus no cleanup means unbounded growth: in our measurements 5000 calls leave 5000 views, ~3KB each, and they live in the connection's own catalog, so in a threaded application every per-thread cursor accumulates its own set.

Individually none of this is fatal, and the memory cost is small, yet we aim to avoid adding hidden costs for users as much as possible

Questions

1. Is the view registered by rel.query() intended to live for the connection's lifetime, or is there a supported way to scope or release it that we've missed?
2. Is the late binding by name intentional? From the Python side it's surprising that an existing relation's results can change because an unrelated call reused a name. If it's intended, a note in the Relational API docs would help; if not, I am happy to file it as a bug with a repro.
3. Is there a recommended pattern for a library that must run SQL against a user-supplied relation and does not own the connection? Our constraints are: no use of the process-global connection (not thread-safe), no access to the relation's own connection, and no way to clean up after ourselves.
4. The operations that force us down this path are ASOF joins and unpivot, tracked in duckdb#16980 and duckdb#16996. If those land in the relational API our need for rel.query() disappears. That may be the real answer to all of the above.

[hamedrabah]

Your observations line up with the current API: there is no public relation-scoped lifetime or release operation for the virtual name introduced by DuckDBPyRelation.query().

The name is resolved through the owning connection's context, and the derived relation remains lazy. Consequently, unregistering/dropping that name before the derived relation is bound or executed breaks it, while reusing the name can change what an already-created lazy relation binds to. Because a relation does not expose its connection, a library receiving only the relation cannot call the connection's public unregister(name) method afterward either.

The only bounded pattern available today is to change the library contract so the caller supplies the connection as well, then:

1. register a unique name;
2. execute and materialize the result inside a try;
3. call connection.unregister(name) in finally.

That does not preserve a lazy returned relation. If laziness is required and the library owns neither the connection nor the registration lifetime, there is no supported cleanup pattern at present; staying inside relational operators is the only catalog-free route.

I would file the late-rebinding reproduction separately as a bug or documentation issue, and treat relation-scoped replacement scans / missing relational ASOF, UNPIVOT, and UNION ALL BY NAME operations as feature work. In other words, this is a real API gap, not a cleanup method you overlooked.