Is there a way to get the connection a DuckDBPyRelation belongs to?

[FBruzzesi]

Hi there 👋🏼 I am a Narwhals maintainer; Narwhals is a compatibility layer that lets library authors write dataframe-agnostic code, DuckDB included. I have been working on thread-safety and hit the same wall twice, so I'd like to ask whether we're missing an API.

What I am trying to achieve

Given only a DuckDBPyRelation, handed in by the user, I'd like to be able to:

1. obtain the connection it belongs to, so we can open a per-thread .cursor() on it; and
2. failing that, at least tell whether two relations belong to the same connection.

As far as we can tell neither is possible today: nothing on a DuckDBPyRelation refers to its connection.

Why I would like to achieve it

1. So we can follow DuckDB's own threading advice on the user's behalf: the multiple threads guide says each thread must call .cursor() on the connection. When all we hold is a relation there's nothing to call .cursor() on. Concretely: reporting the schema of a TIMESTAMPTZ column requires the connection's TimeZone setting, which we read with rel.query(...). That executes on the relation's connection, so two threads inspecting schemas of relations from one connection collide with InvalidInputException: Attempting to execute an unsuccessful or closed pending query result. We currently serialize that single query behind a module-level lock. A per-relation cursor would be the correct fix, and would also stop us serializing work on connections that have nothing to do with each other.
2. So we can produce a custom error message. join, union and cross all raise Cannot combine LEFT and RIGHT relations of different connections!. Our API takes two user frames and joins them internally, so that error surfaces from deep inside our call stack with no hint that the cause is which connection each frame came from. If we could compare the two relations' connections, or just a stable identity for them, we could raise a clear library-level error up front, pointing at the actual fix.

We don't have a clear workaround for this just yet.

Questions

1. Is there a public (or blessed-private) way to obtain the connection from a relation, or a stable identifier comparable between two relations? If not, would exposing something like DuckDBPyRelation.connection be considered?
2. Failing a full connection handle, would a narrower predicate be acceptable, e.g. rel_a.same_connection_as(rel_b), purely so libraries can raise a better error?
3. Is there an idiomatic pattern we're missing for a library that wants to be thread-safe while only ever receiving relations?

Happy to open issues or (try to) contribute a PR if any of these are wanted.

---

Related: #40