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.