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.

[nazeeh111]

This changed in DuckDB 2.0.0. The 2.0 release notes explicitly list rel.query(name, sql) leaving no temporary view behind, and add the three relational operations you mentioned.

I reproduced the rebinding and catalog growth with the official 1.5.6 wheel, then ran the same checks with 2.0.0 on CPython 3.14.7. On 2.0.0 this works without obtaining the relation's connection or doing cleanup:

import duckdb

con = duckdb.connect()
a = con.sql("SELECT 1 AS x").query("input_data", "SELECT x + 10 AS x FROM input_data")
assert a.fetchall() == [(11,)]
b = con.sql("SELECT 2 AS x").query("input_data", "SELECT x + 10 AS x FROM input_data")
assert a.fetchall() == [(11,)]  # 1.5.6 instead returns [(12,)] here
assert b.fetchall() == [(12,)]
assert con.sql("SELECT count(*) FROM duckdb_views() WHERE NOT internal").fetchall() == [(0,)]

The 2.0 implementation builds the SELECT over a common table expression. Its temporary catalog registration is scoped to the call, rather than the returned relation's lifetime. I also checked 5,000 retained query relations, cleanup after a binding error, and four workers using separate cursors: none left views behind on 2.0.0. A table-backed SELECT remained lazy when rows were inserted before fetching. This is not a guarantee that every SQL statement stays lazy: writes and some catalog-dependent queries execute at the call.

For avoiding SQL altogether, 2.0 also provides:

• rel.unpivot("a", "b", name="variable", value="value")
• left.join(right, "l.ts >= r.ts", how="asof"), with aliases l and r
• left.union_by_name(right, all=True)

I ran small examples of all three. For Narwhals, this needs a version-specific path: it does not solve the 1.x lifetime problem, and 2.0 requires Python 3.11+ and has other breaking changes covered in those release notes.

[evertlammerts]

Thanks for the detailed report, and thanks @nazeeh111 for checking it on 2.0.0. I ran the same checks on 2.0.0 and on main. query() now binds the name as a common table expression and leaves nothing in the catalog: reusing a name no longer changes an existing relation, dropping a view of that name no longer breaks it, and 200 unique names left no views behind. unpivot, union_by_name and how='asof' joins are on the relation in 2.0.0 too. One caveat: a statement that writes runs at the call instead of staying lazy. I will mark this answered.