`BinderException` for multiple read only database attachments across multiple connections

[ghukill]

What happens?

For multiple :memory: connections, a BinderException is raised when attempting to attach to an external database (e.g. a local DuckDB file on disk) in read-only mode.

This behavior worked in 1.3.2, with this error cropping up in >= 1.4.0. My hunch is that it was introduced with the changes in this PR, which states,

This PR centralizes the list of attached databases in the DatabaseFilePathManager class. This list can be shared across top-level connections. Any connection created through the DBInstanceCache gets a reference to the shared list of attached databases. This, in turn, prevents the same database from being attached in different top-level connections.

If the attachments were not read-only, this seems like correct and desirable behavior. But my assumption was that attachments from multiple, distinct in-memory connections, when the attachment is attempted in read-only mode, would be allowed to the same external database file.

The PR linked above also includes the commit "Allow multiple read-only attaches to the same database across database instances" which I thought was maybe targeting this behavior? My secondary hunch / assumption is that perhaps because the :memory: connections are by default read/write, it's that parent connection and not the attachment that is getting considered in that commit? Said another way: despite the attachments being read-only, the connection itself is read/write and so that commit doesn't apply.

To Reproduce

It's pretty quickly reproducible by creating a small DuckDB database file, writing data, closing it, then opening a couple of in-memory connections and attempt to attach in read-only mode:

import duckdb

# create a small database file on disk, insert data, and close
tmp_db = "/tmp/test-readonly-attach.duckdb"
conn = duckdb.connect(tmp_db)
conn.execute("CREATE TABLE test_table (id INTEGER, name VARCHAR)")
conn.execute("INSERT INTO test_table VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')")
conn.close()

# open two in-memory connections
conn1 = duckdb.connect(":memory:")
conn2 = duckdb.connect(":memory:")

# attach in READ_ONLY mode to the database on disk; success
conn1.execute(f"ATTACH '{tmp_db}' AS foo (READ_ONLY);")
print("conn1: Successfully attached database as foo (READ_ONLY)")
result = conn1.execute("SELECT * FROM foo.test_table").fetchall()
print(f"conn1: Query result: {result}")

# in secondary connection, attach in READ_ONLY mode to the database on disk; fail
conn2.execute(f"ATTACH '{tmp_db}' AS foo (READ_ONLY);")
# BinderException: Binder Error: Unique file handle conflict: Cannot attach "foo" - the database file "/tmp/test-readonly-attach.duckdb" is already attached by database "foo"

I can confirm that giving the attachment a different alias also does not work.

OS:

arm64

DuckDB Package Version:

1.4.0; 1.4.1

Python Version:

3.12

Full Name:

Graham Hukill

Affiliation:

MIT Libraries

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[ghukill]

This example may more succintly show the connection vs attach dimension I was trying to outline above.

Assuming the existence of /tmp/test-readonly-attach.duckdb,

import duckdb

conn1 = duckdb.connect("/tmp/test-readonly-attach.duckdb", read_only=True)
# OK
conn2 = duckdb.connect("/tmp/test-readonly-attach.duckdb", read_only=True)
# OK

conn3 = duckdb.connect(":memory:")
conn3.execute("""attach '/tmp/test-readonly-attach.duckdb' as foo (READ_ONLY);""")
# FAIL
# BinderException: Binder Error: Unique file handle conflict: Cannot attach "foo" - the database file "/tmp/test-readonly-attach.duckdb" is already attached by database "test-readonly-attach"

[paultiq]

The linked PR has a follow up PR: 19319 which seems like it corrects this...?

[ghukill]

The linked PR has a follow up PR: 19319 which seems like it corrects this...?

@paultiq - yeah, PR 19319 was the PR associated with the commit I mentioned in the first message. My skim of the PR and git commit had me hoping it might, but it doesn't appear to. That prompted my secondary question if it was just addressing connections and not attachments with mode (READ_ONLY).

[paultiq]

I couldn't reproduce your error against a source build (1.5.0.dev59), but I could reproduce against the most recent pre build on pypi (1.5.0dev56), so I'm guessing it was indeed fixed in the past week or two, since dev56 is about a week old - https://pypi.org/project/duckdb/#history.

[ghukill]

Appreciate you looking into this @paultiq! I was able to confirm that with a source build (1.5.0.dev86) I'm no longer seeing the error thrown, and it sounds like it made it in even earlier than that.

Apologies for the miss there, I incorrectly read this line from the PR and thought it was merged into 1.4 (andium):

Mytherin merged commit 0ffbf15 into duckdb:v1.4-andium last week

Would you like me to close this issue? And thanks again! The use case is somewhat unique for how we're juggling connections, but it's all read-only attachments and I wasn't looking forward to working around this.

[paultiq]

Would you like me to close this issue?

I'm just another user... reproducing issues helps me learn new things about the code base.

[evertlammerts]

Thanks for looking into this both! I confirmed that this is fixed in the latest 1.4.2 dev version (pip install --pre 'duckdb<1.5.0') as well:

>>> import duckdb
>>> duckdb.version()
'1.4.2.dev27 (with duckdb 1.4.2-dev105)'
>>> conn1 = duckdb.connect('test.parquet')
>>> conn2 = duckdb.connect('test.parquet')
>>> conn3 = duckdb.connect(':memory:')
>>> c = conn3.execute("attach 'test.parquet' as test (READ_ONLY);")
>>> print(c.sql("SHOW DATABASES;"))
┌───────────────┐
│ database_name │
│    varchar    │
├───────────────┤
│ memory        │
│ test          │
└───────────────┘

Side note: nightly 1.5.0 dev builds are likely to be significantly less stable than nightly 1.4.2 dev builds. Both shouldn't be considered stable of course, but unless you're looking to test out new features you probably want to stay away from the 1.5.0 nightlies.